A brief analysis of MySQL mysqldump principle

Baidu Encyclopedia So to explain: the entire database is loaded into a separate text file. This file contains all the SQL commands needed to rebuild your database. This command obtains all the schemas (schema, which are explained later) and converts them into DDL syntax (create statement, which is the database definition statement), obtains all the data, and creates insert statements from the data

The approximate flow of mysqldump is organized as follows (vernacular):
1: Connect the database, verify the account, password, IP
2: Enter the INFORMATION_SCHEMA library to get information about the database to be backed up, including stored procedures, views, tables
3: Enter the INFORMATION_SCHEMA library to get information about the field name, field type, and so on for each table
4: Querying data for each table, select Sql_no_cache from Tbname
5: Stitching into DDL SQL
6: Write Backup file

A few important parameters must be understood

One thing that needs to be explained is that the single-transaction level is bigger than the lock because-sing** won't work with--x.

--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, would print it as a
Change MASTER command; If equal to 2, that command would
Be prefixed with a comment symbol. This option would turn
--lock-all-tables on, unless--single-transaction is
Specified too (in which case a global read lock are only
Taken a short time at the beginning of the dump; Don ' t
Forget to read about--single-transaction below). In all
Cases, any action on logs would happen at the exact moment
of the dump. Option automatically turns--lock-tables off.

This parameter runs--lock-all-tables and writes the master's Binlog and postion information to the head of the SQL file, unless it is combined with--single-transaction (but does not mean that the table is completely locked, A short global read lock is added when executing)

--single-transaction

This option submits a begin SQL statement before exporting the data, and begin does not block any applications and ensures consistent state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB.
This option and the--lock-tables option are mutually exclusive because lock tables causes any pending transactions to be implicitly committed.
If you want to export large tables, you should use the--quick option together

--skip-opt

Encountered several times mysqldump out of the backup can not be imported, the same library if mysqldump--skip-opt can be imported, analysis of two different backup files, found that--skip-opt is a strip of INSERT statements, But--opt a table has only 1 INSERT statements, so there may be too many table data overflow, you can properly adjust

Table_cache value in my.cnf try to avoid this situation, the production server I set is table_cache=512, memory 2G, problem resolution. --opt import faster than--skip-opt, after all, is optimized, hehe. Table_cache also involves the optimization of MySQL, to be adapted to the actual application.

--opt
This is just a shortcut option, which is equivalent to adding--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables at the same time-- Quick--set-charset option. This option allows mysqldump to export data quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with--skip-opt. Note that if you run mysqldump without specifying the--quick or--opt option, the entire result set is placed in memory. Problems can occur if you export a large database.

Before starting the export, the commit request locks all tables in all databases to ensure data consistency. This is a global read lock, and the--single-transaction and--lock-tables options are turned off automatically.
--lock-tables
It is similar to--lock-all-tables, but locks the currently exported data table instead of locking down all the tables in the library at once. This option applies only to the MyISAM table, if the Innodb table is available with the--single-transaction option.

This article from "Yun Weibang" blog, declined reprint!

A brief analysis of MySQL mysqldump principle