MySQL mysqldump Data Export basic operations

Mysqldump

The mysqldump command is a backup tool in the MySQL database that exports the databases in the MySQL server in a standard SQL language and saves them to a file.

Options

--all-databases,-A: Export all databases on the MySQL server;--all-tablespaces,-y: Export all table spaces for a database--add-drop-database: Add a statement to delete the database before each database statement is created;--add-drop-table: Add statements to delete database tables before each database table statement is created; (Add drop statement by default, use--skip-add-drop-table cancel option)--add-locks: Lock database tables when exporting database tables; (Add lock statement by default, use--skip-add-locks cancel option)--comments: Add comment information;--compact: Compression mode, resulting in less output;--complete-insert: INSERT statement for output completion;--databases,-B: Specifies the database to be backed up, and all parameter names after the parameters are considered database names;--default-character-set: Specifies the default character set;--force: The backup operation continues when an error occurs;--host: Specifies the server to back up the database;--lock-tables: Lock all database tables before backing up;--no-create-db: Prohibit generation of creation of database statements;--no-create-info: Prohibit generation of CREATE database library table statements;--password: Password to connect to MySQL server;the port number of the--port:mysql server;--user: The user name to connect to the MySQL server. 

Common statements export all databases (including system databases)

--all-databases > 导出的文件名--all-databases > all.sql

Export entire database (can be multiple at one time)

--databases 数据库1 数据库2 > 导出的文件名--databases databas1 database2 > databases.sql

Export a table (can be multiple at a time, but only for one database)

--databases 数据库 --tables 表1 表2 > 导出的文件名--databases db1 --tables t1 t2 > tables.sql

Export conditionally (same conditions can be multiple at a time)

--databases 数据库 --tables 表1 表2 --where=‘条件‘ > 导出的文件名--databases db1 --tables a1 --where=‘id=1‘  > tables.sql

Export only table structure does not export data (can be multiple at a time)

--no-data --databases 数据库1 数据库2 > 导出的文件名--no-data --databases db1 > tables.sql

Export Import data across servers

--host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot --databases db2

Import all the data from the DB1 database in the H1 server into the DB2 database in H2, and the DB2 database must be present or the error will be

--host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot  --databases test

Add the-c parameter to enable compression pass

For more detailed information, please refer to: https://www.cnblogs.com/chenmh/p/5300370.html

MySQL mysqldump Data Export basic operations