MHA高可用
server1 IP：172.25.15.1
server2 IP：172.25.15.2
server3 IP：172.25.15.3
server4 IP：172.25.15.4
一、server1 server2 server3 三台全做-安装数据库
[root@server1 ~]# ls
mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
[root@server1 ~]# tar xf mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar #解压安装mysql
[root@server1 ~]# ls
mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-5.7.33-1.el7.x86_64.rpm
mysql-community-common-5.7.33-1.el7.x86_64.rpm
mysql-community-devel-5.7.33-1.el7.x86_64.rpm
mysql-community-embedded-5.7.33-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.33-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.33-1.el7.x86_64.rpm
mysql-community-libs-5.7.33-1.el7.x86_64.rpm
[root@server1 ~]# yum install -y mysql-community-client-5.7.33-1.el7.x86_64.rpm mysql-community-common-5.7.33-1.el7.x86_64.rpm mysql-community-libs-5.7.33-1.el7.x86_64.rpm mysql-community-server-5.7.33-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.33-1.el7.x86_64.rpm
[root@server1 ~]# vim /etc/my.cnf #修改配置文件
添加
server-id=1
log-bin=binlog
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server1 ~]# systemctl start mysqld #启动数据库
[root@server1 ~]# cd /var/lib/mysql #进入数据库目录
[root@server1 mysql]# ls
auto.cnf ib_buffer_pool mysql public_key.pem
ca-key.pem ibdata1 mysql.sock server-cert.pem
ca.pem ib_logfile0 mysql.sock.lock server-key.pem
client-cert.pem ib_logfile1 performance_schema sys
client-key.pem ibtmp1 private_key.pem
[root@server1 mysql]# grep password /var/log/mysqld.log #查询日志文件中的密钥
2021-06-30T03:20:08.236752Z 1 [Note] A temporary password is generated for root@localhost: +U<ZS_CVp9QX
[root@server1 mysql]# mysql_secure_installation #数据库初始化，输入查询好的密钥
设置数据库密码
二、对4台虚拟机相互免密设置
[root@server1 ~]# ssh-keygen #server1生成密钥
[root@server1 ~]# ssh-copy-id server1 #传密钥给server1，完成免密连接
[root@server1 ~]# ssh-copy-id server2
[root@server1 ~]# ssh-copy-id server3
[root@server1 ~]# ssh-copy-id server4
[root@server1 ~]# scp -r .ssh/ server2: #将密钥文件传给server2
id_rsa 100% 1679 2.0MB/s 00:00
id_rsa.pub 100% 394 584.0KB/s 00:00
known_hosts 100% 724 1.1MB/s 00:00
authorized_keys 100% 394 763.4KB/s 00:00
[root@server1 ~]# scp -r .ssh/ server3: #将密钥文件传给server3
id_rsa 100% 1679 1.8MB/s 00:00
id_rsa.pub 100% 394 277.5KB/s 00:00
known_hosts 100% 724 1.1MB/s 00:00
authorized_keys 100% 394 810.8KB/s 00:00
[root@server1 ~]# scp -r .ssh/ server4: #将密钥文件传给server4
id_rsa 100% 1679 1.7MB/s 00:00
id_rsa.pub 100% 394 508.0KB/s 00:00
known_hosts 100% 724 1.0MB/s 00:00
authorized_keys 100% 394 804.0KB/s 00:00
[root@server1 ~]#
三、mysql 一主两从配置
1.主：server1
[root@server1 mysql]# mysql -pWestos+007 #之前初始化设置了密码Westos+007
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007'; #修改repl数据库用户密码为Westos+007
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 | 437 | | | 119ad899-d952-11eb-95b2-525400769252:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
2.从：server2
[root@server2 mysql]# vim /etc/my.cnf #修改配置文件
添加
server-id=2
log-bin=binlog
log-slave-updates=ON
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server2 mysql]# systemctl start mysqld #启动数据库
[root@server2 mysql]# systemctl restart mysqld #重启数据库
[root@server2 ~]# grep password /var/log/mysqld.log
[root@server2 ~]# mysql_secure_installation
[root@server2 mysql]# mysql -pWestos+007
mysql> change master to master_host='172.25.15.1', master_user='repl',master_password='Westos+007',master_auto_position=1; #设置172.25.15.1可以通过repl远程登录数据库
Query OK, 0 rows affected, 2 warnings (0.02 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: 172.25.15.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 437
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 644
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.从：server3
[root@server3 ~]# vim /etc/my.cnf
添加
server-id=2
log-bin=binlog
log-slave-updates=ON
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server3 ~]# systemctl start mysqld
[root@server3 ~]# grep password /var/log/mysqld.log
2021-06-30T04:04:21.193624Z 1 [Note] A temporary password is generated for root@localhost: _Kl;jD91K.g/
[root@server3 ~]# mysql_secure_installation
[root@server3 mysql]# mysql -pWestos+007
mysql> change master to master_host='172.25.15.1', master_user='repl',master_password='Westos+007',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.15.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 437
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 644
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.在server1查看是否成功
mysql> select * from performance_schema.replication_group_members;
mysql> grant all on *.* to root@'%' identified by 'Westos+007'; #修改root用户密码为Westos+007
Query OK, 0 rows affected, 1 warning (0.00 sec)
四、在server1 server 2 server3 server4安装MHA-node
Node工具包（由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs //保存和复制master的二进制日志
apply_diff_relay_logs //识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog //去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs //清除中继日志（不会阻塞SQL线程）
mha4mysql-node-0.58-0.el7.centos.noarch.rpm #提前下载好安装包
[root@server1~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@server2 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@server3 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@server4 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
五、管理机server4
1.安装MHA并配置
安装完成会生成工具
Manager工具包主要包括以下几个工具：
masterha_check_ssh //检查MHA的SSH配置状况
masterha_check_repl //检查MySQL复制状况
masterha_manger //启动MHA
masterha_check_status //检测当前MHA运行状态
masterha_master_monitor //检测master是否宕机
masterha_master_switch //控制故障转移（自动或者手动）
masterha_conf_host //添加或删除配置的server信息
[root@server4 ~]# ls
mha4mysql-manager-0.58.tar.gz mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@server4 ~]# tar zxf mha4mysql-manager-0.58.tar.gz #解压MHA软件包
[root@server4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@server4 MHA-7]# yum install -y * #安装所有的软件包
[root@server4 ~]# ls
mha4mysql-manager-0.58 mysql-router-community-8.0.21-1.el7.x86_64.rpm
mha4mysql-manager-0.58.tar.gz
[root@server4 ~]# cd mha4mysql-manager-0.58/
[root@server4 mha4mysql-manager-0.58]# ls
AUTHORS COPYING lib MANIFEST README samples tests
bin debian Makefile.PL MANIFEST.SKIP rpm t
[root@server4 mha4mysql-manager-0.58]# cd samples/conf/
[root@server4 conf]# ls
app1.cnf masterha_default.cnf
[root@server4 conf]# cat masterha_default.cnf #查看文件内容
[server default]
user=root
password=rootpass
ssh_user=root
master_binlog_dir= /var/lib/mysql,/var/log/mysql
remote_workdir=/data/log/masterha
secondary_check_script= masterha_secondary_check -s remote_host1 -s remote_host2
ping_interval=3
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
[root@server4 conf]# mkdir /etc/masterha/ -p #新建目录
[root@server4 conf]# ls
app1.cnf masterha_default.cnf
[root@server4 conf]# cp app1.cnf /etc/masterha/ #复制app1.cnf到/etc/masterha/目录下
[root@server4 conf]# cd /etc/masterha/
[root@server4 masterha]# ls
app1.cnf
[root@server4 masterha]# vim app1.cnf #编写app1.cnf文件
[root@server4 masterha]# cat app1.cnf
[server default]
user=root
password=Westos+007
ssh_user=root
repl_user=repl
repl_password=Westos+007
master_binlog_dir=/var/lib/mysql
remote_workdir=/tmp
ping_interval=3
#report_script=/usr/local/send_report
secondary_check_script= masterha_secondary_check -s 172.25.15.250 -s 172.25.15.3
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log
[server1]
hostname=172.25.15.1
[server2]
hostname=172.25.15.2
candidate_master=1
[server3]
hostname=172.25.15.3
no_master=1
2.对配置进行两步检测
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
....
Wed Jun 30 01:50:52 2021 - [info] All SSH connection tests passed successfully.
成功无问题
[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
.....
MySQL Replication Health is OK.
配置完成
六、手动切换说明
MHA的故障切换过程，共包括以下的步骤：
1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作
3.复制dead maste和最新slave相差的relay log，并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件（binlog events）
6.提升一个slave为新的master进行复制
7.使其他的slave连接新的master进行复制
[root@server4 ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.15.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Wed Jun 30 02:50:18 2021 - [info] Switching master to 172.25.15.2(172.25.15.2:3306) completed successfully.
手动恢复
[root@server2 ~]# mysql -pWestos+007
mysql> change master to master_host='172.25.15.1', master_user='repl', master_password='westos',MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
七、自动切换
-
MHA在线切换的大概过程：
1.检测复制设置和确定当前主服务器
2.确定新的主服务器
3.阻塞写入到当前主服务器
4.等待所有从服务器赶上复制
5.授予写入到新的主服务器
6.重新设置从服务器 -
为了保证数据完全一致性，在最快的时间内完成切换，MHA的在线切换必须满足以下条件才会切换成功，否则会切换失败。
1.所有slave的IO线程都在运行
2.所有slave的SQL线程都在运行
3.所有的show slave status的输出中Seconds_Behind_Master参数小于或者等于running_updates_limit秒，如果在切换过程中不指定running_updates_limit,那么默认情况下running_updates_limit为1秒。
4.在master端，通过show processlist输出，没有一个更新花费的时间大于running_updates_limit秒。
[root@server4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf
[root@server4 app1]# masterha_check_ssh --conf=/etc/masterha/app.cnf
[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf & ##打入后台自动执行切换
[root@server4 app1]# rm -fr app.failover.complete
[root@server4 app1]# ls
app.master_status.health manager.log
[root@server1 ~]# /etc/init.d/mysqld stop #停止现在的master server1，观察是否自动切换
Shutting down MySQL............ SUCCESS!
[root@server4 app1]# cat manager.log
Master failover to 172.25.15.2(172.25.15.2:3306) completed successfully.
[root@server4 app1]# ls
app.failover.complete manager.log
[root@server4 app1]# rm -fr app.failover.complete
手动恢复
[root@server1 ~]# mysql -pWestos+007
mysql> change master to master_host='172.25.15.1', master_user='repl', master_password='westos',MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
扫一扫
1082
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
