How to Restore MySQL Database from SQL Dump File

Restoring a MySQL database from a standard SQL dump file is a fundamental task for database administrators and developers alike. This guide provides a straightforward, step-by-step walkthrough on how to efficiently import .sql backup files into a MySQL server using the command-line interface, handle compressed files, and optimize the process for large datasets.

Step 1: Create the Target Database

Before restoring the SQL dump, you must ensure that the destination database exists on your MySQL server. If it does not, log into your MySQL server and create it using the following SQL command:

CREATE DATABASE database_name;

Step 2: Restore the Database via Command Line

The most reliable and efficient way to restore a MySQL database is by using the standard mysql command-line utility. Open your terminal or command prompt and execute the following command:

mysql -u username -p database_name < path/to/backup.sql

-u username: Specifies your MySQL username (e.g., root).
-p: Prompts you to enter your password securely.
database_name: The name of the target database you created in Step 1.
<: The redirection operator that sends the contents of the SQL file into the MySQL utility.
path/to/backup.sql: The absolute or relative path to your SQL dump file.

Step 3: Restoring Compressed Dump Files

If your SQL dump file is compressed (such as a .sql.gz file), you can restore it directly without extracting it first. This saves disk space and time:

gunzip < path/to/backup.sql.gz | mysql -u username -p database_name

For Windows users utilizing PowerShell, the equivalent command is:

Write-Output (Get-Content path\to\backup.sql) | mysql -u username -p database_name

Step 4: Optimizing Restoration for Large SQL Dumps

Importing exceptionally large database dumps can sometimes result in timeout errors or slow performance. To optimize the restoration process, consider the following adjustments:

Increase Packet Size: If your dump contains large rows, increase the maximum allowed packet size by adding the --max_allowed_packet flag to your command:
```
mysql -u username -p --max_allowed_packet=100M database_name < path/to/backup.sql
```
Disable Foreign Key and Unique Checks: You can speed up the import process significantly by disabling foreign key constraints and autocommit during the import. If you have access to modify the dump file, ensure these lines are at the very beginning of your SQL file:
```
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
```
And add these lines at the very end of the file to re-enable them:
```
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
```