A robust database backup and restore approach is crucial for safeguarding data and minimizing downtime in case of an unforeseen event or issue. In MySQL Server, you can use mysqldump – a command-line tool – to backup and restore the databases. The mysqldump tool allows to create logical backup of MySQL database by generating SQL statements that are used for reproducing the database objects and table data. It can dump single or multiple MySQL databases into a dump file for backup. If your database gets corrupted or damaged, you can reload this dump file to restore the MySQL database. In this article, we will be explaining the process to restore dump file (database) by using the mysqldump command.
Table of Contents
Process to Restore Database (Dump File) using the mysqldump Utility
As the dump file contains a set of SQL statements, these statements are reproduced by mysqldump command when you restore the database (reload the dump file). So, you must ensure that you have all the roles and privileges required to execute these statements. These include CREATE privilege for objects in the statements, ALTER privilege for the affected database, SELECT privilege for dumped tables, and RELOAD or FLUSH TABLE privilege.
To check if all the privileges and roles granted to your account, you can use the SHOW GRANTS statement as given below:
SHOW GRANTS
[FOR user_or_role
[USING role [, role] ...]]
user_or_role: {
user (see Section 8.2.4, “Specifying Account Names”)
| role (see Section 8.2.5, “Specifying Role Names”.
}
After ensuring that your account has all the roles and privileges, follow the below instructions to restore the dump file using the mysqldump utility:
Note: When restoring the database, you need to use mysql, instead of mysqldump. Otherwise, the schema and the data will not be generated by the mysqldump utility.
First, you need to drop and recreate the MySQL database. For this, use the following command:
mysql > drop db_namemysql > create db_name'
This command will drop the selected database and then create a new database.
Once the new database is created, you can use the following command to restore the database:
mysql -u root -p db_name < dump.sql
Once the above command is successfully executed, you can check if the restored database has all the objects. For this, run the following command:
Mysql> use db_name;
Mysql>show tables;
The USE option displays the status of the current database and the SHOW option displays the information in the database.
Limitations of mysqldump Utility:
It is simple to use the mysqldump utility for restoring the dump file (database). However, it has certain limitations, such as:
- It only works if the MySQL Server is running.
- Restoring large-sized database (beyond 10 GB) is a time-consuming process.
- It does not support restoring dump file containing InnoDB table with CREATE TABLESPACE statement.
An Alternative Solution to Restore Database
To overcome the limitations of mysqldump utility or in case the utility fails to restore the database, then you can use a professional MySQL repair tool, such as Stellar Repair for MySQL. It is an easy-to-use DIY tool that can repair the corrupt database and recover all the objects in just a few clicks. It can repair database created in both InnoDB and MyISAM storage engines with complete integrity. The tool supports MySQL versions, including 8.x, 6.x, 5.x, 4.x, and 3.x.
Some key features of Stellar Repair for MySQL:
- Recovers all the database objects from corrupted database, including tables, keys, views, table properties, data types, and triggers.
- Repairs MySQL database tables created using InnoDB and MyISAM storage engines.
- Batch repairs multiple MySQL databases in a single go.
- Previews recoverable database objects before saving.
- Allows to save the repaired database in multiple file formats, such as MySQL, HTML, CSV, and XLS.
- Supports Linux CentOS 7 (64-bit), Red Hat Enterprise Linux 7 (64-bit), Ubuntu 16.04 (32-bit & 64-bit), and Ubuntu 18.04 & 19.10 (64-bit).
- Compatible with Windows 11 and earlier versions.
Conclusion:
The mysqldump utility in MySQL Server allows to backup and restore the database. If your MySQL database is corrupted or damaged, you can restore the database using the mysqldump utility. In this post, we have explained how to use mysqldump utility to restore the dump file (database). In case you fail to restore the dump file due to permission or any other issue, you can use a specialized MySQL repair tool, such as Stellar Repair for MySQL to repair the corrupted MySQL database file. The tool can recover all the data from corrupt MySQL database with complete precision and integrity. You can download the free trial version of the tool to ascertain its repair capabilities.