Backing Up & Restoring MySQL database

The easy way to make a backup of a database is to use MySQL Backup. MySQL Backup is a Perl script that uses mysqldump, tar, and gzip. The documentation is in the script, and it’s simple to use. Starting at around line 104, comment out the three lines referencing CGI commands. These are for running backups from a Web browser, which is not a secure way to run the backups. The easiest thing to do is set everything up in the script, then run it automatically from a cron job. Anywhere a program or file is named, be sure to use the full absolute path name.

You’ll have the option to backup all tables, or to select certain ones. The backups are stored locally by default, and can be uploaded via FTP to another location. There is even an option to email the backups to whatever lucky soul is elected to receive them.

This cron job runs the script every midnight:

# crontab -e
0 0 * * * /usr/sbin/scripts/mysql_backup

Restoring a database from backup is done by redirecting the contents of the backup file to the original location:

# mysql -u nikesh -p [password] My_DB

linux-poison