Ask Your Question
1

howto backup mysql using cron

asked 2012-02-15 12:11:38 -0600

callkalpa gravatar image

Hi,

I have a mysql database with user root that I want to get backups using mysql dump reguslarly. My system is Fedora 16. How can I accomplish this using cron?

edit retag flag offensive close merge delete

2 Answers

Sort by ยป oldest newest most voted
1

answered 2012-02-15 17:11:43 -0600

ztank1013 gravatar image

Edit the crontab file running the following command:

sudo crontab -e

and add the following line (or similar) to it:

17 17 * * * /usr/bin/mysqldump -h host.with.db -uroot -prootpassword DataBaseName > /path/to/directory/dbdump.sql

save and exit from the crontab file. This will run once per day at 5:17 p.m.

If the DB lives in the same host you are running the cron job, the "-h host.with.db" option is not needed. Be careful and do not add "spaces" after -u and -p options or it will not work. Replace DataBaseName with you real DB name. Finally redirect (>) sql output file to your favorite location/folder.

Note: Although what I just showed should work I strongly discourage the use of root user to dump a DB. I also would not recommend to run a cron job (or any job from the command line) which has the clear text password in it. It is a security risk and should be avoided! If someone else have access to your box with a simple ps command might see the full syntax of your cron command and grab the root password for your box!

edit flag offensive delete link more
0

answered 2012-02-15 19:04:30 -0600

KevinA gravatar image

I agree with ztank1013 about adding the password to the cron job. One way around it would be to just take the command and put it in a bash script with restrictive permissions. So under /root/ create a file called mysqlBackup.sh and put the below in to the file.

#!/bin/bash
/usr/bin/mysqldump -h host.with.db -uroot -prootpassword DataBaseName > /path/to/directory/dbdump.sql

Again fill the command in with the appropriate values as stated above. Next as root do:

chmod 700 /root/mysqlBackup.sh
chown root:root /root/mysqlBackup.sh

Finally make your crontab entry as instructed above but use the script instead so it reads as follows:
17 17 * * * /root/mysqlBackup.sh

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2012-02-15 12:11:38 -0600

Seen: 1,517 times

Last updated: Feb 15 '12