MySQL set variable的源码实现分析

发表于 | 分类于

背景

项目在使用Inception的时候，对于大表需要考虑对延迟的控制，会使用到pt-online-schema-change的–recursion-method。
公司在使用RDS数据库，故对于osc只能使用–recursion-method=dsn这种方式。目前inception不支持这种方式，故研究其代码。
同时也是对MySQL set variable这个SQL对应的背后知识有源码层次的了解。
本篇目前在RDS数据库情况，使用Inception对于大表alter的操作保护方案，支持指定从库IP列表的dsn方式。

MySQL gdb调试

1
gdb --args /usr/local/mysql/bin/Inception --defaults-file=/usr/local/mysql/etc/inception.cnf

针对gdb过程中遇到的 optimized out，参照How to compile without optimizations -O0 using CMake进行处理。
选择方式在CMakeLists.txt中加入:

1
2
SET(CMAKE_CXX_FLAGS "-O0 -g3 -gdwarf-2")
SET(CMAKE_C_FLAGS "-O0 -g3 -gdwarf-2")

以枚举变量为例

变量类型

作用域 类型 存储地方 涉及文件
Session SESSION_VAR struct system_variables sql/sql_class.h
Global GLOBAL_VAR 按照类型存储 sql/mysqld.cc和sql/mysqld.h

set variable调用关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
(gdb) bt
#0  find_type (lib=0xfdf370 <Sys_inception_osc_recursion_method+208>, find=0x7ffff0004c00 "processlist", length=11, part_match=false)
    at /opt/source/inception/sql/strfunc.cc:123
#1  0x0000000000707d0f in Sys_var_typelib::do_check (this=0xfdf2a0 <Sys_inception_osc_recursion_method>, thd=0x1005f40, var=0x7ffff0004cc0)
    at /opt/source/inception/sql/sys_vars.h:288
#2  0x00000000006299b4 in sys_var::check (this=0xfdf2a0 <Sys_inception_osc_recursion_method>, thd=0x1005f40, var=0x7ffff0004cc0) at /opt/source/inception/sql/set_var.cc:213
#3  0x000000000062a742 in set_var::check (this=0x7ffff0004cc0, thd=0x1005f40) at /opt/source/inception/sql/set_var.cc:624
#4  0x00000000006a2fe1 in mysql_execute_inception_set_command (thd=0x1005f40) at /opt/source/inception/sql/sql_parse.cc:4219
#5  0x00000000006a31a7 in mysql_execute_inception_command (thd=0x1005f40) at /opt/source/inception/sql/sql_parse.cc:4255
#6  0x00000000006add22 in mysql_check_command (thd=0x1005f40) at /opt/source/inception/sql/sql_parse.cc:7794
#7  0x00000000006b91e4 in mysql_process_command (thd=0x1005f40, parser_state=0x7ffff7fe8c80) at /opt/source/inception/sql/sql_parse.cc:11636
#8  0x00000000006b9261 in mysql_parse (thd=0x1005f40, length=66, parser_state=0x7ffff7fe8c80) at /opt/source/inception/sql/sql_parse.cc:11664
#9  0x000000000069ad11 in dispatch_command (command=COM_QUERY, thd=0x1005f40, packet=0x100a4d1 "inception set session inception_osc_recursion_method='processlist'", 
    packet_length=66) at /opt/source/inception/sql/sql_parse.cc:1059
#10 0x0000000000698a30 in do_command (thd=0x1005f40) at /opt/source/inception/sql/sql_parse.cc:489
#11 0x0000000000673dd0 in do_handle_one_connection (thd_arg=0x1005f40) at /opt/source/inception/sql/sql_connect.cc:926
#12 0x0000000000673b9a in handle_one_connection (arg=0x1005f40) at /opt/source/inception/sql/sql_connect.cc:842
#13 0x00007ffff7bc6e25 in start_thread () from /lib64/libpthread.so.0
#14 0x00007ffff6c96bad in clone () from /lib64/libc.so.6

在find_type中会对参数进行比较进行比较，判断字符串是否和符合预期，不符合预期报语法错误。

支持recursion_method

代码分析

在Inception中使用Sys_var_enum类型支持recursion_method的设置。
这是一个枚举类型，只支持固定的值。

1
2
3
4
5
6
7
const char *osc_recursion_method[]= {"processlist", "hosts", "none", "dsn", NullS};
static Sys_var_enum Sys_inception_osc_recursion_method(
    "inception_osc_recursion_method",
    "Preferred recursion method used to find slaves.",
    SESSION_VAR(inception_osc_recursion_method), CMD_LINE(REQUIRED_ARG),
    osc_recursion_method, DEFAULT(recursion_method_processlist), 
    NO_MUTEX_GUARD, NOT_IN_BINLOG);

对osc_recursion_method加dsn之后，可以设置inception_osc_recursion_method为dsn，否则报错。

1
2
3
4
5
6
7
8
9
10
mysql> inception set inception_osc_recursion_method='dsn';
Query OK, 0 rows affected (2.88 sec)

mysql> inception get variables 'inception_osc_recursion_method';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| inception_osc_recursion_method | dsn   |
+--------------------------------+-------+
1 row in set (0.00 sec)

但是实际pt-osc在设置dsn为如下模式:

1
dsn=h=192.40.120.26,P=4500,D=dpadmin,t=dsns_5627

这个不是一个固定的值，而是随着操作端口的不同，t的值是可以变化的，例如dsns_4300。
所以这里无法使用enum类型的，如果支持这种的话，需要使用Sys_var_charptr类型。

将enum修改为string类型

两者变量继承关系
  • Sys_var_charptr

Sys_var_charptr

  • Sys_var_enum
    Sys_var_enum

Sys_var_charptr只支持Global变量

修改代码

  • 修改sql/mysqld.h
1
extern char* inception_osc_recursion_method;
  • 修改sql/mysqld.cc
    Global变量需要修改sql/mysqld.cc，Session级别变量在sql/sql_class.h中定义即可。
1
2
3
//Added by Guosong 20180905 START
char* inception_osc_recursion_method= NULL;
//Added by Guosong 20180905 END
  • sql/sys_var.cc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//Modified by Guosong START 20180905
/*const char *osc_recursion_method[]= {"processlist", "hosts", "none", "dsn", NullS};
static Sys_var_charptr Sys_inception_osc_recursion_method(
    "inception_osc_recursion_method",
    "Preferred recursion method used to find slaves.",
    SESSION_VAR(inception_osc_recursion_method), CMD_LINE(REQUIRED_ARG),
    osc_recursion_method, DEFAULT("none"), 
    NO_MUTEX_GUARD, NOT_IN_BINLOG);*/
static Sys_var_charptr Sys_inception_osc_recursion_method(
    "inception_osc_recursion_method",
    "Preferred recursion method used to find slaves.",
    GLOBAL_VAR(inception_osc_recursion_method), CMD_LINE(REQUIRED_ARG),
    IN_FS_CHARSET, DEFAULT(0), 
    NO_MUTEX_GUARD, NOT_IN_BINLOG);
//Modified by Guosong END 20190905
  • 修改sql/sql_parse.cc
1
2
sprintf(cmd_line, "--recursion-method=%s", 
¦   inception_osc_recursion_method);
  • 测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> inception set inception_osc_recursion_method='processlist';
Query OK, 0 rows affected (0.00 sec)

mysql> inception get variables 'inception_osc_recursion_method';
+--------------------------------+-------------+
| Variable_name                  | Value       |
+--------------------------------+-------------+
| inception_osc_recursion_method | processlist |
+--------------------------------+-------------+
1 row in set (0.00 sec)

mysql> inception set inception_osc_recursion_method='dsn=h=10.40.120.26,P=4300,D=dpadmin,t=dsns_5627';
Query OK, 0 rows affected (0.00 sec)

mysql> inception get variables 'inception_osc_recursion_method';
+--------------------------------+-------------------------------------------------+
| Variable_name                  | Value                                           |
+--------------------------------+-------------------------------------------------+
| inception_osc_recursion_method | dsn=h=10.40.120.26,P=4300,D=dpadmin,t=dsns_5627 |
+--------------------------------+-------------------------------------------------+
1 row in set (0.00 sec)
  • 改成Global ReadOnly更合适 
1
2
3
4
5
6
static Sys_var_charptr Sys_inception_osc_recursion_method(
    "inception_osc_recursion_method",
    "Preferred recursion method used to find slaves.",
    READ_ONLY GLOBAL_VAR(inception_osc_recursion_method), CMD_LINE(REQUIRED_ARG),
    IN_FS_CHARSET, DEFAULT(0), 
    NO_MUTEX_GUARD, NOT_IN_BINLOG);
1
2
3
4
5
6
7
8
9
10
mysql> inception get variables 'inception_osc_recursion_method';
+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| inception_osc_recursion_method | dsn=D=dpadmin,t=dsns |
+--------------------------------+----------------------+
1 row in set (0.00 sec)

mysql> inception set inception_osc_recursion_method='processlist';
ERROR 2575 (HY000): Variable 'inception_osc_recursion_method' is a read only variable.

这种方式可以支持dns的方式，不过需要操作的主库存在dpadmin.dsns表，其保存对应的从库IP列表，用于操作大表的保护工作。

参考