Restoring MySQL Backups

Restoring MySQL Backups

Profile picture for user LukasVileikis
Lukas Vileikis
25 February 2021

Restoring MySQL backups should be a part of any MySQL backup strategy. Restoring your backups helps you ensure that your backups were restored successfully - in this post we will try to cover your options when it comes to restoring your data in MySQL.

Restoring Backups in MySQL

Before actually restoring backups, you need to decide what you want to restore since the backup process differs depending on what you’re restoring: are you restoring a physical backup or a logical backup? After you’ve decided, it’s time to get going.

Restoring Logical Backups in MySQL

Logical backups, simply put, are statements that help you re-create the data. To restore a logical backup, one option is to use mysqldump (here database_name is the name of your database and backup.sql is the name of your backup file):

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

Another option is to use the LOAD DATA [LOCAL] INFILE statement (here tmp_table is your table name, the /tmp/file.csv is the name of your file and the FIELDS TERMINATED BY ‘\t’ statement tells the database that every field is terminated by a tab (that is very useful for restoring data from, for example, excel files)):

LOAD DATA LOCAL INFILE ‘/tmp/file.csv’ INTO TABLE tmp_table FIELDS TERMINATED BY ‘\t’;

Restoring Physical Backups in MySQL

Physical backups are backups that copy the actual data files. Restoring physical backups generally is more complex than restoring logical backups because there are certain nuances depending on what engine you are restoring. For example, to restore InnoDB, you need to:

  1. Shut down MySQL / MariaDB to avoid issues after you restore.
  2. Put all of InnoDB’s files into their original locations (InnoDB files consist of the ibdata1 file, the ib_logfile* files containing the redo logs, the .ibd files and the .frm files), also make sure the contents of your my.cnf are as they should be. 
  3. Start MySQL / MariaDB.

When dealing with InnoDB, avoid copying the .ibd and .frm files alone - only do that if you can guarantee that the tablespace ID of the .ibd file is an exact match to the tablespace ID in the metadata of the ibdata1 file.

Thankfully, restoring MyISAM is not as complex: simply copying the .frm, .MYD and .MYI files into their original locations will do.

Restoring MySQL Backups with Backup Ninja

As far as MySQL backup restoration is concerned, Backup Ninja can provide a set of commands to be executed on the database host depending on the database version, backup method, encryption and compression options.

To restore your mysqldump MySQL backup, use the following command:

cat [backup-file] | mysql -h127.0.0.1 -uroot -p

To restore a mariabackup, first the backup must be streamed using mbstream to the respective MariaDB data directory:

cat [uncompressed and decrypted backup file] | mbstream -x -C /path/to/the/MariaDB/data/directory

Then, assign correct ownership to the MariaDB data directory:

chown -Rf mysql:mysql /path/to/the/MariaDB/data/directory

Finally, restart the MariaDB service:

systemctl restart mariadb

Backups can also be restored automatically by simply navigating to the Backups section of Backup Ninja, clicking “More” next to the backup you want to restore and clicking “Restore”:

Restoring Backups in Backup Ninja

For more information on how to restore your backups with Backup Ninja, take a look at the documentation.

Summary

Restoring MySQL backups helps you ensure that your backups do work and can be restored in time of need. Before restoring your backups, decide on what backups you are restoring (logical backups or physical backups), then follow the steps outlined above.

Tags