"20180306" MySQL some essays about Gtid

Some information about MySQL Gtid

2. Gtid Persistent Media has two, one is table mysql.gtid_executed, the other is Binlog log.
4. The Gtid information in table mysql.gtid_executed tables is not updated in real time and is only recorded in table mysql.gtid_executed when the Binlog binary log is cut.
6. The Gtid information in the Binlog binary log is generated after commit, and at this time the last commit and Seqeue number are generated (both values are related to MySQL's group commit and parallel replication)
8. Binlog_gtid_simple_recovery When this parameter is not turned on, MySQL restart or restore will scan all binlog for gtid_executed information, which can take a long time. When this parameter is turned on, MySQL will only scan the first binlog and the last Binlog file. It is also important to note that when you open gtid in the middle, you will also scan all the previous binlog until you get
10. Sql_log_bin supports session-level dynamic modification, and after this parameter is closed, the current session does not produce Gtid.
12. Gtid master-slave replication.
    • Gtid is not generated after the main library shuts down Binlog, and gtid_executed and gtid_purged and table mysql.gtid_executed are updated in real time when Binlog is closed from the library.
    • Reset Master will clear all the Binlog logs and will also be aware of all gtid_executed and gtid_purged information. When setting global gtid_purged information, it is important to note that gtid_executed must be empty, and the values of gtid_purged and gtid_exectued are consistent after the set is complete.
14. Previous Gtid Event is a collection of all Gtid that is contained at the beginning of each binlog that describes all previous binlog (including Binlog that has been deleted). In 5.6 If Gtid is not turned on, then Binlog will not contain this previous Gtid event, but in 5.7 does not open Gtid will also contain this previous Gtid event, the actual change in the meaning of this is also very large, Simply put, he provides the basis for a fast scan Binlog (binlog_gtid_simple_recovery=ture) to get the right gtid set, otherwise it will scan a lot of binlog, wasting I/O performance, which is a very serious problem in 5.6. Another problem to be aware of is that when the Gtid is not turned on, the value of Previous Gtid event shows the empty, this time midway Open Gtid, MySQL restart read binlog get gtid_ Executed the message, it will reread the old Binlog until previous Gtid event and get Gtid event.
16. Gtid_mode the value of each online modification will be taken as binlog cutting, if the occurrence of binlog Delete can also rely on Previous Gtid event quickly and accurately find gtid_purged (gtid_state.lost_gtids)
18. The data in show slave status is read from memory. When Relay_log_info_repository is table, show slave status and table data may be inconsistent. This is mainly because the non-transactional engine still adheres to the frequency that Sync_relay_log_info writes to the table.
20. Mysql.gtid_executed indicates that 5.7.5 only started an optimization, before the table Gtid persistent media is only binlog, but for the slave in replication sometimes do not need to set the Cascade master and slave, so there is no need to open log_slave_ The update parameter, which does not have an open parameter, reduces the disk and IO. Reduces the burden and improves performance.

Problem:

1. Restarts or restores whether the values of gtid_executed and gtid_purged are obtained from the table mysql.gtid_executed or from the Binlog binary.
   • The database restart will read the Binlog and mysql.gtid_executed information separately, but mainly to Binlog.
2. Mysqldump backup is not set when--set-gtid-purged=off will be backed up when set @SESSION. Sql_log_\bin=0, all subsequent operations at this time will not be recorded in the Binlog and will not generate Gtid, and set @ @GLOBAL. The value of the gtid_purged.
   • One thing to note here is that the Mysql.executed table information is also backed up when a full-volume backup is made. One thing to note here is that Binlog is not cut at the time of the backup, but because the Gtid information recorded by mysql.gtid_executed is not real-time, it restores the backup mysql.gtid_ The value in the executed table is less than or equal to the actual gtid_executed and gtid_purged values, and this time there is no binlog generated because of the recovery, resulting in the value of table mysql.gtid_executed and actually gtid_ The values for executed and gtid_purged are not the same.
   • This time can be restarted from the library, from the library will be error 1236 errors.

"20180306" MySQL some essays about Gtid