Backing up databases is a common task for many database users. For example, we might need to migrate the database from development to migration or even restore a copy of a corrupted database.
This tutorial will show you how to copy or clone an entire database, including all the data, indexes, and keys.
This tutorial assumes that you meet the following conditions:
- You have access to a database, and
- Shell Access to your server.
How to Export A Database
For illustration purposes, I will use the Employees Sample Database provided in the resource below:
https://dev.mysql.com/doc/index-other.html
To export a database, use the mysqldump tool as:
mysqldump.exe" employees --result-file=employees.sql --complete-insert --user=root --host=127.0.0.1 --port=3306
How to Restore a Database
Restoring a database is as simple as reversing the redirection symbol. For example, to restore the employees’ database from the resource provided above, use the command:
mysql -u root -p < employees.sql
This will import the exported database with output as shown below:
Enter password: *****
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:38
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:38
Conclusion
This tutorial has shown you how to use MySQLdump to export databases from one server to another or the same server.
