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_undo_directoryif those options are different from the data directory.
- Use the
--copy-back-and-apply-logcommand which restores a single-file backup specified by the
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-filecontains the location of
--datadircontains the data directory,
--backup-imageprovides the path to a single-file backup, and
--backup-dirprovides 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.
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.