mysql

Dive into MySQL Replication

Dive into MySQL replication protocol

Preface

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:

post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  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:

header:
  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
  }

body:
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
  }

rows:
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.

Summary

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: siddontang@gmail.com

  • Skype: live:siddontang_1

深入解析MySQL replication协议

Why

最开始的时候,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了。

Register

首先,我们需要伪造一个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所在的位置。

在v4版本的binlog文件中,第一个event就是FORMAT_DESCRIPTION_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的长度了。这个后续在说明。

而binlog文件的结尾,通常(只要master不当机)就是ROTATE_EVENT或者STOP_EVENT。这里我们重点关注ROTATE_EVENT,格式如下:

Post-header
8              position
Payload
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的实现,才最终搞定了这个个人觉得最困难的部分。

在详细说明ROWS_EVENT之前,我们先来看看TABLE_MAP_EVENT,该event记录的是某个table一些相关信息,格式如下:

post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  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则定义了每个列的类型。

ROWS_EVENT包含了insert,update以及delete三种event,并且有v0,v1以及v2三个版本。

ROWS_EVENT的格式很复杂,如下:

header:
  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
  }

body:
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
  }

rows:
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。

为了节省空间,ROWS_EVENT里面对于各列状态都是采用bitmap的方式来处理的。

首先我们需要得到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打下了坚实的基础。

话说,现在成果已经显现,不然go-mysql-elasticsearch不可能如此快速实现,后续笔者准备基于此做一个更新cache的服务,这样我们的代码里面就不会到处出现更新cache的代码了。

Docker实践

起因

Docker算是现在非常火的一个项目,但笔者对其一直不怎么感冒,毕竟没啥使用场景。只是最近,笔者需要在自己的mac电脑上面安装项目的开发环境,发现需要安装MySQL,LedisDB,xcodis,Redis,Zookeeper等一堆东西,而同样的流程仍然要在Windows的机器上面再来一遍,陡然觉得必须得有一个更好的方式来管理整个项目的开发环境了。自然,笔者将目光放到了Docker上面。

根据官方自己的介绍,Docker其实是一个为开发和运维人员提供构建,分发以及运行分布式应用的开源平台(野心真的不小,难怪CoreOS要新弄一个Rocket来跟他竞争的)。

Docker主要包括Docker Engine,一个轻量级的运行和包管理工具,Docker Hub,一个用来共享和自动化工作流的云服务。实际在使用Docker的工程中,我们通常都是会在Docker Hub上面找到一个base image,编写Dockerfile,构建我们自己的image。所以很多时候,学习使用Docker,我们仅需要了解Docker Engine的东西就可以了。

至于为啥选用Docker,原因还是很明确的,轻量简单,相比于使用VM,Docker实在是太轻量了,笔者在自己的mac air上面同时可以运行多个Docker container进行开发工作,而这个对VM来说是不敢想象的。

后面,笔者将结合自己的经验,来说说如何构建一个MySQL Docker,以及当中踩过的坑。

MySQL Docker

笔者一直从事MySQL相关工具的开发,对于MySQL的依赖很深,但每次安装MySQL其实是让笔者非常头疼的一件事情,不同平台安装方式不一样,加上一堆设置,很容易就把人搞晕了。所以自然,我的Docker第一次尝试就放到了MySQL上面。

对于mac用户,首先需要安装boot2docker这个工具才能使用Docker,这个工具是挺方便的,但也有点坑,后续会说明。

笔者前面说了,通常使用Docker的方式是在Hub上面找一个base image,虽然Hub上面有很多MySQL的image,但笔者因为开发go-mysql,需要在MySQL启动的时候传入特定的参数,所以决定自行编写Dockerfile来构建。

首先,笔者使用的base image为ubuntu:14.04,Dockerfile文件很简单,如下:

FROM ubuntu:14.04

# 安装MySQL 5.6,因为笔者需要使用GTID
RUN apt-get update \
    && apt-get install -y mysql-server-5.6

# 清空apt-get的cache以及MySQL datadir
RUN apt-get clean
RUN rm -rf /var/lib/apt/lists/* /var/lib/mysql

# 使用精简配置,主要是为了省内存,笔者机器至少要跑6个MySQL
ADD my.cnf /etc/mysql/my.cnf

# 这里主要是给mysql_install_db脚本使用
ADD my-default.cnf /usr/share/mysql/my-default.cnf

# 增加启动脚本
ADD start.sh /start.sh
RUN chmod +x /start.sh

# 将MySQL datadir设置成可外部挂载
VOLUME ["/var/lib/mysql"]

# 导出3306端口
EXPOSE 3306

# 启动执行start.sh脚本
CMD ["/start.sh"]

我们需要注意,对于MySQL这种需要存储数据的服务来说,一定需要给datadir设置VOLUMN,这样你才能存储数据。笔者当初就忘记设置VOLUMN,结果启动6个MySQL Docker container之后,突然发现这几个MySQL使用的是同一份数据。

如果有VOLUMN, 我们可以在docker run的时候指定对应的外部挂载点,如果没有指定,Docker会在自己的vm目录下面生成一个唯一的挂载点,我们可以通过docker inspect命令详细了解每个container的情况。

对于start.sh,比较简单:

  • 判断MySQL datadir下面有没有数据,如果没有,调用mysql_install_db初始化。
  • 允许任意ip都能使用root账号访问,mysql -uroot -e "GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION;",否则我们在外部无法连接MySQL。
  • 启动mysql

构建好了MySQL Docker image,我们就能使用docker run来运行了,很简单

docker run -d -p 3306:3306 --name=mysql siddontang/mysql:latest

这里,我们基于siddontang/mysql这个image创建了一个名叫mysql的container并运行,它会调用start.sh脚本来启动MySQL。

而我们通过docker stop mysql就可以停止mysql container了。

如果笔者需要运行多个MySQL,仅仅需要多新建几个container并运行就可以了,当然得指定对应的端口。可以看到,这种方式非常的简单,虽然使用mysqld_multi也能达到同样的效果,但是如果我需要在新增一个MySQL实例,mysqld_mutli还需要去更改配置文件,以及在对应的MySQL里面设置允许mysqld_multi stop的权限,其实算是比较麻烦的。而这些,在Docker里面,一个docker run就搞定了。

完整的构建代码在这里,mysql-docker,你也可以pull笔者提交到Hub的image siddontang/mysql来直接使用docker pull siddontang/mysql:latest

Boot2Docker Pitfall

从前面可以看到,Docker的使用是非常方便的,但笔者在使用的时候仍然碰到了一点坑,这里记录一下。

IP

最开始碰到的就是ip问题,笔者在run的时候做了端口映射,但是外部使用MySQL客户端死活连接不上,而这个只在笔者mac上面出现,linux上面正常,后来发现是boot2docker的问题,我们需要使用boot2docker ip返回的ip来访问container,在笔者的机器上面,这个ip为192.168.59.103。

Volumn

仍然是boot2docker的问题,笔者在docker run的时候,使用-v来将外部的目录绑定到datadir这个VOLUMN上面,这个在linux上面是成功的,可是在mac上面,笔者发现mysql_install_db死活没有权限写入磁盘。后来才知道,boot2docker只允许对自己VM下面的路径进行绑定。鉴于在mac下面仅仅是调试,数据不许持久化保存,这个问题也懒得管了。反正只要不删除掉container,数据还是会在的。

Flatten Image

在使用Dockerfile构建自己的image的时候,对于Dockerfile里面的每一步,Docker都会生成一个layer来对应,也就是每一步都是一次提交,到最后你会发现,生成的image非常的庞大,而当你push这个image到Hub上面的时候,你的所有layer都会提交上去,加之我们国家的网速水平,会让人崩溃的。

所以我们需要精简生成的image大小,也就是flatten,这个Docker官方还没有支持,但至少我们还是有办法的:

  • docker export and docker import,通过对特定container的export和import操作,我们可以生成一个无历史的新container,详见这里
  • docker-squash,很方便的一个工具,笔者就使用这个进行image的flatten处理。

后记

总的来说,Docker还是很容易上手的,只要我们熟悉了它的命令,Dockerfile的编写以及相应的运行机制,就能很方便的用Docker来进行团队的持续集成开发。而在生产环境中使用Docker,笔者还没有相关的经验,没准后续私有云会采用Docker进行部署。

后续,对于多个Container的交互,以及服务发现,扩容等,笔者也还需要好好研究,CoreOS没准是一个方向,或者研究下rocket :-)

MySQL高可用浅析

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

Replication

要保证MySQL数据不丢失,replication是一个很好的解决方案,而MySQL也提供了一套强大的replication机制。只是我们需要知道,为了性能考量,replication是采用的asynchronous模式,也就是写入的数据并不会同步更新到slave上面,如果这时候master当机,我们仍然可能会面临数据丢失的风险。

为了解决这个问题,我们可以使用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。

如果真的想完全保证数据不会丢失,现阶段一个比较好的办法就是使用gelera,一个MySQL集群解决方案,它通过同时写三份的策略来保证数据不会丢失。笔者没有任何使用gelera的经验,只是知道业界已经有公司将其用于生产环境中,性能应该也不是问题。但gelera对MySQL代码侵入性较强,可能对某些有代码洁癖的同学来说不合适了:-)

我们还可以使用drbd来实现MySQL数据复制,MySQL官方文档有一篇文档有详细介绍,但笔者并未采用这套方案,MHA的作者写了一些采用drdb的问题,在这里,仅供参考。

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

Monitor

前面我们说了使用replication机制来保证master当机之后尽可能的数据不丢失,但是我们不能等到master当了几分钟才知道出现问题了。所以一套好的监控工具是必不可少的。

当master当掉之后,monitor能快速的检测到并做后续处理,譬如邮件通知管理员,或者通知守护程序快速进行failover。

通常,对于一个服务的监控,我们采用keepalived或者heartbeat的方式,这样当master当机之后,我们能很方便的切换到备机上面。但他们仍然不能很即时的检测到服务不可用。笔者的公司现阶段使用的是keepalived的方式,但后续笔者更倾向于使用zookeeper来解决整个MySQL集群的monitor以及failover。

对于任何一个MySQL实例,我们都有一个对应的agent程序,agent跟该MySQL实例放到同一台机器上面,并且定时的对MySQL实例发送ping命令检测其可用性,同时该agent通过ephemeral的方式挂载到zookeeper上面。这样,我们可以就能知道MySQL是否当机,主要有以下几种情况:

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

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

使用zookeeper的好处在于它能很方便的对整个集群进行监控,并能即时的获取整个集群的变化信息并触发相应的事件通知感兴趣的服务,同时协调多个服务进行相关处理。而这些是keepalived或者heartbeat做不到或者做起来太麻烦的。

使用zookeeper的问题在于部署起来较为复杂,同时如果进行了failover,如何让应用程序获取到最新的数据库地址也是一个比较麻烦的问题。

对于部署问题,我们要保证一个MySQL搭配一个agent,幸好这年头有了docker,所以真心很简单。而对于第二个数据库地址更改的问题,其实并不是使用了zookeeper才会有的,我们可以通知应用动态更新配置信息,或者使用proxy来解决。

虽然zookeeper的好处很多,但如果你的业务不复杂,譬如只有一个master,一个slave,zookeeper可能并不是最好的选择,没准keepalived就够了。

Failover

通过monitor,我们可以很方便的进行MySQL监控,同时在MySQL当机之后通知相应的服务做failover处理,假设现在有这样的一个MySQL集群,a为master,b,c为其slave,当a当掉之后,我们需要做failover,那么我们选择b,c中的哪一个作为新的master呢?

原则很简单,哪一个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开始复制呢?

GTID

为了解决这一个问题,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
  meta.create_pseudo_gtid_view_event
  on schedule every 10 second starts current_timestamp
  on completion preserve
  enable
  do
    begin
      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;
      DEALLOCATE PREPARE st;
    end
;;

delimiter ;

set global event_scheduler := 1;

它在MySQL上面创建了一个事件,每隔1s,就将一个uuid写入到一个view里面,而这个是会记录到binlog中的,虽然我们仍然不能像GTID那样直接定位到一个event,但也能定位到一个1s的区间了,这样我们就能在很小的一个区间里面对比两个MySQL的binlog了。

继续上面的例子,假设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中实现。

Mixer MySQL词法分析

介绍

mixer希望在proxy这层就提供自定义路由,sql黑名单,防止sql注入攻击等功能,而这些的基石就在于将用户发上来的sql语句进行解析。也就是我最头大的词法分析和语法分析。

到现在为止,我只是实现了一个比较简单的词法分析器,用以将sql语句分解成多个token。而对于从token在进行语法分析,构建sql的AST,我现在还真没啥经验(编译原理太差了),急需牛人帮忙。

所以,这里只是简单介绍一下mixer的词法分析。

tokenize

在很多地方,我们都需要进行词法分析,通常会有几种方式:

  • 使用一个强大的工具,譬如lex,mysql-proxy就用的这种方式
  • 使用正则表达式
  • state machine

对于使用工具,我觉得有一个不怎么好的地方在于学习成本,譬如我用lex的时候就需要学习它的语法,同时通过工具生成的代码可读性都不怎么好,代码量大,更严重的是可能会比较慢。所以mysql自身也是自己实现一个词法分析模块。

而对于正则表达式,性能问题可能是一个很需要考虑的,而且复杂度并不比使用类似lex这样的工具低。

状态机可能是我觉得自己动手实现词法解析一个很好的方式,对于sql的词法解析,我觉得使用state machine的方式来自己写一个难度并不大,所以mixer自己实现了一个。

state machine

通常,一个状态机的实现采用的是state + action + switch的做法,可能如下:

switch state {
    case state1:
        state = action1()
    case state2:
        state = action2()
    case state3:
        state = action3()
}

对于一个state,我们通过switch知道它将会由哪一个action进行处理,而对于每一个action,我们则知道执行完成之后下一个state是什么。

对于上面的实现,如果state过多,可能会导致太多的case语句,我们可以通过state function进行简化。

一个state function就是执行当前的state action,并且直接返回下一个state function。

我们可以这样做:

type stateFn func(*Lexer) stateFn

for state := startState; state != nil {
    state = state(lexer)
}

所以我们需要实现的就是每一个state function以及对应的它的下一个需要执行的state function。

mixer lexer

mixer的词法分析实现主要参考这个。主要实现在parser模块

对于一个lexer,需要提供的是NextToken的功能,供外部获取下一个token,从而进行后续的操作(譬如语法分析)。

lexer的next token如下:

func (l *Lexer) NextToken() (Token, error) {
    for {
        select {
            case t := <-l.tokens:
                return t, nil
            default:
                if l.state == nil {
                    return Token{TK_EOF, ""}, l.err
                }
                l.state = l.state(l)
                if l.err != nil {
                    return Token{TK_UNKNOWN, ""}, l.err
                }
        }
    }
}

tokens是一个channel,每次state解析的token都会emit到这个channel上面,供NextToken获取,如果channel为空了,则再次调用state function。

可以看到,用go实现一个词法解析是很容易的事情,剩下的就是写相应的state function用来解析sql。

todo

mixer的词法分析还有很多不完善的地方,譬如对于科学计数法数值的解析就不完善,后续准备参考mysql官方的词法分析模块在好好完善一下。

mixer的代码在这里https://github.com/siddontang/mixer,希望感兴趣的童鞋共同完善。

Go MySQL支持database/sql接口

go-mysql已经支持golang database/sql接口,并通过https://github.com/bradfitz/go-sql-test测试用例。

现在go-mysql可以直接通过golang sql接口使用,如下:

import _ "github.com/siddontang/go-mysql/mysql"
import "database/sql"

后续的使用,可以直接参考相关golang sql的教程,譬如这个

golang sql接口的兼容主要在driver.go的文件中,

go-mysql支持的dsn格式为:

<username>:<password>@<host>:<port>/<database>

因为在实现go-mysql的过程中,我就有意识的将一些接口设计成能跟database/sql进行适配。除了Rows接口的适配,因为我总觉得使用起来不方便,但是通过Resultset进行适配Rows也很方便。因为Resultset存储了Query之后所有的数据,所以我们可以通过一个int型iterator,就可以模拟Rows接口:

func (r *mysqlRows) Close() error {
    r.iter = -1
    return nil
}

func (r *mysqlRows) Next(dest []driver.Value) error {
    if r.iter >= r.r.RowNumber() {
        return io.EOF
    }

    data := r.r.Data[r.iter]

    for i := range data {
        //set data[i] to dest[i]
    }

    r.iter++
    return nil
}

Mixer MySQL通讯协议解析

综述

要实现一个mysql proxy,首先需要做的就是理解并实现mysql通讯协议。这样才能通过proxy架起client到server之间的桥梁。

mixer的mysql协议实现主要参考mysql官方的internal manual,并用Wireshark同时进行验证。在实现的过程中,当然踩了很多坑,这里记录一下,算是对协议分析的一个总结。

需要注意的是,mixer并没有支持所有的mysql协议,譬如备份,存储过程等,主要在于精力有限,同时也为了实现简单。

数据类型

mysql协议只有两种基本的数据类型,integer和string。

integer

integer包括fixed length integer和length encoded integer两种,对于length encoded integer,用的地方比较多,这里详细说明一下。

对于一个integer,我们按照如下的方式将其转成length encoded integer:

  • 如果value < 251,使用1 byte
  • 如果value >= 251 同时 value < 2 ** 16,使用fc + 2 byte
  • 如果value >= 2 ** 16 同时 value < 2 ** 24,使用fd + 3 byte
  • 如果value >= 2 ** 24 同时 value < 2 ** 64,使用fe + 8 byte

相应的,对于一个length encoded integer,我们可以通过判断第一个byte的值来转成相应的integer。

string

string包括:

  • fixed length string,固定长度string
  • null terminated string,以null结尾的string
  • variable length string,通过另一个值决定长度的string
  • length encoded string,通过起始length encoded integer决定长度的string
  • rest of packet string,从当前位置到包结尾的string

Packet

在mysql中,如果client或server要发送数据,它需要将数据按照(2 ** 24 - 1)拆分成packet,给每一个packet添加header,然后再以此发送。

对于一个packet,格式如下:

3              payload length
1              sequence id
string[len]    payload

前面3个字节表明的是该packet的长度,每个packet最大不超过16MB。第4个字节表明的是该packet的序列号,从0开始,对于多个packet依次递增,等到下一个新的命令发送数据的时候才重置为0。前面4个字节组成了一个packet的header,后面就是该packet实际的数据。

因为一个packet最大能发送的数据位16MB,所以如果需要发送大于16MB的数据,就需要拆分成多个packet进行发送。

通常,server会回给client三种类型的packet

  • OK Packet,操作成功
  • Err Packet,操作失败
  • EOF Packet,end of file

登陆交互

要实现proxy,首先需要解决的就是登陆问题,包括proxy模拟server处理client的登陆,proxy模拟client登陆server。

为了简单,mixer只支持username + password的方式进行登陆,这应该也是最通用的登陆方式。同时不支持ssl以及compression。

一个完整的登陆流程如下:

  • client首先connect到server
  • server发送initial handshake packet,包括支持的capability,一个用于加密的随机salt等
  • client返回handshake结果,包括自己支持的capability,以及用salt加密的密码
  • server验证,如果成功,返回ok packet,否则返回err packet并关闭连接

这里,不得不说实现登陆协议的时候踩过的一个很大的坑,因为我使用的是HandshakeV10协议,在文档里面,协议有这样的规定:

if capabilities & CLIENT_SECURE_CONNECTION {
    string[$len]   auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
}

如果根据文档的说明,算出来auth-plugin-data-part-2的长度是13,因为auth-plugin-data的长度是20。但是,实际情况是,auth-plugin-data-part-2的长度应该为12,第13位一直为0。只有这样,我们才能根据salt算出正确的加密密码。这一点,在mysql-proxy官方的文档,以及多个msyql client driver上面,Wireshark的分析中都是如此,在go-sql-driver中,作者都直接写了如下的注释:

// second part of the password cipher [12? bytes]
// The documentation is ambiguous about the length.
// The official Python library uses the fixed length 12
// which is not documented but seems to work.

可想而知,这个坑有多坑爹。至少我开始是栽在上面了。加密老是不对。

Command

搞定了登陆,剩下的就是mysql的命令支持,mixer只实现了基本的命令。主要集中在text protocol以及prepared statment里面。

COM_PING

最基本的ping实现,用来检查mysql是否存活。

COM_INIT_DB

虽然叫init db,其实压根干的事情就跟use db一样,用来切换使用db的。

COM_QUERY

可以算是最重要的一个命令,我们在命令行使用的多数mysql语句,都是通过该命令发送的。

在COM_QUERY中,mixer主要支持了select,update,insert,delete,replace等基本的操作语句,同时支持begin,commit,rollback事物操作,还支持set names和set autocommit。

COM_QUERY有4中返回packet

  • OK Packet
  • Err Packet
  • Local In File(不支持)
  • Text Resultset

这里重点说明一下text resultset,因为它包含的就是我们最常用的select的结果集。

一个text resultset,包括如下几个包:

  • 一个以length encoded integer编码的column-count packet
  • column-count个column定义packet
  • eof packet
  • 一个或者多个row packet,每个row packet有column-count个数据
  • eof packet或者err packet

对于一个row packet的里面的数据,我们通过如下方式获取:

  • 如果值为NULL,那么就是0xfb
  • 否则,任何值都是用length encoded string表示

COMSTMT*

COMSTMT族协议就是通常的prepared statement,当我在atlas群里面说支持prepared statement的时候,很多人以为我支持的是在COM_QUERY中使用的prepare,execute和deallocate prepare这组语句。其实这两个还是很有区别的。

为什么我不现在不想支持COM_QUERY的prepare,主要在于这种prepare需要进行变量设置,mixer在后端跟server是维护的一个连接池,所以对于client设置的变量,proxy维护起来特别麻烦,并且每次跟server使用新的连接的时候,还需要将所有的变量重设,这增大了复杂度。所以我不支持变量的设置,这点看cobar也是如此。既然不支持变量,所以COM_QUERY的prepare我也不会支持了。

COMSTMT*这组命令,主要用在各个语言的client driver中,所以我觉得只支持这种的prepare就够了。

对于COM_STMT_EXECUTE的返回结果,因为prepare的语句可能是select,所以会返回binary resultset,binary resultset组成跟前面text resultset差不多,唯一需要注意的就是row packet采用的是binary row packet。

对于每一个binary row packet,第一个byte为0,后面紧跟着一个null bitmap,然后才是实际的数据。

在binary row packet中,使用null bitmap来表明该行某一列的数据为NULL。null bitmap长度通过 (column-count + 7 + 2) / 8计算得到,而对于每列数据,如果为NULL,那么它在null bitmap中的位置通过如下方式计算:

NULL-bitmap-byte = ((field-pos + offset) / 8)
NULL-bitmap-bit  = ((field-pos + offset) % 8)

offset在binary resultset中为2,field-pos为该列的位置。

对于实际非NULL数据,则是根据每列定义的数据类型来获取,譬如如果type为MYSQL_TYPE_LONGLONG,那么该数据值的长度就是8字节,如果type为MYSQL_TYPE_STRING,那么该数据值就是一个length encoded string。

后记

我通过Wireshark分析了一些mysql protocol,主要在这里,这里不得不强烈推荐wireshark,它让我在学习mysql protocol过程中事半功倍。

mixer的代码在这里https://github.com/siddontang/mixer,欢迎反馈。

Mixer 一个用Go实现的MySQL Proxy

介绍

mixer是一个用go实现的mysql proxy,支持基本的mysql代理功能。

mysql的中间件很多,对于市面上面现有的功能强大的proxy,我主要考察了如下几个:

  • mysql-proxy,mysql官方的代理,使用起来并不友好,需要进行lua定制,而且本人对其稳定性和性能存疑。
  • Cobar,阿里的东西,品质没的说,但对于我们项目,有点杀鸡用牛刀的感觉,另外我们都不会java。
  • Atlas,360出品的基于mysql-proxy的增强版,几乎用c重写了核心框架,性能和稳定性都没话说。

当然,还有很多强大的proxy,我不可能一一涉及,而现阶段我们项目中使用的是Atlas(这算不算给Atlas打了一个广告?)。

既然有这么多的proxy,为什么我还想自己实现一个呢?可能最主要的原因在于兴趣使然吧。

mysql功能支持

当开始着手进行mixer开发的时候,我就知道,mixer不是mysql,它不可能proxy所有mysql的功能。所以,我决定mixer只支持如下mysql命令:

  • COM_QUERY
    • select, insert, update, delete, replace
    • set autocommit
    • set names
    • begin, commit, rollback
  • COM_PING
  • COM_INIT_DB
  • COMSTMT_PREPARE, COM_STMT_EXEC等COM_STMT*命令,仅支持上述COM_QUERY命令的prepare

[mixer](https://github.com/siddontang/mixer不支持命令挺多的,列举一些:

  • set variable。如果支持,mixer需要维护每一个变量的状态,增加了复杂度。但mixer支持autocommit和names的设置。
  • sql text模式的prepare statement。
  • show命令。
  • 存储过程。

虽然很多功能现阶段没有,但不排除后续支持。

高可用方案

mixer提供了一套mysql高可用使用方案,现阶段主要功能如下:

  • 读写分离,将select发送到slave,其余发送到master执行,事物所有在master执行。现阶段只支持一主一备。
  • 主备自动切换,当主mysql不可用,根据相关规则切换到backup mysql执行。

Todo

mixer还不完善,很多功能需要实现,后续优先需要实现的功能:

  • parser,将sql进行语法解析,构建AST,在proxy层面就防止一些mysql隐患,譬如注入攻击,delete没有where等。
  • 自定义路由,根据路由规则将sql路由到不同mysql执行。譬如根据主键将select语句hash到不同的slave上面执行。
  • 统计功能。

代码在这里https://github.com/siddontang/mixer。非常希望对proxy感兴趣的童鞋参与进来,共同完善mixer,使其成为另一个mysql中间件解决方案。

Go MySQL接口开发

介绍

go-mysql是一个用go写的mysql driver,使用接口类似于go自身的database sql,但是稍微有一点不同,现阶段还不支持集成进go database/sql中,但实现难度并不大,后续可能会接入。

go-mysql最先开始于mixer(一个用go实现的mysql proxy)中,随着mixer的演化,我觉得有必要将其mysql模块独立出来使用。对于mixer,后续我会详细介绍。

为什么要自己实现一套新的接口,而不是go自身的sql接口呢?最主要的原因在于我很不习惯使用Query的查询方式。go自身的query例子:

age := 27
rows, err := db.Query("SELECT name FROM users WHERE age=?", age)
if err != nil {
    log.Fatal(err)
}
for rows.Next() {
    var name string
    if err := rows.Scan(&name); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

可以看到,使用起来非常的繁琐复杂,如果代码里面select语句很多(恰恰我们代码里面n多select),那么如果每次select都要靠这种方式得到我们需要的结果,那我可能写代码会写崩溃的。所以势必我们需要提供一套封装用来简化select的结果集获取。

Resultset

go-mysql跟go自身的sql接口最大的不一样在于Query的时候直接返回了一个resultset,而这个resultset定义如下:

type Field struct {
    Name []byte
    Type uint8
    Flag uint16
}

type Resultset struct {
    Status uint16 //server status for this query resultset

    Fields     []Field
    FieldNames map[string]int

    Data [][]interface{}
}

Field用来表示查询的时候返回的数据每列的名字,数据类型以及一些特定的Flag。而resultset中的Data则是存放了query结果中对于的实际数据。因为对于mysql select来说,它返回的是一个“m x n”的结果集,我们直接使用[][]interface{}在go中表示。

Resultset提供了非常方便的接口用于select数据的获取:

//指定某一行,某一列获取数据,结果为string
func (r *Resultset) GetString(row, column int) (string, error)
//执行某一行,某一列的名字获取数据,结果为string
func (r *Resultset) GetStringByName(row int, columnName int) (string, error)

接口

go-mysql除了query之外,几乎提供了与go database/sql一样的接口使用方式:

//创建一个db,最大允许保活16个空闲连接
//dsn格式为:<username>:<password>@<host>:<port>/<database>
db := NewDB("qing:admin@127.0.0.1:3306/mixer", 16)

//ping一下,看mysql server是不是还是活的
db.Ping()

//执行exec,包括insert,update,delete,replace
r, err := db.Exec("insert into mixer_conn (id, str) values (1, `abc`)")
println(r.LastInsertId(), r.RowsAffected())

//执行exec,语句中包含 ?占位符,需要传递相应的参数
r, err := db.Exec("insert into mixer_conn (id, str) values (?, ?)", 2, "efg")
println(r.LastInsertId(), r.RowsAffected())

//执行select,得到结果集,并获取相关数据
r, err := db.Query("select str from mixer_conn where id     = 1")
str, _ = r.GetString(0, 0)
str, _ = r.GetStringByName(0, "str")

//开始一个事物
tx, err = db.Begin()

//在事物里面执行语句
tx.Exec("insert into mixer_conn (id, str) values (3, `abc`)")

//提交事物
tx.Commit()

//创建一个prepare statement
s, err := db.Prepare("insert into mixer_conn (id, str) values(?, ?)")

//执行 prepare statement
s.Exec(5, "abc")

//关闭 prepare statement
s.Close()

不光如此,go-mysql还提供单独获取一个conn,用于给外部额外使用的功能,譬如:

conn, err := db.GetConn()

//我需要设置conn的字符集为gb2312
conn.SetCharset("gb2312")    

conn.Close()

可以看到,go-mysql的使用非常简单,现阶段正逐渐在我们项目中使用,也非常期待您的反馈。

MySQL索引研究

介绍

这段时间在重构server,尤其是重新设计表结构以满足功能需求以及后续的性能需求。因为我们使用的是mysql,所以为了支持更多的并发访问,对大数据量的表设计一些index是必不可少的,而现在看我当时设计的index,又发现了很多不足的地方。同时又因为深入重新在学习了解了一些index知识,觉得有必要记录一下,以免自己再犯同样的错误。

假设有如下的表结构,后续所有的例子都通过该表来说明:

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。

对于上面我们的表结构,是完全能够存放到同一个page里面的,也就是说,我们即使select的时候没有选择对应的列,在innodb层面也仍然会将该数据读出,也就说无论怎样,io消耗是一样的。具体可以参考这篇

这里在谈谈主键id类型选择的问题,因为innodb采用的是b+tree来进行数据的存储,网上对于是否采用自增id或者guid这种争论满天飞,现在我的想法如下:

  • 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

在上面那些查询语句立马,index都能很好的工作,然后我就自认为下面这种的也行:

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之后就无效了。

其实这种情况可以用通用的情况来归纳,在一个组合索引里面,如果出现了多个范围查询,那么mysql不可能为每一个范围都使用索引,一般碰到第一个范围之后就会停止索引工作了。

这里给了我一个深刻的教训,就是设计好表结构以及index之后,不要自认为就能按照我想的方式工作了,最好需要explain一下,看看mysql到底是怎么工作的,不然出了性能瓶颈都不知道。

覆盖索引

当我们需要查询的数据通过索引就能查到的时候,这种索引就叫做覆盖索引。譬如下面这些:

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,表明使用了覆盖索引。

对于第二个例子,为什么fileid也可以使用覆盖索引呢,因为对于innodb来说,非聚簇索引保存的是主键id,也就是fileid,所以通过索引也能够直接找到fileid。

使用覆盖索引的好处是很明显的,数据只需要通过索引就能获取,而不需要通过主键在进行随机的io读取。一个很简单的例子。

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)

因为我们需要查询entid,而没有任何一个索引能覆盖entid,这里我们给opver单独建立一个索引。对于第一个查询,mysql因为需要获取entid,所以会通过fileid查到实际的数据并取出,在进行排序,同时因为limit的跨度很大,mysql会丢弃很多数据,所以导致该查询会很慢。

而对于第二个查询,在join立马,我们只是通过覆盖索引找到了fileid,而不需要通过fileid去随机io读取实际数据,取出所有的fileid之后,在取出了entid。虽然该查询有一个join操作,但是因为join的性能很高,所以比第一个查询快很多。

关于索引覆盖,网上有很多关于这个的介绍,譬如这个,总之用好了覆盖索引,是能极大提升效率的,但是也不可能为了覆盖索引去建立覆盖索引,如果索引泛滥了,也是一件很头疼的事情。

filesort

对于先前联合索引遇到的问题,我发现如果不重新设计整个的查询机制,按照现有做法是完全不能避免filesort的。因为我们的业务逻辑就是需要查询一批groupid里面,opver大于某个值的所有数据。既然避免不了,那我们就可以看看到底性能高不高。

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

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

不过如果能把mysql的硬盘换成ssd,或者把内存加到100g以上,我觉得这个filesort也还真不成问题了。

后续

这几天对于mysql索引的研究就先记录一下,感觉对mysql的理解还需要加深,后续如果还有什么新的感想也会在这里记录。同时bs一下自己,号称用了mysql这么多年,到现在了还有很多东西没有领会,是时候开始好好深入研究了。