Shulou(Shulou.com)06/01 Report--

This article introduces how to upgrade MYSQL's MHA program to GTID and binlog server settings, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Today's text

In fact, MHA is really nothing to say, a mature program can not be highly available in a mature MYSQL. But generally speaking, most enterprises deploy MHA with traditional replication methods, and MYSQL has entered the world of GTID since 5.6. while MHA also supports GTID from 0.56, although the Japanese no longer upgrade the version of MHA for everyone, it seems that recently a white boy is continuing to upgrade the latest version of MHA. So far, no database has the high availability of MYSQL (I am sorry, in fact, PG has a lot of solutions).

So we can talk about how to upgrade MYSQL's MHA scheme to GTID, one of which is why there is an extra binlog server setting.

We intercepted a segment from the BLOG of Yoshinori above.

In the binlog section, you can define a mysqlbinlog streaming server. When MHA performs a GTID-based failover, MHA checks the binlog server, and if the BINLOG recorded by the binlog server precedes the other secondary servers, MHA applies differential binlog events from the binlog server to the new primary server before restoring. When MHA performs a non-gtid (traditional)-based failover, MHA ignores the binlog server, and more details can be found in the documentation.

And it is also mentioned that custom mysql binlog location is supported. In the case of MYSQL replication using GTID and auto_postion=1, MHA will not be able to use the old mode to obtain differential logs. Here, using BINLOG SERVER can effectively improve the master-slave switching of enhanced MYSQL, allowing the new master to obtain differential logs from BINLOG SERVER and make up.

Since MYSQL 5.6, MYSQL has provided the concept of BINLOG SEVER, using BINLOG SERVER to back up BINLOG logs, and according to related principles, such backed up BINLOG logs are basically real-time.

First of all, the parameter master_binlog_dir is added from MHA 0.56. this parameter is set to prevent the specific location of the BINLOG from being unable to be obtained after the MYSQL crashes. (of course, if the LINUX system crashes, then this setting is also invalid.)

After starting the replication mode of GTID and adding the BINLOG SERVER option, try to terminate MASTER, and you can see that MHA has automatically determined that MYSQL is replicating in the way GTID is used.

And the location information of the relevant GTID is already in the information.

The switch is successful, so how to establish a BINLOG SERVER is actually necessary in the case of a large number of MYSQL, it is necessary to establish a BINLOG SERVER to store the BINLOG data of the MYSQL server. (you can also specify the binlog directory of master and other slave as binlog server)

It can be set in a defined MYSQL SERVER.

Nohup mysqlbinlog-R-raw-host='192.168.198.201'-port=3306-user='repl'-password='1234.com'-stop-never--stop-never-slave-server-id=1202 mysql-bin.000001-result-file=/data/binlog_backup/ &

Where mysql-bin.000001 tells the BINLOG from which the BINLOG is transmitted

R-- read-from-remote-server: read binlog from a remote machine. To ensure remote mysql storage, you need to provide parameters-- host,-- user,-- password. When using this option, mysqlbinlog will disguise itself as a slave, connect to read, request the specified binlog file, and create a binlog dump thread to push the binlog to mysqlbinlog server after the main database receives the request.

-- raw: store logs in binlog format for later use

-- host: the host IP or hostname of the remote library

-- port: Port number of the remote library

-- user: the account used for replication on the remote library

-- password: copy the password of the account on the remote database

-- stop-never: connect to the remote server to read the binlog log, and will not exit until the remote server is closed. Or be dropped by pkill.

-- stop-never-slave-server-id: if you need to start more than one binlog server, you need to specify server-id for the binlog server.

-- result-file: specify the directory to be stored locally. Note that the suffix needs to be added with /.

After executing the command, you can quickly copy the BINLOG of the source side to the destination machine, but it should be noted that if the source side carries out the purge operation, the log of the destination side will not be reduced, so you also need to consider how to regularly clean up the BINLOG files that are not in use.

At the same time, if you are afraid of the restart of the MASTER host and so on, you can write a script to run it regularly.

The following is a SHELL program found on the Internet

# cat > binlog_cp.sh

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.