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

MHA (Master High Availability) is currently a relatively mature solution for the high availability of MySQL.

It is a set of excellent high availability software for failover and master-slave promotion in MySQL high availability environment. In

During the MySQL failover process, MHA can automatically complete the database failover operation within 0: 30 seconds.

And in the process of failover, MHA can ensure the consistency of the data to the maximum extent

To the real high availability.

The software consists of two parts: MHA Manager (management node) and MHA Node (data node). MHA Manager

Can be deployed on a separate machine to manage multiple master-slave clusters, or it can be deployed on a single slave

Section

Light it.

MHA Node runs on each MySQL server, and MHA Manager regularly detects master in the cluster

Node, which automatically promotes the slave of the latest data to the new master when the master fails, and then sets the

All other slave points back to the new master. The entire failover process is completely transparent to the application. MHA can

To

Combined with semi-synchronous replication, MHA mainly supports the architecture of one master and multiple slaves. To build MHA, a replication cluster is required.

There must be at least three database servers in, one master and two slaves, that is, one acting as master, one acting as standby master, and another

The other one acts as a slave library.

MHA switching steps:

1. Save binaries from a down master

two。 Detect slave with the latest date to update

3. Apply differential relay relay log to other slave

4. Apply binary daily solstice events saved from master to other slave

5. Upgrade a slave to master

6. Make other slave point to the latest master for replication.

Tools for manager:

Masterha_check_ssh checks the SSH configuration status of MHA

Masterha_check_repl checks MySQL replication status

Masterha_manger starts MHA

Masterha_check_status detects the current MHA running status

Masterha_master_monitor detects whether master is down.

Masterha_master_switch controls failover (automatic or manual)

Masterha_conf_host adds or removes configured server information

Tools for node:

Save_binary_logs saves and copies binary logs of master

Apply_diff_relay_logs identifies differential relay log events and applies their differential events to other slave

Filter_mysqlbinlog removes unnecessary ROLLBACK events (MHA no longer uses this tool)

Purge_relay_logs clears relay logs (does not block SQL threads)

Mha experimental environment configuration: ours is built on the basis of master-slave replication (one master and two slaves). Mha can be placed on a slave or single.

Alone

Run:

Lab environment configuration:

Rhel6.5

Iptables selinux down

172.25.40.1 server1.example.com master

172.25.40.2 server2.example.com slave candidate master

172.25.40.3 server3.example.com slave

Mha

Master side configuration:

Yum install perl-DBD-mysql-yrpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Build secret-free login

Ssh-keygen

# # generating key ssh-copy-id 172.25.40.2

# # distribute it to the other two mysql hosts so that you can connect without secret

Ssh-copy-id 172.25.40.3

Mysql main configuration file (/ etc/my.cnf) configuration

[mysqlnd] server-id=1

# # unique indication of the database, which cannot be repeated

Log-bin=mysql-bin

# # binary record file name is mysql-bin

Binlog-do-db=test # # the same library is test

Binlog-ignore-db=mysql # # out of sync mysql

Gtid-mode=ON # # turn on gtid mode

Enforce-gtid-consistency=ON## forces the use of gtid mode

Slave-parallel-type=LOGICAL_CLOCK # # turn on multithreading mode

Slave-parallel-workers=16 # # start 16 threads

Relay_log_info_repository=TABLE

# # storing relay-log information in tabular form

Relay_log_recovery=ON

Rpl_semi_sync_master_enabled=ON # # set semi-synchronous master and slave to enable

Rpl_semi_sync_slave_enabled=ON

Restart mysql on both servers

/ etc/init.d/mysqld restart

Enter the database to download the semi-synchronization module

Mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.50 sec) mysql > set global rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)

Authorization to copy permissions and all permissions of the root user to the authenticated user created

Mysql > grant all privileges on *. * to 'root'@'172.168.40.%' identified'xxxxxxxxxx';Query OK, 0 rows affected, 1 warning (0.00 sec) bymysql > grant replication slave on *. * to' redhat'@'172.168.40.%' identified by'xxxxxxxxx';mysql > grant all on *. * to root@'172.25.40.%' identified by'xxxxxxxx'; Query OK, 0 rows affected, 1 warning (0.38 sec)

Slave (candidate master) side configuration:

Yum install perl-DBD-mysql-yrpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Build secret-free login

Ssh-keygen

# # generating key

Ssh-copy-id 172.25.40.1 # # is distributed to two other mysql hosts so that there is no secret connection.

Ssh-copy-id 172.25.40.3

Mysql main configuration file (/ etc/my.cnf) configuration

[mysqlnd] except that server-id is different, other parameters are the same server-id=2

Restart mysql on both servers

/ etc/init.d/mysqld restart enters the database to download the semi-synchronization module

Mysql > install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.14 sec) mysql > set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec) mysql > set global read_only=1

# set to read-only mode, because it is an alternative master, when

When master is down, candidate master acts as master, so it does not need to be written into the configuration file.

Query OK, 0 rows affected (0.00 sec)

Authorization to copy permissions and all permissions of the root user to the authenticated user created

Mysql > grant all privileges on. To root@'172.25.40.%' identified by 'xxxxxxxx'

# # this password is the password written in the manager host configuration file

Slave side configuration:

Yum install perl-DBD-mysql-yrpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Build secret-free login

Ssh-keygen

# # generating key

Ssh-copy-id 172.25.40.1 connection # is distributed to two other mysql hosts so that there is no secret connection.

Ssh-copy-id 172.25.40.2

Mysql main configuration file (/ etc/my.cnf) configuration

[mysqlnd] except that server-id is different, other parameters are the same server-id=3

Restart mysql on both servers

/ etc/init.d/mysqld restart

Enter the database to download the semi-synchronization module

Mysql > install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.14 sec) lave_enabled=1;Query OK, 0 rows affected (0.00 mysql > set global rpl_semi_sync_slave_enabled=1)

Authorization to copy permissions and all permissions of the root user to the authenticated user created

Mysql > grant all privileges on *. * to 'root'@'172.168.40.%' identified'xxxxxxxxx';Query OK, 0 rows affected, 1 warning (0.00 sec) bymysql > grant replication slave on *. * to' redhat'@'172.168.40.%' identified by'xxxxxxxx';mysql > change master tomaster_host='172.25.40.1',master_user='redhat',master_password='xxxxxxxxxx',master_auto_position=1

Mha host

Yum install perl-* mha4mysql-manager-0.56-0.el6.noarch.rpm-ymkdir / usr/local/mha

# # creating a working directory workdir

Mkdir / etc/mhacd / etc/mha/

# # create the directory where the configuration file resides

Vim app.conf [server default] manager_workdir=/usr/local/mha manager_log=/usr/local/mha/mha.log master_binlog_dir=/var/lib/mysql user=root # # Database users who log in to the database Password for manager monitoring administrative user and password password=xxxxxxxx # # password for login to database when login password initialization ping_interval=1 remote_workdir=/tmp repl_user=redhat # # replication user (authorized on slave) password ssh_user=root for replication repl_password=xxxxxxxx # # slave replication user between master and slave # # ssh user [server1] hostname=172.25.40.1 port=3306 [server2] hostname=172.25.40.2 port=3306 candidate_master=1 # # standby host check_repl_delay=0 # # when the difference is greater than 100m Without master, the two parameters are usually used together [server3] hostname=172.25.40.3 port=3306 # no_master=1 # # will never be used as a master

Check MySQL replication status

Masterha_check_repl-conf=/etc/mha/app.cnf Tue Apr 24 09:14:45 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Tue Apr 24 09:14:45 2018-[info] Reading application default configuration from / etc/mha/app.conf..Tue Apr 24 09:14:45 2018-[info] Reading server configuration from / etc/mha/app.conf..Tue Apr 24 09:14:45 2018-[info] MHA::MasterMonitor version 0.56.Tue Apr 24 09:14:45 2018-[info] GTID failover mode = 1Tue Apr 24 09:14:45 2018-[info] Dead Servers:Tue Apr 24 09:14:45 2018-[info] Alive Servers:Tue Apr 24 09:14:45 2018-[info] 172.25.40.1 (172.25.40.1 info) Tue Apr 24 09:14:45 2018-[info] 172.25.40.2 (172.25.40.2 info) Tue Apr 24 09:14:45 2018-[info] 172.25.40.3 (172.25.40.3 info) 3306) Tue Apr 24 09:14: 45 2018-[info] Alive Slaves:Tue Apr 24 09:14:45 2018-[info] 172.25.40.2 (172.25.40.2 info) Version=5.7.17-log (oldest major version between slaves) log-bin:enabledTue Apr 24 09:14:45 2018-[info] GTID ONTue Apr 24 09:14:45 2018-[info] Replicating from 172.25.40.1 (172.25.40.1 info) Tue Apr 24 09: 14:45 2018-[info] Primary candidate for the new Master (candidate_master is set) Tue Apr 24 09:14:45 2018-[info] 172.25.40.3 (172.25.40.3 info) Version=5.7.17 (oldest major version between slaves) log-bin:disabledTue Apr 24 09:14:45 2018-[info] GTID ONTue Apr 24 09:14:45 2018-[info] Replicating from 172.25.40.1 (172.25) .40.1 Tue Apr 24 09:14:45 2018-[info] Not candidate for the new Master (no_master is set) Tue Apr 24 09:14:45 2018-[info] Current Alive Master: 172.25.40.1 (172.25.40.1 Not candidate for the new Master 3306) Tue Apr 24 09:14:45 2018-[info] Checking slave configurations..Tue Apr 24 09:14:45 2018-[info] read_only=1 is not set on slave 172.25.40. 2 (172.25.40.2 Tue Apr 3306). Tue Apr 24 09:14:45 2018-[info] read_only=1 is not set on slave 172.25.40.3 (172.25.40.3 read_only=1 is not set on slave 3306). Tue Apr 24 09:14:45 2018-[warning] read_only=1 is not set on slave 172.25.40.3 (172.25.40.3) This host cannot be a master.Tue Apr 24 09:14:45 2018-[info] Checking replication filtering settings..Tue Apr 24 09:14:45 2018-[info] binlog_do_db=, binlog_ignore_db= Tue Apr 24 09:14:45 2018-[info] Replication filtering check ok.Tue Apr 24 09:14:46 2018-[info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.Tue Apr 24 09:14:46 2018-[info] Checking SSH publickey authentication settings on the current master..Tue Apr 24 09:14:46 2018-[info] HealthCheck: SSH to 172.25.40.1 is reachable.Tue Apr 24 09:14:46 2018-[info] 172.25.40.1 (172.25.40.1 Vera 3306) (current master) +-172.25.40.2 (172.25.40.2 Vol 3306) + -- 172.25.40.3 (172.25.40.3) Tue Apr 24 09:14:46 2018-[info] Checking replication health on 172.25.40.2..Tue Apr 24 09:14:46 2018-[info] ok.Tue Apr 24 09:14:46 2018-[info] Checking replication health on 172.25.40.3..Tue Apr 24 09:14:46 2018-[info] ok.Tue Apr 24 09:14:46 2018-[ Warning] master_ip_failover_script is not defined.Tue Apr 24 09:14:46 2018-[warning] shutdown_script is not defined.Tue Apr 24 09:14:46 2018-[info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

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.