Dive into MySQL Replication

Dive into MySQL replication protocol


Let’s consider following scenario, we store huge data in MySQL and want to know data changes immediately(data inserted, deleted or updated), then do something for these changes like updating associated cache data.

Using MySQL trigger + UDF may be a feasible way, but I have not used it and would not recommend this. If we have many tables, maintaining so many triggers is horrible. At the same time, UDF may fail so that we will lost some changes, and even worse, a bad UDF implementation may block service or even crash MySQL.

We need a better solution, and this is binlog. MySQL records every changes into binlog, so if we can sync the binlog, we will know the data changes immediately, then do something.

Using rsync may be a good way, but I prefer another: acting as a slave and using MySQL replication protocol to sync.

MySQL Packet

First, we must know how to send or receive data with MySQL, and this is packet:

Split the data into packets of size 16MB.
Prepend to each chunk a packet header.

A packet header has 3 bytes for following payload data length and 1 byte for sequence ID.

The sequence ID is incremented with each packet between client and server communication, it starts at 0 and is reset to 0 when a new command begins, e.g, we send a packet, the sequence ID is 0, and the response packet sequence ID must be 1, if not, some error happens between the communication.

So a packet looks like below:

3              payload length
1              Sequence ID
string[len]    payload

As you see, sending data with packet is very easy, I have implemented a base library in go-mysql packet pkg.

Connection phase

When we first connect to MySQL server, we must do a handshake to let server authorizing us to communicate with it later, this is called connection phase.

Let’s consider a popular common connection phase.

  • Client connects server using socket connect API.
  • Server sends a initial handshake packet which contains server’s capabilities and a 20 bytes random salt.
  • Client answers with a handshake response packet, telling server its capabilities and a 20 bytes scrambled password.
  • Server response ok packet or err packet.

Although MySQL supports many authentication method, I only use username + password, you can see codes in go-mysql client and server pkg.

Now we know how to send/receive data, how to establish the connection, and it’s time to move on for syncing binlog. :-)

Register a Slave

When we want to sync binlog, we must register a slave at master first, that is acting as a pseudo slave.

We only need to send COM_REGISTER_SLAVE command, the payload is:

1              COM_REGISTER_SLAVE
4              server-id
1              slaves hostname length
string[$len]   slaves hostname
1              slaves user len
string[$len]   slaves user
1              slaves password len
string[$len]   slaves password
2              slaves mysql-port
4              replication rank
4              master-id

We must use an unique server id for our pseudo slave, I prefer using a ID bigger than 1000 which is different from our real MySQL servers.

Dump BinLog from master

After register, we should send COM_BINLOG_DUMP command, it is very easy too, payload is:

1              COM_BINLOG_DUMP
4              binlog-pos
2              flags
4              server-id
string[EOF]    binlog-filename

If the binlog filename is empty, server will use the first known binlog.

You can set flags to 1 to tell server to reply a EOF packet instead of blocking connection if there is no more binlog event. But I prefer blocking so that if there is any new event, server can send to us immediately.

Although MySQL supports COM_BINLOG_DUMP_GTID commands, I still prefer using command binlog filename + position, because it is very easy and for our pseudo slave, we only need to sync binlog, save it in a place and let other applications like MHA use it.

After we send COM_BINLOG_DUMP command, server will response a binlog network stream which contains many binlog events.

BinLog Event

A MySQL binlog event has many versions, but we only care version 4(MySQL 5.0+) and don’t support earlier versions.

A binlog event contains three parts, header, post header and payload, but I like parsing post header and payload at same time.

A event header looks below:

4              timestamp
1              event type
4              server-id
4              event-size
4              log pos
2              flags

You can see all binlog event types here. The log pos is position of the next event, we can save this position for resuming syncing later.

The first binlog event in the binlog file is FORMAT_DESCRIPTION_EVENT, this event is very important, we use it to determine table ID size for row based event, and check the last 5 bytes to see whether CRC32 is enabled or not for the following events, etc…

Another event we should care is ROTATE_EVENT, it tells us a new binlog coming.

Parsing a binlog event is very easy, we only need to refer the document and parse it one by one, except row based replication event.

Row Based Replication

Parsing row based replication event is very hard if we want to know the real data changes for a column in one row.

First, we must parse TABLE_MAP_EVENT, payload is:

    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
  2              flags

  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7

The post_header_len is saved in FORMAT_DESCRIPTION_EVENT, column count and def tells us how many columns in one row and their data types, like tiny int, short int, float, set, etc. column meta def is tricky, but we must use it to parse following ROWS_EVENT.

A ROWS_EVENT contains WRITE_ROWS_EVENT(insert), DELETE_ROWS_EVENT(delete) and UPDATE_ROWS_EVENT(update), every event contains v0, v1 and v2 three versions, most of time, we only need to care v1 and v2.

A ROWS_EVENT looks below:

  if post_header_len == 6 {
4                    table id
  } else {
6                    table id
2                    flags
  if version == 2 {
2                    extra-data-length
string.var_len       extra-data

lenenc_int           number of columns
string.var_len       columns-present-bitmap1, length: (num of columns+7)/8
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       columns-present-bitmap2, length: (num of columns+7)/8

string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap1'+7)/8
string.var_len       value of each field as defined in table-map
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap2'+7)/8
string.var_len       value of each field as defined in table-map
  ... repeat rows until event-end

I promise that if you don’t dive into the MySQL source, you can not understand how to parse it at all.

First, let’s see columns-present-bitmap, for a column, it will not be saved in the row data if the associated bit in columns-present-bitmap is 0, and we will skip this column when parsing.

For every row data, first we should calculate the null-bitmap, a big pitfall here, we calculate columns-present-bitmap using (num of columns+7)/8, but we must use (bits set in ‘columns-present-bitmap’+7)/8 for null-bitmap. (You should google bits set if you don’t understand it).

From MySQL 5.6, it supports another two binlog row images: minimal and noblob. For minimal row image update, if we have 16 columns and only the first column data changed, if we use (num of columns+7)/8, we should use 2 bytes to store null bitmap, but if we use (bits set in ‘columns-present-bitmap’+7)/8, we will only use 1 bytes to store null bitmap, saving 1 byte(is it really necessary?). By the way, I sent a pull request for python-mysql-replication to handle minimal and noblob row image paring.

Now we get column-present-bitmap and null-bitmap, for a column, if it’s not set in column-present-bitmap or set in null-bitmap, we will know that this column is null for the current row data.

Then we will parse the rest of none null columns. For some special columns, like MYSQL_TYPE_LONG or MYSQL_TYPE_FLOAT, we can know the data length directly, e.g, MYSQL_TYPE_LONG is 4 bytes and MYSQL_TYPE_TINY_INT is 1 byte.

But for other columns, we should use column meta in TABLE_MAP_EVENT to help us determine the data length. For example, a MYSQL_TYPE_BLOB column, if meta is 1, the data is tiny blob and the first 1 byte in data is the length for payload, if meta is 2, the data is short blob and the first 2 bytes is the lenght for payload.

Paring the real data is very hard and I can not illustrate here fully and clearly. I hope you can see the source in MySQL or go-mysql replication pkg if you have some interest.

Semi Sync Replication

At first, I didn’t support semi sync replication in go-mysql, but after I develop go-mysql-elasticsearch, I realize that if I want to sync MySQL changes into elasticsearch more quickly and immediately, supporting semi sync replication is a good choice and it’s easy to do like below:

  • Check whether master supports semi sync or not, using “SHOW VARIABLES LIKE ‘rpl_semi_sync_master_enabled’”.
  • Tell master we support semi sync using “SET @rpl_semi_sync_slave = 1”.

If all is ok, server will prepend two byte before every binlog event. The first byte is 0xef indicating semi sync and the second byte is semi sync ACK flag, if 1, we must reply a semi sync ACK packet.

It now seems that this is a wise decision. In facebook, they even develop a semi sync binlog, you can see more here. I develop a similar go-mysqlbinlog supporting semi sync too, but it still needs improvement for production environment.


Learning mysql protocol is a hard but happy journey for me, and I have done some interesting things too, like mixer, a MySQL proxy which the modified version(cm) has been used in production in wandoujia company. go-mysql-elasticsearch, a tool to sync MySQL data into elasticsearch immediately.

Now I have been developing go-mysql, a powerful MySQL toolset. I would be very glad if some people find it can help them for their own MySQL development too.

Later I will also try to use go-mysqlbinlog + MHA to build our own MySQL HA solution, I don’t know perl, but luckily, I can understand MHA code.

Below is my contact, any advice and feedback is very welcome.

  • Email:

  • Skype: live:siddontang_1

深入解析MySQL replication协议


最开始的时候,go-mysql只是简单的抽象mixer的代码,提供一个基本的mysql driver以及proxy framework,但做到后面,笔者突然觉得,既然研究了这么久mysql client/server protocol,干脆顺带把replication protocol也给弄明白算了。现在想想,幸好当初决定实现了replication的支持,不然后续go-mysql-elasticsearch这个自动同步MySQL到Elasticsearch的工具就不可能在短时间完成。

其实MySQL replication protocol很简单,client向server发送一个MySQL binlog dump的命令,server就会源源不断的给client发送一个接一个的binlog event了。


首先,我们需要伪造一个slave,向master注册,这样master才会发送binlog event。注册很简单,就是向master发送COM_REGISTER_SLAVE命令,带上slave相关信息。这里需要注意,因为在MySQL的replication topology中,都需要使用一个唯一的server id来区别标示不同的server实例,所以这里我们伪造的slave也需要一个唯一的server id。

Binlog dump

最开始的时候,MySQL只支持一种binlog dump方式,也就是指定binlog filename + position,向master发送COM_BINLOG_DUMP命令。在发送dump命令的时候,我们可以指定flag为BINLOG_DUMP_NON_BLOCK,这样master在没有可发送的binlog event之后,就会返回一个EOF package。不过通常对于slave来说,一直把连接挂着可能更好,这样能更及时收到新产生的binlog event。

在MySQL 5.6之后,支持了另一种dump方式,也就是GTID dump,通过发送COM_BINLOG_DUMP_GTID命令实现,需要带上的是相应的GTID信息,不过笔者觉得,如果只是单纯的实现一个能同步binlog的工具,使用最原始的binlog filename + position就够了,毕竟我们不是MySQL,解析GTID还是稍显麻烦的。这里,顺带吐槽一下MySQL internal文档,里面关于GTID encode的格式说明竟然是错误的,文档格式如下:

4                n_sids
  for n_sids {
string[16]       SID
8                n_intervals
    for n_intervals {
8                start (signed)
8                end (signed)

但实际坑爹的是n_sids的长度是8个字节。这个错误可以算是血的教训,笔者当时debug了很久都没发现为啥GTID dump一直出错,直到笔者查看了MySQL的源码。

MariaDB虽然也引入了GTID,但是并没有提供一个类似MySQL的GTID dump命令,仍是使用的COM_BINLOG_DUMP命令,不过稍微需要额外设置一些session variable,譬如要设置slave_connect_state为当前已经完成的GTID,这样master就能知道下一个event从哪里发送了。

Binlog Event

对于一个binlog event来说,它分为三个部分,header,post-header以及payload。但实际笔者在处理event的时候,把post-header和payload当成了一个整体body。

MySQL的binlog event有很多版本,但这里笔者只关心version 4的,也就是从MySQL 5.1.x之后支持的版本。而且笔者也只支持这个版本的event解析,首先是不想写过多的兼容代码,另一个更主要的原因就在于现在几乎都没有人使用低版本的MySQL了。

Binlog event的header格式如下:

4              timestamp
1              event type
4              server-id
4              event-size
4              log pos
2              flags

header的长度固定为19,event type用来标识这个event的类型,event size则是该event包括header的整体长度,而log pos则是下一个event所在的位置。


2                binlog-version
string[50]       mysql-server version
4                create timestamp
1                event header length
string[p]        event type header lengths

我们需要关注的就是event type header length这个字段,它保存了不同event的post-header长度,通常我们都不需要关注这个值,但是在解析后面非常重要的ROWS_EVENT的时候,就需要它来判断TableID的长度了。这个后续在说明。


8              position
string[p]      name of the next binlog

它里面其实就是标明下一个event所在的binlog filename和position。这里需要注意,当slave发送binlog dump之后,master首先会发送一个ROTATE_EVENT,用来告知slave下一个event所在位置,然后才跟着FORMAT_DESCRIPTION_EVENT。

其实我们可以看到,binlog event的格式很简单,文档都有着详细的说明。通常来说,我们仅仅需要关注几种特定类型的event,所以只需要写出这几种event的解析代码就可以了,剩下的完全可以跳过。

Row Based Replication

如果真要说处理binlog event有啥复杂的,那铁定属于row based replication相关的ROWS_EVENT了,对于一个ROWS_EVENT来说,它记录了每一行数据的变化情况,而对于外部来说,是需要准确的知道这一行数据到底如何变化的,所以我们需要获取到该行每一列的值。而如何解析相关的数据,是非常复杂的。笔者也是看了很久MySQL,MariaDB源码,以及mysql-python-replication的实现,才最终搞定了这个个人觉得最困难的部分。


    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
  2              flags

  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7

table id需要根据post_header_len来判断字节长度,而post_header_len就是存放到FORMAT_DESCRIPTION_EVENT里面的。这里需要注意,虽然我们可以用table id来代表一个特定的table,但是因为alter table或者rotate binlog event等原因,master会改变某个table的table id,所以我们在外部不能使用这个table id来索引某个table。

TABLE_MAP_EVENT最需要关注的就是里面的column meta信息,后续我们解析ROWS_EVENT的时候会根据这个来处理不同数据类型的数据。column def则定义了每个列的类型。



  if post_header_len == 6 {
4                    table id
  } else {
6                    table id
2                    flags
  if version == 2 {
2                    extra-data-length
string.var_len       extra-data

lenenc_int           number of columns
string.var_len       columns-present-bitmap1, length: (num of columns+7)/8
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       columns-present-bitmap2, length: (num of columns+7)/8

string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap1'+7)/8
string.var_len       value of each field as defined in table-map
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap2'+7)/8
string.var_len       value of each field as defined in table-map
  ... repeat rows until event-end

ROWS_EVENT的table id跟TABLE_MAP_EVENT一样,虽然table id可能变化,但是ROWS_EVENT和TABLE_MAP_EVENT的table id是能保证一致的,所以我们也是通过这个来找到对应的TABLE_MAP_EVENT。


首先我们需要得到columns present bitmap的数据,这个值用来表示当前列的一些状态,如果没有设置,也就是某列对应的bit为0,表明该ROWS_EVENT里面没有该列的数据,外部直接使用null代替就成了。

然后就是null bitmap,这个用来表明一行实际的数据里面有哪些列是null的,这里最坑爹的是null bitmap的计算方式并不是(num of columns+7)/8,也就是MySQL计算bitmap最通用的方式,而是通过columns present bitmap的bits set个数来计算的,这个坑真的很大,为啥要这么设计,最主要的原因就在于MySQL 5.6之后binlog row image的格式增加了minimal和noblob,尤其是minimal,update的时候只会记录相应更改字段的数据,譬如我一行有16列,那么用2个byte就能搞定null bitmap了,但是如果这时候只有第一列更新了数据,其实我们只需要使用1个byte就能记录了,因为后面的铁定全为0,就不需要额外空间存放了,不过话说真有必要这么省空间吗?

null bitmap的计算需要通过columns present bitmap的bits set计算,bits set其实也很好理解,就是一个byte按照二进制展示的时候1的个数,譬如1的bits set就是1,而3的bits set就是2,而255的bits set就是8了。

好了,得到了present bitmap以及null bitmap之后,我们就能实际解析这行对应的列数据了,对于每一列,首先判断是否present bitmap标记了,如果为0,则跳过用null表示,然后在看是否在null bitmap里面标记了,如果为1,表明值为null,最后我们就开始解析真有有数据的列了。

但是,因为我们得到的是一行数据的二进制流,我们怎么知道一列数据如何解析?这里,就要靠TABLE_MAP_EVENT里面的column def以及meta了。

column def定义了该列的数据类型,对于一些特定的类型,譬如MYSQL_TYPE_LONG, MYSQL_TYPE_TINY等,长度都是固定的,所以我们可以直接读取对应的长度数据得到实际的值。但是对于一些类型,则没有这么简单了。这时候就需要通过meta来辅助计算了。

譬如对于MYSQL_TYPE_BLOB类型,meta为1表明是tiny blob,第一个字节就是blob的长度,2表明的是short blob,前两个字节为blob的长度等,而对于MYSQL_TYPE_VARCHAR类型,meta则存储的是string长度。这里,笔者并没有列出MYSQL_TYPE_NEWDECIMAL,MYSQL_TYPE_TIME2等,因为它们的实现实在是过于复杂,笔者几乎对照着MySQL的源码实现的。

搞定了这些,我们终于可以完整的解析一个ROWS_EVENT了,顺带说一下,python-mysql-replication里面minimal/noblob row image的支持,也是笔者提交的pull request,貌似是笔者第一次给其他开源项目做贡献。


实现MySQL replication protocol的解析真心是一件很有挑战的事情,虽然辛苦,但是让笔者更加深入的学习了MySQL的源码,为后续笔者改进LedisDB的replication以及更深入的了解MySQL的replication打下了坚实的基础。






mysql> show create table tt \G;
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) NOT NULL DEFAULT '0',
  `fileid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fileid` (`fileid`)
1 row in set (0.00 sec)


shell 1:

shell 1> update tt set id = 2 where fileid = 1;

shell 2:

shell 2> update tt set id = 3 where fileid = 1;

shell 3:

shell 3> update tt set id = 4 where fileid = 1;

假设shell 1先执行,这时候2和3会block,然后shell 1 commit提交,我们发现shell 2执行成功,但是3出现死锁错误,通过show engine innodb status我们得到如下死锁信息:

2015-01-23 14:24:16 10ceed000
TRANSACTION 24897, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x10cea5000, query id 138 root updating
update tt set id = 4 where fileid = 1
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24897 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

TRANSACTION 24896, ACTIVE 8 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 7, OS thread handle 0x10ceed000, query id 136 root updating
update tt set id = 3 where fileid = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;



顺带再说一下,MySQL 加锁处理分析这篇文章也是干活满满,这两篇加起来深入理解了,对MySQL的deadlock就会有一个很全面的认识了。



    id INT,
    tp ENUM ("t1", "t2"),

假设我们需要删除类型为t2的数据,语句可能是这样delete from t where tp = "t2",这样没啥问题,但我们这张表有5亿数据,好吧,真的是5亿,所以以后别再跟我说MySQL表存储百万级别数据就要分表了,百万太小case了。

这事情我交给了一个小盆友去帮我搞定,他最开始写出了如下的语句delete from t where tp = "t2" limit 1000,使用limit来限制一次删除的个数,可以了,不过这有个很严重的问题,就是越往后,随着t2类型的减少,我们几乎都是全表遍历来删除,所以总的应该是O(n*n)的开销。

于是我让他考虑主键,每次操作的时候,记录当前最大的主键,这样下次就可以从这个主键之后开始删除了,首先 select id from t where id > last_max_select_id and tp = "t2" limit 1000,然后delete from t where id in (ids),虽然这次优化采用了两条语句,但是通过主键,我们只需要遍历一次表就可以了,总的来说,性能要快的。

但是,实际测试的时候,我们却发现,select这条语句耗时将近30s,太慢了。虽然我们使用了主键,但是MySQL仍然需要不停的读取数据判断条件,加之t2类型的数据在表里面比较少量,所以为了limit 1000这个条件,MySQL需要持续的进行IO读取操作,结果自然是太慢了。

想清楚了这个,其实就好优化了,我们只需要让条件判断在应用层做,MySQL只查询数据返回,语句就是 select id, tp from t where id > last_max_select_id limit 1000,得到结果集之后,自行判断需要删除的id,然后delete。看似我们需要额外处理逻辑,并且网络开销也增大了,但MySQL只是简单的IO读取,非常快,总的来说,性能提升很显著。当然笔者后续还需要更深入的分析。



对于多数应用来说,MySQL都是作为最关键的数据存储中心的,所以,如何让MySQL提供HA服务,是我们不得不面对的一个问题。当master当机的时候,我们如何保证数据尽可能的不丢失,如何保证快速的获知master当机并进行相应的故障转移处理,都是需要我们好好思考的。这里,笔者将结合这段时间做的MySQL proxy以及toolsets相关工作,说说我们现阶段以及后续会在项目中采用的MySQL HA方案。



为了解决这个问题,我们可以使用semi-synchronous replication,semi-synchronous replication的原理很简单,当master处理完一个事务,它会等待至少一个支持semi-synchronous的slave确认收到了该事件并将其写入relay-log之后,才会返回。这样即使master当机,最少也有一个slave获取到了完整的数据。

但是,semi-synchronous并不是100%的保证数据不会丢失,如果master在完成事务并将其发送给slave的时候崩溃,仍然可能造成数据丢失。只是相比于传统的异步复制,semi-synchronous replication能极大地提升数据安全。更为重要的是,它并不慢,MHA的作者都说他们在facebook的生产环境中使用了semi-synchronous(这里),所以我觉得真心没必要担心它的性能问题,除非你的业务量级已经完全超越了facebook或者google。



在后续的项目中,笔者会优先使用semi-synchronous replication的解决方案,如果数据真的非常重要,则会考虑使用gelera。






  1. 机器当机,这样MySQL以及agent都会当掉,agent与zookeeper连接自然断开
  2. MySQL当掉,agent发现ping不通,主动断开与zookeeper的连接
  3. Agent当掉,但MySQL未当

上面三种情况,我们都可以认为MySQL机器出现了问题,并且zookeeper能够立即感知。agent与zookeeper断开了连接,zookeeper触发相应的children changed事件,监控到该事件的管控服务就可以做相应的处理。譬如如果是上面前两种情况,管控服务就能自动进行failover,但如果是第三种,则可能不做处理,等待机器上面crontab或者supersivord等相关服务自动重启agent。







原则很简单,哪一个slave拥有最近最多的原master数据,就选哪一个作为新的master。我们可以通过show slave status这个命令来获知哪一个slave拥有最新的数据。我们只需要比较两个关键字段Master_Log_File以及Read_Master_Log_Pos,这两个值代表了slave读取到master哪一个binlog文件的哪一个位置,binlog的索引值越大,同时pos越大,则那一个slave就是能被提升为master。这里我们不讨论多个slave可能会被提升为master的情况。

在前面的例子中,假设b被提升为master了,我们需要将c重新指向新的master b来开始复制。我们通过CHANGE MASTER TO来重新设置c的master,但是我们怎么知道要从b的binlog的哪一个文件,哪一个position开始复制呢?


为了解决这一个问题,MySQL 5.6之后引入了GTID的概念,即uuid:gid,uuid为MySQL server的uuid,是全局唯一的,而gid则是一个递增的事务id,通过这两个东西,我们就能唯一标示一个记录到binlog中的事务。使用GTID,我们就能非常方便的进行failover的处理。

仍然是前面的例子,假设b此时读取到的a最后一个GTID为3E11FA47-71CA-11E1-9E33-C80AA9429562:23,而c的为3E11FA47-71CA-11E1-9E33-C80AA9429562:15,当c指向新的master b的时候,我们通过GTID就可以知道,只要在b中的binlog中找到GTID为3E11FA47-71CA-11E1-9E33-C80AA9429562:15这个event,那么c就可以从它的下一个event的位置开始复制了。虽然查找binlog的方式仍然是顺序查找,稍显低效暴力,但比起我们自己去猜测哪一个filename和position,要方便太多了。

google很早也有了一个Global Transaction ID的补丁,不过只是使用的一个递增的整形,LedisDB就借鉴了它的思路来实现failover,只不过google貌似现在也开始逐步迁移到MariaDB上面去了。

MariaDB的GTID实现跟MySQL 5.6是不一样的,这点其实比较麻烦,对于我的MySQL工具集go-mysql来说,意味着要写两套不同的代码来处理GTID的情况了。后续是否支持MariaDB再看情况吧。

Pseudo GTID

GTID虽然是一个好东西,但是仅限于MySQL 5.6+,当前仍然有大部分的业务使用的是5.6之前的版本,笔者的公司就是5.5的,而这些数据库至少长时间也不会升级到5.6的。所以我们仍然需要一套好的机制来选择master binlog的filename以及position。

最初,笔者打算研究MHA的实现,它采用的是首先复制relay log来补足缺失的event的方式,但笔者不怎么信任relay log,同时加之MHA采用的是perl,一个让我完全看不懂的语言,所以放弃了继续研究。

幸运的是,笔者遇到了orchestrator这个项目,这真的是一个非常神奇的项目,它采用了一种Pseudo GTID的方式,核心代码就是这个

create database if not exists meta;

drop event if exists meta.create_pseudo_gtid_view_event;

delimiter ;;
create event if not exists
  on schedule every 10 second starts current_timestamp
  on completion preserve
      set @pseudo_gtid := uuid();
      set @_create_statement := concat('create or replace view meta.pseudo_gtid_view as select \'', @pseudo_gtid, '\' as pseudo_gtid_unique_val from dual');
      PREPARE st FROM @_create_statement;
      EXECUTE st;

delimiter ;

set global event_scheduler := 1;


继续上面的例子,假设c最后一次出现uuid的位置为s1,我们在b里面找到该uuid,位置为s2,然后依次对比后续的event,如果不一致,则可能出现了问题,停止复制。当遍历到c最后一个binlog event之后,我们就能得到此时b下一个event对应的filename以及position了,然后让c指向这个位置开始复制。

使用Pseudo GTID需要slave打开log-slave-update的选项,考虑到GTID也必须打开该选项,所以个人感觉完全可以接受。

后续,笔者自己实现的failover工具,将会采用这种Pseudo GTID的方式实现。

在《MySQL High Availability》这本书中,作者使用了另一种GTID的做法,每次commit的时候,需要在一个表里面记录gtid,然后就通过这个gtid来找到对应的位置信息,只是这种方式需要业务MySQL客户端的支持,笔者不很喜欢,就不采用了。


MySQL HA一直是一个水比较深的领域,笔者仅仅列出了一些最近研究的东西,有些相关工具会尽量在go-mysql中实现。





table tbl,
fileid int,
groupid int,
opver int,
name varchar(1024),
entid int,
primary key (fileid),
engine = innodb


因为使用的mysql engine是innodb,所以对于主键使用的是聚簇索引,对于聚簇索引我们知道它是吧数据存放到index里面的,也就是通过主键查询就能直接定位到数据,非常方便。

以前我受到的教育就是,select *是邪恶的,因为会取出所有列的数据。诚然,对于数据的获取,我们是按需索取,这样就能极大的减少网络传输的消耗。正因为以前有这样的认识,我自然认为innodb在server端io数据读取的时候也是按需索取。但其实不是这样。

在innodb里面,数据是按照页来存放的,通常一页为16k,每一页至少存放2条数据,也就是说,每一条数据在最大为8k。这里有童鞋就可能疑惑了,那如果我表结构里面有blob,text这种类型的肿么办?对于这种大数据类型的,innodb会将其存放到一个overflow page里面去,然后数据页只存放对应的指针。同样,innodb也是按照页来读取数据,也就是说一次读取16k。



  • guid会导致主键过于分散,以至于在数据进行插入,删除,更新的时候会进行b+tree的频繁分裂,同时读取数据的时候因为过于分散会导致io随即读取,性能不高。
  • 自增id虽然能让io进行顺序读取,但是如果服务器数据量太大造成分表了,对于每张表的auto increment的初始值维护也是一件很烦的事情。
  • 鉴于上面那种情况,我们现在考虑的做法就是提供一个id生成器,用来保证主键的顺序递增,同时又保证全局的完全唯一。这个id生成器很容易,使用redis的incr就可以搞定,而且还可以通过incrby进行批量申请,性能妥妥的。


一般为了查询方便,我们有可能在多列上面建立联合索引,譬如在(groupid, opver)上面,那么我就可以很方便的使用如下索引:

select * from tbl where groupid in (1,2,3)
select * from tbl where groupid = 1 and opver > 10 and opver < 100
select * from tbl where groupid = 1 and opver > 10 order by opver desc limit 10


select * from tbl where groupid in (1,2,3) and opver > 10 order by opver desc limit 10

对于这个查询,我自然想到的就是首先mysql会通过索引选出groupid为1,2,3的,然后再在这个结果集里面选出opver满足的进行排序。可真的是这样吗。使用explain之后,才发现,在extra那一栏生生的出现了using filesort。也就是说mysql并没有对opver使用索引进行order by,而是使用了filesort,这里我们的索引在groupid之后就无效了。





select groupid, opver from tbl where groupid = 1 and opver > 10;

select fileid, groupid, opver from tbl where groupid = 1 and opver > 10 order by opver limit 10

因为我们在(groupid, opver)上面建立了索引,那么当我们查询的所有列包含在该索引里面的时候,我们就可以通过覆盖索引直接找到。通过explain可以看到extra里面有using index,表明使用了覆盖索引。



select entid from t1 order by opver limit 100000, 10;

select entid from t1 inner join (select fileid from t1 order by opver limit 100000, 10) as ac using(fileid)






mysql对于filesort,有两种处理方式,一种是单路排序,一种是双路排序。单路排序直接就是取出选择的字段,然后在sort buffer中排序。而双路排序则是取出排序字段以及主键,排序完成之后在通过主键获取实际的数据。可以看出,双路排序会有两次io操作,自然性能会差一点。

早期mysql采用的是双路排序,现在采用的是单路,但是如果我们需要排序的数据超过了配置的sort buffer空间,或者需要排序的单行数据长度大于max_length_for_sort_data,mysql仍然会使用单路排序。mysql默认max_length_for_sort_data为1024,这个需要根据实际数据长度进行配置。