MHA高可用(一主双从)
一 、准备三台服务器（Centos7.x)
10.0.0.41
10.0.0.42
10.0.0.43
二、在三台服务器上同步时间
[root@ localhost ~]# echo "*/5* * * * /usr/sbin/ntpdate ntp1.aliyun.com >/dev/null 2>&1" >>/var/spool/cron/root
修改主机名（主）
[root@ localhost ~]# vim /etc/hostname
master
修改主机名（从）
[root@c7-42 ~]# vim /etc/hostname
slave1
修改主机名（从）
[root@localhost ~]# vim /etc/hostname
slave2
修改完重启机子就可以
这里的主机名要和自己的一样，以便于区分
三、关闭防火墙和selinux （三台服务器执行同样的操作）
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
[root@ master ~]# sed -i ' /^SELINUX/s#enforcing#disabled#g' /etc/selinux/config
[root@slave1 ~]# sed -i ' /^SELINUX/s#enforcing#disabled#g' /etc/selinux/config
[root@slave2 ~]# sed -i ' /^SELINUX/s#enforcing#disabled#g' /etc/selinux/confi
四、配置免密登录（三台都执行）
[root@ master ~]# vim ssh.sh
#!/bin/bash
yum -y install sshpass &> /dev/null
read -p "请输入服务器密码：" passwd
UserName=root
IP="10.0.0."
#创建密钥
ssh-keygen -t dsa -f ~/.ssh/id_dsa -P "" &>/dev/null
#分发公钥
for i in 41 42 43 #改成自己机子的ip
do
sshpass -p "$passwd" ssh-copy-id -i ~/.ssh/id_dsa.pub -p 22 -o StrictHostKeyChecking=no $UserName@$IP$i &>/dev/null
done
五、执行脚本配置，并连接其中一台服务器
[root@ master ~]# sh ssh.sh
请输入服务器密码：123456
[root@ master ~]# ssh root@10.0.0.42
Last login: Tue May 19 23:22:54 2020 from 10.0.0.1
[root@slave1 ~]# exit
logout
Connection to 10.0.0.42 closed.
六、mysql安装yum repo（三台执行同样的操作）
[root@ master ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
[root@ master ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
[root@ master ~]# yum -y install mysql-server
七、启动MySQL，三台执行同样的操作
[root@ master ~]# systemctl restart mysql
八、修改MySQL密码，三台执行同样的操作
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
主master上操作
1.在master服务器上更改mysql配置文件
[root@ master ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
relay_log_purge = 0
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
更改完成后重启mysql
systemctl restart mysql
2.创建同步用户
[root@ master ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.48-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.查看MySQL主库的master状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 530
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 3aaca72f-99a6-11ea-921c-000c29673af1:1-2
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 530 | | | 3aaca72f-99a6-11ea-921c-000c29673af1:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
4.查看GTID状态
mysql> show global variables like '%gtid%';
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | 3aaca72f-99a6-11ea-921c-000c29673af1:1-2 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)
slave1从服务器上操作
1.在slave1服务器上更改mysql配置文件
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
relay_log_purge = 0
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
更改完成后重启mysql
systemctl restart mysql
2.创建同步用户
[root@slave1 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.48-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.关闭复制功能，配置指向master，开启服务器复制状态，检查复制状态，出现俩个yes状态表示成功
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='10.0.0.41',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=530;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 530
Relay_Log_Space: 519
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3aaca72f-99a6-11ea-921c-000c29673af1
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 42015965-99a6-11ea-921d-000c29ade320:1-3
Auto_Position: 0
1 row in set (0.00 sec)
slave2服务器
1.在slave2服务器上更改mysql配置文件
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
[mysqld]
server-id=3
log-bin=mysql-bin
relay_log_purge = 0
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
更改完成后重启mysql
systemctl restart mysql
2.创建同步用户
[root@slave2 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.48-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.关闭复制功能，配置指向master，开启服务器复制状态，检查复制状态，出现俩个yes状态表示成功
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='10.0.0.41',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=530;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 530
Relay_Log_Space: 519
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3aaca72f-99a6-11ea-921c-000c29673af1
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4371cdba-99a6-11ea-921d-000c29877b4b:1-3
Auto_Position: 0
1 row in set (0.00 sec)
在三台服务器装MHA的依赖
yum -y install perl-DBD-mysql
yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
1.授权MHA管理用户（三台执行同样的操作）
mysql> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.安装MHA node节点
[root@ master ~]# ll
total 1128
-rw-------. 1 root root 1273 Apr 17 22:48 anaconda-ks.cfg
-rw-r--r-- 1 root root 3914 May 12 11:11 kp_master.sh
-rw-r--r-- 1 root root 81024 Aug 14 2018 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 36328 Aug 14 2018 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 6140 Nov 12 2015 mysql-community-release-el7-5.noarch.rpm
drwxr-xr-x 9 1001 1001 186 May 12 14:50 nginx-1.14.2
-rw-r--r-- 1 root root 1015384 Dec 4 2018 nginx-1.14.2.tar.gz
-rw-r--r-- 1 root root 376 May 19 15:28 ssh.sh
[root@ master ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-node-0.58-0.el7.centos ################################# [100%]
在slave2上执行
如果安装到错误的机器上可以撤销掉，否则后期vip漂移会出错
[root@slave2 ~]# rpm -qa |grep mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@slave2 ~]# rpm -e mha4mysql-manager
3.安装管理MHA的管理节点
[root@slave2 ~]# ll
total 185172
-rw-------. 1 root root 1273 Apr 17 22:48 anaconda-ks.cfg
-rw-r--r-- 1 root root 8234674 Nov 13 2019 apache-tomcat-7.0.47.tar.gz
-rw-r--r-- 1 root root 181238643 Apr 20 14:40 jdk-8u60-linux-x64.tar.gz
-rw-r--r-- 1 root root 81024 Aug 14 2018 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 36328 Aug 14 2018 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 6140 Nov 12 2015 mysql-community-release-el7-5.noarch.rpm
-rw-r--r-- 1 root root 376 May 19 15:29 ssh.sh
-rw-r--r-- 1 root root 2588 May 12 15:12 super-tomcat.sh
[root@slave2 ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-manager-0.58-0.el7.cent################################# [100%]
[root@slave2 ~]#
4.配置MHA
[root@slave2 ~]# mkdir -p /etc/mha
[root@slave2 ~]# mkdir -p /var/log/mha/app1
[root@slave2 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
user=mha
password=mha
ping_interval=2
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
user=mha
password=mha
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root
[server1]
hostname=10.0.0.41
port=3306
[server2]
hostname=10.0.0.42
port=3306
[server3]
hostname=10.0.0.43
port=3306
ignore_fail=1
no_master=1
#candidate_master=1
#check_repl_delay=0
检查ssh
[root@slave2 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Thu May 21 17:36:31 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 21 17:36:31 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu May 21 17:36:31 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu May 21 17:36:31 2020 - [info] Starting SSH connection tests..
Thu May 21 17:36:34 2020 - [debug]
Thu May 21 17:36:32 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.41(10.0.0.41:22)..
Thu May 21 17:36:33 2020 - [debug] ok.
Thu May 21 17:36:33 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.43(10.0.0.43:22)..
Thu May 21 17:36:33 2020 - [debug] ok.
Thu May 21 17:36:34 2020 - [debug]
Thu May 21 17:36:31 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.42(10.0.0.42:22)..
Thu May 21 17:36:33 2020 - [debug] ok.
Thu May 21 17:36:33 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.43(10.0.0.43:22)..
Thu May 21 17:36:33 2020 - [debug] ok.
Thu May 21 17:36:35 2020 - [debug]
Thu May 21 17:36:32 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.41(10.0.0.41:22)..
Thu May 21 17:36:33 2020 - [debug] ok.
Thu May 21 17:36:33 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.42(10.0.0.42:22)..
Thu May 21 17:36:34 2020 - [debug] ok.
Thu May 21 17:36:35 2020 - [info] All SSH connection tests passed successfully.
启动MHA
[root@slave2 ~]# 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 &
[1] 3005
[root@slave2 ~]# ps -ef |grep mha
root 3024 1774 0 17:45 pts/0 00:00:00 grep --color=auto mha
[1]+ Exit 1 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
2098
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
