MySQL High availability analysis

For most applications, MySQL is the most critical data storage center, so how to make the HA service available to MySQL is a problem we have to face. When Master when the machine, how we ensure that the data as far as possible is not lost, how to ensure a quick knowledge of master and the corresponding failover processing, we need to think carefully. Here, I will combine this time to do MySQL proxy and toolsets related work, talk about our current and follow-up will be used in the project MySQL ha program.

Replication

To ensure that MySQL data is not lost, replication is a good solution, and MySQL provides a powerful replication mechanism. Just we need to know, for performance reasons, replication is the asynchronous mode, that is, the data written is not updated to slave above, if the master when the machine, we can still face the risk of data loss.

To solve this problem, we can use the semi-synchronous replication,semi-synchronous replication principle is very simple, when master finishes a transaction, It waits for at least one slave that supports semi-synchronous to acknowledge receipt of the event and write it to Relay-log before it is returned. This way, even if Master is a machine, at least one slave gets the complete data.

However, semi-synchronous is not 100% guaranteed data is not lost, and if master crashes when the transaction is completed and sent to slave, it can still cause data loss. Just as compared to traditional asynchronous replication, semi-synchronous replication can greatly improve data security. More importantly, it's not slow, and MHA's authors say they used semi-synchronous (here) in their Facebook production environment, so I don't really need to worry about its performance. Unless your level of business has completely surpassed Facebook or Google. As already mentioned in this article, MySQL 5.7 has been using loss-less semi-synchronous replication, so the probability of losing data is already very small.

If you really want to fully guarantee that the data will not be lost, at this stage a better way is to use Gelera, a MySQL cluster solution, it by writing three copies of the strategy to ensure that the data is not lost. I do not have any experience in using Gelera, just know that the industry already has a company to use in the production environment, performance should not be a problem. But Gelera is more intrusive to MySQL code and may not be suitable for some of the students who have code cleanliness:-)

We can also use DRBD to achieve MySQL data replication, MySQL Official document has a document in detail, but the author did not use this package, MHA's author wrote some of the problems using DRDB, here, for reference only.

In subsequent projects, the author will prioritize the use of semi-synchronous replication solutions, and if the data is really important, consider using Gelera.

Monitor

We said we used the replication mechanism to ensure that the data is not lost as much as possible after master, but we can't wait for master to know the problem for a few minutes. So a good set of monitoring tools is essential.

When Master is dropped, monitor can quickly detect and do subsequent processing, such as email notification to the administrator, or notify the daemon to quickly failover.

Usually, for a service monitoring, we use keepalived or heartbeat way, so when the master, we can easily switch to the standby machine. But they still can't detect the service being unavailable very immediately. The author of the company is using the Keepalived method at this stage, but the subsequent author is more inclined to use zookeeper to solve the entire MySQL cluster monitor and failover.

For any MySQL instance, we have a corresponding agent program, the agent with the MySQL instance on the same machine, and timed to send a ping to the MySQL instance to detect its availability, At the same time, the agent is mounted on the zookeeper by means of ephemeral. In this way, we can know whether MySQL is a machine, mainly in the following situations:

1. Machine, so that MySQL and agent will be dropped, agent and zookeeper connection natural disconnect
2. When MySQL dropped, agent found that Ping is not connected, and actively disconnected from the zookeeper
3. Agent dropped, but MySQL didn't

In the three cases above, we can all think that the MySQL machine is having problems and zookeeper can immediately perceive it. Agent and Zookeeper disconnect, zookeeper trigger the corresponding children changed event, monitoring the event of the control services can do the corresponding processing. For example, in the first two cases above, the control service can automatically failover, but if it is a third, it may not be processed, waiting for the machine above the crontab or Supersivord and other related services to automatically restart the agent.

The advantage of using zookeeper is that it can easily monitor the whole cluster, and can get the change information of the whole cluster and trigger the corresponding event to notify the service of interest, and coordinate the processing of multiple services. And these are keepalived or heartbeat that can't be done or too troublesome to do.

The problem with zookeeper is that it is more complex to deploy, and how to get the most current database address is a hassle if failover.

For deployment issues, we want to ensure that a MySQL with an agent, fortunately this year with Docker, so really simple. And for the second database address change problem, actually not use zookeeper, we can notify the application dynamic Update configuration information, VIP, or use proxy to solve.

Although the benefits of zookeeper many, but if your business is not complex, such as only a master, a slave,zookeeper may not be the best choice, maybe keepalived is enough.

Failover

Monitor, we can easily do MySQL monitoring, at the same time in MySQL after the machine to inform the corresponding service to do failover processing, assuming that there is now a MySQL cluster, a for master,b,c for its slave, when a dropped, We need to do failover, then which of the b,c do we choose as the new master?

The principle is simple, which slave has the most recent original master data, whichever one is chosen as the new master. We can passshow slave statusThis command to learn which slave has the latest data. We only need to compare two key fieldsMaster_Log_FileAndRead_Master_Log_Pos, these two values represent the location where slave reads to master which Binlog file, the larger the index value of the Binlog, and the larger the POS, the higher the slave can be promoted to master. Here we do not discuss the case where multiple slave may be promoted to master.

In the previous example, assuming that B was promoted to master, we need to re-point C to the new Master B to start copying. We're going CHANGE MASTER TO to reset C's master, but how do we know which file to Binlog from B, which position to start copying?

GTID

To solve this problem, MySQL 5.6 introduced the concept of Gtid, that is, Uuid:gid,uuid is the MySQL server UUID, is globally unique, and GID is an incremental transaction ID, through these two things, We can only mark a transaction that is recorded in the Binlog. With Gtid, we are able to handle failover very conveniently.

Is still the previous example, assuming B reads a last gtid to3E11FA47-71CA-11E1-9E33-C80AA9429562:23, while the C is3E11FA47-71CA-11E1-9E33-C80AA9429562:15, when C points to the new Master B, we can know by Gtid that as long as we find the Gtid in the Binlog in B3E11FA47-71CA-11E1-9E33-C80AA9429562:15This event, then C can start copying from its next event location. Although the way to find Binlog is still a sequential search, a little less effective violence, but rather than we ourselves to guess which filename and position, it is much more convenient.

Google early also has a global Transaction ID patch, but just use an incremental shaping, ledisdb to learn from its ideas to achieve failover, but Google seems to be gradually moving to mariadb now.

MARIADB's Gtid implementation is not the same as MySQL 5.6, which is actually more troublesome, for my MySQL toolset go-mysql, means to write two different sets of code to handle the situation of Gtid. Follow-up support mariadb look at the situation again.

Pseudo GTID

Although Gtid is a good thing, but only for MySQL 5.6+, there are still most of the business use of the 5.6 version, the author's company is 5.5, and these databases for at least a long time will not be upgraded to 5.6. So we still need a good mechanism to choose the filename of master binlog and position.

At first, the author intends to study the implementation of MHA, it is the first copy of the relay log to complement the missing event, but the author does not trust relay log, but also MHA the use of Perl, a let me completely do not understand the language, so gave up the continuation of research.

Fortunately, I encountered orchestrator this project, this is really a very magical project, it takes a pseudo gtid way, the core code is this

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 second starts current_timestamp on C Ompletion 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; 

It creates an event on top of MySQL, and every 10s, it writes a UUID to a view, and this is recorded in Binlog, although we still can't navigate to an event like Gtid, but we can navigate to a 10s interval, So we can compare two MySQL binlog in a very small range.

Continue with the above example, assuming that the last time the UUID is S1, we find the UUID in B, the position is S2, and then compare the subsequent event, if inconsistent, there may be a problem, stop replication. After traversing to the last Binlog event in C, we can get the filename and position for the next event in B, and then let C point to the location to begin copying.

Using pseudo Gtid requires the option to slave open log-slave-update , considering that the Gtid must also be turned on, so the personal feel is completely acceptable.

Follow-up, the author's own implementation of the failover tool, will use this pseudo gtid way to achieve.

In "MySQL High Availability" This book, the author used another kind of gtid practice, each commit, need to record gtid in a table, and then through the Gtid to find the corresponding location information, Just this way requires the support of the business MySQL client, the author does not like it, it is not used.

Postscript

MySQL ha has been a relatively deep area of water, the author just listed some of the recent research, some of the relevant tools will be as far as possible in the Go-mysql implementation.

Update

After a period of thinking and research, I have a lot of experience and harvest, the design of MySQL ha with a lot of different places before. It turns out that the HA program that I designed was almost the same as Facebook, and recently chatting with people on Facebook and hearing that they were working on it, so they felt the right way.

New ha, I will completely embrace gtid, compare this thing is to solve the original replication that a lot of problems, so I will not consider the non-gtid low version of MySQL. Fortunately, our project has upgraded all MySQL to 5.6 and fully supports Gtid.

Unlike FB that article will mysqlbinlog retrofit support Semi-sync replication protocol, I will go-mysql replication Library support semi-sync replication protocol, This allows the MySQL binlog to be synced to a machine in real time. This is probably the only difference between me and the FB program.

Only synchronous Binlog speed is definitely faster than the original slave, after all, less execution binlog inside the event process, and the other real slaves, we still use the most original synchronization method, do not use Semi-sync replication. We then monitor the entire cluster through MHA and perform failover processing.

I used to think that MHA is not good to understand, but in fact this is a very powerful tool, and really look at Perl, found that still see understand. MHA has been used by many companies in the production environment, has been tested, and directly used is definitely better than writing one for yourself. So later I will not consider zookeeper, consider myself write agent.

However, although the idea is very good, but this set of HA program is not implemented in the project, the main reason is that the author intends to leave the recent, if now rushed to implement, the subsequent problems are no one to maintain. :-)

MySQL High availability analysis