How to backup a database in MySQL from command line

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:

crontab -e

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/

Enjoy!

Advertisements

One thought on “How to backup a database in MySQL from command line

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s