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

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: