MySQL binlog从主库传输到从库的问题

                              MySQL binlog从主库传输到从库的问题

    之前和朋友讨论是否mysql的主库上没有发布提交，而binlog就会被写到从库上去了.从而
出现数据一致性的问题。通过代码中我们可以看到发送binlog只会在提交，flush log之类的
操作会才会调用发送日志的函数(一共有六种).

 以下我们可以来模拟这种情况.首先找出主库的dump线程号

[root@c12 data]# pstack 26152

Thread 2 (Thread 0x4caee940 (LWP 29070)):
#0  0x00000036a280b150 in () from /lib64/libpthread.so.0
#1  0x00000000008898f0 in MYSQL_BIN_LOG::wait_for_update_bin_log(THD*, timespec const*) ()
#2  0x00000000008a294b in mysql_binlog_send(THD*, char*, unsigned long long, Gtid_set const*) ()
#3  0x00000000008a2e81 in com_binlog_dump(THD*, char*, unsigned int) ()
#4  0x00000000006e8d19 in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
#5  0x00000000006b5d5f in do_handle_one_connection(THD*) ()
#6  0x00000000006b5e37 in handle_one_connection ()
#7  0x0000000000acde7a in pfs_spawn_thread ()
#8  0x00000036a280673d in start_thread () from /lib64/libpthread.so.0
#9  0x00000036a1cd3d1d in clone () from /lib64/libc.so.6

使用gdb调试器在跟踪mysqld进程
[root@c12 ~]# gdb

(gdb) attach 16096

(gdb) thread 2
[Switching to thread 2 (Thread 0x4caee940 (LWP 29070))]#0  0x00000036a280b150 in () from /lib64/libpthread.so.0
(gdb) Thread 2 (Thread 0x4caee940 (LWP 29070)):

设置断点

(gdb) break MYSQL_BIN_LOG::wait_for_update_bin_log
Breakpoint 1 at 0x88985b: file /mnt/workspace/percona-server-5.6-binaries/label_exp/centos5-64/Percona-Server-5.6.14-rel62.0/sql/binlog.cc, line 5912.
(gdb) c
Continuing.

打开另一个会话，执行一个sql,自动提交模式

mysql> insert into t20 values(30);
Query OK, 1 row affected (0.00 sec)

在调试器可以看到以下信息输出，breakpoint被触发

Breakpoint 1, MYSQL_BIN_LOG::wait_for_update_bin_log (this=0x131ff40, thd=0xea0e070, timeout=0x4caed510)
    at /mnt/workspace/percona-server-5.6-binaries/label_exp/centos5-64/Percona-Server-5.6.14-rel62.0/sql/binlog.cc:5912
5912    /mnt/workspace/percona-server-5.6-binaries/label_exp/centos5-64/Percona-Server-5.6.14-rel62.0/sql/binlog.cc: No such file or directory.
        in /mnt/workspace/percona-server-5.6-binaries/label_exp/centos5-64/Percona-Server-5.6.14-rel62.0/sql/binlog.cc

 

再次执行sql,关闭自动提交

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t20 values(50);
Query OK, 1 row affected (0.00 sec)

在调试器中没有看到任何输出, 调试器处在Continuing.状态,说明函数没有被触发

(gdb) c
Continuing.

发布提交，

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

调试器输出,断点被触发
Breakpoint 1, MYSQL_BIN_LOG::wait_for_update_bin_log (this=0x131ff40, thd=0xea0e070, timeout=0x4caed510)
    at /mnt/workspace/percona-server-5.6-binaries/label_exp/centos5-64/Percona-Server-5.6.14-rel62.0/sql/binlog.cc:5912
5912    in /mnt/workspace/percona-server-5.6-binaries/label_exp/centos5-64/Percona-Server-5.6.14-rel62.0/sql/binlog.cc

结论：主库没有发布提交操作，binlog不会写到从库上去。flush log的操作可以自行验证.