I ought to day that the best way to back a MySQL database server up would involve shutting the server down, copying all the data directory to a safe location and starting it back again. This would allow us to restore the entire data structure upon a data corruption and restore all the stored procedures, UDFs and other user-defined addons that we usually forget to backup.
However a cold backup is not always possible. High availability applications may never be stopped, therefore we have to hot-backup the database schemas by generating a SQL dump so we can restore it in case of a data loss.
I usually use this very simple Bash script named “db_dump.sh” to back up my schemas:
#!/bin/bash DATE=`date "+%d_%m_%y"` # makes the db dump locking tables mysqldump -h $1 -u$3 -p$4 --single-transaction --opt $2 > /www/databases/$2_$DATE.sql # compressing the SQL file gzip /www/databases/$2_$DATE.sql # delete th SQL file rm -f /www/databases/$2_$DATE.sql # sending the file to other node of the Web cluster, NAS, or server backup scp /www/databases/$2_$DATE.sql.gz root@node_2_cluster:/www/databases/
Then I add one crontab line per database schema I want to backup. Just edit the crontab with:
Remember that the former command uses your default editor as set in your .bashrc config file. You can change it by modifying the environmnet variable EDITOR, for instance, to set “vi” as the default editor:
EDITOR=vi; export EDITOR
Now add the new cron job:
0 2 * * * sh /path/to/script/db_dump.sh localhost my_database_schema mysql_user mysql_password >> /var/log/backups.log
Note that the last line in the bash script uses SCP to copy the file from one server to another. By default, this would prompt you for the username and password to authenticate the SSH session so you have to configure SSH autologin withouth password between those servers in order this script to work.
I will explain how to that in next posts. UPDATED: Explained now here https://injustfiveminutes.wordpress.com/2012/10/22/ssh-authentication-without-password-using-rsa-key/