Backup MySQL Databases In Linux Regularly
The post Using Cron to Automate Linux Tasks by Jack got me thinking that some users might be interested in a practical example. The following tutorial will explain how to setup a small script to backup MySQL databases on a Linux server which will be run daily using a cron job. The best way to start would be to read up on the introduction by Jack about Cron so that you got a understanding of the basics.
We start by creating the bash script first and continue with the cron job after the bash script has been tested to work as intended. All commands in the following paragraphs are executed from the command line.
vi backup.sh
This will create an empty text file in the vi editor. This will be the script that will be executed by the cron job on a daily basis.
bin/sh
/usr/bin/mysqldump -A -u [Username] -p[Password] | gzip > /backups/mysqldump`date +%m_%d_%y`.gz
Press i to enter insert mode and copy and paste the two lines above in the editor. What the script does is to use the mysqldump function to create a mysql backup and store it gzipped in the backups directory using mysqldumpDate.gz as the name with data being the day the backup was created.
The two variables [Username] and [Password] have to be edited and the username and password of a user with sufficient rights entered. It might also be necessary to change the location of the backup folder.
Press ESC to enter command mode and :x to save the file.
./backup.sh
Execute the script to see if it is working correctly. It should create the dump of the MySQL database in the defined directory. It might be necessary to change the permission level of the backup.sh file if you get a permission error during execution.
chmod 755 backup.sh
If the script is working as intended it can be added as a cron job. The following command will open the user crontab list.
crontab -e
If you are in the vi editor you press i to enter insert mode again and add the following line
0 7 * * * /path/to/backup.sh
Please refer to the tutorial for a detailed instruction on how to set the cron job up to suite your needs. This one will execute the script every day on 7am.
It is a good idea to check back after the next execution to verify that everything is working as intended.
Advertisement
1) You’ve just unleashed VI on a lot of unsuspecting innocents!
2) I do not remember ever seeing a smiley icon ever used to save and exit VI before (hint: reread the post _after_ posting it).
Dotan you really do not know about the secret smiley commands in Vi? :)
vi is the devil. it works perfectly but you have to sell your soul to understand it.
Actually VI is easy (so is Emacs, maybe just slightly harder).
If you want a challenge try TECO ( http://en.wikipedia.org/wiki/Text_Editor_and_Corrector ). >;-)
I just run the cPanel backup job on my server that backs up all the accounts on an external drive, not just the mySQL DB
I wish I always felt comfortable enough to do backups attended, I realize the chance of problems is low but still but it has happened. In my case, backing up a forum, I put too much faith in mysqldump locking tables before running.
i love nano :D
nice script
I just wanna know how to take back up MySQL databases
I prefer to backup my databases individually, so I can restore them in order of imporatnce if necessary:
for DB in `mysql -u root -p$PW -Bse ‘show databases’`
do
mysqldump -u root -p$PW $DB > $MYTMP/$DB.sql
done
Where $PW is a password set earlier in the script.
adam
Interesting way to do a backup.
i know its an old post (almost a year old) but i really needed to say this :
PICO shall live forever..
VI was created by the same devil as the one who created Kraft Singles wrapping.. :)