Restoring a MySQL backup should be a part of any MySQL backup strategy. After all, a MySQL backup is taken because people take precautions in the event of a primary data failure – if data is backed up, it can be recovered.

Restoring MySQL Logical Backups

In the event of a primary data failure, data obviously needs to be recovered. Since there are logical and physical backup types, their restoration methods are also different. We will start by looking at how we can restore logical backups in MySQL. We will discuss the following options:

  • Restoring data via phpMyAdmin;
  • Restoring data via the command line;
  • Restoring data using MySQL Enterprise Backup.

Restoring Data via phpMyAdmin

Restoring data via phpMyAdmin is perhaps one of the most basic ways of performing data restoration in MySQL. To restore your data dump using the tool:

  • Open up phpMyAdmin;
  • Click on the plus sign on the left pane near the database you want to import the data into;
  • Click on the Import tab in the top row;
  • Choose the backup you want to import into the database and click on the Go button.

Your data should now be restored.

Restoring Data via the Command Line

To restore a MySQL backup via the command line, run the following command:

$ mysql -u root -p database_name < backup.sql

Here root is your user name and database_name is the database name you’re restoring the dump into. After running this command, you will be prompted for the password of your MySQL user. In regards to the backup itself, you should also specify the exact path to the dump you are restoring. Do note that this command, depending on the content of the dump file, could drop the contents of your database before running the queries to rebuild it too.

Restoring Data using MySQL Enterprise Backup

To restore a MySQL backup using MySQL Enterprise Backup:

  • Shut down the database server.
  • Delete all of the files inside of the server’s data directory.
  • Delete all of the files inside directories specified by --innodb_data_home_dir, --innodb_log_group_home_dir and --innodb_undo_directory if those options are different from the data directory.
  • Use the --copy-back-and-apply-log command which restores a single-file backup specified by the --backup-image option. For example:
    $ mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql --backup-image=/home/backups/backup.mbi --backup-dir=/home/restore-tmp copy-back-and-apply-log
    In this case, --defaults-file contains the location of my.cnf, --datadir contains the data directory, --backup-image provides the path to a single-file backup, and --backup-dir provides a location to an empty folder to store some temporary files (see our earlier post about MySQL Enterprise Backup to learn more about the tool).
  • As soon as you see the message mysqlbackup completed OK! you can rest assured that the database directory from the backup is successfully restored.

Summary

MySQL backups can be restored using a couple of methods. Perhaps the most popular method is to restore data via phpMyAdmin or the command line, but you can also restore your data using MySQL Enterprise Backup – more information about it can be found in our earlier post about MySQL Enterprise Backup.

Contributor
Comments to: Restoring a MySQL Backup

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.