By Delia / Last Updated December 20, 2021

How to do MySQL Restore?

Sometimes, it’s emergency for you to restore MySQL database if encounter the system failure, power outage, file system corruption, hardware problems, etc. Don’t be in panic, we will show you different ways to do MySQL restore quickly and easily to less downtime.

MySQL restore from MySQL Workbench

If you used mysqldump command to backup/dump MySQL databases, then you will get the MySQL dump file. Hence, you could try the following steps to get mysqldump restore database from the dump file.

1. Launch MySQL Workbench, connect your MySQL Server.

2. Choose Data Import/Restore in Management tab at the Navigator. Or click Server > Data Import.

Data Import MySQL Workbench

3. Choose the dump file by clicking Import from Dump Project Folder option or Import from Self-Contained File in Import from Disk tab, Choose default schema to be imported to, and select database objects to import.

Choose MySQL Dump File Workbench

4. Click Start Export in Import Progress tab to do MySQL restore from .sql file.

Start Import

And your MySQL database you choosed before has been restored successfully.

MySQL Import Finished Workbench

Please Note:

If your MySQL Server version and MySQL client programs (including mysqldump) version is different, some features may not be backed up properly. It’s recommended to upgrade or downgrade local MySQL client programs to the version is equal to or newer than that of the MySQL Server.

Mysqldump Version Mismatch

You could upgrade MySQL Server 5.6 to 8.0 in MySQL Installer, Choose MySQL Server, and click Add... and expand MySQL Servers > Expand MySQL Server > MySQL Server 8.0, and choose the version you want to install, and then follow the guide to upgrade MySQL Server 5.6 to 8.0.

Restore a MySQL Database in Windows Command Prompt

1. Please shut down your Database server.

2. Delete all files in the Server’s data directory (including the files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory  for restore, if the directories are different from hte data directory).

3. You could use mysql command to restore MySQL database in Windows command prompt:

mysql -u username -p dbname < filename.sql

For example restore a MySQL database named mysql from the mysql.sql file, please input the command:

mysql -u root -p mysql < mysql.sql

MySQL Restore Database

Besides, you could perfrom MySQL restore database in MySQL Shell, for example:

mysql -u root -p mysql < Dump20201221.sql

MySQL Restore Database MySQL Shell

Above steps are applied to restore MySQL dump to new database.

MySQL Restore database with phpMyAdmin

1. Open phpMyAdmin, then click Import tab.

phpMyAdmin Import

2. Click Choose File button to select the MySQL dump file.

phpMyAdmin Choose MySQL Dump File

3. Stay with the default settings in Partial Import, choose SQL format, and set up other settings. Press Go button.

phpMyAdmin Import MySQL Dump File Go

MySQL restore all databases with AOMEI Backupper

AOMEI Backupper Technician Plus is another powerful MySQL backup software for Windows, which provides you to create schedule backup, real-time sync for MySQL database and restore MySQL with simple clicks.

Please download AOMEI Backupper Technician Plus 30-day free trial to restore all MySQL databases from backup files if you have created one with it.

Download Free Trial Windows PCs & Servers
Secure Download

Step 1. Launch AOMEI Backupper Technician Plus, click Restore tab, then choose Select Task or Select Image File.  

Select MySQL Dump File

Step 2. Choose the MySQL dump file and click Next.

MySQL Database Backup Image

Step 3. Tick all the data to restore MySQL all databases. And press Next.

Select All MySQL Databases

Step 4. Select the Restore to original location option and press Start Restore >> button to perform MySQL restore all databases.

Restore All MySQL Databases

Besides, you could tick some table files to let MySQL restore tables from dump file. Or only select the files you want to restore.

The Epilogue

Learn how to backup MySQL database and restore MySQL databases in time to avoid that your business data get lost.