Mysqldump's Single-transaction detailed

Mysqldump's single-transaction detailed single-transaction

• Turn on the General Log option

2. To view the current general log situation

   mysql> Show variables span class= "kw" >like   '%general_log% ' ; +------------------+------- -------------------------------------+| variable_name | value  |+------------------+----------------- ---------------------------+| General_log | off  | | General_log_file | /data/mysqldata/3306 /general_statement.log |+------------------+------ --------------------------------------+  2  rows  in  set  (0.00  sec)  

4. option to turn on general log

   setglobal general_log=on;

6. Use the mysqldump command:.

   [[email protected] ~]$ /usr/local/mysql/bin/mysqldump -uroot -p‘[email protected]‘ -S /data/mysqldata/3306--single-transaction --default-character-set=utf8 zdemo student > /tmp/studentbackup.sql

   Two parameters are used

• --single-transaction
  ?? This option sets the isolation level to: repeatable READ. And then execute a START transaction statement, so that the entire data in the dump process to ensure consistency of data, this option is useful for INNODB data tables, and does not lock the table. But this does not guarantee data consistency between the MyISAM table and the memory table.
  ?? To ensure that --single-transaction the dump file is valid when using the command. The following statements are not required ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE because consistent reads cannot isolate the above statements. Therefore, if you use the above statement during the dump process, it may result in inconsistent or unusable file data from the dump.
  ?? How to verify the above procedure, you can open the general log to see if the process as mentioned above.

• --default-character-set=utf8
  The exported dump file character set is Uft8, and the command that verifies the character set of the file can be usedfile -i

The general query log file is as follows:

2018-06-18t11: 42:31.035205z 9163 Query/*!40100 SET @ @SQL_MODE ="'*/2018-06-18t11: 42:31.036090z 9163 Query/*!40103 SET time_zone=' +00:00 '*/2018-06-18t11: 42:31.036905z 9163 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2018-06-18t11: 42:31.037521z 9163 Query SET session net_read_timeout=, session net_write_timeout= 7002018-06-18t11: 42:31.038398z 9163 Query SET SESSION TRANSACTION isolation level repeatable READ2018-06-18t11: 42:31.038977z 9163 Query START TRANSACTION/*!40100 with consistent SNAPSHOT */2018-06-18t11: 42:31.039859z 9163 Query SHOW VARIABLES like' Gtid\_mode '2018-06-18t11: 42:31.058093z 9163 Query UNLOCK Tables Intermediate log omitted......2018-06-18t11: 42:31.084432z 9163 Query savepoint sp2018-06-18t11: 42:31.087632z 9163 Query Show CREATE table' Student '2018-06-18t11: 42:31.088094z 9163 Query SET SESSION character_set_results =' UTF8 '2018-06-18t11: 42:31.088407z 9163 Query Show fields from' Student '2018-06-18t11: 42:31.092360z 9163 Query Show fields from' Student '2018-06-18t11: 42:31.094718z 9163 Query SELECT/*!40001 sql_no_cache */* FROM' Student '2018-06-18t11: 42:32.815435z 9163 Query ROLLBACK to SavePoint SP2018-06-18t11: 42:32.815546z 9163 Query RELEASE savepoint sp

From the above log analysis:
Set SESSION TRANSACTION Isolation level repeatable READ setting isolation levels to repeatable READ
Start TRANSACTION opens the transaction
?? The implementation of the transaction is a matter of MVCC mechanism through the INNODB storage engine, the details are as follows: InnoDB is a multi-versioned storage engine. It retains an older version of the data information for the modified row. Used to support transactional attributes. For example: Concurrency and data rollback. This information remains in the table space in the data structure, which is called the rollback segment rollback segment. (There is also a similar data structure in Oracle).
?? When a transaction needs to be rolled back, InnoDB uses the rollback segment information to perform the undo operation. For a row, InnoDB will use the previous version of the information to secure read consistency (consistent read).
?? The Undo log is divided into two parts in the rollback segment (rollback segment), part called Insert Undo log (insert undo logs), and the other part is called Update undo log (update undo logs). Inserting the Undo log is used only for transaction rollback, such as when a transaction is committed, the log can be discarded. Update the Undo log with read consistency, InnoDB specifies a snapshot of the snapshot that was built from the update to the previous version of the data row in the Undo log. Read consistency is ensured by snapshots of earlier versions of the data row, which can be discarded if this transactional data protection is not required.

Log analysis of the save point

SAVEPOINT SP......中间日志省略...SELECT`student`......中间日志省略...ROLLBACK TO SAVEPOINT spRELEASE SAVEPOINT sp

You can see that data consistency data is guaranteed through repeatable read transactions, and then
Set the SavePoint SP, and when the snapshot of all the data is read, the save point SP is rolled back. Can be likened to the game after the archive, and then take the file back into a game file, delete this file. Can be used as this file does not exist within this game.

View current session Level

# # Session-level current transaction level mysql> show variables like '%isolation% ';+-----------------------+-----------------+| variable_name |Value|+-----------------------+-----------------+| transaction_isolation | Repeatable-read |+-----------------------+-----------------+1 Row inch Set(0.03SEC) # # System global level current transaction level mysql> showGlobalVariables like '%isolation% ';+-----------------------+-----------------+| variable_name |Value|+-----------------------+-----------------+| transaction_isolation | Repeatable-read +-----------------------+-----------------+1 Row inch Set(0.11SEC) # # Modify the global level transaction level Mysql>setGlobaltransaction_isolation=' read-committed '

Even if the global transaction level is modified, the mysqldump export will also set the isolation transaction level to: Repeatable READ. Used to ensure read consistency of the data.

Character set type of the exported file

[[email protected]studentbackup.sql: text/plain;charset=utf-8

Mysqldump's Single-transaction detailed