Backup Restore MySql Database

How to backup databases from the MySQL server and restore to another MySQL server?

The efficient tool to backup database from MySQL server is "mysqldump". Since we are having many UI tools to backup and restore the database for MySQL server, But I personally recommend to use "mysqldump" command for backup and restore MySQL databases.

The following are commands for backup the databases. To execute the mysqldump commands, open terminal in Linux or command prompt in windows with MySQL Server <version>\bin directory.

Backup Single Specific Database

Syntax:

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Example:

mysqldump -u root -p employee > D:\employee.sql

Backup Multiple Specific Databases

Syntax:

mysqldump -u root -p[root_password] --databases [database1_name] [database2_name] > dumpfilename.sql

Example:

mysqldump -u root -p --databases employee department > D:\employee_department.sql

Backup All Databases

Syntax:

mysqldump -u root -p[root_password] --all-databases > dumpfilename.sql

Example:

mysqldump -u root -p --all-databases > D:\alldb.sql

Backup only Specific table

Syntax :

mysqldump -u root -p[root_password] [database_name] [table_name] > dumpfilename.sql

Example:

mysqldump -u root -p Employee sales > D:\employee_sales.sql

[Note: Password will be asked once the command starts execution.It is not required to give in command.]

The following are the commands for restoring the backup databases from the "*.sql" dump file. But before restoring the databases,we must create the databases to restore the schema and data.

Create Database before restoring

Syntax:

create database [databasename]

Example:

create database employee

Restore Specific Database

Syntax :

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Example:

mysql -u root -p employee < D:\employee.sql

Restore multiple or all databases

Syntax:

mysql -u root -p[root_password] < dumpfilename.sql

Example:

mysql -u root -p < D:\alldb.sql