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

This article is mainly to give you a brief introduction to MySQL high-availability MHA analysis, you can check the relevant professional terms online or find some related books to supplement, here do not dabble in, go straight to the topic, hope to bring you some practical help.

MHA (MySQL | Master High Availability) is a very mature solution based on the master-slave model. We make a high availability for master, so that even when the master database cloud server goes down, our slave can be loaded in time and directly become the master host to ensure the reliable operation of the service.

Since mha4mysql does not exist in the image repository and epel, I need to download the rpm package.

Mha4mysql-manager-0.56-0.el6.noarch.rpm

Mha4mysql-node-0.56-0.el6.noarch.rpm

It is recommended to separate the mha server from the master server; otherwise, if the master is physically down, the mha will also be down.

Install mha4mysql-node nodes on Master and slave

Mha server both rpm packages need to be installed

MHA: CentOS 7.5B 172.16.75.2

Master: CentOS 7.5D 172.16.75.4

Slave: CentOS 7.5C 172.16.75.3

All three hosts need SSH secret-free communication.

Because if master goes down, slave needs to be topped, so both slave and master need to enable binary log and relay log.

Master profile:

[mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group # customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd innodb_file_per_table=ONskip_name_resolve=ONserver_id=401log_bin=/var/lib/mysql/binlogsync_binlog=1innodb_flush_log_at_trx_commit=1relay_log_purge=0relay_log=relay_log [mysqld_safe] log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory#!includedir / etc/my.cnf.d

Slave profile:

[mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group # customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd innodb_file_per_table=ONskip_name_resolve=ONserver_id=301read_only=ONrelay_log=slavelogrelay_log_purge=0log_bin=binlog [mysqld_safe] log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory#!includedir / etc/my.cnf.d

Then the other configurations remain the same, and the master-slave model is built.

1. After downloading the rpm package of mha, install the corresponding installation package on the corresponding node. By viewing the mha4mysql-manager configuration file, all the scripts are executed in binary. So the configuration file of mha needs to be written by itself.

[root@slave1 ~] # rpm-ql mha4mysql-manager/usr/bin/masterha_check_repl/usr/bin/masterha_check_ssh/usr/bin/masterha_check_status/usr/bin/masterha_conf_host/usr/bin/masterha_manager/usr/bin/masterha_master_monitor/usr/bin/masterha_master_switch/usr/bin/masterha_secondary_check/usr/bin/masterha_stop/usr/share/man/man1/masterha_check_repl.1.gz/usr/ Share/man/man1/masterha_check_ssh.1.gz/usr/share/man/man1/masterha_check_status.1.gz/usr/share/man/man1/masterha_conf_host.1.gz/usr/share/man/man1/masterha_manager.1.gz/usr/share/man/man1/masterha_master_monitor.1.gz/usr/share/man/man1/masterha_master_switch.1.gz/usr/share/man/man1/masterha_secondary_check.1. Gz/usr/share/man/man1/masterha_stop.1.gz/usr/share/perl5/vendor_perl/MHA/Config.pm/usr/share/perl5/vendor_perl/MHA/DBHelper.pm/usr/share/perl5/vendor_perl/MHA/FileStatus.pm/usr/share/perl5/vendor_perl/MHA/HealthCheck.pm/usr/share/perl5/vendor_perl/MHA/ManagerAdmin.pm/usr/share/perl5/vendor_perl/MHA/ManagerAdminWrapper.pm/usr/share/perl5/ Vendor_perl/MHA/ManagerConst.pm/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm/usr/share/perl5/vendor_perl/MHA/MasterRotate.pm/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm/usr/share/perl5/vendor_perl/MHA/Server.pm/usr/share/perl5/vendor_perl/MHA/ServerManager.pm

two。 Create a configuration file corresponding to mha

[root@slave1 ~] # mkdir / etc/mha [root@slave1 ~] # vim / etc/mha/app1.cnf [root@slave1 ~] # [root@slave1 ~] # cat / etc/mha/app1.cnf [server default] user=mha # Log in to user password=mhapassmanager_workdir=/data/masterha/app1manager_log=/data/masterha/app1/manager.logremote_workdir=/data/masterha/app1ssh_user=rootrepl_user=repuser # master to be the replication slave authorized user repl_password=123456ping_interval=1 [server1] hostname=172.16.75.4candidate_ Master=1 [server2] hostname=172.16.75.3candidate_master= 1 [root @ slave1 ~] # [root@slave1 ~] # mkdir-pv / data/masterha/app1mkdir: created directory "/ data" mkdir: created directory "/ data/masterha" mkdir: created directory "/ data/masterha/app1"

3. According to the contents of the configuration file, master also needs to be an authorized user to mha.

Master mysql:

MariaDB [(none)] > grant all on *. * to 'mha'@'%' identified by' mhapass';Query OK, 0 rows affected (0.08 sec)

4. Do ssh test and repl test on mha server (finally show OK)

[root@slave1] # masterha_check_ssh-- conf=/etc/mha/app1.cnfWed Nov 7 20:46:17 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Wed Nov 7 20:46:17 2018-[info] Reading application default configuration from / etc/mastermha/app1.cnf..Wed Nov 7 20:46:17 2018-[info] Reading server configuration from / etc/mastermha/app1.cnf..Wed Nov 7 20:46:17 2018-[info] Starting SSH connection tests..Wed Nov 7 20:46:18 2018-[debug] Wed Nov 7 20:46:17 2018-[debug] Connecting via SSH from root@172.16.75. 4 (172.16.75.4 to root@172.16.75.3 22) to root@172.16.75.3 (172.16.75.3 to root@172.16.75.3 22).. Wed Nov 7 20:46:17 2018-[debug] ok.Wed Nov 7 20:46:18 2018-[debug] Wed Nov 7 20:46:17 2018-[debug] Connecting via SSH from root@172.16.75.3 (172.16.75.3 debug 22) to root@172.16.75.4 (172.16. Wed Nov 7 20:46:18 2018-[debug] ok.Wed Nov 7 20:46:18 2018-[info] All SSH connection tests passed successfully. [root@slave1 ~] # masterha_check_repl-conf=/etc/mha/app.cnf... 172.16.75.4 (172.16.75.4info) (current master) +-172.16.75.3 (172.16.75.3) Thu Nov 8 09:37:35 2018-[info] Checking replication health on 172.16.75.3..Thu Nov 8 09:37:35 2018-[info] ok.Thu Nov 8 09:37:35 2018-[warning] master_ip_failover_script is not defined.Thu Nov 8 09:37: 35 2018-[warning] shutdown_script is not defined.Thu Nov 8 09:37:35 2018-[info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.

5. Start the mha4mysql process

[root@slave1] # nohup masterha_manager-- conf=/etc/mha/app.cnf > / data/masterha/app1/manager.log 2 > & 1 & [1] 85154

6. Detect the status of mha

[root@slave1] # masterha_check_status-- conf=/etc/mha/app.cnfapp (pid:85154) is running (0:PING_OK), master:172.16.75.4

7. Test:

Turn off the mysql process on master; then check the mha log on manager:

Master:

[root@slave2 ~] # systemctl stop mariadb

Manager:

[root@slave1] # masterha_check_status-- conf=/etc/mha/app.cnfapp master is down and failover is running (50:FAILOVER_RUNNING). Master:172.16.75.4 [root@slave1 ~] # cat / data/masterha/app1/manager.log. -Failover Report-app: MySQL Master failover 172.16.75.4 (172.16.75.4 MySQL Master failover 3306) to 172.16.75.3 (172.16.75.3 MySQL Master failover 3306) succeeded Master 172.16.75.4 (172.16.75.4 MySQL Master failover 3306) is down! Check MHA Manager logs at slave1.ljy.com:/data/masterha/app1/manager.log for details. Started automated (non-interactive) failover.The latest slave 172.16.75.3 (172.16.75.3) has all relay logs for recovery.Selected 172.16.75.3 (172.16.75.3) as a new master.172.16.75.3 (172.16.75.3) as a new master.172.16.75.3 (172.16.75.3): OK: Applying all logs succeeded.Generating relay diff files from the latest slave succeeded.172.16.75.3 (172.16.75.3): Resetting slave info succeeded .Master failover to 172.16.75.3 (172.16.75.3virtual 3306) completed successfully.

Finally, when the Master failover to 172.16.75.3 (172.16.75.3 Master failover to 3306) completed successfully.

It means that our master has been transferred to slave, although the previous Master database server down dropped, but we timely switch to the slave host through mha to ensure the reliability of the database server.

The big pit encountered: Checking if super_read_only is defined and turned on..DBD::mysql::st execute failed: Unknown system variable 'super_read_only' at / usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 245.

The initial use of version 0.58 of mha resulted in a "super read only" error in repl testing. This is due to the incompatibility between our mysql version and mha version, so we can switch to version 0.56, because version 0.58 of mha is compatible with later versions of mariadb-10 with the "super_read_only" option. The MariaDB-5.56 installed on our normal CentOS-7 series hosts does not have the "super_read_only" option, so when testing repl permissions, we will say that we have not set this option

In short, mha is highly necessary in the production environment. While we need Proxysql to do read-write separation to improve server performance, we also need mha to do high master to ensure the reliability of the server and ensure the continuous operation of our database.

MySQL high availability MHA analysis will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.