Restoring a MySQL database from a backup file (usually .sql) using MySQL Workbench is a straightforward process.
Here’s a detailed step-by-step guide to performing this operation:
Prerequisites
- You need to have MySQL Workbench installed.
- You need to have the backup (dump) file saved on your computer (in .sql format).
- You need the user credentials to access the database server.
Step-by-Step: Restoring the Backup
1. Connect to the Server
Open MySQL Workbench and click on the database instance (connection) where you want to restore the data.
2. Access the Import Tool
In the top menu, click Server and select Data Import.
Alternatively: In the left sidebar (Navigator), click the “Administration” tab and select “Data Import/Restore”.
3. Select the File Source
On the screen that opens, you will see two main options under “Import Options”:
Import from Dump Project Folder: Use this option if your backup is a folder containing multiple .sql files (one for each table).
Import from Self-Contained File: Use this option if your backup is a single .sql file containing the entire database (this is the most common option).
Click the button with ellipses (…) and locate your .sql file.
4. Define the Destination (Target Schema)
Below the file selection, locate the Default Target Schema section:
If your backup file already contains the CREATE DATABASE command, you can leave this option blank or select the corresponding database.
If the file does not contain the database creation (or if you want to restore to a database with a different name), click the New… button to create an empty schema and select it from the dropdown list.
5. Select the content to import.
Check the options:
Dump Structure and Data: Restores the structure (tables/columns) and the data (rows). (Recommended)
Dump Data Only: Restores only the data (useful if the tables already exist and are empty).
Dump Structure Only: Creates only the empty tables, without the data.
6. Start the Import
- Click on the Import Progress tab (located just below the “Data Import” title).
- Click the Start Import button in the bottom right corner.
The progress bar will show the progress.
At the end, a message will confirm whether the import was successful.
⚠️ Important Precautions
Warning: If you import data into a database that already has tables with the same name, the process may fail or overwrite your current data, depending on how the backup was generated (if it contains DROP TABLE IF EXISTS commands). Always verify that you are importing to the correct location.
Solving Common Problems
| Erro | Probable Cause | Solution |
| Error 2006 (MySQL server has gone away) | The file is too large for the current package limit. | Increase the value of max_allowed_packet in the server settings (/etc/my.cnf). |
| Access Denied | The user does not have permission to create tables or data. | Verify that the database user has ALL PRIVILEGES or GRANT privileges in the destination database. |
| Workbench “Not Responding” | Large .sql files (e.g., 5GB+) can cause the graphical interface to crash. | For very large files, it is recommended to use the command line. |