Categories
MySQL database restore can be done using the mysql client. The sql backup file created by the mysqldump utility is used for the restoring process. Using the .sql backup file, all databases, a single database or even selected tables can be restored.
Restoring a database is a very simple process. For restoring a database, you will need to create a new empty database. Then restore the database backup to this new location.
The general syntax for restoring a database is as below:
mysql -u [user_name] -p [database_name] < [dumpfile_name.sql]
Where:
- -u [user_name]: Username to connect to the MySQL server.
- -p : Enter the password after executing the command
- [database name]: Database that you want to restore
- [dumpfile_name.sql]: Path and name of the backup file.
How to restore MySQL databse?
For restoring the database follow the below steps:
- Create Database
- Restore the database
STEP 1: Create a new database
To restore a database dump, an empty database must already exist to import the data. If not, you will need to create a new database. Also, make sure the user should have the required access to create a new database. Execute the below command:
mysql -u root -p “create database database_name”;
Where database_name is the name of the new database you want to create.
STEP 2: Restore the dump
After creating the database. Execute the below command to import the files into the database. The sql file contains SQL queries with INSERT INTO statements, that import the data into the tables. Also, the MySQL user you’re running the command, must have write access to that database.
mysql -u root -p DB1 < DB1_bkp.sql
Where ‘DB1’ is the database name and ‘DB1_bkp.sql’ is the backup sql file.
Restore a single MySQL database from a full MySQL dump:
To restore a single database from a full MySQL dump, containing multiple databases, use the – -one-database option.
mysql – -one-database DB1 < all_bkp.sql
Where ‘DB1’ is the database you want to restore and ‘all_bkp.sql’ is the full backup sql file.
Conclusion
Following the above steps will help in restoring the mysql backup.
