MHA builds four machines 1 master and 2 slave 1mha
First set up master-slave
Install mysql
Download MySQL: WGet from the linux command line https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
Unzip: tar -xvf mysql-5.7.28-1 el7. x86_ 64.rpm-bundle. tar
Installation: RPM -ivh mysql-community-common-5.7.28-1 el7. x86_ 64.rpm
rpm -ivh mysql-community-libs-mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
Initialize mysql and generate password: mysqld --initialize --user=mysql
Query password: cat / var / log / mysqld log
Copy the password and log in to mysql: mysql -uroot -p
Change Password:
Turn off the firewall: systemctl stop firewalld
Modify the main library configuration: VI / etc / my cnf
Add configuration
#Open bin_log
log_bin=mysql-bin
#Unique identification of the database
server-id=1
#Sync to disk without refreshing binlog
sync-binlog=1
#Ignore synchronized Libraries
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
Save: wq
Restart mysql: systemctl restart mysqld
Authorization: Grant replication slave on to ‘root’@’%’ identified by ‘root’；
grant all privileges on . to 'root'@'%' identified by 'root'；
Refresh permissions: flush privileges;
View bin_log name and location: show master status;
Installing and closing the firewall from mysql database is the same as the main database
Modify slave library configuration: VI / etc / my cnf
#The other one is changed from library id to 3
server-id=2
#relay_log name
relay_log=mysql-relay-bin
#Set read-only from library
read_only=1
Restart mysql: systemctl restart mysqld
Log in to mysql: mysql -uroot -p
Set master library: change master to master_host=’*****’,master_port=3306,master_user=‘root’,master_password=‘root’,master_log_file=‘mysql-bin.000002’,master_log_pos=‘154’;
Start slave synchronization: start slave;
View synchronization status: show slave status \G
Installation mha:
Install mha4mysql node on four servers
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
wget https://github.com/yoshinorim/mha4mysql- manager/releases/download/v0.58/mha4mysql-manager-0.58- 0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
Install mha4mysql node and mha4mysql manager on the MHA server.
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
wget https://github.com/yoshinorim/mha4mysql- manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Perl parallel forkmanager not found. You can download EPEL: Yum install - y EPEL release
to configure:
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
vim /etc/masterha_default.cnf
[server default] #user name user=root #password password=root #ssh login account ssh_user=root #Master slave copy account repl_user=root #Master slave copy password repl_password=root #ping times ping_interval=1 #Host for secondary inspection secondary_check_script=masterha_secondary_check -s 192.168.247.130 -s 192.168.247.131 -s 192.168.247.132
mkdir /etc/mha
vim /etc/mha/app1.cnf
[server default] manager_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1 [server1] hostname=192.168.247.130 master_binlog_dir="/var/lib/mysql" [server2] hostname=192.168.247.131 master_binlog_dir="/var/lib/mysql" [server3] hostname=192.168.247.132 master_binlog_dir="/var/lib/mysql"
ssh interworking
Execute on four servers: SSH keygen - t RSA generates public and private keys
Then each server executes SSH copy ID 192.168 The ip addresses of the other three servers copy their public keys to other servers (enter the password of the target server)
Ready to start mha
Communication detection: masterha_check_ssh --conf=/etc/mha/app1.cnf
Master slave replication detection: masterha_check_repl --conf=/etc/mha/app1.cnf
Start:
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf – ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
View status
masterha_check_status --conf=/etc/mha/app1.cnf
Problems encountered:
masterha_check_ssh failed
Wed Dec 16 09:56:46 2020 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63] Wed Dec 16 09:56:46 2020 - [debug] Connecting via SSH from root@192.168.247.130(192.168.247.130:22) to root@192.168.247.131(192.168.247.131:22).. Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password). Wed Dec 16 09:56:46 2020 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@192.168.247.130(192.168.247.130:22) to root@192.168.247.131(192.168.247.131:22) failed!
Check whether the firewall is turned off
The ssh from 192.168.247.130 to 192.168.247.131 is not connected
Perform SSH copy ID 192.168.247.131 solution on 192.168.247.130
masterha_check_repl failed
[root@localhost etc]# masterha_check_repl --conf=/etc/mha/app1.cnf Wed Dec 16 11:11:57 2020 - [info] Reading default configuration from /etc/masterha_default.cnf.. Wed Dec 16 11:11:57 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Wed Dec 16 11:11:57 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Wed Dec 16 11:11:57 2020 - [info] MHA::MasterMonitor version 0.58. Wed Dec 16 11:11:58 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.247.131(192.168.247.131:3306) :1130:Host '192.168.247.133' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings. Wed Dec 16 11:11:58 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Enter mysql at 192.168.247.131
Execute grant all privileges on to root@’%’ identified by ‘root’; Allow root to log in remotely
[root@localhost etc]# masterha_check_repl --conf=/etc/mha/app1.cnf Wed Dec 16 11:14:02 2020 - [info] Reading default configuration from /etc/masterha_default.cnf.. Wed Dec 16 11:14:02 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Wed Dec 16 11:14:02 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Wed Dec 16 11:14:02 2020 - [info] MHA::MasterMonitor version 0.58. Wed Dec 16 11:14:02 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.247.132(192.168.247.132:3306) :1045:Access denied for user 'root'@'192.168.247.133' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. Wed Dec 16 11:14:02 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Check whether the configured database password is correct
Wed Dec 16 11:16:14 2020 - [info] Checking replication filtering settings.. Wed Dec 16 11:16:14 2020 - [info] binlog_do_db= , binlog_ignore_db= information_schema,performance_schema,sys Wed Dec 16 11:16:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln443] Binlog filtering check failed on 192.168.247.131(192.168.247.131:3306)! All log-bin enabled servers must have same binlog filtering rules (same binlog-do-db and binlog-ignore-db). Check SHOW MASTER STATUS output and set my.cnf correctly. Wed Dec 16 11:16:14 2020 - [warning] Bad Binlog/Replication filtering rules: 192.168.247.130 (current_master) Binlog_Do_DB: Binlog_Ignore_DB: information_schema,performance_schema,sys Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 192.168.247.131 Binlog_Do_DB: Binlog_Ignore_DB: Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 192.168.247.132 Binlog_Do_DB: Binlog_Ignore_DB: Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:
My. Of master library and slave Library CNF rules are consistent. I ignore information in the main database here_ schema,performance_ Schema, Sys, not from the library
So from the library's my CNF add
vi /etc/my.cnf
add to
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
After the main library is offline, the main library will be online again and switched to the main library again:
View the log on mha tail - F / var / log / mha / app1 / manager log
Stop mysql on the main database server
systemctl stop mysqld
Check which slave library becomes the new master library on the mha server
Restart systemctl start mysqld
Hang to the new master library: change master to master_host=‘192.168.247.130’,master_port=3306,master_user=‘root’,master_password=‘root’,master_log_file=‘mysql-bin.000001’,master_log_pos=154;
Main library: MHA switch_ master_ switch --conf=/etc/mha/app1. cnf --master_ state=alive --new_ master_ host=192.168.247.130 --new_ master_ port=3306 --orig_ master_ is_ new_ slave --running_ updates_ limit=10000
Wed Dec 16 11:57:39 2020 - [info] Skipping executing FLUSH NO_WRITE_TO_BINLOG TABLES. Wed Dec 16 11:57:39 2020 - [info] Checking MHA is not monitoring or doing failover.. Wed Dec 16 11:57:39 2020 - [info] Checking replication health on 192.168.247.132.. Wed Dec 16 11:57:39 2020 - [info] ok. Wed Dec 16 11:57:39 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln1218] 192.168.247.130 is not alive! Wed Dec 16 11:57:39 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterRotate.pm, ln233] Failed to get new master! Wed Dec 16 11:57:39 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/bin/masterha_master_switch line 53.
When the service stops, mha will rewrite / etc / mha / app1 CNF, remove the offline server
[server1] hostname=192.168.247.130 master_binlog_dir="/var/lib/mysql" [server2] hostname=192.168.247.131 master_binlog_dir="/var/lib/mysql" [server3] hostname=192.168.247.132 master_binlog_dir="/var/lib/mysql"
After going offline, it became
[server2] hostname=192.168.247.131 master_binlog_dir="/var/lib/mysql" [server3] hostname=192.168.247.132 master_binlog_dir="/var/lib/mysql"
You need to add the server that was offline before switching