8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Mysql » Here

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.

Related articles.

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 --force > all_backup.sql

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

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

$ # Multiple Tables
$ mysqldump --user=root --password=mypassword mydatabase mytable1 mytable2 mytable3 > mydatabase_mytables_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 logs, 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.

You must make sure the "[client]" section of your "/etc/my.cnf" file, or local config file, does not contain any "ssl" references. The mysqlbinlog utility doesn't support SSL and will produce the following type of error if the client configuration includes it.

mysqlbinlog: unknown variable 'ssl-ca=/home/mysql/certs/ca-cert.pem'

If you get this, simply comment out the references while you run the utility and put them back afterwards. You do not need a server restart.

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

# Pull out changes for a specific time period
mysqlbinlog --start-datetime="2014-01-1 21:00:02" --stop-datetime="2014-01-02 09:00:00" \
            --database=hr mysqld-bin.000001

# Pull out changes between specific log_pos events
mysqlbinlog --start-position=123456 --stop-position=234567 \
            --database=hr mysqld-bin.000001

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.