MySQL Import and Export commands for large data volumes

Importing and exporting data is a data backup for mysql. Next I will introduce how to use the mysql command to back up and restore the database when there is a large amount of data. I hope some methods will be helpful to you.

In the face of large data volumes and large file SQL operations, we need to use mysql's powerful command operations:

1. Database Import command

The Code is as follows:	Copy code
Mysql-h localhost-u root-p Use dbname Source backup. SQL

Note: You must use commands to connect to the database and select the corresponding database.

2. Database Backup command

MySQL Export command mysqldump. The basic usage is:

The Code is as follows:	Copy code
Mysqldump [OPTIONS] database [tables]

Note: you cannot connect to the database first, but directly connect to the database for export.

Simple Example:

 

The Code is as follows:	Copy code
Mysqldump-h localhost-u root-p dbname> backup. SQL

The mysqldump command of MySQL is used as follows:

Mysqldump [OPTIONS] database [tables] Description:

1. Run mysqldump-help to obtain the help information supported by your mysqldump version.
2. If the port is the default 3306 port,-P {port number} can be omitted.

3. The content in the command line format {} is a variable.
Host Name: {hostname}
Port: {port} (3306 by default, which can be default)
Username: {user} {username} (such as root)
Password: {password}
Database Name: {database}
Table Name: {table} {table1} {table2}
File Name: {backup. SQL}

. Common Operations:

Back up the entire MySQL database:

The Code is as follows:	Copy code
Mysqldump-h host name-P port-u user name-p password (-database) database Name> file name. SQL Mysqldump-h {hostname}-P {port}-u {username}-p {password} {database}> {backup. SQL}

Note: parameters and values can be separated. For example,-P3306 can be written as-P 3306. passwords can be left blank to ensure security.

MySQL database compression backup:

The Code is as follows:	Copy code
Mysqldump-h {hostname}-u {username}-p {password} {database} | gzip> your backup. SQL .gz}

Back up a MySQL database table:

Mysqldump-h host name-P port-u user name-p password (-tables |-quick) database name table name 1 (table name 2 ...) > File name. SQL (which can be defaulted in parentheses ).
# Instance:

The Code is as follows:	Copy code
Mysqldump-u root-p myadmindb admin_group admin_group_right admin_logs admin_user_right admin_users> tmp. SQL

Back up multiple MySQL databases at the same time:

The Code is as follows:	Copy code
Mysqldump-h {hostname}-P {port}-u {username}-p {password}-databases {database1} {database2} {database3}> multibackfile. SQL

Command for restoring MySQL database:

The Code is as follows:	Copy code
Mysql-h {hostname}-u {username}-p {password} {database} <{backup. SQL}

Restore the compressed MySQL database:

The Code is as follows:	Copy code
Gunzip <{backup. SQL .gz} | mysql-u {username}-p {password} {database}

Transfer the database to the new server:

The Code is as follows:	Copy code
Mysqldump-u {username}-p {password} {database} | mysql-host = {hostname}-C {database}