Detailed introduction to mysqldump

Detailed introduction to mysqldump

Note: mysqldump is a tool used by the database for backup and data transfer. It can be used for backup when the data volume is small (several GB. When the data volume is large, mysqldump is not recommended for backup. mysqldump is a useful mysql data transfer tool with strong compatibility and cross-version features;

I. Database Export

Description of exported objects:

Mysqldump can be used to export a single table, multiple tables, a single database, multiple databases, and all databases.

Shell> mysqldump [options] db_name [tbl_name...] // export a specified database or a single table

Shell> mysqldump [options] -- databases db_name... // export multiple databases

Shell> mysqldump [options] -- all-databases // export all

 

Export options: Run mysqldump -- help in the operating system to view all the Optional options of mysqldump. The following are common options.

 

 

-- All-databases,-A: Back up all databases

-- Databases,-B: used to back up multiple databases. If this option is not available, mysqldump uses the first name parameter as the database name, followed by the table name. With this option, mysqldump treats every name as the database name.

-- Force,-f: continues the backup even if an SQL error is found.

-- Host = host_name,-h host_name: Backup host name. The default value is localhost.

-- No-data,-d: only export the table structure

-- Password [= password],-p [password]: password

-- Port = port_num,-P port_num: Specifies the port number for TCP/IP connection.

-- Quick,-q: quick export

-- Tables: overwrite the -- databases or-B option. The following parameters are treated as table names-

-- User = user_name,-u user_name: user Name

-- Xml,-X: export as an xml file
 

 

1. Export the data named B in the table under test.

Mysqldump-uroot-h 127.0.0.1 test B> B _test. SQL

 

2. Export all data whose database is test

Mysqldump-uroot-h 127.0.0.1 test> test. SQL

 

3. Export the database named test and retail.

Mysqldump-uroot-h 127.0.0.1-B test retail>/data/db. SQL

 

4. Export all databases

Mysqldump-uroot-h127.0.0.1-A>/data/all. SQL

 

5. Export the database structure (add option-d to the above script)

Mysqldump-uroot-h 127.0.0.1 test B-d> B _test. SQL

 

Ii. Database Import

1. Import Method

Mysql command line import:

Mysql> source/data/test. SQL

System Command Line import:

Mysql-uroot-p </data/test. SQL

 

2. Restore a single database (you must specify a database)

Mysql> use test

Mysql> source/data/test. SQL

 

3. Restore multiple databases (you do not need to specify a database)

Mysql-uroot-p </data/all. SQL

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

Mysqldump missing-q Parameter causes MySQL to be killed by oom

Mysqldump and LVM logical volume Snapshot

MySQL backup solution --> (using mysqldump and binlog binary logs)

[MySQL] Using mysqldump for text backup

This article permanently updates the link address: