Based on MySQL database, talk about MySQL high availability operation and maintenance
- php是最好的语言Original
- 2018-07-25 15:52:281898browse
This article will introduce another high-availability implementation solution for MySQL, namely: MHA (Master High Availability). Master HA. It is an open source high-availability program that provides automatic fault recovery function for MySQL's master-slave architecture.
1. Introduction to basic knowledge
1. MHA definition
Master HA. It is an open source high-availability program that provides automatic fault recovery function for MySQL's master-slave architecture. During master-slave replication, when MHA detects a MySQL Master node failure, it will automatically promote a Slave node in the replication cluster to become the new Master node. During switchover, data consistency issues are also avoided by obtaining additional information from other nodes. In addition, MHA also provides an online failover function for the master node, which can switch Master/Slave nodes as needed.
2. MHA structure
All nodes in MHA mainly have two roles, namely: management node and data node.
Management Node (MHA Manager): Generally deployed on a single machine, it is used to manage multiple master and slave clusters, and each cluster composed of master and slave is called an application.
Data Node (MHA Node): Runs on each MySQL server and accelerates node failover by monitoring scripts with the function of parsing and cleaning logs.
3. HMA component
(1) Manager component
masterha_check_sh：MHA依赖的ssh环境监测工具 masterha_check_repl：MySQL复制环境监测工具 masterha_manager：MHA服务主程序 masterha_check_status：MHA运行状态探测工具 masterha_master_monitor：MySQL master节点可用性监测工具 masterha_master_switch：master节点切换工具 masterha_conf_host：添加或删除配置的节点 masterha_stop：关闭MHA服务的工具
(2) Node component
save_binary_logs：保存和赋值master的二进制日志 apply_diff_relay_logs：识别差异的中继日志并应用于其他slave filter_mysqlbinlog：去除不必要的ROLLBACK事件（MHA已经不再使用这个工具） purge_relay_logs：清除中继日志（不会阻塞SQL线程）
(3) Custom extension
secondary_check_script：通过多条网络路由监测master的可用性 master_ip_failover_script：更新application使用的masterip shutdown_script：强制关闭master节点 report_script：发送报告 init_conf_load_script：加载初始配置参数 master_ip_online_change_script：更新master节点ip地址
2. MHA setup
1. Environment preparation
Operating system: CentOS6.9_X86_64
MySQL version: MySQL5.6.39 universal binary
Server planning:
Master node Master: 192.168.199.104 (mysql-master,master)
Slave node 1:192.168.199.105(mysql-slave01,slave01)
Slave node 2:192.168.199.106(mysql-slave02,slave02)
Management node: 192.168.199.107(mysql-manager,manager)
2. Build
(1) Configure the host of each node. It will be more convenient to use later without having to write the IP
[root@mysql-master ~]# vim /etc/hosts #添加如下内容： 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.199.104 mysql-master master 192.168.199.105 mysql-slave01 slave01 192.168.199.106 mysql-slave02 slave02 192.168.199.107 mysql-manager manager
(2) Copy the configured host file to the other three nodes
[root@mysql-master ~]# scp /etc/hosts slave01:/etc/ [root@mysql-master ~]# scp /etc/hosts slave02:/etc/ [root@mysql-master ~]# scp /etc/hosts manager:/etc/
(3) Configure master-slave synchronization, slave01 and slave02 are the slave libraries of the master
Enable the master binary Log
[root@mysql-master ~]# vim /etc/my.cnf #在[mysqld]下面添加如下内容： server-id = 104 skip-name-resolve log_bin=/mysql_data/mysql-bin log_bin_index=/mysql_data/mysql-bin.index binlog_format = mixed
Check the current binary log point location on the master and create a replication account
[root@mysql-master ~]# mysql -uroot –proot
#查看当前二进制日志点的位置
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysql-bin.000090
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
#创建从库的复制账户
mysql> GRANT REPLICATION SLAVE ,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.199.%' IDENTIFIED BY 'repl';
#刷新权限
mysql> FLUSH PRIVILEGES;Enable binary logs and relay logs on slave01, and configure them as the slave library of the master
[root@mysql-slave01 ~]# vim /etc/my.cnf #在[mysqld]下添加如下内容： server-id = 105 log_bin = /mysql_data/mysql-bin log_bin_index = /mysql_data/mysql-bin.index relay_log = /mysql_data/mysql-relay relay_log_index = /mysql_data/mysql-relay.index read_only relay_log_purge = 0
Parameter explanation:
relay_log_purge:该参数表示不自动清理中继日志，因为MHA需要根据slave的中继日志判断slave同步master的binlog到什么位置了 read_only:表示是只读，MHA需要根据这个参数来识别主从库 bin_log:开启从库的二进制日志，因为在主节点出现故障时，需要将其中某个从库提升为主库，所以需要开启从库的二进制日志
Start the slave01 node and point the master node information that needs to be synchronized to the master node:
[root@mysql-slave01 ~]# service mysqld restart [root@mysql-slave01 ~]# mysql -uroot –proot #使用change master命令将主库指向master节点 mysql> CHANGE MASTER TO MASTER_HOST = '192.168.199.104',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000090',MASTER_LOG_POS=120; mysql> START SLAVE; #查看是否同步成功 mysql> SHOW SLAVE STATUS \G 看到如下信息表示同步成功： Slave_IO_Running: Yes Slave_SQL_Running: Yes
Configure the second slave library in the same way, you can Directly copy the configuration file on slave01 to the second slave, and then modify it.
[root@mysql-slave01 ~]# scp /etc/my.cnf slave02:/etc/ [root@mysql-slave02 ~]# vim /etc/my.cnf #修改server-id即可： server-id = 106
After the modification is completed, save and exit, restart mysql, and then configure slave02 as the master's slave library
[root@mysql-slave02 ~]# mysql -uroot –proot mysql> CHANGE MASTER TO MASTER_HOST = '192.168.199.104',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000090',MASTER_LOG_POS=120; mysql> START SLAVE; #查看是否同步成功 mysql> SHOW SLAVE STATUS \G 看到如下信息表示同步成功： Slave_IO_Running: Yes Slave_SQL_Running: Yes
(4) Create a user account with administrative rights
#在主节点master上创建管理账号，使其可以同步到其他连个从节点，注意：这个管理账号需要能够远程连接MySQL mysql> GRANT ALL ON *.* TO 'mha'@'192.168.199.%' IDENTIFIED BY 'mha'; mysql> FLUSH PRIVILEGES;
(5) Since the MHA architecture requires mutual trust communication between each node, each node needs to be configured to log in without a secret key. Since there are many nodes, it is troublesome to generate and copy each one individually. The secret key can be generated on the master node. Yes, and then let each host have the same private key
#在master上生成秘钥对 [root@mysql-master ~]# ssh-keygen -t rsa -P '' #首先保存到本机的秘钥文件中，使得本机可以无秘钥使用ssh方式登陆本机 [root@mysql-master ~]# cat .ssh/id_rsa.pub >> .ssh/authorized_keys #使用ssh登陆本机测试，发现可以无密码登陆 [root@mysql-master ~]# ssh 192.168.199.104 #修改authorized_keys文件的权限，只能属主查看，同组用户和其他用户不可查看 [root@mysql-master ~]# chmod go= .ssh/authorized_keys
Copy the key pair and private key file to the other three nodes. Note: Ensure their original permissions
[root@mysql-master ~]# scp -p .ssh/id_rsa .ssh/authorized_keys slave01:/root/.ssh/ [root@mysql-master ~]# scp -p .ssh/id_rsa .ssh/authorized_keys slave02:/root/.ssh/ [root@mysql-master ~]# scp -p .ssh/id_rsa .ssh/authorized_keys manager:/root/.ssh/ #测试无密码登陆，在主节点上执行如下命令，查看slave01节点的ip地址 [root@mysql-master ~]# ssh slave01 'ifconfig eth0' #能够查看到slave01的ip地址表示无秘钥登陆配置成功，其他俩节点可以自己验证
(6) Download the mha4mysql-manager and mha4mysql-node installation packages
The software package versions used here are as follows:
管理节点：mha4mysql-manager-0.56.tar.gz 数据节点：mha4mysql-node-0.56.tar.gz
(7) Configure epel, because some mha4mysql packages come from base, Some packages come from epel
[root@mysql-master ~]# rpm –ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
(8) Unzip and use the perl command to execute the Makefile.PL script
[root@mysql-master ~]# tar xf mha4mysql-manager-0.56.tar.gz [root@mysql-master ~]# cd mha4mysql-manager-0.56 [root@mysql-master mha4mysql-manager-0.56]# perl Makefile.PL
Note: During the execution of Makefile.PL, if something similar to the following appears If the above error occurs, you need to install the library file that the perl program depends on:
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4. BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4
If the above error occurs, you need to install the dependency:
[root@mysql-master ~]# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan perl-Time-HiRes perl-DBI perl-DBD-MySQL
(9) Make sure "perl MakeFile .PL" is executed correctly, use the make command to complete the installation. Note: You can use "echo $?" to view the execution result of this command to determine whether the previous command was executed correctly. Usually 0 means done correctly.
[root@mysql-master mha4mysql-manager-0.56]# make [root@mysql-master mha4mysql-manager-0.56]# make install
(10) After the manager installation on the master is completed, compile and install mha4mysql-node in the same way
[root@mysql-manager ~]# tar xf mha4mysql-node-0.56.tar.gz [root@mysql-manager ~]# cd mha4mysql-node-0.56 [root@mysql-manager mha4mysql-node-0.56]# perl Makefile.PL #判断Makefile.PL是否正常执行完成 [root@mysql-manager mha4mysql-node-0.56]# echo $? 0 [root@mysql-manager mha4mysql-node-0.56]# make && make install
(11) On the other three servers (master, slave01, slave02) Install mha4mysql-node
[root@mysql-slave01 ~]# tar mha4mysql-node-0.56.tar.gz [root@mysql-slave01 ~]# cd mha4mysql-node-0.56 [root@mysql-slave01 mha4mysql-node-0.56]# perl Makefile.PL [root@mysql-slave01 mha4mysql-node-0.56]# make && make install
The other two nodes are installed in the same way, which is omitted here.
(12) The installation is completed. You can see that there are some scripts under /usr/local/bin, which are script files generated by mha4mysql.
(13) Initialize MHA
Edit mha Configuration files mainly include the following two categories:
global configuration: provides default configuration for each application
application configuration: used to specify which servers
#创建目录并编辑配置文件 [root@mysql-manager ~]# mkdir -pv /etc/masterha/ [root@mysql-manager ~]# vim /etc/masterha/app01.conf
Write the following content:
[server default] user=mha #管理用户的用户名 password=mha #管理用户密码 manager_workdir=/data/masterha/app01 #manager节点的工作目录，用来存放一些二进制日志，不存在会自动创建 manager_log=/data/masterha/app01/manager.log #日志文件位置 remote_workdir=/data/masterha/app01 #远程的每一个节点的工作目录，没有会自动生成 ssh_user=root #需要使用ssh来完成一些管理操作 repl_user=repl #拥有复制权限的用户名 repl_password=repl #拥有复制权限的密码 ping_interval=1 #每隔多长时间监测一次主节点是否在线，心跳信息监测 #其他主机 [server1] hostname=192.168.199.104 #ssh_port=3306 #如果MySQL没有使用默认端口号，则此处需要指定，使用默认端口不需要指定 candidate_master=1 #表示该节点是否在主节点故障之后参与成为主节点 master_binlog_dir=/mysql_data/ #指定mysql的binlog日志路径 [server2] hostname=192.168.199.105 candidate_master=1 master_binlog_dir=/mysql_data/ [server3] hostname=192.168.199.106 #no_master=1 #表示主节点故障之后，该节点不参与成为主节点 master_binlog_dir=/mysql_data/
(14) Check whether the communication between each node is normal
[root@mysql-manager ~]# masterha_check_ssh --conf=/etc/masterha/app01.conf
Error 1:Can't locate Config/Tiny.pm in @INC (@INC contains:XXX
Reason: This error is due to the lack of dependency packages
Solution: Install the prompted dependency packages
If the operating system itself does not come with the following rpm packages, you need to download them from the Internet yourself, you can download them from
http://rpmfind.net/ Download the required rpm packages. The following rpm packages are all for CentOS6.9 Will depend on other packages.
错误2：master_check_ssh执行中的错误：
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
原因：/root/.ssh/下的文件内容不一样或者文件的权限有问题，我遇到的是文件内容有问题。
解决办法：重新将其他主机上的秘钥对和私钥拷贝一份就正常了。
（15）检查主从复制环境是否正常
[root@mysql-manager ~]# masterha_check_repl --conf=/etc/masterha/app01.conf
错误1：
Sat Jun 2 03:07:26 2018 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 27) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right.
原因：缺少perl-DBD-MySQL依赖库
解决办法：
[root@mysql-manager ~]# yum install -y perl-DBD-MySQL*
错误2：
Sat Jun 2 03:11:06 2018 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover
错误3：
Failed to save binary log: Binlog not found from /var/lib/mysql,/var/log/mysql! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master
原因：mysql的binlog日志目录没有在默认的/var/log/mysql下，而是在其他地方，导致目录找不到。
解决办法：在manager节点的配置文件中每个节点下制定二进制日志的目录路径。
[server1] hostname=192.168.199.105 candidate_master=1 master_binlog_dir=/mysql_data/ #指定二进制日志文件目录
错误4：
Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 106. mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
原因：使用ssh连接之后找不到mysqlbinlog命令
解决办法：将/usr/local/mysql-5.6.39/目录连接到/usr/bin下
[root@mysql-master ~]# ln –s /usr/local/mysql-5.6.39/bin /usr/bin [root@mysql-slave01 ~]# ln –s /usr/local/mysql-5.6.39/bin /usr/bin [root@mysql-slave02 ~]# ln –s /usr/local/mysql-5.6.39/bin /usr/bin
（16）启动MHA
#前台启动，日志会直接打印在控制台上 [root@mysql-manager app01]# masterha_manager --conf=/etc/masterha/app01.conf #后台启动 [root@mysql-manager app01]# nohup masterha_manager --conf=/etc/masterha/app01.conf &
（17）验证高可用故障自动转移
#停止master节点的mysqld服务
[root@mysql-master ~]# killall mysqld mysqld_safe
#查看原来的两个slave节点信息，发现已经有slave01被提升为master了
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.105
Master_User: repl
...
#查看slave01的read_only属性，发现已经被修改为OFF了
mysql> SHOW VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+注意：当某一个master故障之后，从库提升为主库，原来的manager节点上的manager服务会自动退出。需要手动重启。
（18）故障的服务器再次恢复时，需要使用”CHANGE MASTER”命令手动将其加入到从节点中
（19）manager节点启动之后，可以使用masterha_check_status命令查看当前的主节点状态信息
[root@mysql-manager ~]# masterha_check_status --conf=/etc/masterha/app01.conf
app01 (pid:20582) is running(0:PING_OK), master:192.168.199.105MHA的其他用法，如在线主从切换等，可以自行参考官方文档，此处不再介绍。另外还有MHA的manager节点的自动重启功能，报警功能，故障主机彻底摘除功能等可以通过shell脚本实现，后面有时间会继续介绍。
至此，MySQL的MHA高可用基本功能搭建完成。整个过程中有无数的坑，可能读者在搭建过程中，还会遇到其他的深坑，最好能够硬着头皮一个一个去解决！篇幅较长，所有内容都是经过验证一个字一个字敲上去的，但也难免有笔误，如有错误，欢迎在下方评论指出！
相关推荐：
The above is the detailed content of Based on MySQL database, talk about MySQL high availability operation and maintenance. For more information, please follow other related articles on the PHP Chinese website!