Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : Hosting Security and Technology Tutorials : How to Backup SQL DB's via Cron!

[linux-tech]

Not sure if this one would be better off here or in the programming forums, moreso here I'd say as it's a Server related thing (all server dbs). Here's the idea and what this will do though.

This will take all of your sql databases, perform a "dump" on them, put them into the appropriate .sql format, and even create a "drop" command if the table exists. Perfect for moving to another host, repairing a db, or just routine backups

First, the script:

DATE=`date +%m%d%y`
THISMONTH=`date +%b-%y`
BACKMONTH=/backups/sql/$THISMONTH/
BACKDEST=$BACKMONTH/$DATE
if [ ! -d $BACKMONTH ];then
mkdir $BACKMONTH
fi
if [ ! -d $BACKDEST ];then
mkdir $BACKDEST
fi

for var in `find /var/lib/mysql/ -type d | \
sed -e "s/\/var\/lib\/mysql\///"`; do
mysqldump  --add-drop-table  $var >> $BACKDEST/$var.sql
done

Simple, and easy to use. Make SURE you change the BACKMONTH directory to something that exists, otherwise nothing will work properly!

Save that as any file, just remember the file name you called it.
Change the permissions on that script (chmod a+x script.sh)

Now, the cron entry:

0 0 * * * /path/to/script.sh

This will launch the backup @ midnight as necessary.

[sehe]

what about removing sed and replacing

for var in `find /var/lib/mysql/ -type d | \                      
sed -e "s/\/var\/lib\/mysql\///"`; do

with

for var in `find /var/lib/mysql/ -type d -printf "%P\n"`; do

?

[linux-tech]

I just posted what works for me here, and has been working for years.

[Cool Surfer]

thanks, nice tip will try it out.

[studiori.com]

Originally Posted by linux-tech

...

0 0 * * * /path/to/script.sh

This will launch the backup @ midnight as necessary.

Mysql package come with ready to use script mysqlhotcopy which lock/copy/unlock selected databases very fast and may be scheduled via cron.

[linux-tech]

Originally Posted by studiori.com

Mysql package come with ready to use script mysqlhotcopy which lock/copy/unlock selected databases very fast and may be scheduled via cron.

While this is good (somewhat) for backing up sql to another host, or copying DBs to another host, it is no good for making actual backups, because this doesn't "dump" the db, it copies it, exactly as it is, in binary format, and can present a deal of problems with the mysql versions don't match on the hosts.

[studiori.com]

Originally Posted by linux-tech

While this is good (somewhat) for backing up sql to another host, or copying DBs to another host, it is no good for making actual backups, because this doesn't "dump" the db, it copies it, exactly as it is, in binary format, and can present a deal of problems with the mysql versions don't match on the hosts.

It's true , but we want cron backups. Dump is more cpu sensitive than a copying especially if we have huge databases.

[linux-tech]

Dump is more cpu sensitive than a copying

Again, not so.
The same data has to be parsed either way, in fact with the extra stuff you're throwing into mysqlhotcopy (lock dbs, etc), you're creating MORE work for the system to do, thusly, creating more CPU intensity.

The downside of mysqlhotcopy, again is that it uses the BINARY data, meaning you're not going to save a lot on compression, whereas with the dump, which is a fully human readable text file (though don't edit it if you don't know what you're doing), it's possible to compress it down to next to nothing, saving even more on space.

While mysqlhotcopy may be a somewhat minor alternative, it's no real solution for backing things up. It was indended to copy db a to db b, and rename it. Your best way is always going to be going with the fully readable, compressable dump. Both will take close to the same time, neither will load your cpu incredibly (I do dumps of 150+m dbs nightly and notice no spike, whatsoever), the only difference is ease of use. Mysqldump adapts itself to the linux environment, mysqlhotcopy does not. Mysqldump reads and writes to a single file, hotcopy, again, the original binary data.

[redmalloc]

Originally Posted by linux-tech

This will take all of your sql databases, perform a "dump" on them, put them into the appropriate .sql format, and even create a "drop" command if the table exists. Perfect for moving to another host, repairing a db, or just routine backups

Thanks for the script. It was exactly what I was looking for and didn't even know.

One comment, one question:

Comment: I notice that the script generates a double forward slash in the path in these lines:

BACKMONTH=/backups/sql/$THISMONTH/
BACKDEST=$BACKMONTH/$DATE

Makes:

BACKMONTH=/backups/sql/$THISMONTH//$DATE

Linux seems to handle this fine and I don't know if it is a typo i.e. does the double slash have a special meaning?

I changed mine to a single forward slash like so:

BACKMONTH=/backups/sql/$THISMONTH/
BACKDEST=$BACKMONTH$DATE

Question: Is there anything wrong with tar/gzip'ing up these files?

[linux-tech]

Question: Is there anything wrong with tar/gzip'ing up these files?

Not really, no. It's pretty simple to do if you want to, I just didn't add that in. I've got another that I'm working on and will post it here in the next few days when I can test.

[akucharski]

thanks for this nice script

[stooley]

where do I input my password for mysql?

[linux-tech]

Originally Posted by stooley

where do I input my password for mysql?

As root, you should have something like .my.cnf setup . It should look something like this

[client]
user="root"
pass=""

This should sit in /root/.my.cnf and will authenticate you without the need for providing a password in the script

[stooley]

Originally Posted by linux-tech

As root, you should have something like .my.cnf setup . It should look something like this

[client]
user="root"
pass=""

This should sit in /root/.my.cnf and will authenticate you without the need for providing a password in the script

that tis be the problem!

Thanks linux-tech!

[stooley]

one last question,

what command would I add to .qz the databases?