MySQL large amount of data import and Export command detailed

In the face of large data volumes, large file SQL operations, we need to rely on MySQL 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 need to use the command to connect to the database and select the appropriate database to use.

2. Database backup command

MySQL's Export command mysqldump, the basic usage is:

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

Note: Cannot connect the database first, is the direct connection database export

Simple example:

The code is as follows	Copy Code
Mysqldump-h localhost-u root-p dbname > Backup.sql

MySQL's mysqldump command, the basic usage is:

mysqldump [OPTIONS] Database [tables] Description:

1. By executing mysqldump–help, you can get the use Help information that your mysqldump version supports.
2. If the port is the default 3306, you can omit the-p {port number}.

3. The content in the command line format {} is variable
Host name: {hostname}
Port: {port} (general default 3306, can default)
User name: {user} {username} (for example, root)
Password: {password}
DB name: {database}
Table name: {table} {table1} {table2}
File name: {backup.sql}

. Common operations:

Backup MySQL Entire database:

The code is as follows	Copy Code
MYSQLDUMP-H host name-P Port-u username-p password (–database) database name > filename. sql Mysqldump-h{hostname}-p{port}-u{username}-p{password} {database} > {backup.sql}

Note: parameters and values can be separated, such as-p3306 can be written as-P 3306, the password can not be filled out to ensure safety.

MySQL Database compressed backup:

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

Back up a MySQL database (some) tables:

MYSQLDUMP-H host name-P Port-u username-p password (–tables |–quick) database Name Table name 1 (table Name 2 ...) > filename. SQL (default in parentheses).
#实例:

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

Backup 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

To restore the MySQL database command:

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

To restore a compressed MySQL database:

The code is as follows	Copy Code
Gunzip < {backup.sql.gz} | Mysql–u{username}–p{password} {Database}

To transfer a database to a new server:

The code is as follows	Copy Code
Mysqldump–u{username}–p{password} {Database} | Mysql–host={hostname}–c {Database}