Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

MySQL Backup and Recovery

This article provides a quick guide to performing backup and recovery of MySQL databases. The article only describes those methods available without needing additional licensing.

Logical Backups

Logical Backup (mysqldump)

Amongst other things, the mysqldump command allows you to do logical backups of your database by producing the SQL statements necessary to rebuild all the schema objects. An example is shown below.

$ # All DBs
$ mysqldump --user=root --password=mypassword --all-databases > all_backup.sql

$ # Individual DB (or comma separated list for multiple DBs)
$ mysqldump --user=root --password=mypassword mydatabase > mydatabase_backup.sql

$ # Individual Table
$ mysqldump --user=root --password=mypassword mydatabase mytable > mydatabase_mytable_backup.sql

The full syntax of the command can be found here.

Recovery from Logical Backup (mysql)

The logical backup created using the mysqldump command can be applied to the database using the MySQL command line tool, as shown below.

$ # All DBs
$ mysql --user=root --password=mypassword < all_backup.sql

$ # Individual DB
$ mysql --user=root --password=mypassword --database=mydatabase < mydatabase_backup.sql

The full syntax of the MySQL command line tool can be found here.

Cold Backups

Cold backups are a type of physical backup as you copy the database files while the database is offline.

Cold Backup

The basic process of a cold backup involves stopping MySQL, copying the files, the restarting MySQL. You can use whichever method you want to copy the files (cp, scp, tar, zip etc.).

# service mysqld stop
# cd /var/lib/mysql
# tar -cvzf /tmp/mysql-backup.tar.gz ./*
# service mysqld start

Recovery from Cold Backup

To recover the database from a cold backup, stop MySQL, restore the backup files and start MySQL again.

# service mysqld stop
# cd /var/lib/mysql
# tar -xvzf /tmp/mysql-backup.tar.gz
# service mysqld start

Binary Logs : Point In Time Recovery (PITR)

Binary logs record all changes to the databases, which are important if you need to do a Point In Time Recovery (PITR). Without the binary logs, you can only recover the database to the point in time of a specific backup. The binary logs allow you to wind forward from that point by applying all the changes that were written to the binary logs. Unless you have a read-only system, it is likely you will need to enable the binary logs.

To enable the binary blogs, edit the "/etc/my.cnf" file, uncommenting the "log_bin" entry.

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin

The binary logs will be written to the "datadir" location specified in the "/etc/my.cnf" file, with a default prefix of "mysqld". If you want alter the prefix and path you can do this by specifying an explicit base name.

# Prefix set to "mydb". Stored in the default location.
log_bin=mydb

# Files stored in "/u01/log_bin" with the prefix "mydb".
log_bin=/u01/log_bin/mydb

Restart the MySQL service for the change to take effect.

# service mysqld restart

The mysqlbinlog utility converts the contents of the binary logs to text, which can be replayed against the database. The following exmaples show how it can be used.

# Display contents.
mysqlbinlog mysqld-bin.000001 | more

# Apply contents directly to the database.
mysqlbinlog mysqld-bin.000001 | mysql -u root -p

# Push contents to a file, then apply the file.
# Allows you to edit the file before applying
mysqlbinlog mysqld-bin.000001 > tmpfile
mysql -u root -p < tmpfile

# Pull out changes for a specific database
mysqlbinlog --database=hr mysqld-bin.000001 | more

When applying changes from multiple binary log files, do them using a single connection or push them in an ordered fashion to a temp file to be applied later.

# Display contents.
mysqlbinlog mysqld-bin.000001 mysqld-bin.000002 | more

# Apply contents directly to the database.
mysqlbinlog mysqld-bin.000001 mysqld-bin.000002 | mysql -u root -p

# Push contents to a file, then apply the file.
# Allows you to edit the file before applying
mysqlbinlog mysqld-bin.000001 > tmpfile
mysqlbinlog mysqld-bin.000002 >> tmpfile
mysql -u root -p < tmpfile

# Pull out changes for a specific database
mysqlbinlog --database=hr mysqld-bin.000001 mysqld-bin.000002 | more

For more information see:

Hope this helps. Regards Tim...

Back to the Top.