asked on

Cron job for MySQL backup

Hi there,

At the moment I am using the following script to back up my databases:

mysqldump \
-uroot \
--password='password' \
-hlocalhost \
--all-databases \
--opt \
--allow-keywords \
--flush-logs \
--hex-blob \
--master-data \
--max_allowed_packet=16M \
--quote-names \
--result-file=BACKUP_MYSQL_4.0.SQL

How can I run this as a daily cron job with a file name corresponding to the date or just incrementing in number?

Thanks,

Mark

SOLUTION

Cyclops3590

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

pjedmond

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marky9074

ASKER

I hadnt thought of access during the backup. To be honest I had been doing it manually whilst it was up anyway, as I dont get a lot of traffic. But I guess I should think about doing it properly. I like the simplicity of the first method. How do I go about taking the database offline in the script?

Mark

Cyclops3590

just stop it before the script. assume it'd be like this
/etc/init.d/mysqld stop
then start it up again after you're done with the backup
/etc/init.d/mysqld start

of course you may want to build a couple of protection in there to
/etc/init.d/mysqld stop || mail -s mysql_did_not_stop <your email address>
this way if the daemon doesn't stop properly, it'll email you that fact, also
/etc/init.d/mysqld start || mail -s mysql_did_not_start <your email address>

This way if an error occurs you'll be notified and won't have to worry about it (I'd still check it once in a while though just to double check everything is working correctly in the script)

marky9074

ASKER

But if I stop myql how can I run the mysqldump command?

pjedmond

mysqldump

does not require the database to be running...so you'd run it as normal! :)

marky9074

ASKER

Why does it do this then?

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) whe n trying to connect

pjedmond

Sorry - my mistake! I hadn't looked at my backup script for a long time! The script I was looking at stops anyone doing anything by locking the table:

mysqldump -a -l -u mambo -p mambo > backupfile.mysql

(the -l option which prevents any 'write' operations during the backup' The website gives a custom error under these circumstances.

HTH:)

Cyclops3590

oops my bad...obviously been a while since I've had to backup a mysql database. pjedmond is right you need to lock the databases. However I'd give pjedmond's script a shot and customize it if you have to. Piece of mind is priceless when running backups and being notified when something goes wrong is worth the extra effort.

pjedmond

Another idea if locking the database causes problems is to stop the database and just copy the database table files whilst it cannot be accessed:)

/var/lib/mysql/name_of_database

Really easy:) and probably the fastest way to do the backup:)

marky9074

ASKER

Have used this instead to lock all tables...

--lock-all-tables, -x

Seemed smaller (and simpler) to use dump, when backup up all my databases so will try the simple script and see how it goes...

Thanks,

Mark