Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the time to change the mysql.gtid_ execute table and other variables in Mysql 5.7". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Timing of modification of the main library (2) binlog is opened

Timing of modification of mysql.gtid_ executed table

Save all the Gtid that was executed in the last binlog file until the binlog was switched (rotate). It is not updated in real time.

The stack frames are as follows:

# 0 Gtid_table_persistor::save (this=0x2f9f9c0, gtid_set=0x7ffff03595a0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_persist.cc:425#1 0x0000000001803dbe in Gtid_state::save (this=0x2ff8bb0, gtid_set=0x7ffff03595a0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:796#2 0x0000000001803f62 in Gtid_state::save_gtids_of_last_binlog_into_table (this=0x2ff8bb0 On_rotation=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:835#3 0x000000000185266d in MYSQL_BIN_LOG::new_file_impl (this=0x2dffc80, need_lock_log=false, extra_description_event=0x0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:6751#4 0x00000000018520a7 in MYSQL_BIN_LOG::new_file_without_locking (this=0x2dffc80 Extra_description_event=0x0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:6636#5 0x0000000001853e67 in MYSQL_BIN_LOG::rotate (this=0x2dffc80, force_rotate=true, check_purge=0x7ffff0359c4b) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:7292

The main logic is in Gtid_state::save_gtids_of_last_binlog_into_table. We discuss this functional logic in a later section.

Timing of gtid_executed variable modification

As mentioned earlier, the Gtid is generated in the ordered_commit flush phase, and the gtid_executed variable is counted in the commit phase, which is updated in real time.

The stack frames are as follows:

# 0 Gtid_set::_add_gtid (this=0x2ff8d38, sidno=1, gno=16) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid.h:1135#1 0x0000000001804576 in Gtid_set::_add_gtid (this=0x2ff8d38, gtid=...) At / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid.h:1166#2 0x00000000018024ba in Gtid_state::update_gtids_impl (this=0x2ff8bb0, thd=0x7fff2c000b70, is_commit=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:304#3 0x00000000018020df in Gtid_state::update_on_commit (this=0x2ff8bb0 Thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:148#4 0x00000000018573d4 in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2dffc80, thd=0x7fff2c000b70, first=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8646#5 0x0000000001858b51 in MYSQL_BIN_LOG::ordered_commit (this=0x2dffc80, thd=0x7fff2c000b70, all=false Skip_commit=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9304

The main logic is in Gtid_state::update_gtids_impl. We discuss this functional logic in a later section.

Timing of gtid_purged variable modification

In the case of cleaning binlog triggered by Mysql, such as purge binary logs to or automatically deleted after the number of days set by the parameter expire_logs_days, the missing Gtid needs to be counted in this variable.

The stack frames are as follows:

# 0 MYSQL_BIN_LOG::init_gtid_sets (this=0x2e00280, all_gtids=0x0, lost_gtids=0x2fcaee8, verify_checksum=false, need_lock=false, trx_parser=0x0, gtid_partial_trx=0x0, is_server_starting=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:4333#1 0x0000000001850b8e in MYSQL_BIN_LOG::purge_logs (this=0x2e00280 To_log=0x7fff57a74ad0 "/ root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/mysqld.1/test.000202", included=false, need_lock_index=true, need_update_threads=true, decrease_log_space=0x0, auto_purge=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:6036#2 0x0000000001848ecf in purge_master_logs (thd=0x7fff49200dc0 To_log=0x7fff492051a8 "test.000202") at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:2815

The main logic is in MYSQL_BIN_LOG::purge_logs, and then we look at the code snippet, and the MYSQL_BIN_LOG::init_gtid_sets function is a very important function, mainly used in:

Mysql initializes various Gtid_set in the entire Gtid_state when it starts.

When Mysql needs to confirm the value of the gtid_purged variable (and Gtid_state.lost_gtids) after deleting binlog (such as purge binary logfiles or exceeding the expire_logs_days setting).

I will then explain the initialization of the Mysql Gtid module in a separate section and explain this function.

Second, the analysis of the source code function of the timing of modification of the main library.

Here we will analyze the main logic functions mentioned above.

Gtid_state::save_gtids_of_last_binlog_into_table functional logic

Logged_gtids_last_binlog.add_interval_memory (PREALLOCATED_INTERVAL_COUNT, iv); / / build a logged_gtids_last_binlog collection here to save the Gtid / * logged_gtids_last_binlog= executed_gtids-previous_gtids_logged-gtids_only_in_table * / global_sid_lock- > wrlock () that needs to be written to the table and previous_gtids_logged after switching. / / ret= (logged_gtids_last_binlog.add_gtid_set (& executed_gtids)! = / / add all currently executed Gtid to logged_gtids_last_binlog such as: executed_gtids start=1, end=27 RETURN_STATUS_OK); if (! ret) {logged_gtids_last_binlog.remove_gtid_set (& previous_gtids_logged) / / get all the Gtid contained in the previous binlog file, and make a subtraction list, such as: previous_gtids_logged is start=1, end=25 / / logged_gtids_last_binlog is start=26, end=27 logged_gtids_last_binlog.remove_gtid_set (& gtids_only_in_table) / / the main library here must be empty, unless the exception if (! logged_gtids_last_binlog.is_empty ()) {/ * Prepare previous_gtids_logged for next binlog on binlog rotation * / if (on_rotation) ret= previous_gtids_logged.add_gtid_set (& logged_gtids_last_binlog) / / add the Gtid collection of start=26 and end=27 to previous_gtids_logged, so that previous_gtids_logged also completes global_sid_lock- > unlock (); / * Save set of GTIDs of the last binlog into gtid_executed table * / if (! ret) ret= save (& logged_gtids_last_binlog); / / write the Gtid collection of start=26, end=27 to the table mysql.gtid_executed table}

Code snippet of Gtid_state::update_gtids_impl function

While (g.sidno! = 0) {if (g.sidno! = prev_sidno) sid_locks.lock (g.sidno); owned_gtids.remove_gtid (g); / / remove git.next () from owned_gtid; g = git.get (); if (is_commit) executed_gtids._add_gtid (g); / / add this Gtid to executed_gtids}

Code snippet of MYSQL_BIN_LOG::purge_logs function

If (! is_relay_log) {global_sid_lock- > wrlock () Error= init_gtid_sets (NULL, const_cast (gtid_state- > get_lost_gtids ()), opt_master_verify_checksum, false/*false=don't need lock*/, NULL/*trx_parser*/, NULL/*gtid_partial_trx*/) / / here I see that passing gtid_state- > lost_gtids directly to init_gtid_sets / / init_gtid_sets will do a forward search to get gtid_state- > lost_gtids later. / / discuss global_sid_lock- > unlock () in detail If (error) goto err;} 3. Modification time of slave library (2) when binlog is enabled and parameter log_slave_updates is enabled

In this case, Gtid transactions executed by sql_thread can be maintained through binlog, so the mysql.gtid_ executed table and gtid_purged variables do not need to be updated in real time.

Timing of modification of mysql.gtid_ executed table

Consistent with the main library. And update during log switching without discussion

Timing of gtid_executed variable modification

Update in real time like the main library without discussion

Timing of gtid_purged variable modification

Consistent with the main library, binlog is updated when it is deleted without discussion

IV. Analysis of the source code function of the timing of modification from the library

Commit_owned_gtids function logic:

/ / if binlog is not enabled including (log_bin=0 and sql_log_bin = 0) or binlog is enabled but slave thread and slave update is not enabled, gtid will be recorded to the table / / but it should be noted here that if binlog is not enabled on the main library, then thd- > owned_gtid.sidno = = 0 because Gtid is not generated at this time The commit phase if ((! opt_bin_log | | (thd- > slave_thread & &! opt_log_slave_updates)) & & (all | |! thd- > in_multi_stmt_transaction_mode ()) & & / all represents whether the begin thing in_multi_stmt_transaction_mode is displayed or not. The opposite is true! thd- > is_operating_gtid_table_implicitly & & / / is GTID _ NEXT mode flase! thd- > is_operating_substatement_implicitly) / / whether it is a clause flase {/ * If the binary log is disabled for this thread (either by log_bin=0 or sql_log_bin=0 or by log_slave_updates=0 for a slave thread) Then the statement will not be written to the binary log. In this case, we should save its GTID into mysql.gtid_executed table and @ @ GLOBAL.GTID_EXECUTED as it did when binlog is enabled. * / if (thd- > owned_gtid.sidno > 0) {error= gtid_state- > save (thd); / / this is the real-time update of the mysql.gtid_ executed table * need_clear_owned_gtid_ptr= true;} else if (thd- > owned_gtid.sidno = = THD::OWNED_SIDNO_ANONYMOUS) * need_clear_owned_gtid_ptr= true;}

Logic fragment of Gtid_state::update_gtids_impl_own_gtid function

This function is 5.7.17, and 5.7.14 has no logic in Gtid_state::update_gtids_impl.

If (is_commit) {DBUG_EXECUTE_IF ("rpl_gtid_update_on_commit_simulate_out_of_memory", DBUG_SET ("+ d Any session adds transaction owned GTID into global executed_gtids.) If binlog is disabled, we report @ @ GLOBAL.GTID_PURGED from executed_gtids, since @ @ GLOBAL.GTID_PURGED and @ @ GLOBAL.GTID_EXECUTED are always same, so we did not save gtid into lost_gtids for every transaction for improving performance. If binlog is enabled and log_slave_updates is disabled, slave SQL thread or slave worker thread adds transaction owned GTID into global executed_gtids, lost_gtids and gtids_only_in_table. * / executed_gtids._add_gtid (thd- > owned_gtid); / / add the executed_gtids collection thd- > rpl_thd_ctx.session_gtids_ctx (). Notify_after_gtid_executed_update (thd) If (thd- > slave_thread & & opt_bin_log & &! opt_log_slave_updates) / / if the slave thread is on at the same time binlog is turned on and log_slave_updates is closed / / if binlog is closed, use executed_gtids to improve performance. The previous note says {lost_. Gtids._add_gtid (thd- > owned_gtid) / / write lost_gtids, that is, update the parameter gtid_purged variable gtids_only_in_table._add_gtid (thd- > owned_gtid);}} V. General change time

Timing of modification of mysql.gtid_ executed table

Clear this table during reset master

The stack frames are as follows:

# 0 Gtid_table_persistor::delete_all (this=0x2f9f9c0, table=0x7fff2c0116a0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_persist.cc:795#1 0x000000000180a4ef in Gtid_table_persistor::reset (this=0x2f9f9c0, thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_persist.cc:689#2 0x0000000001801f2e in Gtid_state::clear (this=0x2ff8bb0 Thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:36#3 0x000000000184fee6 in MYSQL_BIN_LOG::reset_logs (this=0x2dffe80, thd=0x7fff2c000b70, delete_only=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:5586#4 0x0000000001872308 in reset_master (thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_master.cc:587

The main logic is in Gtid_state::clear.

In set global gitd_purged, set up this table

The stack frames are as follows:

# 0 Gtid_table_persistor::save (this=0x2f9f9c0, gtid_set=0x7ffff0359a70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_persist.cc:425#1 0x000000000180400a in Gtid_state::save (this=0x2ff8bb0, gtid_set=0x7ffff0359a70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:796#2 0x0000000001803c25 in Gtid_state::add_lost_gtids (this=0x2ff8bb0 Gtid_set=0x7ffff0359a70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_gtid_state.cc:737#3 0x00000000016778f3 in Sys_var_gtid_purged::global_update (this=0x2de9fe0, thd=0x7fff2c000b70, var=0x7fff2c006630) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sys_vars.cc:5888#4 0x00000000014d5cd1 in sys_var::update (this=0x2de9fe0, thd=0x7fff2c000b70 Var=0x7fff2c006630) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/set_var.cc:184#5 0x00000000014d74ee in set_var::update (this=0x7fff2c006630, thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/set_var.cc:812#6 0x00000000014d6d1a in sql_set_variables (thd=0x7fff2c000b70, var_list=0x7fff2c003528) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/set_var.cc:669

The main logic is in Gtid_state::add_lost_gtids.

Timing of gtid_executed variable modification

Clear this variable during reset master

Stack frame same as above

When you set global gitd_purged, set this variable

Stack frame same as above

Initialize the setting of the gtid_executed variable when mysql starts, which will be described in detail in later chapters.

Timing of gtid_purged variable modification

Clear this variable during reset master

Stack frame same as above

When you set global gitd_purged, set this variable

Stack frame same as above

Initialize the setting of the gtid_executed variable when mysql starts, which will be described in detail in later chapters.

VI. General source code function analysis of change timing

Gtid_state::clear functional logic

Int Gtid_state::clear (THD * thd) {.... / / the wrlock implies that no other thread can hold any of the mutexes sid_lock- > assert_some_wrlock (); lost_gtids.clear (); / / clear the gtid_purged variable executed_gtids.clear () here; / / clear the gtid_executed variable gtids_only_in_table.clear () here; / / clear only in table Gtid set previous_gtids_logged.clear (); / / clear previous gtids logged Gtid set / * Reset gtid_executed table. * / if ((ret= gtid_table_persistor- > reset (thd)) = = 1) / / clear the mysql.gtid_ executed table {/ * Gtid table is not ready to be used, so failed to open it. Ignore the error. * / thd- > clear_error (); ret= 0;} next_free_gno= 1; DBUG_RETURN (ret);}

Gtid_state::add_lost_gtids functional logic

Enum_return_status Gtid_state::add_lost_gtids (const Gtid_set * gtid_set) {. If (save (gtid_set)) / / add the value of set gtid_purge to the mysql.gtid_executed table RETURN_REPORTED_ERROR; PROPAGATE_REPORTED_ERROR (gtids_only_in_table.add_gtid_set (gtid_set)); PROPAGATE_REPORTED_ERROR (lost_gtids.add_gtid_set (gtid_set)); / / here add the value of set gtid_purge to the gtid_purge variable PROPAGATE_REPORTED_ERROR (executed_gtids.add_gtid_set (gtid_set)) / / here add the value of set gtid_purge to the gtid_executed variable lock_sidnos (gtid_set); broadcast_sidnos (gtid_set); unlock_sidnos (gtid_set); DBUG_RETURN (RETURN_STATUS_OK);} "what is the timing of mysql.gtid_ execution table and other variables change in Mysql 5.7". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.