SimpleBackupsSimpleBackups

How to Restore a MySQL Dump

Last updated on

Originally posted on

A MySQL backup is a copy of your database data (also known as MySQL dump) that you can use to restore your database in case of data loss, corruption, or migration. Restoring a MySQL backup is the process of importing the backup data into your database, replacing the existing data. This can help you recover from a disaster, test your backup, or move your data to a new location.

Prerequisites

  • MySQL installed
  • MySQL user credentials
  • A MySQL backup file aka MySQL dump; if you don't already have one, SimpleBackups provides an automated MySQL backup
  • An existing database

How to verify your MySQL dump integrity

Before restoring a MySQL backup, it is a good practice to verify the backup file and make sure it is not corrupted or incomplete. You can use the mysqlcheck command to check the integrity and validity of the backup file. The syntax is:

mysqlcheck -u [user] -p --databases [database_name] < [filename].sql
  • mysqlcheck - this is a utility program that checks and repairs MySQL tables
  • -u [user] - this flag is used to specify which user we will use to access the database
  • -p - this flag signals that our user uses a password
  • --databases [database_name] - this flag tells mysqlcheck to check all the tables in the specified database
  • < [filename].sql - this means that we will input(import) the contents of [filename].sql file into mysqlcheck

If the backup file is valid, you will see a message like this:

[database_name].[table_name] OK

If the backup file is corrupted or invalid, you will see an error message like this:

[database_name].[table_name]
Error    : Table '\[database_name].\[table_name]' doesn't exist
status   : Operation failed

In this case, you should not restore the backup file and try to fix it or use another backup file.

How to restore a MySQL backup/dump

The following steps assume you already have a MySQL backup:

  1. Create a new MySQL database where you will restore your backup, or use an existing database.

  2. To restore the backup, use the command:

    mysql -u [user] -p [database_name] < [filename].sql
    • mysql - this is our main mysql program
    • -u [user] - this flag is used to specify which user we will use to access the database
    • -p - this flag signals that our user uses a password
    • [database_name] - this will be our database's name where we will restore our backup
    • < [filename].sql - this means that we will input(import) the contents of [filename].sql file into our [database_name] database

Notes:

  • After entering this command, you may be asked to enter the password for the MySQL user that you used.
  • Please be careful when using an existing database that has records as this command will overwrite your existing database and end up losing your records.

How to restore a MySQL backup/dump to a different database

If you want to restore a MySQL backup to a different database than the one it was created from, you need to modify the backup file and replace the original database name with the new one. You can use a text editor or a command-line tool like sed to do this. For example, if you want to restore a backup file named old_db.sql to a new database named new_db, you can use the following command:

scp backup.sql [user]@192.168.0.1:~

This command will replace all occurrences of old_db with new_db in the backup file. Then, you can use the same command as before to restore the backup:

mysql -u [user] -p [database_name] < backup.sql

How to restore a MySQL backup/dump to a different MySQL version

If you want to restore a MySQL backup to a different MySQL version than the one it was created from, you need to make sure that the backup file is compatible with the new version. You can use the mysqldump command to create a backup file that is compatible with any MySQL version. The syntax is:

mysqldump -u [user] -p --compatible=[version] [database_name] > [filename].sql
  • mysqldump - this is a utility program that dumps MySQL database data
  • -u [user] - this flag is used to specify which user we will use to access the database
  • -p - this flag signals that our user uses a password
  • --compatible=[version] - this flag tells mysqldump to produce output that is compatible with the specified MySQL version. You can use values like ansi, mysql323, mysql40, mysql41, mysql50, mysql51, mysql56, mysql57, mysql80, or maxdb
  • [database_name] - this will be our database’s name that we will backup
  • > [filename].sql - this means that we will output(export) the contents of [database_name] database into [filename].sql file

For example, if you want to create a backup file named backup.sql that is compatible with MySQL 8.0, you can use the following command:

mysqldump -u [user] -p --compatible=mysql80 [database_name] > backup.sql

Then, you can use the same command as before to restore the backup:

mysql -u [user] -p [database_name] < backup.sql

Bonus: How to backup and restore a MySQL backup using SimpleRestore & SimpleBackups

If you want to restore a MySQL backup without using the command line, you can use SimpleRestore, a web-based tool that simplifies the backup and restore process. SimpleRestore allows you to upload your backup file, select your database, and restore your backup with a few clicks. You can also schedule backups, monitor backups, and manage backups using SimpleBackups.


Table of contents