共计 11577 个字符，预计需要花费 29 分钟才能阅读完成。
本篇内容介绍了“Mysql 5.7 中 mysql.gtid_executed 表及其他变量更改时机是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
一、主库修改时机
(2) binlog 打开
mysql.gtid_executed 表修改时机
在 binlog 发生切换 (rotate) 的时候保存直到上一个 binlog 文件执行过的全部 Gtid，它不是实时更新的。
栈帧如下:
#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其主要逻辑在 Gtid_state::save_gtids_of_last_binlog_into_table 中我们在随后的部分讨论这个函数逻辑。
gtid_executed 变量修改时机
如前文所述 ordered_commit flush 阶段生成 Gtid，在 commit 阶段才计入 gtid_executed 变量，它是实时更新的。
栈帧如下：
#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其主要逻辑在 Gtid_state::update_gtids_impl 中我们在随后的部分讨论这个函数逻辑。
gtid_purged 变量修改时机
在 Mysql 触发的清理 binlog 的情况下，比如 purge binary logs to 或者超过参数 expire_logs_days 设置的天数后自动删除，需要将丢失的 Gtid 计入这个变量中。
栈帧如下:
#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其主要逻辑在 MYSQL_BIN_LOG::purge_logs 中，我们随后查看其代码片段，同时 MYSQL_BIN_LOG::init_gtid_sets 函数是一个及其重要的函数，主要用在：
Mysql 启动时候初始化整个 Gtid_state 中的各种 Gtid_set。
Mysql 删除 binlog(如 purge binary logfiles 或者超过 expire_logs_days 设置)后需要确认 gtid_purged 变量 (及 Gtid_state.lost_gtids) 的值的时候。
随后我会单独一节来讲解 Mysql Gtid 模块的初始化还会讲解这个函数。
二、主库修改时机源码函数分析
这里就对上面提到的主要逻辑函数进行分析
Gtid_state::save_gtids_of_last_binlog_into_table 函数逻辑
logged_gtids_last_binlog.add_interval_memory(PREALLOCATED_INTERVAL_COUNT, iv); // 这里构建一个 logged_gtids_last_binlog 集合来保存切换后需要写入表和 previous_gtids_logged 的 Gtid
/*
logged_gtids_last_binlog= executed_gtids - previous_gtids_logged -
gtids_only_in_table
*/
global_sid_lock- wrlock();//
ret= (logged_gtids_last_binlog.add_gtid_set( executed_gtids) != // 将当前执行过的 Gtid 全部加入 logged_gtids_last_binlog 列如:executed_gtids start=1, end=27
RETURN_STATUS_OK);
if (!ret)
{ logged_gtids_last_binlog.remove_gtid_set( previous_gtids_logged); // 获得上一个 binlog 文件包含的全部 Gtid，并且做一个差集 列如:previous_gtids_logged 为 start=1, end=25
// 做完差集后 logged_gtids_last_binlog 为 start=26, end=27
logged_gtids_last_binlog.remove_gtid_set(gtids_only_in_table);// 此处主库一定为空，除非异常情况
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);// 将这个 start=26, end=27 的 Gtid 集合加入到 previous_gtids_logged 中，这样 previous_gtids_logged 也完整了
global_sid_lock- unlock();
/* Save set of GTIDs of the last binlog into gtid_executed table */
if (!ret)
ret= save(logged_gtids_last_binlog);// 将这个 start=26, end=27 的 Gtid 集合写入到表 mysql.gtid_executed 表中
}Gtid_state::update_gtids_impl 函数代码片段
while (g.sidno != 0)
{ if (g.sidno != prev_sidno)
sid_locks.lock(g.sidno);
owned_gtids.remove_gtid(g); // 从 owned_gtid 中去掉
git.next();
g= git.get();
if (is_commit)
executed_gtids._add_gtid(g);// 将这个 Gtid 加入到 executed_gtids
}MYSQL_BIN_LOG::purge_logs 函数代码片段
if (!is_relay_log)
{ global_sid_lock- wrlock();
error= init_gtid_sets(NULL,
const_cast Gtid_set * (gtid_state- get_lost_gtids()),
opt_master_verify_checksum,
false/*false=don t need lock*/,
NULL/*trx_parser*/, NULL/*gtid_partial_trx*/);// 这里我看到将 gtid_state- lost_gtids 直接传入给了 init_gtid_sets
//init_gtid_sets 会做正向查找获得 gtid_state- lost_gtids 这个函数稍后
// 详细讨论
global_sid_lock- unlock();
if (error)
goto err;
} 三、从库修改时机
(2)binlog 开启同时参数 log_slave_updates 开启的情况
这种情况 sql_thread 执行过的 Gtid 事物可以通过 binlog 进行维护，所以 mysql.gtid_executed 表和 gtid_purged 变量不需要实时更新。
mysql.gtid_executed 表修改时机
和主库一致。及在进行日志切换的时候进行更新，不做讨论
gtid_executed 变量修改时机
和主库一样实时更新，不做讨论
gtid_purged 变量修改时机
和主库一致，binlog 删除时更新，不做讨论
四、从库修改时机源码函数分析
commit_owned_gtids 函数逻辑：
// 如果 binlog 没有开启包括 (log_bin=0 和 sql_log_bin =0 ) 或者 开启了 binlog 但是 slave 线程并且 slave update 没有开启, 都会记录 gtid 到表
// 但是这里要注意一点在主库上如果 binlog 不开启那么 thd- owned_gtid.sidno ==0 因为这个时候 Gtid 都没有生成, 生成阶段为 order_commit 的 commit 阶段
if ((!opt_bin_log || (thd- slave_thread !opt_log_slave_updates))
(all || !thd- in_multi_stmt_transaction_mode()) //all 代表是否是显示 begin 事物 in_multi_stmt_transaction_mode 则相反
!thd- is_operating_gtid_table_implicitly // 是否是 GTID_NEXT 方式 flase
!thd- is_operating_substatement_implicitly)// 是否是子语句 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);// 就是这里进行了 mysql.gtid_executed 表的实时更新
*need_clear_owned_gtid_ptr= true;
}
else if (thd- owned_gtid.sidno == THD::OWNED_SIDNO_ANONYMOUS)
*need_clear_owned_gtid_ptr= true;
}
Gtid_state::update_gtids_impl_own_gtid 函数逻辑片段
这个函数是 5.7.17 的，5.7.14 没有逻辑放到了 Gtid_state::update_gtids_impl 中
if (is_commit)
{
DBUG_EXECUTE_IF(
rpl_gtid_update_on_commit_simulate_out_of_memory ,
DBUG_SET(+d,rpl_gtid_get_free_interval_simulate_out_of_memory);
/*
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); // 加入 executed_gtids 集合
thd- rpl_thd_ctx.session_gtids_ctx().
notify_after_gtid_executed_update(thd);
if (thd- slave_thread opt_bin_log !opt_log_slave_updates)// 如果是 slave 线程同时 binlog 开启了并且 log_slave_updates 关闭了
// 如果 binlog 关闭则使用 executed_gtids 这样提高性能前面的注释说了
{ lost_gtids._add_gtid(thd- owned_gtid); // 写入 lost_gtids 也就是更新参数 gtid_purged 变量
gtids_only_in_table._add_gtid(thd- owned_gtid);
}
}五、通用更改时机
mysql.gtid_executed 表修改时机
在 reset master 的时候清空本表
栈帧如下：
#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其主要逻辑在 Gtid_state::clear 中。
在 set global gitd_purged 的时候，设置本表
栈帧如下：
#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其主要逻辑在 Gtid_state::add_lost_gtids 中。
gtid_executed 变量修改时机
在 reset master 的时候清空本变量
栈帧同上
在 set global gitd_purged 的时候，设置本变量
栈帧同上
在 mysql 启动的时候初始化设置 gtid_executed 变量，这个将在后面章节详细描述描述步骤。
gtid_purged 变量修改时机
在 reset master 的时候清空本变量
栈帧同上
在 set global gitd_purged 的时候，设置本变量
栈帧同上
在 mysql 启动的时候初始化设置 gtid_executed 变量，这个将在后面章节详细描述描述步骤。
六、通用更改时机源码函数分析
Gtid_state::clear 函数逻辑
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();// 此处清空 gtid_purged 变量
executed_gtids.clear();// 此处清空 gtid_executed 变量
gtids_only_in_table.clear();// 清空 only in table Gtid set
previous_gtids_logged.clear();// 清空 previous gtids logged Gtid set
/* Reset gtid_executed table. */
if ((ret= gtid_table_persistor- reset(thd)) == 1)// 此处清空 mysql.gtid_executed 表
{
/*
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 函数逻辑
enum_return_status Gtid_state::add_lost_gtids(const Gtid_set *gtid_set)
......
if (save(gtid_set)) // 此处将 set gtid_purge 的值加入到 mysql.gtid_executed 表中
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));// 此处将 set gtid_purge 的值加入到 gtid_purge 变量中
PROPAGATE_REPORTED_ERROR(executed_gtids.add_gtid_set(gtid_set));// 此处将 set gtid_purge 的值加入到 gtid_executed 变量中
lock_sidnos(gtid_set);
broadcast_sidnos(gtid_set);
unlock_sidnos(gtid_set);
DBUG_RETURN(RETURN_STATUS_OK);
}“Mysql 5.7 中 mysql.gtid_executed 表及其他变量更改时机是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
正文完
