This article goes to: http://blog.csdn.net/cug_jiang126com/article/details/49824471
In the mysqldump process, before in fact has not been very understanding why add--single-transaction can ensure that the InnoDB data is exactly the same, and MyISAM engine can not guarantee, must add--lock-all-tables, The previous period of time has been spent looking at the entire mysqldump process in detail.
Understanding Master-data and--dump-slave
--master-data=2 indicates that the main library's Binlog and POS points are recorded during the dump and commented out the line in the dump file;
--master-data=1 indicates that the Binlog and POS points of the main library are recorded during the dump, and this line is not commented out in the dump file, that is, the recovery will be performed;
--dump-slave=2 means that during the dump process, the dump,mysqldump process from the library is also executed from the library, recording the Binlog and Pos points at the time of the main library, and commenting out the line in the dump file;
--dump-slave=1 means that during the dump process, the dump,mysqldump process from the library is also executed from the library, recording the Binlog and Pos points at the time of the main library, and not commenting out the line in the dump file;
Note: When performing a backup from a library, that is--dump-slave=2, the entire dump process is the state of Stop Io_thread
In- depth understanding of--single-transaction:
Open General_log, prepare a small db of data, turn on backup, add--single-transaction and--master-data=2 parameters, view general_log, information as follows, each step adds my understanding
The entire dump process is the same connection ID 32, which guarantees that the session-level variables are not affected by the other connections when they are set
Thread_id:32
Argument: [email protected] on
14. Row ***************************
Thread_id:32
Argument:/*!40100 SET @ @SQL_MODE = ' * *
15. Row ***************************
Thread_id:32
Argument:/*!40103 SET time_zone= ' +00:00 ' * *
16. Row ***************************
Thread_id:32
Argument:flush/*!40101 LOCAL */TABLES
17. Row ***************************
Thread_id:32
Argument:flush TABLES with READ LOCK
Annotations: Because--master-data=2 is turned on, the flush tables with read lock is required to lock the entire library, recording the Master_log_file and Master_log_pos points at that time
18. Row ***************************
Thread_id:32
Argument:set SESSION TRANSACTION Isolation level repeatable READ
Annotations: The role of the--single-transaction parameter, set the isolation level of the transaction is repeatable read, that is, repeatable read, so that all the same query in a transaction to read the same data, it is probably guaranteed during dump, If the other InnoDB engine thread modifies the data of the table and commits it, there is no effect on the data of the dump thread, but this is not enough, and you need to look at the next
19. Row ***************************
Thread_id:32
Argument:start TRANSACTION/*!40100 with consistent SNAPSHOT * *
This opens a transaction and sets with consistent snapshot as the snapshot level (if the MySQL version is above a certain version value, I am not sure what version 40100 represents). Imagine, if only repeatable read, then at the beginning of the transaction is not dump data, when the other threads modify and commit the data, then the first query results are submitted by other threads, and with consistent snapshot can be guaranteed when the transaction is opened, The result of the first query is the data A at the beginning of the transaction, even if the other thread changes its data to B, the result of the check is still a, the specific test to see my test results below
20. Row ***************************
Thread_id:32
Argument:show MASTER STATUS
This time to execute this command to record the Master_log_file and Master_log_pos points at the time, to note why the record, and not between the row and the row between the record, the individual think it should be possible, here is the test results, start Transaction does not produce Binlog movement, and the actions of both row and row are in the same thread ID
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1690 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Mysql> START TRANSACTION/*!40100 with consistent SNAPSHOT */;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1690 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
21st. Row ***************************
Thread_id:32
Argument:unlock TABLES
When the record is complete, it is released immediately, because it is already in one transaction, and the other threads are already repeatable in this thread, which is the reason why this step must be after the rows, and if both rows and rows are not there before the rows, Since the transaction is not yet open, once released, other threads can immediately change the data, thus making it impossible to guarantee the most accurate POS point at which transactions are opened. 22. Row ***************************
Thread_id:32
Argument:select Logfile_group_name, file_name, Total_extents, Initial_size, ENGINE, EXTRA from INFORMATION_SCHEMA. FILES WHERE file_type = ' UNDO LOG ' and file_name is not NULL and Logfile_group_name in (SELECT DISTINCT logfile_group_name From INFORMATION_SCHEMA. FILES WHERE file_type = ' datafile ' and Tablespace_name in (SELECT DISTINCT tablespace_name from INFORMATION_SCHEMA. Partitions WHERE table_schema= ' MySQL ' and table_name in (' user '))] GROUP by Logfile_group_name, file_name, ENGINE ORDER by Logfile_group_name
23. Row ***************************
Thread_id:32
Argument:select DISTINCT tablespace_name, file_name, Logfile_group_name, Extent_size, Initial_size, ENGINE from Information_schema. FILES WHERE file_type = ' datafile ' and Tablespace_name in (SELECT DISTINCT tablespace_name from INFORMATION_SCHEMA. Partitions WHERE table_schema= ' MySQL ' and table_name in (' user ') ORDER by Tablespace_name, Logfile_group_name
24. Row ***************************
Thread_id:32
Argument:mysql
25. Row ***************************
Thread_id:32
Argument:show TABLES like ' user '
26. Row ***************************
Thread_id:32
Argument:show table status like ' user '
Dump table before all need to show the respective information, to ensure that the table, view, etc. are not damaged, available, each step wrong mysqldump will error and interrupt, give the corresponding error code, common Myqldump error Please refer to my other blog/http/ blog.csdn.net/cug_jiang126com/article/details/49359699
27. Row ***************************
Thread_id:32
Argument:set OPTION sql_quote_show_create=1
28. Row ***************************
Thread_id:32
Argument:set SESSION character_set_results = ' binary '
29. Row ***************************
Thread_id:32
Argument:show CREATE table ' user '
30. Row ***************************
Thread_id:32
Argument:set SESSION character_set_results = ' UTF8 '
31. Row ***************************
Thread_id:32
Argument:show fields from ' user '
32. Row ***************************
Thread_id:32
Argument:select/*!40001 Sql_no_cache */* from ' user '
This is the information we see when we show processlist, and how the data is dump into a local file via a SELECT statement, and it's turned into the corresponding create and INSERT statements, which is the work of the Mysqldump client tool. There's no discussion here.
33. Row ***************************
Finally, there is no commit, because in the entire transaction, there is no data modification, just to ensure repeatable read of the backup point-in-time consistency of the snapshot, dump after the completion of the submission should be irrelevant.
Why is the MyISAM engine not guaranteed to have a consistent backup under--single-transaction?
Because it does not support the transaction at all, nature will not be able to achieve the above process, although the addition of the--single-transaction parameter MyISAM table processing and the above is fully consistent, but because the transaction is not supported, the entire dump process can not guarantee repeatable read, Unable to get a consistent backup. While InnoDB in the backup process, while other threads are also writing data, the data from the dump is guaranteed to be the Binlog POS data at the beginning of the backup.
How does the MyISAM engine achieve consistent data if it is to be guaranteed?
It is done by adding--lock-all-tables, so that after the flush tables with read lock, until the entire dump process is over, the disconnected thread will not unlock the tables release lock (no need to actively send unlock tables instructions), The entire dump process is not writable by other threads, thus ensuring data consistency
What if I had to add the--single-transaction parameter to the Mysiam engine and then use this backup to create a library or restore to a specified point in time?
My personal understanding is that if the entire dump process only a simple insert operation, there is no relationship, there will certainly be a lot of primary key repeat errors, skip or ignore it just fine. If it is an update operation, it will be a problem, in several cases
1) If it is a point-in-time recovery, it is assumed that the entire dump process has an operation such as update a set id=5 where id=4, which is equivalent to repeating the operation twice, and should be less problematic
2) If it is created from the library, encountered above the SQL from the library will be error, cannot find the record, then skip just fine
3) Whether to restore or create from the library, if the dump process has update a set id=id+5 operations, then there is a problem, repeat two times, the data all changed.
deep understanding of--lock-all-tables
Open General_log, prepare a small db of data, turn on backup, add--lock-all-tables (in fact the default setting) and--master-data=2 parameters, view general_log, information as follows, Understand how--lock-all-tables guarantees data consistency
Mysql> Select Thread_id,argument from General_log where thread_id=185\g
1. Row ***************************
thread_id:185
Argument: [email protected] on
2. Row ***************************
thread_id:185
Argument:/*!40100 SET @ @SQL_MODE = ' * *
3. Row ***************************
thread_id:185
Argument:/*!40103 SET time_zone= ' +00:00 ' * *
4. Row ***************************
thread_id:185
Argument:flush/*!40101 LOCAL */TABLES
5. Row ***************************
thread_id:185
Argument:flush TABLES with READ LOCK
This flush tables with read lock will not actively unlock tables, ensuring that the entire dump process is not changed or the concept of a transaction
6. Row ***************************
thread_id:185
Argument:show MASTER STATUS
Also record the location of the main library
7. Row ***************************
thread_id:185
Argument:select Logfile_group_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
8. Row ***************************
thread_id:185
Argument:select DISTINCT tablespace_name, file_name, Logfile_group_name, Extent_size, Initial_size, ENGINE from Information_schema. FILES WHERE file_type = ' datafile ' ORDER by Tablespace_name, Logfile_group_name
9. Row ***************************
thread_id:185
Argument:show DATABASES
10. Row ***************************
thread_id:185
Argument:jjj
11. Row ***************************
thread_id:185
Argument:show CREATE DATABASE IF not EXISTS ' JJJ '
test Repeatable Read and snapshot reads (with consistent SNAPSHOT)
Preparation Work 3.1 (test-readable)Session 1:
Mysql> select * from XX;
+------+
| ID |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
Mysql> SET SESSION TRANSACTION isolation level repeatable READ;
Query OK, 0 rows Affected (0.00 sec)
To set the transaction isolation level to repeatable READ
Mysql> START TRANSACTION;
Query OK, 0 rows Affected (0.00 sec)
Let's not open a snapshot, read the observation.
Session 2:
mysql> INSERT INTO XX values (5);
Query OK, 1 row Affected (0.00 sec)
Session 1:
Mysql> select * from XX;
+------+
| ID |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in Set (0.00 sec)
Annotations: Because there is no snapshot read, so when session 2 has data updates, you can find this data, and then
Down we continue to insert data in Session 2
Session 2:
mysql> INSERT INTO XX values (6);
Query OK, 1 row Affected (0.00 sec)
Then we look at session 1 data.
Session 1
Mysql> select * from XX;
+------+
| ID |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in Set (0.00 sec)
Query discovery is still only 5, which means that it can be implemented in a repeatable way.
Prepare to Work 3.2 (test snapshot Read)Session 1
Mysql> select * from XX;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)
Mysql> SET SESSION TRANSACTION isolation level repeatable READ;
Query OK, 0 rows Affected (0.00 sec)
Mysql> START TRANSACTION/*!40100 with consistent SNAPSHOT */;
Query OK, 0 rows Affected (0.00 sec)
Then we insert the data in Session 2
Session 2:
mysql> INSERT INTO XX values (2);
Query OK, 1 row Affected (0.00 sec)
Then we look at the results of Session 1.
Session 1:
Mysql> select * from XX;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)
There is only one data that proves that the snapshot read
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)
Mysql> select * from XX;
+------+
| ID |
+------+
| 1 |
| 2 |
+------+
2 rows in Set (0.00 sec)
Transaction 1 commits before a second record is seen
[Turn] in-depth understanding of mysqldump principle--single-transaction--lock-all-tables--master-data
