Restore MySQL Database using mysql Client

The mysql client is a command-line tool which can be used to restore a MySQL database or a collection of databases from the backup file that contains SQL statements. For example, the backup file created by mysqldump.

The general syntax is:

mysql -u {username} -p {database_name} < {filename}

• --user={username}, -u {username} - the account username which will be used for connecting to the MySQL server.
• --password[=password], -p[password] - the account password which will be used for connecting to the MySQL server. The password value is optional. If not provided, the tool will prompt for one. If provided, there must be no space between --password= or -p option and the password.
• {database_name} - the name of database.
• {filename} - the filename of database backup.

Restore a multiple databases

We can create the backup file by using mysqldump with the --all-databases or --databases option. In this case, the backup file contains CREATE DATABASE and USE statements. When this backup file is used to restore databases, don't need to provide database name to command-line.

mysql -u root -p < backup.sql

When mysql is used interactively, a source command can be used to restore databases from the backup file.

mysql -u root -p

# Interactive mode:
mysql> source backup.sql;

Restore a single database

We can back up a single database using mysqldump without --databases option. In this case, the backup file does not contain CREATE DATABASE and USE statements. When restoring a database from this backup file, create the database first and then provide the database name to the command-line.

mysqladmin -u root -p create market
mysql -u root -p market < backup.sql

Alternatively, it can be done using mysql interactively.

mysql -u root -p

# Interactive mode:
mysql> CREATE DATABASE IF NOT EXISTS market;
mysql> USE market;
mysql> source backup.sql;

Copy tables from one database to another

The mysql client can read from a pipe. So, we can copy tables from one database to another by combining the mysqldump and mysql into a single command via pipe.

mysqldump -u root -proot market | mysql -u root -proot market_copy

Copy tables to another database server

We can copy the tables over the network to another database server by combining the mysqldump and mysql together using pipe.

mysqldump -u root -proot market | mysql -h other-host.com -u root -proot market_copy