Mysqldump--single-transaction and--lock-tables parameters detailed

Mysqldump backup principle mysqldump in the backup process, is to use the query to back up the relevant table data, and then export, stitching into the form of INSERT statements for backup.   about the--single-transaction and--lock-tables--single-transaction options and--lock-tables options are mutually exclusive, because lock tables causes any pending transactions to be implicitly committed   Related exploration Experiment 1. Turn on MySQL General log2. Backing up the database  /usr/local/mysql/bin/mysqldump-uroot-p-H 127.0.0.1--hex-blob-a-- Events--ignore-table=mysql.events--single-transaction--master-data=2 >/tmp/20160304alls.sql      with different parameters, the backup method is different, because mysqldump is the way to query the database backup, so we turn on General-log to see how the mysqldump completes the backup process. During the backup process, obtain the corresponding partial backup of the executed statement list for analysis   --master-data=2 backup head:  9:51:09    25646 connect    [email protected] on        25646 query   /*!40100 SET @ @SQL_MODE = ' */  & nbsp     25646 query   /*!40103 SET time_zone= ' +00:00 ' */        25646 query  &NB Sp Flush/*!40101 LOCAL */tables        25646 query    FLUSH TABLES with READ lock    &NBsp   25646 query    SET SESSION TRANSACTION Isolation level repeatable read        25646 Q uery    START TRANSACTION/*!40100 with consistent SNAPSHOT */        25646 query  &nbs P Show VARIABLES like ' Gtid\_mode '         25646 query    SHOW MASTER status    &NBSP ;   25646 query    UNLOCK tables  --master-data=2 role: This code, by flush TABLES with READ LOCK, then show MA STER STATUS, and then unlock TABLES, in the process to obtain the master at lock time Binlog, the process of acquiring Binlog position must be locked, so as to accurately obtain the binlog position of the response, and this position, Will be in the backup file--change MASTER to master_log_file= ' mysql-bin.000009 ', master_log_pos=655717, which is ideal for point-to-point backup recovery (-- master-data=2)     --single-transaction5646 query    SET SESSION TRANSACTION Isolation Level repeatable read        25646 query    START TRANSACTION/*!40100 with consistent SNAPS Hot */        25646 query    Show VARIABLES like ' Gtid\_mode '         25646 query    SHOW MASTER status  &nbsp ;     25646 query    UNLOCK tables        25646 query    SELECT logfile_gr Oup_name, file_name, Total_extents, Initial_size, ENGINE, EXTRA from INFORMATION_SCHEMA. FILES WHERE file_type = ' UNDO LOG ' and file_name are not NULL for GROUP by Logfile_group_name, file_name, ENGINE ORDER by Logfi le_group_name        25646 query    SELECT DISTINCT tablespace_name, file_name, Logfile_gro Up_name, Extent_size, Initial_size, ENGINE from INFORMATION_SCHEMA. FILES WHERE file_type = ' datafile ' ORDER by Tablespace_name, logfile_group_name        25646 Query&nbs P   Show databases        25646 query    SHOW VARIABLES like ' ndbinfo\_version '   &NB Sp     25646 Init db    hive        25646 query    SHOWCREATE DATABASE IF not EXISTS ' hive '         25646 query    savepoint sp    &N Bsp   25646 query    show tables        25646 query    Show table status like ' bu Cketing\_cols '         25646 query    SET sql_quote_show_create=1        25646 query    SET SESSION character_set_results = ' binary '         25646 query    Show create TABLE ' Bucketing_cols '         25646 query    SET SESSION character_set_results = ' UTF8 '         25646 query    Show fields from ' Bucketing_cols '         25646 query    SELECT/*!40001 sql_no_cache * * from ' bucketing_cols '         25646 QUERY&NB Sp   SET SESSION character_set_results = ' binary '         25646 query    use ' hive '   & nbsp   &NBSp 25646 query    SELECT @ @collation_database         25646 query    SHOW TRIGGERS LIK E ' Bucketing\_cols '         25646 query    SET SESSION character_set_results = ' UTF8 '   & nbsp     25646 query    ROLLBACK to SavePoint sp   when paired with the--single-transaction parameter, the flush TABLES with READ LOCK to add the start TRANSACTION statement to open single transaction &NBSP, this time the lock, just to determine the master-data in the binlog of the specific location and open the transaction, opened the transaction, the read lock has been released, and in the log can be seen in the rollback process, is rolled back to a single transaction, is also an SP point, and each time a change is made to the table and parameters, the transaction is rolled back. Through the analysis of backup log, it can be found that all of the backup phases are rollback to SP point after completion. That is, to return to the SavePoint SP point in time, that is, after the backup is complete, the backup library is still at SP points, and this so-called SP rollback, is actually called the undo in the data snapshot to achieve. Using--single-transaction ensures data consistency throughout the backup set during the backup process.     --lock-tables 14707 query    SET autocommit=0        14707 query    SELECT @ @session .tx_isolation        14707 query    rollback  &nbsp ;     14707 query    set autocommit=1        14707 query    SET Session TR Ansaction Read write        14711 query    SELECT @ @session .tx_read_only        14711 query    SET autocommit=0        14711 query    SELECT @ @sessio n.tx_isolation        14711 query    SELECT ' Org.apache.Hadoop.hive.metastore.model.MDatabase ' as ' nucleus_type ', ' A0 '. ' DESC ', ' A0 '. ' Db_location_uri ', ' A0 '. ' NAME ', ' A0 '. ' Owner_name ', ' A0 '. ' Owner_type ', ' A0 '. ' db_id ' from ' DBS ' A0 ' WHERE ' A0 '. ' NAME ' = '         14711 QUERY&N Bsp   SELECT ' org.apache.hadoop.hive.metastore.model.MTableColumnStatistics ' as ' nucleus_type ', ' A0 '. ' Avg_col_len ' , ' A0 '. ' column_name ', ' A0 '. ' Column_type ', ' A0 '. ' Db_name ', ' A0 '. ' Big_decimal_high_value ', ' A0 '. ' Big_decimal_low_ ' VALUE ', ' A0 '. ' Double_high_value ', ' A0 '. ' Double_low_value ', ' A0 '. ' last_analyzed ', ' A0 '. ' Long_high_value ', ' A0 '. ' LONG _low_value ', ' A0 '. ' Max_col_len ', ' A0 '. ' Num_distincts ', ' A0 '. ' num_falses ', ' A0 '. ' Num_nulls ', ' A0 '. ' Num_trues ', ' A0 '. ' table_name ', ' A0 '. ' cs_id ' from ' tab_col_stats ' A0 ' WHERE ' A0 '. ' db_name ' = '         14711 query    SELECT ' org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics ' as ' nucleus_type ', ' A0 '. ' Avg_col_ LEN ', ' A0 '. ' column_name ', ' A0 '. ' Column_type ', ' A0 '. ' Db_name ', ' A0 '. ' Big_decimal_high_value ', ' A0 '. ' Big_decimal_low_value ', ' A0 '. ' Double_high_value ', ' A0 '. ' Double_low_value ', ' A0 '. ' last_analyzed ', ' A0 '. ' LONG _high_value ', ' A0 '. ' Long_low_value ', ' A0 '. ' Max_col_len ', ' A0 '. ' Num_distincts ', ' A0 '. ' num_falses ', ' A0 '. ' Num_nulls ', ' A0 '. ' Num_trues ', ' A0 '. ' Partition_name ', ' A0 '. ' table_name ', ' A0 '. ' cs_id ' from ' part_col_stats ' A0 ' WHERE ' A0 '. ' Db_ NAME ' = '         14711 query    commit        14711 query  &N Bsp rollback        14711 query    SET autocommit=1        14711 query    Set session transaction read Write --lock-tables from the log can be seen, after the use of this parameter, in each of the different backup ID of the backup process, will first open a transaction, and then commit, In this way, during the whole backup process, there will be multiple transaction opening and submission process, a large number of transactions in the backup process to open and close, which will inevitably and with the--single-transaction gap. We can get the following conclusions: 1. The backup process with the--lock-tables parameter will be longer in time, and this time gap will be more pronounced when the table is 2. With the--lock-tables parameter, only the tables in the corresponding backup process will be affected, not the tables that have been backed up, but during the--single-transaction process, because a single transaction checkpoint has been identified as an SP at the beginning, The rollback process for all backup processes is an SP point, so even if the table after the backup is completed, the related locks within the transaction are affected. Thus visible in the--single-Transaction backup process, although the backup speed has been accelerated, but the impact on the database time is also relatively long. 3. With--lock-tables parameters, there is no guarantee of data consistency in the case of full-database backup. That is, all the data is not necessarily the data at a point in time.      --lock-all-tables   14707 query     SET autocommit=0                 14707 query     select @ @session .tx_isolation                14707 query     rollback        &NBSP ;       14707 query     set autocommit=1              &NBSP ; 14707 query     set session transaction read write                1 4710 query     select @ @session .tx_read_only                14710 Q uery     set autocommit=0                14710 query    & nbsp SELECT @ @session .tx_isolation  &nbsp             14710 query     select ' Org.apache.hadoop.hive.metastore.model.MDatabase ' as ' nucleus_type ', ' A0 '. ' DESC ', ' A0 '. ' Db_location_uri ', ' A0 '. ' Name ', ' A0 '. ' Owner_name ', ' A0 '. ' Owner_type ', ' A0 '. ' db_id ' from ' DBS ' A0 ' WHERE ' A0 '. ' Name ' = '                 14710 query     select ' Org.apache.hadoop.hive.metastore.model.MTableColumnStatistics ' as ' nucleus_type ', ' A0 '. ' Avg_col_len ', ' A0 '. ' COLUMN ' _name ', ' A0 '. ' Column_type ', ' A0 '. ' Db_name ', ' A0 '. ' Big_decimal_high_value ', ' A0 '. ' Big_decimal_low_value ', ' A0 '. ' Double_high_value ', ' A0 '. ' Double_low_value ', ' A0 '. ' last_analyzed ', ' A0 '. ' Long_high_value ', ' A0 '. ' Long_low_value ', ' A0 '. ' Max_col_len ', ' A0 '. ' Num_distincts ', ' A0 '. ' num_falses ', ' A0 '. ' Num_nulls ', ' A0 '. ' Num_trues ', ' A0 '. ' table_name ', ' A0 '. ' cs_id ' from ' tab_col_stats ' A0 ' WHERE ' A0 '. ' db_name ' = '                 147 Ten query     select ' Org.apache.hadoop.hiVe.metastore.model.MPartitionColumnStatistics ' as ' nucleus_type ', ' A0 '. ' Avg_col_len ', ' A0 '. ' column_name ', ' A0 '. ' Column_type ', ' A0 '. ' Db_name ', ' A0 '. ' Big_decimal_high_value ', ' A0 '. ' Big_decimal_low_value ', ' A0 '. ' Double_high_value ', ' A0 '. ' Double_low_value ', ' A0 '. ' last_analyzed ', ' A0 '. ' Long_high_value ', ' A0 '. ' Long_low_value ', ' A0 '. ' Max_col_len ' , ' A0 '. ' Num_distincts ', ' A0 '. ' num_falses ', ' A0 '. ' Num_nulls ', ' A0 '. ' Num_trues ', ' A0 '. ' Partition_name ', ' A0 '. ' Table_ ' NAME ', ' A0 '. ' cs_id ' from ' part_col_stats ' A0 ' WHERE ' A0 '. ' db_name ' = '               &N Bsp 14710 query     commit                14710 query    &nbs p;rollback                14710 query     set autocommit=1  &N Bsp             14710 query     set session transaction read write   though official Fang said that--lock-all-tables all the tables were locked, but I found that the locking process and--lock-tables are almost identical.     If you feel like it's a mess, look at the conclusion.:  Summary: 1. To achieve a point-to-point backup recovery, or for a full backup of a data at a point, you must use the--single-transaction and--master-data=2 two parameters, such as the master-slave construction time, because in the--single-transaction parameter, No guarantee of transactional consistency at the point of backup for data backup 2.--single-transaction backups have a long impact on the database, but after adding this parameter, the backup speed for multiple large tables will certainly accelerate. 3.--lock-all-tables does not keep all data consistent for a certain point in time.

Source: http://blog.csdn.net/u013983450/article/details/52179442

Mysqldump--single-transaction and--lock-tables parameters detailed