You are here
How to Backup MySQL Database automatically (for Linux users)
If you are a serious webmaster and have your own server, you should take special care of your data. If you are a BSP (Blog Service Provider) or have a large forum, you need backup the database every day. But it is a boring work indeed. Why not to let the computer do the dirty work? Cron can help you.
"cron" is a time-based scheduling utility in Unix/Linux operating system. You can find it in most of Unix/Linux distributions and usually it stats up with the operating system automatically. It reads a configure file (/etc/crontab) when it starts up and keeps running while the computer is running.
It will execute some programs at some given moment. For example, I can use cron to reboot my server at 3:00 am in each day. So, of course, I can use it to backup MySQL database automatically.
The most important thing is to edit its configure file. Usually it is /etc/crontab. For example, if I want to backup MySQL in 2:15 am per day, I need to add the following line:
15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`data '+%m-%d-%Y'`.sql.gz
The first part is "15 2 * * *". The five fields mean "minute, hour, day of month, month, day of week" respectively. The character '*' stands for "any". Since I want to backup in each day, I only set the minute and the hour.
The next, "root", means "run following command as root account".
So the last part is the command. This command is so long that some people feel it is a little difficult to understand it. Let me explain it in detail.
"mysqldump -u root -pPASSWORD --all-databases" means backing up all the MySQL databases (to learn more, read How to backup MySQL with mysqldump).
But what's the backup file? I put a vertical bar and "gzip". It means passing the backup to gzip to compress it. And then I use a redirect symbol to specify the compressed backup file: /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz.
There is a trick. I put `date '+%m-%d-%Y'` in the file name. What does it mean? "date" is a command that shows the current time. date '+%m-%d-%Y" shows current date using 'MM-DD-YYYY' format. I use the back quote (or backslash) ` to quote the whole command. What I mean is to use its result to replace the command string.
So this command generates a compressed backup file every day and its name is "database_MM-DD-YYYY". Here "MM-DD-YYYY" indicates the backup date.
Finally, you should use '/etc/init.d/cron restart' command to restart the cron. Then your computer or your server will backup MySQL databse at 2:15am every day.
About
Backup HowTo editor and PHP developer from China.
Homepage
http://www.programmingnote.com/
Full name
Wang Jinbo
Comments
You forgot to mention the
You forgot to mention the need to escape % char in cron tab with /... :)
Your automatic backup script
Your automatic backup script for Linux will not work, use this instead in the crontab:
15 2 * * * /usr/bin/mysqldump –opt –all-databases -u root -pPASSWORD > /foo/bar/db-`date +%Y-%m-%d`.sql
If you edit crontab via
If you edit crontab via crontab -e command then you do not have to restart the daemon. It will reference the environment variable EDITOR to chose the editor you prefer to use.
Question
What if don't want to use the dates?
Say, i have the backup file name dbbackup.sql.gz. Will that replace the existing file every time the cron runs?
Don't using the dates
I think, it will do. At least for me at Debian. Whenever I run mysqldump command it replace the older files.
Jan Polzer, Backup HowTo owner
/bin/sh: -c: line 0:
/bin/sh: -c: line 0: unexpected EOF while looking for matching ``'
/bin/sh: -c: line 1: syntax error: unexpected end of file
usr/bin/mysqldump –opt –all-databases -u root -pPASSWORD > /foo/bar/db-`date +
Please let me know what was wrong
i have got same problem
i have got same problem
Correction
You have to change database_`data ' to
database_`date '15 2 * * *
15 2 * * * /usr/bin/mysqldump --opt --all-databases -u root -pPASSWORD | gzip > /backup/db/db-`date +\%Y-\%m-\%d`.sql.gz
would be correct, make sure your path to saving the file is actually a valid path, if not just create it.
correction
Error itself is telling that you are missing the clossing matching ``'
Why don't you people dust
cd backupdir
mysqldump -u usernmae -ppassword db name or --all-db>name`date +%a`.sql
it will overwrite your database on weekday i.e it will overwrite your monday backup on monday so on. So everytime you will have full week backup. Which you can download on any day for storage.
More help
For those of you getting the error:
/bin/sh: -c: line 0: unexpected EOF while looking for matching ``'
/bin/sh: -c: line 1: syntax error: unexpected end of file
The anonymous user is correct about adding the \ before %
So the full command would look something like:
mysqldump -u root -pPASSWORD database_name | gzip > /home/backup/database_`date +\%m-\%d-\%Y`.sql.gz
But then I get the error:
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
Probably because I created the backup folder in ftp. So either create the backup folder in shell or change the user and password part to:
-u ftpuser -pFTPPASSWORD
I also got the error:
Permission denied
mysqldump: Got errno 32 on write
because I had run the mysqldump command in shell to test it and it created a sql.gz file by the user root. So I just deleted that sql.gz file and then cron could create a new one and overwrite it with the ftp username and ftp password.