Last article( https://blog.csdn.net/aofavx/article/details/106783795 )Mysql master-slave mode deployment process has been completed. Now use MHA to achieve high availability of database.
Reference website: https://www.cnblogs.com/keerya/p/7883766.html
1. Software version and environment:
System: CentOS7 system
Software required
MySQL 5.7.30 installation package: mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
Mha4mysql management end file: mha4mysql-manager-0.58-0.el7 centos.noarch.rpm
Mha4mysql node file: mha4mysql-node-0.58-0.el7 centos.noarch.rpm
2. IP and roles of each machine
172.18.0.12 - Master master database - responsible for data writing
172.18.0.13 – slave from library 1 - responsible for data reading
172.18.0.14 – slave from library 2 - responsible for data reading
172.18.0.15 – MHA server - database node monitoring and automatic switching of master/slave nodes.
2. Construction of MHA high availability environment
1. Grant administrator users remote access on the master node.
grant all on *.* to 'mhaadmin'@'%' identified by 'mhaadmin';
3. Generate key on each node server
SSH keygen - t RSA (path default, no password)
4. Copy public key to other servers
ssh-copy-id -i .ssh/id_rsa.pub root@172.18.0.12 ssh-copy-id -i .ssh/id_rsa.pub root@172.18.0.13 ssh-copy-id -i .ssh/id_rsa.pub root@172.18.0.14 ssh-copy-id -i .ssh/id_rsa.pub root@172.18.0.15
5. Download MHA installation package address https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
6. Install mha
yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm 4 All nodes are installed yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha Server installation
There may be perl errors when installing manager. You can modify the yum image address and install the dependency package.
It can also be solved according to the method in the error record below.
7. Set the mha profile
Create / etc / MHA first_ master,/etc/mha_ Master / work folder
[server default] //Configuration for server1,2,3 servers user=mhaadmin //mha management user password=mhaadmin //mha management password manager_workdir=/etc/mha_master/ //mha_master's own work path manager_log=/etc/mha_master/manager.log // mha_master's own log file remote_workdir=/etc/mha_master/work //Where is the working directory for each remote host ssh_user=root // Key authentication based on ssh repl_user=root //Database user name repl_password=root //Database password ping_interval=1 //ping interval length [server1] //Node 2 hostname=172.18.0.12 //Node 2 host address ssh_port=22 //ssh port of node 2 candidate_master=1 //Can I be a candidate / master node in the future [server2] hostname=172.18.0.13 ssh_port=22 candidate_master=1 [server3] hostname=172.18.0.14 ssh_port=22 candidate_master=1
8. Check the communication of each node
masterha_check_ssh -conf=/etc/mha_master/mha.cnf
9. Test cluster connection configuration
masterha_check_repl -conf=/etc/mha_master/mha.cnf
Output log:
Mon Jun 15 05:55:47 2020 - [info] Checking replication health on 172.18.0.13.. Mon Jun 15 05:55:47 2020 - [info] ok. Mon Jun 15 05:55:47 2020 - [info] Checking replication health on 172.18.0.14.. Mon Jun 15 05:55:47 2020 - [info] ok. Mon Jun 15 05:55:47 2020 - [warning] master_ip_failover_script is not defined. Mon Jun 15 05:55:47 2020 - [warning] shutdown_script is not defined. Mon Jun 15 05:55:47 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. //No error message indicates successful startup.
10. Enable MHA
nohup masterha_manager -conf=/etc/mha_master/mha.cnf &> /etc/mha_master/manager.log & //Stop MHA command: masterha_stop -conf=/etc/mha_master/mha.cnf
11. View the master node status
masterha_check_status -conf=/etc/mha_master/mha.cnf
Output log:
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
mha (pid:631) is running(0:PING_OK), master:172.18.0.12
//Indicates that the MHA service is running OK, otherwise, it will be displayed as "mha is stopped(1:NOT_RUNNING)
5. Test the master-slave node switching
- Turn off the master server and simulate the master node crash. View MHA log tail -f /etc/mha_master/manager.log
Indicates that the manager detects the 172.18.0.12 node failure, and then automatically performs the failover to promote 172.18.0.13 to the primary node.
Note that when the failover is complete, the manager will automatically stop using the master ha_ check_ Status command detection will encounter an error message as follows:
13. Add records to the new master database to view the synchronization of the slave database
Record from library:
slave status:
Problem sorting
1. An error is reported when installing MySQL server:
error: Failed dependencies: /usr/bin/perl is needed by mysql-community-server-5.7.30-1.el7.x86_64
Solution:
Install dependency package net-tools.x86_64，libaio.x86_64，perl.x86_64 ，numactl .
yum -y install perl.x86_64 yum -y install libaio.x86_64 yum -y install net-tools.x86_64 yum -y install numactl
Reference website: https://blog.csdn.net/u010886217/article/details/89416159
2. The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
Problem analysis
The problem prompts that the master and slave use the same server UUID, and check one by one:
Check master and slave servers_ ID
show variables like 'server_id';
Check master-slave status:
show master status;
The final inspection found that their auto.cnf The server UUID in is the same because it is a container started from the same msyql image.
Problem solving
Stop mysqld service from the library and delete it auto.cnf File, and then start the database service:
systemctl stop mysqld.service mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak systemctl start mysqld.service
Check the status of the slave database again. The synchronization of the master database has been enabled.
show slave status\G;
Reference website: https://blog.csdn.net/sunbocong/article/details/81634296
3. Installation of MHA manager reports an error
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Error: Package: mha4mysql-manager-0.58-0.el7.centos.noarch (/mha4mysql-manager-0.58-0.el7.centos.noarch)
Requires: perl(Log::Dispatch)
Error: Package: mha4mysql-manager-0.58-0.el7.centos.noarch (/mha4mysql-manager-0.58-0.el7.centos.noarch)
Requires: perl(Log::Dispatch::File)
Error: Package: mha4mysql-manager-0.58-0.el7.centos.noarch (/mha4mysql-manager-0.58-0.el7.centos.noarch)
Requires: perl(Log::Dispatch::Screen)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest
resolvent:
solve: Update epel yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm This method uses all perl dependency packages to report errors. : 1. Installation yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Reference website: https://blog.csdn.net/q936889811/article/details/80077344
https://fedoraproject.org/wiki/EPEL
https://blog.csdn.net/aeolus_pu/article/details/8991652
4. After selecting a new master, the slave database cannot connect to the new master. Run show slave status \G; to report an error
Last_IO_Error: error connecting to master 'root@172.18.0.13:3306' - retry-time: 60 retries: 3
Solution:
Authorize the root user on the new master to access other ip addresses.
grant all privileges on *.* to 'root'@'%' identified by 'root'; privileges
5. After the original offline master is online again, the MHA detects and reports an error:
[external link picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-rtjxeypd-1592279018087) (IMG / image-2020061535321614. PNG))
According to the principle, only one host can write data at the same time, otherwise it may cause catastrophic failure of inconsistent data!
resolvent:
set global read_only=1