Complete MySQL Restore Guide: Recovery, Errors & Optimization

1. Introduction

What is MySQL Restore?

MySQL restore refers to the process of restoring backed-up data to the original database.
By performing a restore, you can recover data in the event of data loss or system failure, allowing business operations and system management to continue.

Databases can become corrupted or lost for various reasons. For example, the following cases can be considered.

• Server crashes or hardware failures
• Accidentally deleting data
• Data corruption due to updates or system changes
• Data loss due to malware or external attacks

To prepare for such situations, it is important to take appropriate backups.
And by performing a restore at the necessary time, you can quickly recover the system.

What You Can Learn in This Article

This article provides a detailed explanation of MySQL restore.
To accommodate beginners to advanced users, we introduce everything from basic restore methods to advanced restore techniques.
Specifically, you can learn the following content.

• Basic MySQL Restore Procedures
• Restore Methods Using Command Line (mysqldump)
• Restore Using GUI Tools (phpMyAdmin, MySQL Workbench)
• Methods to Restore Specific Data Only
• Optimization for Restoring Large Amounts of Data
• Advanced Recovery Using Binary Logs
• Methods to Verify Data After Restore
• Troubleshooting When Errors Occur

By referring to this guide, you can design an appropriate backup strategy and quickly perform restores in case of emergencies.
From the next chapter, we will explain in detail the preparations before performing a restore.

2. Preparation Before Restore

Types of MySQL Backups

To perform a restore, it is important to obtain an appropriate backup in advance. MySQL backup methods include the following types.

1. Backup Using mysqldump

mysqldump is a tool that exports MySQL databases in SQL format. It is the most common method and easy to restore.

mysqldump -u username -p database_name > backup.sql

This method saves data as a text file, making it easy to edit, but it is not suitable for large amounts of data.

2. Backup Using phpMyAdmin

This is a method to easily obtain a backup using the phpMyAdmin GUI. It can be exported as an SQL file.

1. Log in to phpMyAdmin
2. Select the “Export” tab
3. Set the format to “SQL” and click “Go”

This method is easy to handle for beginners, but it is not suitable for large-scale data.

3. Backup Using MySQL Workbench

MySQL Workbench allows you to create backups using the GUI. You can export specific databases or tables using the Data Export feature.

4. Backup Using Binary Logs

Using binary logs allows you to record changes up to a specific point in time, enabling data recovery.

mysqlbinlog --start-datetime="2024-02-01 10:00:00" --stop-datetime="2024-02-01 12:00:00" binlog.000001 > restore.sql

This method enables advanced recovery, but requires proper log management.

Pre-Restore Checks

To successfully perform a restore, you need to check the following points in advance.

1. Character Encoding Check (UTF-8 vs SJIS)

If the character encoding differs between the time of backup and restore, the data may become garbled. Check the encoding of the backup file.

file backup.sql

Additionally, specifying --default-character-set=utf8mb4 during restore can avoid character encoding issues.

mysql -u username -p --default-character-set=utf8mb4 database_name < backup.sql

2. Creating the Database to Restore

Before executing the restore, check if the target database exists, and create it if it does not.

mysql -u username -p -e "CREATE DATABASE IF NOT EXISTS database_name;"

3. Backup File Integrity Check

To check if the backup file is not corrupted, display part of its contents.

head -n 20 backup.sql

Also, if the file size is abnormally small, it may indicate that the backup was not taken properly.

Choosing the Restore Procedure [Comparison Table]

The restore method varies depending on the usage environment and data size. Refer to the following table to choose the appropriate restore method.

3. MySQL Database Restore Procedure

Restoring a Single Database

mysqldump Backup Restore Method

The most common restore method is to restore backup data obtained using mysqldump. Steps:

1. Verify that the backup file is correct

   head -n 20 backup.sql

→ Check the beginning of the backup file to ensure there are no errors.

2. Create the target database (if it doesn’t exist)

   mysql -u username -p -e "CREATE DATABASE IF NOT EXISTS database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

3. Restore the data

   mysql -u username -p database_name < backup.sql

Specifying Options to Prevent Garbled Characters

If the data encoding differs, garbled characters may occur during restore.
To prevent this, it is common to specify --default-character-set=utf8mb4.

mysql -u username -p --default-character-set=utf8mb4 database_name < backup.sql

Notes:

• Verify that the character encoding matches between backup and restore
• Set the default character encoding to UTF-8 when creating the database

  CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Restoring Multiple Databases

If the backup file contains multiple databases, you can restore using the --databases option.

mysql -u username -p < backup.sql

If you want to restore only a specific database, execute as follows.

mysql -u username -p --one-database target_database_name < backup.sql

Example:

mysql -u root -p --one-database sales_db < all_databases_backup.sql

→ Restores only sales_db.

Restoring All Databases

To restore all databases at once, use --all-databases.

mysql -u username -p --all-databases < backup.sql

Points:

• Using --all-databases restores all databases in the backup file.
• It is important to check in advance if there are DROP DATABASE or CREATE DATABASE statements.
• For large amounts of data, optimize memory settings (details explained in “5. Optimizing Restore for Large Data Volumes”).

Restore Using GUI Tools

Restore Using phpMyAdmin

1. Log in to phpMyAdmin
2. Select the “Import” tab
3. Select and upload the backup file (SQL)
4. Click “Execute” to start the restore

✅Advantages:

• Beginner-friendly and easy to operate
• Can restore without using commands

⚠️Disadvantages:

• There are file size limitations
• Not suitable for large-scale data

Restore Using MySQL Workbench

1. Open MySQL Workbench
2. Select the “Server > Data Import” menu
3. Select the backup file
4. Specify the target database
5. Press the “Start Import” button to execute the restore

✅Advantages:

• Can operate intuitively with GUI
• Possible to restore only specific tables

⚠️Disadvantages:

• May cause high server load
• Be careful with compatibility with MySQL Server version

4. How to Verify Data After MySQL Restore

Basic Commands to Confirm Restore Success

1. Check the Database List

After restore, confirm if the databases have been created correctly.

SHOW DATABASES;

✅Checkpoints

• Whether all databases included in the backup file are displayed
• Whether the target database name for restore is correct

2. Check the Table List for Each Database

Even if the database is created, if the tables are not restored correctly, it has no meaning.
Use the following command to check the table list in the database.

USE database_name;
SHOW TABLES;

✅Checkpoints

• Whether all necessary tables are displayed
• Whether some tables are missing due to mysqldump options

3. Check the Number of Data Rows in Tables

Even after restore is complete, you can confirm if the data is properly restored using COUNT(*).

SELECT COUNT(*) FROM table_name;

✅Checkpoints

• Whether the result of COUNT(*) matches the number of data rows before backup
• Whether data is not missing
• Whether there are not abnormally many NULL or 0 data

4. Confirm if Specific Data is Correctly Restored

To confirm if the data is correctly restored, extract and check some actual data.

SELECT * FROM table_name LIMIT 10;

✅Checkpoints

• Whether there are no anomalies in the order or values of the data
• Whether garbled characters have not occurred

Checking for Garbled Characters or Data Corruption

If the character encoding is not appropriate during restore, data may become garbled.
To prevent this issue, check the character encoding after restore.

1. Check the Database Encoding

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='database_name';

2. Check the Table Encoding

SHOW CREATE TABLE table_name;

💡 Measures Against Garbled Characters

• Specify --default-character-set=utf8mb4 when exporting with mysqldump
• Also specify --default-character-set=utf8mb4 during restore
• Correct the SET NAMES setting in the backup file

Verifying Index and Foreign Key Integrity

1. Confirm if Indexes are Correctly Set

SHOW INDEX FROM table_name;

✅Checkpoints

• Whether indexes are correctly restored
• Whether searches on specific columns are not abnormally slow

2. Checking Foreign Key Constraints

When restoring tables with foreign key constraints, it is necessary to confirm if the constraints are properly applied.

SELECT TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'database_name';

✅Checkpoints

• Whether all foreign key constraints are restored
• Whether settings like ON DELETE CASCADE or ON UPDATE CASCADE are appropriate

Verify Restore Issues by Checking Log Files

If an error occurs during restore, you can identify the problem by checking the MySQL error log.

1. Check the MySQL Error Log

sudo cat /var/log/mysql/error.log

✅Checkpoints for Error Log

• ERROR 1366 (HY000): Incorrect string value → Possible garbled characters
• ERROR 1452 (23000): Cannot add or update a child row → Foreign key constraint error
• ERROR 2006 (HY000): MySQL server has gone away → Possible backup size too large

Performance Optimization After Restore

After restore, it is important to confirm not only data integrity but also that performance is not affected.

1. Check Query Execution Speed

If data searches become slow after restore, indexes may not have been properly restored.

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

2. Optimize Tables

To prevent data fragmentation and improve performance, optimize the tables.

OPTIMIZE TABLE table_name;

3. Clear Cache

If a large amount of data is restored, you can temporarily clear the cache to improve performance.

RESET QUERY CACHE;

Summary

To confirm if the data after restore is normally restored, the following steps are important.

✅Basic Database and Table Verification✅Check Number of Data Rows and Garbled Characters✅Verification of Indexes and Foreign Keys✅Analyze Error Logs to Identify Issues✅Implement Performance OptimizationRestoring a database involves not only applying the backup but also verifying data integrity and functionality.

5. Optimizing Restore for Large Volumes of Data

max_allowed_packet Setting Adjustment

1. What is max_allowed_packet?

MySQL limits the maximum packet size that can be sent at once using max_allowed_packet.
If this value is small, errors may occur when restoring large SQL queries.

2. Configuration Method

To check the current value of max_allowed_packet, execute the following command.

SHOW VARIABLES LIKE 'max_allowed_packet';

The default value is 16MB (16,777,216 bytes), but when restoring large volumes of data, it is recommended to change it to 256MB or more.

3. Temporarily Changing the Setting

To temporarily change it within a MySQL session, execute the following command.

SET GLOBAL max_allowed_packet=268435456;  -- 256MB

4. Permanently Changing the Setting

Edit the MySQL configuration file (my.cnf or my.ini) and add or modify the following line.

[mysqld]
max_allowed_packet=256M

After making the change, restart MySQL.

sudo systemctl restart mysql

✅Checkpoints

• If you encounter the error ERROR 2006 (HY000): MySQL server has gone away, increase the value of max_allowed_packet.
• If restoring large amounts of data fails midway, review this setting.

Optimizing innodb_buffer_pool_size

1. What is innodb_buffer_pool_size?

innodb_buffer_pool_size is a parameter that determines the amount of memory used by the InnoDB storage engine.
If this value is small, the restore process will frequently access the disk, resulting in slower speeds.

2. Checking the Current Setting

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

The default value is around 128MB, but when handling large volumes of data, it is recommended to allocate 50-70% of the server memory.

3. Configuration Method

To change the setting, edit my.cnf and add or modify the following line.

[mysqld]
innodb_buffer_pool_size=2G

After the change, restart MySQL.

sudo systemctl restart mysql

✅Checkpoints

• If the server has sufficient memory, increasing innodb_buffer_pool_size will improve restore speed
• In small-scale environments, adjust while monitoring memory usage

Partitioning and Improving Restore Speed

1. Benefits of Partitioning

As the database grows larger, a single table stores a large amount of data, increasing the load during restore.
By partitioning the table, you can speed up the restore.

2. Partition Setup Method

For example, to partition by date using created_at, set it up as follows.

CREATE TABLE orders (
    id INT NOT NULL,
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

During restore, you can target specific partitions only.

✅Checkpoints

• By dividing the restore into partitions rather than restoring large amounts of data in bulk, you can achieve faster speeds
• Considering partitions from the table design stage makes managing large volumes of data easier

Fast Restore Using --disable-keys

1. What is --disable-keys?

In MySQL, when inserting large amounts of data into a table with indexes, the indexes are updated each time data is inserted, which slows down the restore process. Using the --disable-keys option temporarily disables index updates, speeding up the restore.

2. How to Use --disable-keys

1. Edit the backup file and add the following line

ALTER TABLE table_name DISABLE KEYS;

2. Execute the restore process

mysql -u username -p database_name < backup.sql

3. After restore is complete, add the following line to enable indexes

ALTER TABLE table_name ENABLE KEYS;

✅Checkpoints

• When inserting large amounts of data, using DISABLE KEYS significantly improves restore speed
• Don’t forget to execute ENABLE KEYS after restore to apply the indexes

6. Troubleshooting During MySQL Restore

Typical Error Messages and Solutions

1. “Database Does Not Exist” Error

✅Error Message

ERROR 1049 (42000): Unknown database 'database_name'

✅Cause

• When restoring with the mysql command, the target database has not been created.

✅Solution

1. Create the Database Manually

   mysql -u username -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

2. Execute the Restore

   mysql -u username -p database_name < backup.sql

2. “Garbled Characters Occur” Error

✅Error Message

ERROR 1366 (HY000): Incorrect string value

✅Cause

• The character encoding differs between backup and restore.
• The database’s default character encoding is not appropriate.

✅Solution

1. Check the Encoding of the Backup File

   file backup.sql

2. Specify --default-character-set=utf8mb4 During Restore

   mysql -u username -p --default-character-set=utf8mb4 database_name < backup.sql

3. Unify the Database’s Character Encoding

   ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. “MySQL Stops During Restore” Error

✅Error Message

ERROR 2006 (HY000): MySQL server has gone away

✅Cause

• The backup file is too large.
• The max_allowed_packet setting is too small.
• MySQL crashes due to insufficient memory.

✅Solution

1. Increase max_allowed_packet

   SET GLOBAL max_allowed_packet=256M;

2. Adjust innodb_buffer_pool_size

   [mysqld]
   innodb_buffer_pool_size=2G

3. Compress the Backup and Restore

   mysqldump -u username -p database_name | gzip > backup.sql.gz
   gunzip < backup.sql.gz | mysql -u username -p database_name

4. Split the SQL File

   split -b 500M backup.sql backup_part_

Restore the split files in order:

   cat backup_part_* | mysql -u username -p database_name

Measures When the Backup File is Too Large

1. Split the SQL File and Restore

If the data to restore is too large, splitting the file into smaller parts and restoring it can increase the success rate.

split -b 500M backup.sql backup_part_

Restore the split files in order:

cat backup_part_* | mysql -u username -p database_name

2. Use the --single-transaction Option of mysqldump

Using this option restores tables individually, which can reduce the load when restoring large amounts of data.

mysqldump --single-transaction -u username -p database_name > backup.sql

3. Temporarily Disable innodb_flush_log_at_trx_commit

By reducing the frequency of transaction log writes during large-scale data restore, you can improve the restore speed.

SET GLOBAL innodb_flush_log_at_trx_commit=0;

Don’t forget to restore the original setting (default: 1) after the restore.

SET GLOBAL innodb_flush_log_at_trx_commit=1;

Verify Restore Issues by Checking Log Files

1. Check MySQL’s Error Log

If the restore fails, you can identify the cause by checking MySQL’s error log.

sudo cat /var/log/mysql/error.log

2. Check Detailed Error Messages with SHOW WARNINGS;

SHOW WARNINGS;

Common Warnings

7. Frequently Asked Questions (FAQ)

Q1: What to do if “Database does not exist” is displayed during restore?

✅Error Message

ERROR 1049 (42000): Unknown database 'database_name'

✅Cause

• The backup file does not contain CREATE DATABASE statements
• The database is specified during restore but does not exist

✅Solution

1. Create the database manually

   mysql -u username -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

2. Execute the restore

   mysql -u username -p database_name < backup.sql

Q2: What is the solution if garbled characters occur?

✅Error Message

ERROR 1366 (HY000): Incorrect string value

✅Cause

• The character encoding differs between backup and restore times
• The database’s default character encoding is not appropriate

✅Solution

1. Check the encoding of the backup file

   file backup.sql

2. Specify --default-character-set=utf8mb4 during restore

   mysql -u username -p --default-character-set=utf8mb4 database_name < backup.sql

3. Unify the database’s character encoding

   ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Q3: How to restore large SQL files (1GB or more)?

✅Issues

• Restore takes a long time
• The error ERROR 2006 (HY000): MySQL server has gone away occurs

✅Solution

1. Increase max_allowed_packet

   SET GLOBAL max_allowed_packet=256M;

2. Adjust innodb_buffer_pool_size

   [mysqld]
   innodb_buffer_pool_size=2G

3. Compress the backup and restore

   mysqldump -u username -p database_name | gzip > backup.sql.gz
   gunzip < backup.sql.gz | mysql -u username -p database_name

4. Split the SQL file

   split -b 500M backup.sql backup_part_

Restore the split files in order:

   cat backup_part_* | mysql -u username -p database_name

Q4: What are the restore procedures on AWS RDS (cloud environment)?

✅Procedure

1. Obtain backup locally

   mysqldump -u username -p --databases database_name > backup.sql

2. Transfer the backup file to the AWS RDS instance

   scp backup.sql username@server_ip:/path/to/backup/

3. Connect to AWS RDS and restore

   mysql -h rds_endpoint -u username -p database_name < backup.sql

✅Notes

• On AWS RDS, since there is no SUPER privilege, it is necessary to obtain the backup by specifying --set-gtid-purged=OFF.

   mysqldump -u username -p --set-gtid-purged=OFF --databases database_name > backup.sql

Q5: How to automatically test backup and restore periodically?

✅SolutionUse Linux cron jobs to automatically obtain backups and perform restore tests every day。

1. Automatic Backup Script

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y%m%d")
DB_NAME="your_database"
USER="your_user"
PASSWORD="your_password"

# Obtain backup
mysqldump -u $USER -p$PASSWORD $DB_NAME > $BACKUP_DIR/backup_$DATE.sql

# Delete backups older than 30 days
find $BACKUP_DIR -type f -name "backup_*.sql" -mtime +30 -exec rm {} ;

2. Automatic Restore Test Script

#!/bin/bash
DB_NAME="restore_test"
USER="your_user"
PASSWORD="your_password"
BACKUP_FILE="/var/backups/mysql/backup_latest.sql"

# Create test database
mysql -u $USER -p$PASSWORD -e "DROP DATABASE IF EXISTS $DB_NAME; CREATE DATABASE $DB_NAME;"

# Execute restore
mysql -u $USER -p$PASSWORD $DB_NAME < $BACKUP_FILE

3. Add to cron job

crontab -e

Add the following lines (backup at 3 AM every day, restore test at 4 AM)

0 3 * * * /path/to/backup_script.sh
0 4 * * * /path/to/restore_test_script.sh

✅Checkpoints

• Conduct automatic backup and restore tests regularly
• Always verify that the backup files are not corrupted

8. Summary

Review of Basic MySQL Restore Procedures

✅Preparation Before Restore

• Understand Backup Types (mysqldump, phpMyAdmin, binary logs, etc.)
• Check Database and Character Encoding Before Restore
• Select the Appropriate Restore Method

✅MySQL Restore Procedures

✅Data Verification After Restore

• Check if databases are correctly created with SHOW DATABASES;
• Check if all tables are restored with SHOW TABLES;
• Verify data count with SELECT COUNT(*)
• Check restore warnings with SHOW WARNINGS;

✅Optimization for Restoring Large Data Volumes

• Adjust max_allowed_packet or innodb_buffer_pool_size
• Split and restore backups (split -b 500M backup.sql backup_part_)
• Optimize index updates using --disable-keys

✅Troubleshooting During Restore

• “Database does not exist” → Execute CREATE DATABASE
• “Character garbling” → Specify --default-character-set=utf8mb4
• “Restore stops midway” → Increase max_allowed_packet
• “Restoring large data volumes” → Split files or use --single-transaction
• “Restore on AWS RDS” → Specify --set-gtid-purged=OFF
• “Check error logs” → Use SHOW WARNINGS;

Key Points for Effective Backup and Restore Operations

By properly managing backups and restores, you can minimize the risk of data loss. By performing regular backups and restore tests, you can smoothly recover data in the event of an actual failure.

1. Obtaining Regular Backups

• Schedule daily/weekly regular backups
• Combine full backups + differential backups
• Save backups to local and remote locations
• Local: /var/backups/mysql/
• Cloud storage (S3, Google Drive, FTP)

2. Automated Backup Scripts

Automating backups reduces effort and prevents backup omissions.

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y%m%d")
DB_NAME="your_database"
USER="your_user"
PASSWORD="your_password"

# Obtain backup
mysqldump -u $USER -p$PASSWORD $DB_NAME > $BACKUP_DIR/backup_$DATE.sql

# Delete backups older than 30 days
find $BACKUP_DIR -type f -name "backup_*.sql" -mtime +30 -exec rm {} ;

3. Automated Restore Tests

It is important to regularly test whether backups function correctly.

#!/bin/bash
DB_NAME="restore_test"
USER="your_user"
PASSWORD="your_password"
BACKUP_FILE="/var/backups/mysql/backup_latest.sql"

# Create test database
mysql -u $USER -p$PASSWORD -e "DROP DATABASE IF EXISTS $DB_NAME; CREATE DATABASE $DB_NAME;"

# Execute restore
mysql -u $USER -p$PASSWORD $DB_NAME < $BACKUP_FILE

4. Implementing Monitoring and Alerts

• Receive notifications if backups fail
• Set MAILTO in cron
• Use Slack or email notifications

MAILTO="your_email@example.com"
0 3 * * * /path/to/backup_script.sh

To Successfully Perform MySQL Restores

Backups and restores are the most important elements of data protection.
Especially in business operations or development environments, regular backups and recovery tests are essential.

Let’s utilize the procedures introduced in this article to improve MySQL backup and restore operations.

🔹 Checklist for Successful MySQL Restores

☑Are backups obtained regularly?☑Have you verified the contents of backup files in advance?☑Are you performing data integrity checks after restore?☑Are you using appropriate settings for restoring large data volumes?☑Have you prepared procedures for handling troubles?☑Have you introduced automation scripts to optimize backups and restores?

Next Steps

Refer to this article and test MySQL restores to confirm they can be recovered without issues.
Also, it is important to document the restore procedures and share them within the team. To protect your data, let’s continuously improve backup and restore operations!🚀