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
Leave a Comment
Cancel reply