[MySQL 5.6] Gtid implementation, operation and maintenance changes and existing bugs

[MySQL 5.6] Gtid implementation, operation and maintenance changes and existing bugs

Http://www.tuicool.com/articles/NjqQju

Since not much in-depth attention to gtid, here to make up for themselves, this article is I look at the document and code collation records.

The main purpose of this article is to write down the backtrace associated with Gtid for future troubleshooting. In addition, we will discuss the current bug in the MySQL5.6.11 version. Preface: What is Gtid what is Gtid, in short, is the global transaction ID (transaction identifier), originally implemented by Google, Official MySQL added this feature in 5.6, the reason for this article is that 5.6 introduced a whole bunch of gtid related variables, deeply puzzled. In the middle of last year, also wrote a short blog, roughly introduced the next gtid is what, http://mysqllover.com/?p=87. This article also does not intend to introduce too many words, because there are already a lot of similar articles on the network. GTID format resembles: 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1 This is a GTID record generated on one of my servers, and the type of event it behaves in Binlog is: gtid_log_event: GTID used to represent subsequent transactions there are also two types of GTID events: anonymous_gtid_log_event: Anonymous GTID Event Type (no matter what) Previous_gtids_log_event: Used to represent the Gtid collection that was executed before the current Binlog file, recorded in the Binlog file header, for example: # at 120#130502 23:23:27 Server ID 119821 end_log_pos 231 CRC32 0x4f33bb48 previous-gtids # 10A27632-A909-11E2-8BC 7-0010184e9e08:1, # 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1-1129 This string, separated by ":", is preceded by the server's Server_uuid, which is a 128-bit random string, generated at the first boot (function Generate_server_uuid), and the corresponding variables is a read-only variable Server_ Uuid. It can guarantee the global uniqueness with very high probability and coexist in the file DATA/AUTO.CNF. So be careful to protect this file from being deleted or modified, or you will be in trouble. The second part is a self-increment transaction ID number, and the transaction ID number +server_uuid to uniquely indicate a transaction. In addition to the individual Gtid, there is a concept of a gtid set. The representation of a GTID set is similar to: 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1-31 gtid_executed and gtid_purged are typical GTID set type variables; In a replication topology, Gtid_executed may contain several sets of data, such as:

Mysql> show global variables like '%gtid_executed% ' \g

1. Row ***************************variable_name:gtid_executed VALUE:10A27632-A909-11E2-8BC7-0010184E9E08:1-4,153C04 06-A909-11E2-8BC7-0010184E9E08:1-3,7A07CD08-AC1B-11E2-9FCF-0010184E9E08:1-31,

F914fb74-a908-11e2-8bc6-0010184e9e08:1

The topics discussed in this article include:

I. Gtid generation and recording on the main library two. How the repository uses Gtid replication three. Changes in the primary and standby operations four. MySQL5.6.11 existing bug One, Gtid A on the main library. The gtid of each transaction on the main library of the related variables includes the changed parts and the invariant parts. Before the discussion, be clear about the four variables maintained by Gtid: gtid_purged : The Binlog transaction has been deleted, it is a subset of the gtid_executed, and the variable cannot be set starting from MySQL5.6.9. gtid_owned : Represents the Gtid of the transaction being executed and the corresponding thread ID. For example, the following:

Mysql> show global variables like '%gtid_owned% ' \g

1. Row ***************************variable_name:gtid_owned Value:7a07cd08-ac1b-11e2-9fcf-0010184e9e08:11560057#67:1 1560038#89:11560059#7:11560034#32:11560053#56:11560052#112:11560055#128:11560054#65:11559997#96:11560056# 90:11560051#85:11560058#39:11560061#12:11560060#125:11560035#62:11560062#51 row in Set (0.01 sec) gtid_executed Represents a transaction that has already been performed on the instance, and the reset MASTER will empty the variable; We can also influence gtid_executed by setting Gtid_next to execute an empty transaction. gtid_next is the session level variable, indicating that the next GTID to be used in memory also maintains a global object gtid_state corresponding to Gtid_purged, gtid_owned, gtid_executed. Gtid_state maintains three sets, of which logged_gtids corresponds to gtid_executed, lost_gtids corresponds to Gtid_purged,owned_gtids. How to assign and use Gtid_ownedb In the process of executing a transaction in the main library, about Gtid mainly involves the following parts: Transaction begins when the first SQL is executed , assign a Group (Group_cache::add_logged_group) to the group_cache of the Binlog cache before writing the first "BEGIN" Query EVENT, and write a gtid_log_ event, the transaction is not assigned at this time id,backtrace the following: HANDLER::HA_WRITE_ROW-&GT;BINLOG_LOG_ROW-&GT;WRITE_LOCKED_TABLE_MAPS-&GT;THD:: Binlog_write_table_map->binlog_start_trans_and_stmt->binlog_cache_data::write_event->group_cache::add _logged_group It is unclear when a transaction will have multiple Gtid group_cache. in the flush phase of Binlog Group commit :  The first step, call Group_cache::generate_automatic_gno to generates the current line into a gtid, assign it to Thd->owned_gtid, and add it to the Owned_gtids, BackTrace as follows: Mysql_bin_log::p rocess_flush_stage_queue->mysql_bin_log::flush_thread_caches->binlog_cache_ mngr::flush->binlog_cache_data::flush->gtid_before_write_cache->group_cache::generate_automatic_gno- >Gtid_state::acquire_ownership->Owned_gtids::add_gtid_owner   that is, until the transaction is complete, prepare to brush the Binlog to Binlog Cache, the Gtid is assigned to it. When the Gtid_next type is automatic, the call Generate_automatic_gno generates the transaction ID (GNO), and the allocation process is probably as follows: 1.gtid_state->lock _sidno (Automatic_gtid.sidno), for the current Sidno lock, the allocation process is mutually exclusive 2.gtid_state->get_automatic_gno (AUTOMATIC_GTID.SIDNO); Get transactions id        |–> initialization candidate (candidate) Gno for 1        |–> from logged_gtids[$sidno] For each GNO interval (iv):               |–> when candidate < Iv->start (or Max_gno, If IV is NULL), determine if candidate is occupied, if not, use the candidate, return from function, otherwise candidate++, continue this step         |–> Set the candidate toIv->end,iv point to the next interval, continue to the second step         from this process, it can be seen that there is a scene where fragmentation exists, and the possible distribution of Gno is not the largest gno in the world. However, in the case where the main library does not manually set Gtid_next, we can assume that the Gno on the main library is always incremented.  3.gtid_state->acquire_ownership (THD, Automatic_gtid);         |–> join to Owned_ Gtids in the collection (Owned_gtids.add_gtid_owner) and assigns a value to thd->owned_gtid= Gtid4.gtid_state->unlock_sidno (automatic_ GTID.SIDNO);   Unlock the second step, call Gtid_state::update_on_flush to add the current transaction's grid to the Logged_gtids, backtrace as follows: Mysql_bin_log::p rocess_flush_ stage_queue->mysql_bin_log::flush_thread_caches->binlog_cache_mngr::flush->binlog_cache_data::flush- >MYSQL_BIN_LOG::write_cache->Gtid_state::update_on_flush  in the commit phase of the bin log Group commitCall Gtid_state::update_owned_gtids_impl from Owned_gtids to remove the gtid of the current transaction, BackTrace as follows:mysql_bin_log::ordered_commit-> Mysql_bin_log::finish_commit->gtid_state::update_owned_gtids_impl the above steps involve modifications to Logged_gtids and Owned_gtids. Lost_gtids, in addition to maintenance at startup, is maintained when performing purge operations. For example, when we execute purge binary logs to ' mysql-bin.000205′, Mysql-bin.index is first updated, and then the first Binlog file is found based on the index file Previous_gtids_log _event event, update Lost_gtids collection, BackTrace as follows: Purge_master_logs->mysql_bin_log::p urge_logs->mysql_bin_log::init_ gtid_sets->read_gtids_from_binlog->previous_gtids_log_event::add_to_set->gtid_set::add_gtid_encoding- >gtid_set::add_gno_interval about Binlog Group Commit, see previously written blogs:http://mysqllover.com/?p=581 C. How to persist gtid when you restart MySQL, we see that gtid_executed and gtid_purged are consistent before restarting. Persistent Gtid is managed through the global object gtid_state. Gtid_state calls the function Gtid_server_init allocates memory at system startup, and if Binlog is turned on, further initialization is done: quoted code:

5419 if (Mysql_bin_log.init_gtid_sets (

5420             Const_cast<Gtid_set *> (gtid_ State->get_logged_gtids ()),5421             Const_cast<gtid_set *> (Gtid_state->get_lost_gtids ()),5422              opt_master_verify_checksum,5423              true/*true=need lock*/)) 5424         Unireg_ Abort (1);  gtid_state contains 3 Gtid collections: Logged_gtids, lost_gtids, owned_gtids, the first two are gtid_set types, owned_ The Gtids type for owned_gtids mysql_bin_log::init_gtid_sets is primarily used to initialize Logged_gtids and Lost_gtids, the function's logic is simply described under:  1. Scan the Mysql-index file, collect the Binlog filename, and add it to filename_list 2. Start reading from the last file, calling functions read_gtids_from_binlog:      |– > Open the Binlog file if read to Previous_gtids_log_event event           (1) Anyway, add it to Logged_gtids (prev_ Gtids_ev->add_to_set (All_gtids))           (2) If the file is the first Binlog file, add it to Lost_gtids (prev_gtids_ev->add_to _set (prev_gtids)) .           |–> get gtid_log_event events           ( 1) Read the event corresponding to the sidno,sidno= Gtid_ev->get_sidno (false);                This is a 32-bit integral type, Use Sidno to represent a server_uuid, starting from 1, which is mainly in memory-saving considerations. Maintained in the global object Global_sid_map.                 When Sidno is not joined to map, call Global_sid_map->add_sid (SID), Sidno increments from 1.            (2) All_gtids->ensure_sidno (Sidno)             &N Bsp  all_gtids is a gtid_set type and can be understood as a collection, Ensure_sidno is to ensure that this collection can hold at least sidno elements            (3 ) All_gtids->_add_gtid (Sidno, Gtid_ev->get_gno ()                  Add the Gtid recorded in the event to All_gtids[sidno] (eventually call Gtid_set::add_gno_interval, where you actually add (Gno, gno+1) Such an interval, here   &NBsp             covers interval merging, intersection, etc.    ) when the first file has neither Previous_gtids_log_event nor Gtid_log_ event, continue reading the previous file if only the Previous_gtids_log_event event exists, the function Read_gtids_from_binlog returns Got_previous_gtids if there is a gtid_log_ event, return got_gtids  There is obviously a problem here, that is, if we do not use Gtid_mode before the reboot and generate a lot of binlog, we may need to scan a large number of binlog files after this reboot. This is a very obvious bug, and we'll focus on that later.  3. If the second scan does not reach the first file, start scanning from the first file, similar to the 2nd step, read to the first Previous_gtids_log_event event, and add to Lost_gtids.   Simply put, if we open the Gtid_mode, then only need to read the first Binlog file and the last Binlog file, you can determine the Logged_gtids and lost_gtids these two gtid set.   Gtida on the library. How to keep the primary and standby Gtid consistent because the Gtid of each transaction is recorded in Binlog, the copy thread of the standby can be set to a thread-level gtid_next to ensure the gtid of the master and the repository are consistent.   By default, the Thd->variables.gtid_next.type on the main library is Automatic_group, and the standby is the gtid_group  standby SQL thread Gtid_next output: (GDB) p Thd->variables.gtid_next$ = { type = Gtid_group, GTID = { Sidno = 2, Gno = 1127, static max_text_length = }, static max_text_length =. These variables are assigned when executing gtid_log_event: gtid_log_event::d o_apply_event, the general process is: 1.rpl_sidno sidno= Get_sidno (true);  Get Sidno2.thd->variables.gtid_next.set (Sidno, Spec.gtid.gno); Set Gtid_next3.gtid_acquire_ownership_single (THD);

|–> checks if the Gtid is in the Logged_gtids collection, and if so, returns (Gtid_pre_statement_checks ignores the transaction)

     |–> If the Gtid is already owned by another thread, wait (Gtid_state->wait_for_gtid (THD, Gtid_next)) or set the current thread to owner (Gtid _state->acquire_ownership (THD, Gtid_next))   mentioned above, it is possible that the gtid of the current transaction is already in Logged_gtids, so in the execution rows_log_event::d o_ In the Apply_event or Mysql_execute_command function, the function is called gtid_pre_statement_checks the function will also check whether the Gtid is legitimate before each SQL executes. The main processes include: 1. When the option enforce_gtid_consistency is turned on, check that the DDL is allowed to execute (thd->is_ddl_gtid_compatible ()), and if not, return to Gtid_statement_ CANCEL2. Check whether the current SQL generates an implicit commit and Gtid_next is set (gtid_next->type! = Automatic_group), and if so, throws an error er_cant_do_implicit_ Commit_in_trx_when_gtid_next_is_set and return to Gtid_statement_cancel, note that this will cause bug#690453. For begin/commit/rollback/(SET OPTION or SELECT) and do not use stored procedures/These types of SQL, always allow execution, return Gtid_statement_execute4.gtid_next->type to Undefined_group, throw an error Er_ Gtid_next_type_undefined_group, return gtid_statement_cancel5.gtid_next->type = = Gtid_group and thd->owned_ Gtid.sidno = = 0 o'clock, returns gtid_statement_skip  in the fifth step, the special case  b of the function gtid_acquire_ownership_single is processed. How to initiate a dump request

The biggest benefit of introducing Gtid is of course we can switch the main and standby topologies as we wish. In a normal-running replication structure, we can simply execute the following SQL in the repository:

Change MASTER to Master_user= ' $USERNAME ', master_host= ', master_port= ', master_auto_position=1; When we open gtid, we don't need to specify the Binlog file or location, and MySQL will do it for us automatically. The key here is master_auto_position. The IO thread connects to the main library, which can be divided into the following steps: 1. IO line Cheng The main library after the TCP link is established, the main library's UUID (GET_MASTER_UUID) is acquired, and a user variable @slave_uuid (io_thread_init_commands) 2 is set on the main library. Registering slave (register_slave_on_master) on the main library calls Register_slave on the main library to register the repository, and the Host,user,password,port,server_ ID and other information is logged to slave_list hash. 3. Call Request_dump, start to request data to the main library, there are two cases: master_auto_position=0, the type of command sent to the main library is Com_binlog_dump, which is the traditional request BINLOG mode Master_ Auto_position=1, the command type is Com_binlog_dump_gtid, which is the new way. Here we only discuss the second kind. In the second case, the Gtid collection already executed by the repository is read first quoted code in rpl_slave.cc:

2974 if (Command = = Com_binlog_dump_gtid)

2975 {2976//Get set of GTIDs2977 Sid_map Sid_map (Null/*no lock needed*/); 2978 gtid_set gtid_executed (& SID_MAP); 2979 global_sid_lock->wrlock (); 2980 gtid_state->dbug_print (); 2981 if (gtid_executed.add_gtid_se T (Mi->rli->get_gtid_set ())! = RETURN_STATUS_OK | | 2982 Gtid_executed.add_gtid_set (Gtid_state->get_logged_gtids ())! =

2983 RETURN_STATUS_OK)

After the build finishes sending the package, it is sent to the main library.

After accepting the command on the main library, call the entry function Com_binlog_dump_gtid, as follows:

1.slave_gtid_executed.add_gtid_encoding (Packet_position, data_size); Read the Gtid set 2 from the standby library. Read the UUID of the standby (get_slave_ UUID), the Kill zombie Thread (Kill_zombie_dump_threads) is based on the UUID, which is also useful for SLAVE IO threads to perform set @SLAVE_UUID. 3. Enter the Mysql_binlog_send function:         |–> call Mysql_bin_log::find_first_log_not_in_gtid_set, Start scanning from the last Binlog, get the previous_gtids_log_event of the file header, if it is a subset of slave_gtid_executed, save the current Binlog file name, otherwise proceed to scan forward.           This step is designed to find the last Binlog file that the repository executes.                   |–> start scanning from the head of this file, when encountering Gtid_event, Will determine if the Gtid is included in slave_gtid_executed:                          gtid_log_event Gtid_ev (packet->ptr () + ev_offset,                                   packet->length () –checksum_size,                                  p_fdle);                          skip_group= Slave_gtid_executed->contains_gtid (g Tid_ev.get_sidno (Sid_map),                                                        Gtid_ev.get_gno ());          Main Library Gtid decide if a transaction can be ignored, thus deciding where to start the execution     Note that after you use master_log_position, do not specify the location of Binlog, otherwise you will get an error. Third, operation and maintenance operations

A. How to ignore replication errors when a repository replication error occurs, the traditional method of skipping errors is to set Sql_slave_skip_counter, and then start slave. However, if Gtid is turned on, the failure will be set:

mysql> Set Global sql_slave_skip_counter = 1;

ERROR 1858 (HY000): Sql_slave_skip_counter can Not was set when the server is running with @ @GLOBAL. Gtid_mode = on. Instead, for each transaction so want to skip, generate a empty transaction with the same GTID as the transaction

The error message tells us that the wrong transaction can be skipped by generating an empty transaction. We manually generated a backup copy error: Last_sql_error:error ' Unknown table ' test.t1 ' on query. Default database: ' Test '. Query: ' DROP TABLE ' t1 '/* generated by Server */' view binlog, the DDL corresponding Gtid is 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131 Execute on the standby:

Mysql> STOP SLAVE;

Query OK, 0 rows Affected (0.00 sec) mysql> SET SESSION gtid_next = ' 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131′; Query OK, 0 rows Affected (0.00 sec) mysql> BEGIN; COMMIT; Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

mysql> SET SESSION gtid_next = AUTOMATIC;

Query OK, 0 rows Affected (0.00 sec)

Mysql> START SLAVE; If you look at show slave status again, you will see that the error transaction has been skipped. The principle of this method is simple, the gtid of the empty transaction is added to the gtid_executed, which is equivalent to telling the repository that the Gtid corresponding transaction has been executed. B. Refer to the main library using change master to ... master_auto_position=1; note in the entire replication topology, you need to open Gtid_mode C. The new until condition 5.6 provides a new util condition, You can determine the location to which the standby replication is performed according to Gtid Sql_before_gtids: Stop replication before the specified Gtid sql_after_gtids: Stop replication after the specified gtid the judgment function is Relay_log_info::is_ Until_satisfied detailed documents are shown in:http://dev.mysql.com/doc/refman/5.6/en/start-slave.html D. Reduce the size of the Binlog file appropriately if you turn on Gtid, it is theoretically best to scale down the maximum value of each Binlog file to reduce the time it takes to scan the file. Iv. Bugs in existence bug#69097 , even if Gtid_mode is turned off, the Binlog file is scanned at startup. When Gtid_mode is not used before reboot, it is possible to scan all binlog files after rebooting, which is obviously unacceptable if binlog files are many. bug#69096 , the replication error cannot be skipped through gtid_next_list because the gtid_next_list is not compiled by default. Todo:gtid_next_list's logic is not mentioned above, there is no time to see. bug#69095 , set the copy mode of the standby to statement/mixed. The main library is set to row mode, and executing DML causes the standby replication to break Last_sql_error:error executing row event: ' cannot execute statement:impossible to write to bi Nary log since statement is in row format and Binlog_format = statement. ' Backtrace:handle_slave_worker->slave_wor of judging the error Ker_exec_job->rows_log_event::d O_apply_event->open_and_lock_tables->open_and_lock_tables->lock_ TABLES-&GT;THD::d Ecide_logging_format

WORKAROUND: Set the copy mode of the standby to ' ROW ', keeping the master-and-standby consistent

This bug has nothing to do with Gtid bug#69045 , when the main library performs an action like FLUSH privileges, if both the master and the standby have Gtid_mode enabled, replication interrupts Last_sql_error:error ' cannot execute statements with Implicit commit inside a transaction when @ @SESSION. Gtid_next! = AUTOMATIC or @ @SESSION. Gtid_next_list! = NULL. ' On query. Default database: ". Query: ' Flush privileges ' is also a very low-level bug, in the MySQL5.6.11 version, if it is possible to lead to implicitly committed transactions, then gtid_next must be equal to automatic, for the standby replication thread, it is easy to break, The judgment logic is referenced in the function gtid_pre_statement_checks in document 1. Ali Long source of three blog posts (one , two , three) 2.mysql5.6.11 Source Code