Results 1 to 14 of 14
-
09-21-2006, 06:34 AM #1Web Hosting Master
- Join Date
- Feb 2005
- Location
- Northern VA
- Posts
- 1,582
Help Backing up multiple mySQL databases
I have a VPS account with about 6 accounts that have mySQL dB's that I want to back up. There are about 30 accounts total so I I'd like to selectively back up just those dB's.
I have some backup space on another server. I've already figured out how to RSync the home directories of the accounts to keep them backed up and it's easy to do in one fell swoop.
Is there a way to back up the dB's easily by some sort of cron run? I know I can dump and then tar and gz the files but those are each separate steps and I'm not the strongest Unix guy. Add to that I would have to then do that for each dB I'm interested in. I know where these mySQL folders are located as I have root access but it doesn't seem efficient to Rsync those folders on another server (waste of space and bandwidth).
I'm sure somebody has figured all of this out but I'm honestly a Unix novice and don't have a good idea on how to create scripts that might do all the steps I'd need to do.
Bottom line: Ideas are welcomeRich
Husband, Father, Retired Marine, Geek
-
09-21-2006, 07:26 AM #2Junior Guru Wannabe
- Join Date
- Aug 2006
- Location
- Milton Keynes, UK
- Posts
- 52
I had the same problem that I only wanted to backup selected databases. I used the following commands in crontab to backup each database to the users webspace. This has the advantages that the user can then download their own dbs easily and also rsync will back it up. I would strongly suggest the you dump the file outside of the web root.
Code:0 1 * * * root mysqldump -uroot -pxxxxx database1 > /home/site1/dbdumps/database1.sql 0 2 * * * root mysqldump -uroot -pxxxxx database2 > /home/site2/dbdumps/database2.sql
You could put all the commands in one script file with a gzip command after each to save space.
-
09-21-2006, 07:35 AM #3Web Hosting Master
- Join Date
- Feb 2005
- Location
- Northern VA
- Posts
- 1,582
duplu,
Great information. Can I ask you to expand on the "How to" for my situation.
I'm not really a host, per se, but just provide service pro bono for some organizations that don't even use their CPanel. With the above I could definitely dump the mySQL dB's to one place.
Let me ask you a few questions:
1. How do I set up a script file? I know that is an awfully basic question but my expertise does not lie in server admin.
2. Let's assume I want to do this:
a. Dump each dB to a sql file
b. gzip it
c. rsync it to an external site
Q: Could I put that in a script file - 3 commands for each dB?
I've worked with a couple of cron scripts to set up cron jobs but don't really know how to create one myself.Last edited by DevilDog; 09-21-2006 at 07:40 AM.
Rich
Husband, Father, Retired Marine, Geek
-
09-21-2006, 07:49 AM #4Junior Guru Wannabe
- Join Date
- Aug 2006
- Location
- Milton Keynes, UK
- Posts
- 52
Create your script file:
nano -w /root/dbbackups.sh
Paste in the following text and amend as necessary for your database names and site locations and also the MySQL root password. You can also add your rsync commands at the bottom:
Code:#! /bin/sh # Database backup script # Dump Dbs mysqldump -uroot -pxxxxx database1 > /home/site1/dbdumps/database1.sql mysqldump -uroot -pxxxxx database2 > /home/site2/dbdumps/database2.sql # ZIP dbs gzip /home/site1/dbdumps/database1.sql gzip /home/site1/dbdumps/database2.sql #rsync command
chmod 755 /root/dbbackups.sh
Amend the crontab to run the script at 1am.
nano -w /etc/crontab
Add the following line at the bottom:
Code:1 0 * * * root /root/dbbackups.sh 1> /dev/null
/root/dbbackups.sh
-
09-21-2006, 07:52 AM #5Web Hosting Master
- Join Date
- Feb 2005
- Location
- Northern VA
- Posts
- 1,582
Awesome.
Thanks dupluRich
Husband, Father, Retired Marine, Geek
-
09-26-2006, 11:56 PM #6Web Hosting Evangelist
- Join Date
- Jul 2005
- Posts
- 529
Hi there guys, sorry to bring back this topic. I need some help on this matter. I have the databases backedup.. however, I'm trying to figure out HOW I can send them to another server? You guys talk about resyncing... how would I do this and get more info about it?
Thanks,
Freshfroot.
-
09-27-2006, 12:10 AM #7Web Hosting Guru
- Join Date
- May 2006
- Location
- /home/India/Kolkata
- Posts
- 314
try phpmybackup
-
09-27-2006, 12:27 AM #8Web Hosting Master
- Join Date
- Nov 2001
- Location
- Philadelphia, Pa
- Posts
- 948
You could probably setup a second script (or ad it to the first one) to tar it, then scp it.
-
09-27-2006, 12:35 AM #9Web Hosting Evangelist
- Join Date
- Jul 2005
- Posts
- 529
Originally Posted by derek.bodner
-
09-27-2006, 02:19 AM #10Newbie
- Join Date
- Jun 2006
- Location
- Tucson
- Posts
- 23
Here's one idea. If you are sending the dumps to another box, you should just install mysql and set up replication on the second machine, and only have it sync up once per day. That way if there's a catastrophe you can just bind the old database server's IP address to the recovery box and let it fly. Also you can do offline dumps on the second machine without it impacting production environment.
Just a different way about doing it. Here's info from mysql:
http://dev.mysql.com/doc/refman/5.1/en/replication.html
-
09-27-2006, 04:22 AM #11Web Hosting Master
- Join Date
- Feb 2005
- Location
- Northern VA
- Posts
- 1,582
Assume you want to sync a folder on host1 with a folder on host2.
log into host 1 and from the command line:
rsync -zaHlvp --safe-links --progress -e /usr/bin/ssh path/to/folderonhost1 username@host2.com:path/to/folderonhost2
This will rsync the contents of folderonhost1 (all subdirectories included) with the folderonhost2.
rsync is nice if you're syncing contents between folders. You can use it to copy individual files too but it's probably easier to just scp if you're doing a few files.
rsync is nice if you have more than oneRich
Husband, Father, Retired Marine, Geek
-
09-28-2006, 07:21 PM #12Web Hosting Evangelist
- Join Date
- Jul 2005
- Posts
- 529
I assume the code above is only used in SSH?
I have found a script that allows you to FTP, upload mysql then log out.
The only issue I'm having, is getting it to work right.
Code:#!/bin/sh # Backup description fdesc='FORUMNAME' # Backup filename fname='BACKUPNAME' # Destination directory (with trailing slash) fdir=/BACKUPDIR # Database Settings: fdb=DBNAME fuser=DBUSERNAME fpw=DBPASSWORD # FTP address ftph=ftp.otherwebhost.org echo "Backing up $fdesc database..." tempname=$fdir$fname-`eval date +%Y-%m-%d`.sql mysqldump --opt -quick -u$fuser -p$fpw $fdb > $tempname echo "Gzipping $fdesc database..." gzip $tempname a=`eval date +%d` a=`expr $a - 2` echo "Deleting old backups... (Error messages are normal)" tempname=$fdir$fname-`eval date +%Y-%m-`$a.sql.gz rm $tempname tempname=$fdir$fname-`eval date +%Y-%m-`0$a.sql.gz rm $tempname # FTP backup cd $fdir echo "Connecting to FTP, will attempt to upload..." ftp <<** open $ftph put $fname-`eval date +%Y-%m-%d`.sql.gz delete $fname-`eval date +%Y-%m-`$a.sql.gz delete $fname-`eval date +%Y-%m-`0$a.sql.gz bye ** echo "Upload Complete!"
The issue is, I have a back.sql file.. I already have it on my server, but I want to FTP it over to another server. I'm just figuring out how to use this script to do just that.
-
09-28-2006, 07:46 PM #13Junior Guru Wannabe
- Join Date
- Sep 2006
- Posts
- 53
take this way
what about tar & gzip the directory /var/lib/mysql
& then post it to http access place
& wget it in the other server & decompress it there in the same place
if u interest in this tell me to give u the instructions
-
09-28-2006, 08:57 PM #14Web Hosting Evangelist
- Join Date
- Jul 2005
- Posts
- 529
Originally Posted by gotzaway
Without SSH and downloading/uploading myself.. so the next best thing I can think of is cron