MySQL database uses mysqldump to export data details, mysqlmysqldump

MySQL database uses mysqldump to export data details, mysqlmysqldump

Mysqldump is a client program used by mysql to store databases. It mainly produces a series of SQL statements that can be encapsulated into a file. This package contains all the SQL commands required to reconstruct your DATABASE, such as CREATE DATABASE, CREATE TABLE, and INSERT. It can be used for lightweight fast migration or database recovery. It is a logical backup method for mysql databases.

During routine maintenance work, data is often exported, while mysqldump is a tool that is frequently used in data export. It comes with many function parameters, some common operations will be listed in the article, and all the parameter details will be listed at the end of the article.

Syntax:

By default, the exported text content is roughly as follows:Create Database judgment statement-delete table-Create Table-lock table-Disable index-insert data-enable index-Unlock table.

Usage: mysqldump [OPTIONS] database [tables]OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]OR mysqldump [OPTIONS] --all-databases [OPTIONS] 

Insert Test Data

CREATE DATABASE db1 DEFAULT CHARSET utf8;USE db1;CREATE TABLE a1(id int);insert into a1() values(1),(2);CREATE TABLE a2(id int);insert into a2() values(2);CREATE TABLE a3(id int);insert into a3() values(3);CREATE DATABASE db2 DEFAULT CHARSET utf8;USE db2;CREATE TABLE b1(id int);insert into b1() values(1);CREATE TABLE b2(id int);insert into b2() values(2); 

1. Export all databases

This command exports all databases including the system database

mysqldump -uroot -proot --all-databases >/tmp/all.sql 

2. Export all data of db1 and db2 Databases

mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql 

3. Export the a1 and a2 tables in db1

Note: you can export a specified table to only one database, and the exported content is different from that of the exported database. The exported Text of the specified table does not contain the database creation judgment statement, only Delete tables-create tables-import data

mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql 

4. Export the data with id = 1 in table A1.

Conditional export can only export a single table

mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql 

5. generate a new binlog file,-F

Sometimes you only need to add the-F parameter to generate a new binlog file after exporting data.

mysqldump -uroot -proot --databases db1 -F >/tmp/db1.sql 

6. Only export the table structure without exporting data, -- no-data

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql 

7. Export data across servers

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

Import all data from the db1 database on the h1 server to the db2 database in h2. the db2 database must exist or an error is reported.

Mysqldump -- host = 192.168.80.w.uroot-proot-C -- databases test | mysql -- host = 192.168.80.w.uroot-proot test
With the-C parameter, compression transmission can be enabled.

8. append the binlog location and file name of the master database to the exported data file, -- dump-slave

This parameter is executed on the slave server, which is equivalent to executing show slave status. When it is set to 1, it will be output to the data file using the change master command; when it is set to 2, a comment will be added before change.

This option will enable -- lock-all-tables unless -- single-transaction is specified.

The -- lock-tables option is automatically disabled after execution. -- Dump-slave: 1 by default

mysqldump -uroot -proot --dump-slave=1 --database db1 >/tmp/db1.sql 

mysqldump -uroot -proot --dump-slave=2 --database db1 >/tmp/db1.sql 

9. append the location and file name of the binlog of the current server to the output file, -- master-data

The parameter is the same as the -- dump-slave method, but it records the binlog of the current server, which is equivalent to executing the show master status Command.

10 -- opt

Equivalent to -- add-drop-table, -- add-locks, -- create-options, -- quick, -- extended-insert, -- lock-tables, -- set-charset, -- disable-keys this option is enabled by default and can be disabled with -- skip-opt.

mysqldump -uroot -p --host=localhost --all-databases --opt 

11 ensure export consistency-single-transaction

This option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures Database Consistency during export. It is only applicable to the multi-version storage engine (it does not show locking by judging the version to compare data), only InnoDB. This option and the -- lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions. To export a large table, use the -- quick option.

-- Quick,-q

Directly export the data to the standard output without buffering the query. This option is enabled by default. You can use -- skip-quick to cancel this option.

12 -- lock-tables,-l

Lock all tables before export. Use read local to lock the table to allow Concurrent Insertion of MyISAM tables. For tables that support transactions such as InnoDB and BDB, -- single-transaction is a better choice because it does not need to lock the table at all.

Note that when exporting multiple databases, -- lock-tables locks the tables for each database respectively. Therefore, this option does not guarantee the logical consistency between the tables in the exported files in the database. The export statuses of different database tables can be completely different.

13 Export stored procedures and user-defined functions-routines,-R

mysqldump -uroot -p --host=localhost --all-databases --routines 

Parameter description:

-- All-databases,-A exports all databases. Mysqldump-uroot-p -- all-databases -- all-tablespaces,-Y exports all tablespaces. Mysqldump-uroot-p -- all-databases -- all-tablespaces -- no-tablespaces,-y does not export any tablespace information. Mysqldump-uroot-p -- all-databases -- no-tablespaces -- add-drop-database add the drop database statement before each database is created. Mysqldump-uroot-p -- all-databases -- add-drop-database -- add-drop-table add the drop table statement before each data table is created. (The default value is "Open" and the -- skip-add-drop-table cancel option is used.) mysqldump-uroot-p -- all-databases (the drop statement is added by default) mysqldump-uroot-p -- all-databases-skip-add-drop-table (cancel the drop statement) -- add-locks adds lock tables and unlock table before each table is exported. (The default value is "open". Use the -- skip-add-locks cancel option.) mysqldump-uroot-p -- all-databases (the LOCK statement is added by default) mysqldump-uroot-p -- all-databases-skip-add-locks (cancel LOCK statement) -- allow-keywords allows creation of column names that are keywords. This is done by the table name prefix on each column name. Mysqldump-uroot-p -- all-databases -- allow-keywords -- apply-slave-statements add 'Stop SLAVE 'before 'change Master ', add 'start SLAVE 'At the end of the export '. Mysqldump-uroot-p -- all-databases -- apply-slave-statements -- character-sets-dir character set file directory mysqldump-uroot-p -- all-databases -- character-sets-dir =/usr/local/mysql/share/mysql/charsets -- comments additional comments. It is enabled by default. You can use -- skip-comments to cancel mysqldump-uroot-p -- all-databases (default record comment) mysqldump-uroot-p -- all-databases -- skip-comments (uncomment) -- The data exported by compatible will be compatible with other databases or old MySQL versions. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. It does not guarantee full compatibility, but is as compatible as possible. Mysqldump-uroot-p -- all-databases -- compatible = ansi -- compact exports less output information (for debugging ). Remove comments, headers, and tails. Available options: -- skip-add-drop-table -- skip-add-locks -- skip-comments -- skip-disable-keysmysqldump-uroot-p -- all-databases -- compact -- complete-insert, -c use the complete insert Statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Mysqldump-uroot-p -- all-databases -- complete-insert -- compress, -C enables compression between the client and the server to pass all information. mysqldump-uroot-p -- all-databases -- compress -- create-options, -a includes all MySQL feature options in the create table statement. (Enabled by default) mysqldump-uroot-p -- all-databases -- databases,-B Exports several databases. All name parameters following the parameter are considered as the database name. Mysqldump-uroot-p -- databases test mysql -- debug outputs debug information for debugging. The default value is d: t,/tmp/mysqldump. tracemysqldump-uroot-p -- all-databases -- debugmysqldump-uroot-p -- all-databases -- debug = "d: t,/tmp/debug. trace "-- debug-check memory and open file usage instructions and exit. Mysqldump-uroot-p -- all-databases -- debug-check -- debug-info output debugging information and exit mysqldump-uroot-p -- all-databases -- debug-info -- default-character- set sets the default character set, the default value is utf8mysqldump-uroot-p -- all-databases -- default-character-set = utf8 -- delayed-insert. The insert delayed method is used) export data mysqldump-uroot-p -- all-databases -- delayed-insert -- delete-master-logsmaster to delete logs after backup. this parameter will automatically activate -- master-data. Mysqldump-uroot-p -- all-databases -- delete-master-logs -- disable-keys for each table, use /*! 40000 alter table tbl_name disable keys */; And /*! 40000 alter table tbl_name enable keys */; the statement references the INSERT statement. This allows you to import dump files faster, because it creates an index after inserting all rows. This option is only applicable to MyISAM tables. It is enabled by default. Mysqldump-uroot-p -- all-databases -- dump-slave this option adds the binlog location and file name of the master to the file (show slave status) of the exported data ). When it is set to 1, it will be output to the data file using the change master command; when it is set to 2, a comment will be added before change. This option will enable -- lock-all-tables unless -- single-transaction is specified. This option will automatically disable the -- lock-tables option. The default value is 0. Mysqldump-uroot-p -- all-databases -- dump-slave = 1 mysqldump-uroot-p -- all-databases -- dump-slave = 2 -- master-data this option transfers the current server's binlog location and file name are appended to the output file (show master status ). If the value is 1, the change master command is output. If the value is 2, the comments are added before the change master command. This option will enable the -- lock-all-tables option, unless -- single-transaction is also specified (in this case, the global read lock gets a very short time at the start of export; for other content, refer to the -- single-transaction option below ). This option automatically disables the -- lock-tables option. Mysqldump-uroot-p -- host = localhost -- all-databases -- master-data = 1; mysqldump-uroot-p -- host = localhost -- all-databases -- master-data = 2; -- events,-E export event. Mysqldump-uroot-p -- all-databases -- events -- extended-insert,-e uses the INSERT syntax with multiple VALUES columns. In this way, the exported file is smaller and the import speed is accelerated. The value is enabled by default, and the -- skip-extended-insert cancel option is used. Mysqldump-uroot-p -- all-databasesmysqldump-uroot-p -- all-databases -- skip-extended-insert (cancel option) -- fields-terminated-by ignore a given field in the exported file. Used with the -- tab option, it cannot be used for the -- databases and -- all-databases options mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-terminated-by = "#" -- fields-enclosed -by: each field in the output file is enclosed by a given character. Used with the -- tab option, it cannot be used for the -- databases and -- all-databases options mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#" -- fields-optionally -Each field in the enclosed-by output file is selectively wrapped with a given character. Used with the -- tab option, it cannot be used for the -- databases and -- all-databases options mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#" -- fields-optionally -enclosed-by = "#" -- fields in the fields-escaped-by output file ignore the given characters. Used with the -- tab option, cannot be used for -- databases or -- all-databases options mysqldump-uroot-p mysql user -- tab = "/home/mysql" -- fields-escaped-by = "#" -- flush-logs refresh the log before you start exporting. Note: If you export multiple databases at a time (using options -- databases or -- all-databases), the logs will be refreshed one by one. Except for -- lock-all-tables or -- master-data. In this case, the log is refreshed once, and the corresponding table is locked at the same time. Therefore, if you want to export and refresh logs at the same time, use -- lock-all-tables or -- master-data and -- flush-logs. Mysqldump-uroot-p -- all-databases -- flush-logs -- flush-privileges issues a flush privileges statement after exporting the mysql database. To restore data correctly, this option should be used to export data from the mysql database and dependent mysql database at any time. Mysqldump-uroot-p -- all-databases -- flush-privileges -- force ignores SQL errors during export. Mysqldump-uroot-p -- all-databases -- force -- help displays help information and exits. Mysqldump -- help -- hex-blob exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB. Mysqldump-uroot-p -- all-databases -- hex-blob -- host, -h: the host information mysqldump-uroot-p -- host = localhost -- all-databases -- ignore-table to be exported does not export the specified table. When you specify to ignore multiple tables, you need to repeat multiple times for each table. The database and table names must be specified for each table. Example: -- ignore-table = database. table1 -- ignore-table = database. table2 ...... Mysqldump-uroot-p -- host = localhost -- all-databases -- ignore-table = mysql. user -- include-master-host-port: 'change master .. 'Add 'master _ HOST = 

Summary

Some common export operations are listed in the article, and many other parameters are also frequently used, including "-- add-drop-database" and "-- apply-slave-statements ", "-- triggers" and so on.

The above is a detailed description of how MySQL Databases use mysqldump to export data. I hope it will help you!

Articles you may be interested in:

• Mysql mysqldump: How to export only the table structure or data
• Notes for using MySQL MySqldump to export data
• Use the mysqldump command in PHP to export the database.
• Mysqldump command Import and Export Database methods and instance Summary