mysql 从库日志_MySQL binlog从主库传输到从库的问题

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

之前和朋友讨论是否mysql的主库上没有发布提交，而binlog就会被写到从库上去了.从而

出现数据一致性的问题。通过代码中我们可以看到发送binlog只会在提交，flush log之类的

操作会才会调用发送日志的函数(一共有六种).

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

[root@c12 data]# pstack 26152

Thread 2 (Thread 0x4caee940 (LWP 29070)):

#0  0x00000036a280b150 in pthread_cond_timedwait@@GLIBC_2.3.2 () 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 pthread_cond_timedwait@@GLIBC_2.3.2 () 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的操作可以自行验证.