How to Restore a Single Database from a Full MySQL Database Dump?
MySQL is among the most widely-used relational database management systems for structured data. Regular backups must be performed to avoid data loss, with full MySQL database dumps created using mysqldump being a common practice. However, restoring one database from a full dump with multiple databases can be tricky!
We will take you through the necessary steps to restore a specific database from a full MySQL database dump. Whether you have to recover a single database from a complete backup or extract one database for migration, this guide will help you get it done easily!
Before starting, ensure that:
- You have a full MySQL database dump (full_backup.sql).
- MySQL is installed on your system, and MySQL should be accessible from the command line.
- You should possess adequate user permissions to restore a database or table.
How to Generate a Full MySQL Dump?
Make sure you have two or more databases on your server. For this guide, create three new databases on your server as below.
Step 1: Log in to the MySQL console.
$ mysql
Step 2: Create the databases.
mysql> CREATE DATABASE db1; CREATE DATABASE db2; CREATE DATABASE exampledb3;
Step 3: Switch to the db1 database.
mysql> CREATE DATABASE db1; CREATE DATABASE db2; CREATE DATABASE exampledb3;
Step 4: Create a new table in the database.
mysql> CREATE TABLE Example ( ID int, FirstName varchar(255), LastName varchar(255), Country varchar(255), City varchar(255) );
Step 5: Create a new privileged user.
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'strong-password';
Step 6: Give that user full privileges.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost';
Step 7: Reload MySQL privileges.
mysql> FLUSH PRIVILEGES;
Step 8: Exit the console.
mysql> EXIT
Step 9: Create a full dump of all databases in MySQL.
$ mysqldump -u myuser -p --all-databases > backup.dump
A new backup.dump file will now be added to your working directory.
Step 10: For the restoration of purposes, re-login to the MySQL console.
$ mysql -u myuser -p
Step 11: Drop the databases you created in the previous steps.
mysql> DROP DATABASE db1; mysql> DROP DATABASE db2; mysql> DROP DATABASE exampledb3;
Step 12: Exit the MySQL console.
mysql> EXIT
How to Restore a Single Database from a Full MySQL Database Dump?
Restoring a Single Database is simple and can be performed easily in five easy steps. Let’s explore them!
Step 1: Identify the Full Database Dump
You will require a MySQL dump file that contains all databases. If you need to create a new dump of all databases, use the following command:
mysqldump -u root -p--all-databases > full_backup.sql
It creates a single file, full_backup.sql, containing all databases and their associated data.
Step 2: Extract the Specific Database
Since a full dump consists of multiple databases, you will need to extract only the portion that concerns the required database.
Option 1: Using sed (Linux/macOS)
You can extract the specific database using sed:
sed -n '/^-- Current Database: `your_database_name`/,/^-- Current Database:/p' full_backup.sql > single_db.sql
This command isolates the database your_database_name and saves it in single_db.sql.
Option 2: Using awk (Linux/macOS)
Alternatively, one could also integrate awk:
awk '/^-- Current Database: `your_database_name`/{flag=1; print; next} /^-- Current Database:/{flag=0} flag' full_backup.sql > single_db.sql
It extracts all of the relevant SQL statements for your_database_name.
Option 3: Manual Extraction
- Get out with a manual extraction and open full_backup.sql in a text editor.
- When you find the part of the text with the words CREATE DATABASE your_database_name, copy everything from USE your_database_name; until just before the next — Current Database: section.
- Save it as single_db.sql.
Step 3: Create the Target Database
Before the restoration, create the database on MySQL (if it does not exist):
mysql -u root -p -e "CREATE DATABASE your_database_name;"
Step 4: Restore the Extracted Database
Now restore the extracted database dump:
mysql -u root -p your_database_name < single_db.sql
It imports the extracted data into your_database_name.
Step 5: Verify that the restoration worked
Finally, log into MySQL and perform commands to ensure that the restoration was successful.
mysql -u root -p mysql> use your_database_name; mysql> show tables;
If the tables are successfully displayed, the restoration was successful!
Steps to Restore a Single Table from a MySQL Database Dump
Step 1: Extract a Table
To extract a single table from a complete MySQL dump, use sed or copy it manually from the full MySQL dump, specifically copying the section from the dump containing all CREATE TABLE to the corresponding INSERT INTO statements for the table
That is, to extract your_table_name:
sed -n '/^CREATE TABLE `your_table_name`/,/Table structure for table/p' full_backup.sql > single_table.sql
Alternatively, you could manually open full_backup.sql, copy the portion containing the statements:
CREATE TABLE your_table_name;
All INSERT INTO your_table_name statements.
Save the output as single_table.sql.
Step 2: Restore the Table
Make sure the target database is created and then use it for importing the already extracted table
mysql -u root -p your_database_name < single_table.sql"
Step 3: Inspect the Table Restoration
Check if the table was created and that it contains the expected data:
mysql -u root -p mysql> USE your_database_name; mysql> SHOW TABLES; mysql> SELECT COUNT(*) FROM your_table_name;
Why Do You Require to Restore a Single Database from a Full MySQL Database Dump?
There are several reasons for restoring one of the databases from a full MySQL database dump:
1. Accidental Data Loss
A particular database may be accidentally deleted, damaged, or modified. In such cases, it is mandatory to rectify the matter by restoring such a database by full backup without impairing any other databases.
2. Selective Migration
In migrating a single database from a multi-database backup to a server, you’ll have to extract only the specific database you want instead of restoring everything.
3. Testing and Development
Developers may want to restore a specific database from production backups to a staging or even a development environment for testing.
4. Disaster Recovery
A system crash or a data breach, only restoring the affected database (not all databases) ensures minimal downtime and reduces the impact of data loss.
5. Efficient Storage and Performance
It would save time and system resources if you reverted only the relevant database rather than restoring everything.
6. Migrating or Splitting Databases
When changing databases around or splitting a monolith of a database setup, separate extraction and restoration of specified databases may be needed.
Conclusion
Restoring an entire database or a single table from a full MySQL dump is one of the most valuable skills to master when restoring data that has been lost, migrated, or simply while testing. Using sed, awk, or manual extraction will allow the user to restore selectively only that database or table that is needed as opposed to restoring the whole dump. Always verify your restoration and check for data integrity.
Isn’t that simple? With all of these in mind, you should now be quite adept at effectively managing your MySQL backups and restorations!
Loved this solution? Do not forget to share it with your dev friends.
Thank you for reading!
Frequently Asked Questions
Can I restore a single table without restoring the entire database?
Yes, you can restore a single table with the help of some SQL commands, which can be extracted from a full dump.
What if my MySQL dump file is too big?
You can use tools such as grep, sed, or awk to extract, thus decreasing memory usage during the restoration process.
Is it necessary to stop MySQL to restore a database or table?
No, but to avoid conflicts with active connections, restoration is better done during off-peak hours
