In the daily maintenance work, it is often necessary to export data, and mysqldump is a very frequently used tool in the process of exporting data. It has many functional parameters. Some common operations will be listed in the article, and all parameters will be listed in detail at the end of the article.
Grammar:
By default, there is no parameter export, and the exported text content is as follows: create database judgment statement-delete table-create table-lock table-disable index-insert data-enable index-unlock table.
Usage: mysqldump[ Options] Database [Table]
Or mysqldump[ options]-database [options] DB 1 [DB2 DB3...]
Or mysqldump[ options]-all databases [options]
Insert test data
Copy code
Create database db 1 default character set utf8.
Use db1;
Create table a1(id int);
Insert into a 1 () value (1), (2);
Create table a2 (id int);
Insert into a2 () value (2);
Create table a3 (id int);
Insert a3 () value (3);
Create database db2 default character set utf8
Using db2
Create table b1(id int);
Insert into the value of b 1 () (1);
Create table b2 (id int);
Insert into b2 () value (2);
Copy code
1. Export all databases
This command exports all databases, including the system database.
mysqldump-uroot-proot-all-databases & gt； /tmp/all.sql
2. Export all data of db 1 and db2.
mysqldump-u root-proot-databases db 1 DB2 & gt； /tmp/user.sql
3. Export a 1 and a2 tables in db 1.
Please note that the specified table can only be exported for one database, and the exported content is different from the exported database. There is no judgment statement to create a database in the export text of the specified table, only delete table-create table-import data.
mysqldump-u root-proot-databases db 1-tables a 1 a2 & gt； /tmp/db 1.sql
4. Conditional export: export the data with id= 1 in table a 1 of db 1.
If the conditions of multiple tables are the same, you can export multiple tables at once.
The venue is plastic.
mysqldump-u root-proot-databases db 1-tables a 1-where = ' id = 1 ' & gt； /tmp/a 1.sql
The field is a string, and the exported sql does not contain drop table and create table.
mysqldump-u root-proot-no-create-info-databases db 1-tables a 1-where = " id = ' a ' " & gt； /tmp/a 1.sql
5. Generate a new binlog file,-f.
Sometimes you want to generate a new binlog file after exporting data, just add the -F parameter.
mysqldump-u root-proot-databases db 1-F & gt； /tmp/db 1.sql
6. Only export the table structure, but not the data, -no-data.
mysqldump-ur oot-proot-no-data-databases db 1 & gt； /tmp/db 1.sql
7. Cross-server export import data
MySQL dump-host = h 1-u root-proot-databases db 1 | MySQL-host = H2-u root-proot DB2
Import all the data of db 1 database in server h 1 into db2 database in h2, and the db2 database must exist, otherwise an error will be reported.
MySQL dump-host =192.168.80.137-urot-proot-c-databases test | MySQL-host =192.168.80./
Add the -C parameter to enable compressed delivery.
8. Attach the binlog location and file name of the main library to the exported data file-dump-slave.
Note:-dump-slave command If the current server is a slave server, this command will execute stop slave to get the file and location of the master binlog, and then automatically execute start slave to start the slave server after backup. However, if a large amount of data is backed up, the delay between the slave and the host will become larger. Use-dump-slave to get only the location (relay _ mater _ log _ file, exec _ master _ log _ pos) of the current slave server data execution to the master server, but not the current binlog execution location of the master server, which mainly depends on the data delay of the master and 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 with the command to change the master control; When set to 2, comments will be added before the change.
This option will open -lock-all-tables unless-single-transaction is specified.
The-lock-tables option is automatically closed after execution. -dump-slave defaults to 1.
mysqldump-u root-proot-dump-slave = 1-databases db 1 & gt； /tmp/db 1.sql
mysqldump-u root-proot-dump-slave = 2-database db 1 & gt； /tmp/db 1.sql
9. Attach the location and file name of the binlog of the current server to the output file-master-data.
This parameter is the same as the-dump-slave method, except that it records the binlog of the current server, which is equivalent to executing the values of show master status, status (file, position).
Note:-master-data will not stop the master-slave service of the current server.
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 by-skip-opt.
mysqldump-u root-p-host = localhost-all-databases-opt
1 1. Ensure the consistency of export order transactions.
This option submits the BEGIN SQL statement before exporting the data. Begin will not block any applications and ensure that the database is in a consistent state when exporting. Only applicable to multi-version storage engines (comparing data by judging versions without displaying locks), InnoDB only. This option and the -lock-tables option are mutually exclusive, because locking the tables will make any pending transactions commit implicitly. If you want to export a large table, you should use the-quick option together.
-Come on.-Ask.
Export directly to standard output without buffering the query. By default, it is open. Use-skip-quick to cancel this option.
12.-lock table-l
Lock all tables before starting the export. Use READ LOCAL to lock the table to allow parallel insertion of MyISAM tables. For tables that support transactions, such as InnoDB and BDB, single transaction is a better choice because it doesn't need to lock tables at all.
Note that when exporting multiple databases, --lock-tables locks the tables of each database separately. Therefore, this option cannot guarantee the logical consistency of the tables in the export file between databases. The export status of different database tables may be completely different.
13. Export stored procedures and custom functions -routines.-r
mysqldump-u root-p-host = localhost-all-databases-routines
14. Compressed backup
Compressed backup
mysqldump-ur oot-proot-databases ABC 2 & gt； /dev/null | gzip & gt； /abc.sql.gz
recover
abc.sql.gz | MySQL-uroot-proot ABC
Parameter description:
Copy code
-All databases.-A
Export all databases.
Mysqldump-u root-p-all- database
-all tablespaces, -Y
Export all tablespaces.
mysqldump-u root-p-all-databases-all-tablespaces
-No-Tablespace, -y
No tablespace information is exported.
mysqldump-u root-p-all-databases-no-tablespaces
-Add-Delete-Database
Before creating each database, add a drop database statement.
mysqldump-u root-p-all-databases-add-drop-database
-Add-Delete-Table
Before creating each data table, add a drop data table statement. (On by default, cancel with the option -skip-add-drop-table. )
Myqldump-urot-p-all-databases (drop statement is added by default)
Myqldump-urot-p-all-databases-skip-add-drop-table (cancel the drop statement)
-Additional lock
Add a locked table before exporting each table, and then unlock the table. (It is open by default, so use the -skip-add-locks option to cancel it. )
Myqldump-urot-p-all-databases (add LOCK statement by default)
Myqldump-urot-p-all-databases-skip-add-locks (unlock statement)
-Allow-Keyword
Allows you to create column names as keywords. This is achieved by adding each column name before the table name.
mysqldump-u root-p-all-databases-allow-keywords
-Apply dependent statements
Add "Stop Slave" before "Change Host" and "Start Slave" at the end of export.
mysqldump-u root-p-all-databases-apply-slave-statements
-Character Set-Directory
Character set file directory
mysqldump-u root-p-all-databases-character-sets-dir =/usr/local/MySQL/share/MySQL/charsets
-Comments
Additional annotation information. By default, it is open and can be cancelled by skipping comments.
Myqldump-urot-p-all-databases (default record comment)
Myqldump-urot-p-all-databases-skip-comments (uncomment)
be compatible
The exported data will be compatible with other databases or old versions of MySQL. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc.
To use multiple values, separate them with commas. Full compatibility is not guaranteed, but as far as possible.
mysqldump-u root-p-all-databases-compatible = ansi
-Compact
Export less output information (for debugging). Delete comments and head-tail structures. Options are available:-skip-add-drop-table-skip-add-lock-skip-comments-skip-disable-keys.
mysqldump-u root-p-all-databases-compact
-complete.-insert.-c
Use a complete insert statement (including column names). Doing so can improve the insertion efficiency, but it may be affected by the max_allowed_packet parameter, resulting in the insertion failure.
Mysqldump -uroot -p-all-database-complete-insert
-Compression.-C
Enable compression to transfer all information between the client and the server.
mysqldump-u root-p-all-databases-compress
-create-options, -a
Include all MySQL feature options in the CREATE TABLE statement. (On by default)
Mysqldump-u root-p-all- database
-Database. -B
Export several databases. All name parameters after the parameter are treated as database names.
Database testing
-Debugging
Output debugging information for debugging. The default value is: d: t,/tmp/mysqldump.trace.
mysqldump-u root-p-all-databases-debug
mysqldump-u root-p-all-databases-debug = " d:t，/tmp/debug.trace "
-Debugging-Checking
Check memory and open file instructions and exit.
mysqldump-u root-p-all-databases-debug-check
-Debugging information
Output debugging information and exit.
mysqldump-u root-p-all-databases-debug-info
-Default Character Set
Set the default character set. The default value is utf8.
mysqldump-u root-p-all-databases-default-character-set = utf8
-delayed insertion
Data is exported with insertion delay.
mysqldump-u root-p-all-databases-delayed-insert
-Delete-Master Log
Delete the log after the master backup. This parameter will be automatically activated-master data.
mysqldump-u root-p-all-databases-delete-master-logs
-Disable key
For each table, use/*! 40000 ALTER TABLE tbl_name disable key */; And/*! 40000 ALTER TABLE tbl_name enable key */; Statement references the INSERT statement. In this way, the dump file can be imported faster because it creates an index after all rows are inserted. This option is only available for MyISAM tables and is turned on by default.
Mysqldump-u root-p-all- database
-Dumping slaves
This option appends the primary binlog location and file name to the file from which the data is exported (showing the dependent status). When it is set to 1, it will be output to the data file with the command to change the master control; When set to 2, comments will be added before the change. This option will open -lock-all-tables unless-single-transaction is specified. This option automatically turns off the-lock-tables option. The default value is 0.
mysqldump-u root-p-all-databases-dump-slave = 1
mysqldump-u root-p-all-databases-dump-slave = 2
-Master data
This option appends the location and file name of the binary log of the current server to the output file (showing the main status). If it is 1, the change master command is output; If it is 2, comment information is added before the main command of change is output. This option will turn on the -lock-all-tables option unless-single-transaction is also specified (in this case, the global read lock will get a short time at the beginning of the export; For other contents, please refer to the-single option below). This option automatically turns off the-lock-tables option.
mysqldump-u root-p-host = localhost-all-databases-master-data = 1；
mysqldump-u root-p-host = localhost-all-databases-master-data = 2；
-Events.-E
Export events.
Mysqldump -uroot -p-all-database-events
-expand-Insert,-E
Use INSERT syntax for multivalued columns. This will make the exported file smaller and speed up the import. It is open by default, so use the -skip-skip-extended-insert option to cancel.
Mysqldump-u root-p-all- database
Myqldump-urot-p-all-databases-skip-extended-insert (cancel option)
-Field-Terminator
The given fields are ignored in the export file. Used with the-tab option instead of the -databases and-all-databases options.
mysqldump-u root-p test test-tab = "/home/MySQL "-fields-terminated-by = " # "
-Fields-Enclosed-By
Each field in the output file is wrapped with a given character. Used with the-tab option instead of the -databases and-all-databases options.
mysqldump-u root-p test test-tab = "/home/MySQL "-fields-enclosed-by = " # "
-Fields-Optional-Enclosed-By
Each field in the output file is selectively wrapped with a given character. Used with the-tab option instead of the -databases and-all-databases options.
mysqldump-u root-p test test-tab = "/home/MySQL "-fields-enclosed-by = " # "-fields-optionally-enclosed-by = " # "
-Field-Escape-By
Each field in the output file ignores the given character. Used with the-tab option instead of the -databases and-all-databases options.
mysqldump-u root-p MySQL user-tab = "/home/MySQL "-fields-escaped-by = " # "
-Flush the log
Refresh the log before starting the export.
Please note: If you export more than one database at a time (using the option -databases or-all-databases), the logs will be refreshed one by one. Except using -lock-all-tables or-master-data. In this case, the log will be refreshed once and the corresponding table will be locked. Therefore, if you plan to export and refresh logs at the same time, you should use -lock-all-tables or-master-data and-flush-logs.
mysqldump-u root-p-all-databases-flush-logs
-Flush privilege
After exporting mysql database, issue a FLUSH PRIVILEGES statement. For proper recovery, you should use this option whenever you export a mysql database and the data depends on it.
mysqldump-u root-p-all-databases-flush-privileges
-Power
Ignore SQL errors when exporting.
mysqldump-u root-p-all-databases-force
-Help!
Display help information and exit.
Mysqldump-help
-hexadecimal blob
Export binary string fields in hexadecimal format. You must use this option if you have binary data. The affected field types are BINARY, VARBINARY and BLOB.
mysqldump-u root-p-all-databases-hex-blob
-host.-H
Host information to export
mysqldump-u root-p-host = localhost-all-databases
-Ignore table
Do not export the specified table. When specifying to ignore multiple tables, it needs to be repeated several times, one table at a time. Each table must specify a database and a table name. For example:-ignore-table = database.table1-ignore-table = database.table2. ...
mysqldump-u root-p-host = localhost-all-databases-ignore-table = MySQL . user
-Contains-Master-Host-Port
Add "Primary Host ="
mysqldump-u root-p-host = localhost-all-databases-include-master-host-port
-Insert-Ignore
Use the INSERT IGNORE statement when inserting rows.
mysqldump-u root-p-host = localhost-all-databases-insert-ignore
-OK.-End at
Each line of the output file is divided by a given string. Used with the-tab option instead of the -databases and-all-databases options.
MySQL dump-u root-p-host = localhost test-tab = "/tmp/MySQL "-lines-terminated-by = " # # "
-lock all tables. -x
Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock, and the-single-transaction and-lock-tables options are automatically turned off.
mysqldump-u root-p-host = localhost-all-databases-lock-all-tables
-lock the watch-l
Lock all tables before starting the export. Use READ LOCAL to lock the table to allow parallel insertion of MyISAM tables. For tables that support transactions, such as InnoDB and BDB, single transaction is a better choice because it doesn't need to lock tables at all.
Note that when exporting multiple databases, --lock-tables locks the tables of each database separately. Therefore, this option cannot guarantee the logical consistency of the tables in the export file between databases. The export status of different database tables may be completely different.
mysqldump-u root-p-host = localhost-all-databases-lock-tables
-Log error
Attach warning and error messages to the given file.
mysqldump-u root-p-host = localhost-all-databases-log-error =/tmp/mysqldump _ error _ log . err
-Maximum allowed packets
The maximum packet length sent and accepted by the server.
mysqldump-u root-p-host = localhost-all-databases-max _ allowed _ packet = 10240
-Network buffer length
Cache size for TCP/IP and socket connections.
mysqldump-u root-p-host = localhost-all-databases-net _ buffer _ length = 1024
-No-Automatic submission
Wrap the table with an auto-commit/commit statement.
mysqldump-u root-p-host = localhost-all-databases-no-auto commit
- no-create-db，-n
Only export data, without adding the CREATE DATABASE statement.
mysqldump-u root-p-host = localhost-all-databases-no-create-db
-Do not create information. -t
Only export data, without adding the CREATE TABLE statement.
mysqldump-u root-p-host = localhost-all-databases-no-create-info
-No data. -d
No data is exported, only the database table structure is exported.
mysqldump-u root-p-host = localhost-all-databases-no-data
-No collection name -N
Equivalent to -skip-set-charset
mysqldump-u root-p-host = localhost-all-databases-no-set-names
- 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 by-skip-opt.
mysqldump-u root-p-host = localhost-all-databases-opt
-By main order
If there is a primary key or the first unique key, the records of each table are sorted. It is effective when exporting MyISAM table to InnoDB table, but it takes a long time to export.
mysqldump-u root-p-host = localhost-all-databases-order-by-primary
-password.-P
Password to connect to database
-Pipes (for windows systems)
Connect mysql using named pipes
mysqldump-u root-p-host = localhost-all-databases-pipe
-Port,-P
Connection database port number
-Agreement
The connection protocols used include: tcp, socket, pipe and memory.
mysqldump-u root-p-host = localhost-all-databases-protocol = TCP
-Come on.-Ask.
Export directly to standard output without buffering the query. By default, it is open. Use-skip-quick to cancel this option.
mysqldump-u root-p-host = localhost-all-databases
mysqldump-u root-p-host = localhost-all-databases-skip-quick
-Quote-Name-Ask
Use (`) to name tables and columns. By default, use -skip-quote-names to deselect this option.
mysqldump-u root-p-host = localhost-all-databases
mysqldump-u root-p-host = localhost-all-databases-skip-quote-names
-Replace
Replace INSERT INTO with REPLACE INTO.
mysqldump-u root-p-host = localhost-all-databases-replace
-outcome document. -r
Output directly to the specified file. This option should be used on systems that use carriage return and line feed pairs (such as DOS and Windows) (\\r\\n). This option ensures that only one line is used.
mysqldump-u root-p-host = localhost-all-databases-result-file =/tmp/mysqldump _ result _ file . txt
-Routine.-R
Export stored procedures and custom functions.
mysqldump-u root-p-host = localhost-all-databases-routines
- set-charset
Add "Set Name Default _ Character Set" to the output file. By default, it is open, and the -skip-set-charset close option is used.
mysqldump-u root-p-host = localhost-all-databases
mysqldump-u root-p-host = localhost-all-databases-skip-set-charset
-Single transaction
This option submits the BEGIN SQL statement before exporting the data. Begin will not block any applications and ensure that the database is in a consistent state when exporting. Only applicable to multi-version storage engines, InnoDB only. This option and the -lock-tables option are mutually exclusive, because locking the tables will make any pending transactions commit implicitly. If you want to export a large table, you should use the-quick option together.
mysqldump-u root-p-host = localhost-all-databases-single-transaction
-Dump date
Add the export time to the output file. By default, it is open. Use the -skip-dump-date option to close it.
mysqldump-u root-p-host = localhost-all-databases
mysqldump-u root-p-host = localhost-all-databases-skip-dump-date
-Skip option
Disable the–opt option.
mysqldump-u root-p-host = localhost-all-databases-skip-opt
-Socket.-S
Specify the location of the socket file connecting mysql. The default path is/tmp/mysql.sock..
mysqldump-u root-p-host = localhost-all-databases-socket =/tmp/mysqld . sock
- tab，-T
Create a tab-delimited text file for each table under the given path. Note: Only used for mysqldump and mysqld servers running on the same machine. Note the use of-tab, and you cannot specify the-databases parameter.
mysqldump-u root-p-host = localhost test-tab = "/home/MySQL "
-Table
Override the -databases (-b) parameter and specify the name of the table to export. In future versions, table will be used instead of tables.
mysqldump-u root-p-host = localhost-databases test-tables test
-Trigger
Export trigger. This option is enabled by default and disabled with-skip-triggers.
mysqldump-u root-p-host = localhost-all-databases-triggers
- tz-utc
Set the time zone TIME_ZONE='+00:00' at the top of the export to ensure the correctness of time stamp data exported in different time zones or data moved to other time zones.
mysqldump-u root-p-host = localhost-all-databases-tz-utc
-users.-U
Specifies the user name of the connection.
-Detailed.-v
Output various platform information.
-Version,-V
Output mysqldump version information and exit.
-where?-w
Dumps only the records selected by the given WHERE condition. Note that if a condition contains special spaces or characters for the command interpreter, be sure to refer to it.
mysqldump-u root-p-host = localhost-all-databases-where = " user = ' root ' "
- xml，-X
Export XML format.
mysqldump-u root-p-host = localhost-all-databases-XML
-Plug-in Directory
Directory of client plug-ins for compatibility with different plug-in versions.
mysqldump-u root-p-host = localhost-all-databases-plugin _ dir = "/usr/local/lib/plugin "
-Default authentication
Default permissions for client plug-ins.
mysqldump-u root-p-host = localhost-all-databases-default-auth = "/usr/local/lib/plugin/& lt； Plug-in >
Copy code
error processing
1. Unknown option'-No beep'
The first method: delete the no-beep parameter under my.ini[client];
The second method: add the-no-defaults parameter after mysqldump.
abstract
This paper lists some commonly used export operations, and many other parameters are also commonly used, including "-add-add-drop-database", "apply-apply-slave-statements" and "-triggers". The import and export function of the client is also a good choice. For example, there are many file formats to choose from in the export wizard of workbench and navicatnavicat.
It is a good method to export and import data quickly with-tab, which will generate an sql table structure file and a text data file in the specified directory.