mysql高可用简述

mysql-master	192.168.10.10
mysql-slave	192.168.10.11

主从复制，前提两个节点都安装mysql

master节点

（1）启用二进制日志

（2）为当前节点设置全局唯一的id号

（3）重启数据库

（4）查看从二进制日志的文件和位置开始进行复制

（5）创建有复制权限的用户账号

配置如下

[root@mysql-master ~]# cat /etc/my.cnf<br>[mysqld]
server_id=10   id号
log-bin 　　　　二进制日志开启
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock[root@mysql-master ~]#systemctl restart mysql

　mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-master-bin.000002 | 452 | | | |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> grant replication slave on *.* to repluser@'192.168.10.%' identified by 'enjony';
Query OK, 0 rows affected, 1 warning (0.00 sec)

slave节点

（1）启用中继日志

（2）使用有复制权限的用户账号连接至主服务器，并启动复制线程

[root@mysql-slave ~]# cat /etc/my.cnf

[mysqld] server_id=11 必须全局唯一

#log-bin 可填可不填

#read_only=ON 可填可不填
#relay_log=relay-log可填可不填
#relay_log_index=relay-log.index可填可不填
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.10',MASTER_USER='repluser',MASTER_PASSWORD='enjony',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-master-bin.000002',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MASTER_HOST master节点地址

MASTER_USER master创建的复制用户

MASTER_PASSWORD 复制用户的密码

MASTER_PORT

MASTER_LOG_FILE master的二进制最新的文件名

MASTER_LOG_POS master的二进制最新的文件名位置

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000002
Read_Master_Log_Pos: 452
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 625
Relay_Master_Log_File: mysql-master-bin.000002
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: 452
Relay_Log_Space: 838
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: 10
Master_UUID: 0e691e5f-af6b-11ec-89f3-000c294a9911
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

如果主节点已经运行了一段时间，且有大量数据时，需要增加slave节点，这里新增节点为mysql-slave2（192.168.10.12）

master节点配置

[root@mysql-master backup]# mysqldump -pqawsed -A -F --single-transaction --master-data=1 > /opt/backup/fullbackup_`date +%F_%T`.sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-master backup]# ll
total 832
-rw-r--r-- 1 root root 850111 Mar 30 00:25 fullbackup_2022-03-30_00:25:51.sql

　[root@mysql-master backup]# scp /opt/backup/fullbackup_2022-03-30_00\:25\:51.sql 192.168.10.12:/data

slave2节点配置

1[root@mysql-slave2 data]# cat /etc/my.cnf

[mysqld]

server_id=12

read-only

datadir=/data/mysql

socket=/data/mysql/mysql.sock

log-error=/data/mysql/mysql.log

pid-file=/data/mysql/mysql.pid

[client]

socket=/data/mysql/mysql.sock

[root@mysql-slave2 data]# systemctl restart mysql

2[root@mysql-slave2 data]# grep "^CHANGE" fullbackup_2022-03-30_00\:25\:51.sql 找到二进制日志
CHANGE MASTER TO MASTER_LOG_FILE='mysql-master-bin.000003', MASTER_LOG_POS=154;

3[root@mysql-slave2 data]# vim fullbackup_2022-03-30_00\:25\:51.sql

CHANGE MASTER TO
22 MASTER_HOST='192.168.10.10',
23 MASTER_USER='repluser',
24 MASTER_PASSWORD='enjony',
25 MASTER_PORT=3306,
26 MASTER_LOG_FILE='mysql-master-bin.000003', MASTER_LOG_POS=154;

4[root@mysql-slave2 data]# mysql -pqawsed < fullbackup_2022-03-30_00\:25\:51.sql 导入sql

5开启复制进程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

6查看

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-slave2-relay-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: mysql-master-bin.000003
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: 154
Relay_Log_Space: 541
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: 10
Master_UUID: 0e691e5f-af6b-11ec-89f3-000c294a9911
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

如果要删除slave节点

mysql> stop slave;

mysql>reset slave all;

MHA配置

mysql-master

192.168.10.10

mysql-slave

192.168.10.11

mysql-slave2

192.168.10.12

mysql-mha

192.168.10.13

MHA工作原理总结为以下几条：
（1）从宕机崩溃的 master 保存二进制日志事件（binlog events）；
（2）识别含有最新更新的 slave ；
（3）应用差异的中继日志(relay log) 到其他 slave ；
（4）应用从 master 保存的二进制日志事件(binlog events)；
（5）提升一个 slave 为新 master ；
（6）使用其他的 slave 连接新的 master 进行复制。

1在前面主从复制的基础上新增一些配置

[root@mysql-master mysql]# cat /etc/my.cnf
[mysqld]
server_id=10
log-bin=/data/mysql/mysql-bin    指定路径
datadir=/data/mysql
skip_name_resolve=1    关闭名称解析，非必须，但是最好关闭
general_log　　　　　　仅仅观察所用，可以不配置
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@mysql-master mysql]#systemctl restart mysql
[root@mysql-master mysql]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm   安装mha包，这个所有节点都需要安装

[root@mysql-slave mysql]# cat /etc/my.cnf
[mysqld]
server_id=11       其他slave节点配置不同id
log-bin=/data/mysql/mysql-bin        mha0.58必须指定路径
read_only　　　　开启只读　　
relay_log_purge=0　　开启自动清空中继日志
skip_name_resolve=1
general_log
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@mysql-slave]#systemctl restart mysql
[root@mysql-slave ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@mysql-mha local]# yum -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2.实现mha节点到其他节点的免秘钥认证

随便什么方法都行，rsync方法需要所有节点都安装  yum install -y rsync

[root@mysql-mha local]#ssh-keygen 
[root@mysql-mha local]# ssh-copy-id 127.0.0.1
[root@mysql-mha local]# rsync -av .ssh 192.168.10.11:/root/
[root@mysql-mha local]#  rsync -av .ssh 192.168.10.12:/root/
[root@mysql-mha local]#  rsync -av .ssh 192.168.10.10:/root/

3mha节点建立配置文件

[root@mysql-mha mastermha]# cat  app1.cnf 
[server default]
user=mhauser       连接mysql所有节点的用户，需要有管理员权限
password=enjoy
manager_workdir=/data/mastermha/app1/　　目录会自动生成
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root　　用于远程实现ssh的用户，访问二进制日志
repl_user=repluser　　主从复制用户
repl_password=enjoy　　主从复制密码，这里我的密码是进行了修改
ping_interval=1　　健康性检测间隔
master_ip_failover_script=/usr/local/bin/master_ip_failover  切换vip的脚本
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0   默认值为1,表示如果slave中从库落后主库relay log超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过设置参数check_repl_delay=0，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master

master_binlog_dir=/data/mysql/    二进制日志存放的目录，mha0.58必须指定，前面版本不需要
[server1]
hostname=192.168.10.10
candidate_master=1　　设置优先候选为master
[server2]
hostname=192.168.10.11
candidate_master=1
[server3]
hostname=192.168.10.12

4master_ip_failover脚本

#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.10.100/24';         vip地址
my $interface = 'ens33';　　　　　　　　　　vip绑定网关接口
my $gateway='192.168.10.254';　　　　　　网关，必须填
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code; }
exit $exit_code; }
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP-$vip on the new master-$new_master_host \n";
&start_vip();
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code; }
exit $exit_code; }
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0; }
else {
&usage();
exit 1; }}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; }
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --
orig_master_host=host --orig_master_ip=ip --orig_master_port=port --
new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }

5 master节点配置mha授权

[root@mysql-master]#grant replication slave on *.* to mhauser@'192.168.10.%' identified by 'enjoy';

6配置vip

[root@mysql-master ~]#ifconfig ens33:1 192.168.10.100/24

------------------------------------------------------------------------------

1.确定mysql主从复制没问题

2.确保mysql节点有mhauser和repluser用户（mhauser：mha账号），repluser（主从复制账号））

-----------------------------------------------------------------------------

7检查mha的环境

[root@mysql-mha mastermha]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf

Wed Mar 30 17:01:22 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 30 17:01:22 2022 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Mar 30 17:01:22 2022 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Wed Mar 30 17:01:22 2022 - [info] Starting SSH connection tests..
Wed Mar 30 17:01:23 2022 - [debug]
Wed Mar 30 17:01:22 2022 - [debug] Connecting via SSH from root@192.168.10.10(192.168.10.10:22) to root@192.168.10.11(192.168.10.11:22)..
Wed Mar 30 17:01:22 2022 - [debug] ok.
Wed Mar 30 17:01:22 2022 - [debug] Connecting via SSH from root@192.168.10.10(192.168.10.10:22) to root@192.168.10.12(192.168.10.12:22)..
Wed Mar 30 17:01:23 2022 - [debug] ok.
Wed Mar 30 17:01:24 2022 - [debug]
Wed Mar 30 17:01:23 2022 - [debug] Connecting via SSH from root@192.168.10.12(192.168.10.12:22) to root@192.168.10.10(192.168.10.10:22)..
Wed Mar 30 17:01:23 2022 - [debug] ok.
Wed Mar 30 17:01:23 2022 - [debug] Connecting via SSH from root@192.168.10.12(192.168.10.12:22) to root@192.168.10.11(192.168.10.11:22)..
Wed Mar 30 17:01:23 2022 - [debug] ok.
Wed Mar 30 17:01:24 2022 - [debug]
Wed Mar 30 17:01:22 2022 - [debug] Connecting via SSH from root@192.168.10.11(192.168.10.11:22) to root@192.168.10.10(192.168.10.10:22)..
Wed Mar 30 17:01:23 2022 - [debug] ok.
Wed Mar 30 17:01:23 2022 - [debug] Connecting via SSH from root@192.168.10.11(192.168.10.11:22) to root@192.168.10.12(192.168.10.12:22)..
Wed Mar 30 17:01:23 2022 - [debug] ok.
Wed Mar 30 17:01:24 2022 - [info] All SSH connection tests passed successfully.


[root@mysql-mha mastermha]# masterha_check_repl --conf=/etc/mastermha/app1.cnf

[root@mysql-mha local]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
Wed Mar 30 14:40:16 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 30 14:40:16 2022 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Mar 30 14:40:16 2022 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Wed Mar 30 14:40:16 2022 - [info] MHA::MasterMonitor version 0.58.
Wed Mar 30 14:40:17 2022 - [info] GTID failover mode = 0
Wed Mar 30 14:40:17 2022 - [info] Dead Servers:
Wed Mar 30 14:40:17 2022 - [info] Alive Servers:
Wed Mar 30 14:40:17 2022 - [info] 192.168.10.10(192.168.10.10:3306)
Wed Mar 30 14:40:17 2022 - [info] 192.168.10.11(192.168.10.11:3306)
Wed Mar 30 14:40:17 2022 - [info] 192.168.10.12(192.168.10.12:3306)
Wed Mar 30 14:40:17 2022 - [info] Alive Slaves:
Wed Mar 30 14:40:17 2022 - [info] 192.168.10.11(192.168.10.11:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Wed Mar 30 14:40:17 2022 - [info] Replicating from 192.168.10.10(192.168.10.10:3306)
Wed Mar 30 14:40:17 2022 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Mar 30 14:40:17 2022 - [info] 192.168.10.12(192.168.10.12:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Wed Mar 30 14:40:17 2022 - [info] Replicating from 192.168.10.10(192.168.10.10:3306)
Wed Mar 30 14:40:17 2022 - [info] Current Alive Master: 192.168.10.10(192.168.10.10:3306)
Wed Mar 30 14:40:17 2022 - [info] Checking slave configurations..
Wed Mar 30 14:40:17 2022 - [info] Checking replication filtering settings..
Wed Mar 30 14:40:17 2022 - [info] binlog_do_db= , binlog_ignore_db=
Wed Mar 30 14:40:17 2022 - [info] Replication filtering check ok.
Wed Mar 30 14:40:17 2022 - [info] GTID (with auto-pos) is not supported
Wed Mar 30 14:40:17 2022 - [info] Starting SSH connection tests..
Wed Mar 30 14:40:19 2022 - [info] All SSH connection tests passed successfully.
Wed Mar 30 14:40:19 2022 - [info] Checking MHA Node version..
Wed Mar 30 14:40:19 2022 - [info] Version check ok.
Wed Mar 30 14:40:19 2022 - [info] Checking SSH publickey authentication settings on the current master..
Wed Mar 30 14:40:19 2022 - [info] HealthCheck: SSH to 192.168.10.10 is reachable.
Wed Mar 30 14:40:19 2022 - [info] Master MHA Node version is 0.58.
Wed Mar 30 14:40:19 2022 - [info] Checking recovery script configurations on 192.168.10.10(192.168.10.10:3306)..
Wed Mar 30 14:40:19 2022 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000001
Wed Mar 30 14:40:19 2022 - [info] Connecting to root@192.168.10.10(192.168.10.10:22)..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql/, up to mysql-bin.000001
Wed Mar 30 14:40:19 2022 - [info] Binlog setting check done.
Wed Mar 30 14:40:19 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Mar 30 14:40:19 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.10.11 --slave_ip=192.168.10.11 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Wed Mar 30 14:40:19 2022 - [info] Connecting to root@192.168.10.11(192.168.10.11:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mysql-slave-relay-bin.000002
Temporary relay log file is /data/mysql/mysql-slave-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Wed Mar 30 14:40:20 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.10.12 --slave_ip=192.168.10.12 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Wed Mar 30 14:40:20 2022 - [info] Connecting to root@192.168.10.12(192.168.10.12:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mysql-slave2-relay-bin.000002
Temporary relay log file is /data/mysql/mysql-slave2-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Wed Mar 30 14:40:20 2022 - [info] Slaves settings check done.
Wed Mar 30 14:40:20 2022 - [info]
192.168.10.10(192.168.10.10:3306) (current master)
+--192.168.10.11(192.168.10.11:3306)
+--192.168.10.12(192.168.10.12:3306)

Wed Mar 30 14:40:20 2022 - [info] Checking replication health on 192.168.10.11..
Wed Mar 30 14:40:20 2022 - [info] ok.
Wed Mar 30 14:40:20 2022 - [info] Checking replication health on 192.168.10.12..
Wed Mar 30 14:40:20 2022 - [info] ok.
Wed Mar 30 14:40:20 2022 - [info] Checking master_ip_failover_script status:
Wed Mar 30 14:40:20 2022 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.10.10 --orig_master_ip=192.168.10.10 --orig_master_port=3306

IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.10.100/24;/sbin/arping -I ens33 -c 3 -s 192.168.10.100/24 192.168.10.254 >/dev/null 2>&1===

Checking the Status of the script.. OK
Wed Mar 30 14:40:20 2022 - [info] OK.
Wed Mar 30 14:40:20 2022 - [warning] shutdown_script is not defined.
Wed Mar 30 14:40:20 2022 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

状态检测通过

8启动MHA

[root@mysql-mha local]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null  开启mha
[root@mysql-mha mastermha]# masterha_check_status --conf=/etc/mastermha/app1.cnf    查看状态
app1 (pid:14103) is running(0:PING_OK), master:192.168.10.10

9模拟故障

1	`[root@mysql-master ~]# systemctl stop mysql 停止master节点服务`

　　查看mha节点日志，红色部分可以看出自动让slave节点成为了新的master节点

[root@mysql-mha app1]# tail -n30 manager.log 
Wed Mar 30 21:46:40 2022 - [info] 
Wed Mar 30 21:46:40 2022 - [info] * Phase 5: New master cleanup phase..
Wed Mar 30 21:46:40 2022 - [info] 
Wed Mar 30 21:46:40 2022 - [info] Resetting slave info on the new master..
Wed Mar 30 21:46:40 2022 - [info]  192.168.10.11: Resetting slave info succeeded.
Wed Mar 30 21:46:40 2022 - [info] Master failover to 192.168.10.11(192.168.10.11:3306) completed successfully.
Wed Mar 30 21:46:40 2022 - [info] 

----- Failover Report -----

app1: MySQL Master failover 192.168.10.10(192.168.10.10:3306) to 192.168.10.11(192.168.10.11:3306) succeeded

Master 192.168.10.10(192.168.10.10:3306) is down!

Check MHA Manager logs at mysql-mha:/data/mastermha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.10.10(192.168.10.10:3306)
The latest slave 192.168.10.11(192.168.10.11:3306) has all relay logs for recovery.
Selected 192.168.10.11(192.168.10.11:3306) as a new master.
192.168.10.11(192.168.10.11:3306): OK: Applying all logs succeeded.
192.168.10.11(192.168.10.11:3306): OK: Activated master IP address.
192.168.10.12(192.168.10.12:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.10.12(192.168.10.12:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.10.11(192.168.10.11:3306)
192.168.10.11(192.168.10.11:3306): Resetting slave info succeeded.
Master failover to 192.168.10.11(192.168.10.11:3306) completed successfully.
Wed Mar 30 21:46:40 2022 - [info] Sending mail..
sh: /usr/local/bin/sendmail.sh: No such file or directory
Wed Mar 30 21:46:40 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln2089] Failed to send mail with return code 127:0

验证slave节点（192.168.10.11）

[root@mysql-slave ~]# mysql -pmagedu -e 'show master status\G' 这密码是数据库密码，进行过修改
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:



如果要重启mha服务，需要删除以下文件。发现不删除也可以啦啦啦

[root@mysql-mha app1]# ll /data/mastermha/app1/app1.failover.complete
-rw-r--r-- 1 root root 0 Mar 30 21:46 /data/mastermha/app1/app1.failover.complete

新增机器恢复mha集群

1 新增机器地址为原来模拟故障的地址，不然要修改app1.cnf的地址

2设置为新master的slave节点

[root@mysql-slave opt]# mysqldump -pmagedu -A > /opt/mysql-backup-`date +%F_%H_%S`.sql    新master节点备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-slave opt]# scp /opt/mysql-backup-2022-03-31_01_56.sql 192.168.10.10:/data   传给新slave节点
mysql-backup-2022-03-31_01_56.sql

新slave节点配置主从

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.11',MASTER_USER='repluser',MASTER_PASSWORD='enjoy',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 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: 192.168.10.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-master-relay-bin.000002
Relay_Log_Pos: 320
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:

3进行msyql集群状态检测

[root@mysql-mha mastermha]# masterha_check_repl --conf=/etc/mastermha/app1.cnf

4启动mha

[root@mysql-mha app1]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:16495) is running(0:PING_OK), master:192.168.10.11

Galera Cluster

1概述

Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案

Galera Cluster特点

　　多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的

　　同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失

　　并发复制：从节点APPLY数据时，支持并行执行，更好的性能

　　故障切换：在出现数据库故障时，因支持多点写入，切换容易

　　热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在

　　节点故障期间，节点本身对集群的影响非常小

　　自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，

　　Galera Cluster会自动拉取在线节点数据，最终集群会变为一致

　　对应用透明：集群的维护，对应用程序是透明的

Galera Cluster 缺点

　　由于DDL 需全局验证通过，则集群性能由集群中最差性能节点决定（一般集群节点配置都是一样的）

　　新节点加入或延后较大的节点重新加入需全量拷贝数据(SST，State Snapshot Transfer),作为donor( 贡献者，如：同步数据时的提供者)的节点在同步过程中无法提供读写

　　只支持innodb存储引擎的表

2工作原理

PXC最常使用如下4个端口号：

3306：数据库对外服务的端口号

4444：请求SST的端口号

4567：组成员之间进行沟通的端口号

4568：用于传输IST的端口号

PXC中涉及到的重要概念和核心参数：

（1）集群中节点的数量：整个集群中节点数量应该控制在最少3个、最多8个的范围内。最少3个节点是为了防止出现脑裂现象，因为只有在2个节点下才会出现此现象。脑裂现象的标志就是输入任何命令，返回的结果都是unknown command。节点在集群中，会因新节点的加入或故障、同步失效等原因发生状态的切换。

（2）节点状态的变化阶段：open：节点启动成功，尝试连接到集群时的状态

primary：节点已处于集群中，在新节点加入并选取donor进行数据同步时的状态

joiner：节点处于等待接收同步文件时的状态

joined：节点完成数据同步工作，尝试保持和集群进度一致时的状态

synced：节点正常提供服务时的状态，表示已经同步完成并和集群进度保持一致

donor：节点处于为新加入的节点提供全量数据时的状态

备注：donor节点就是数据的贡献者，如果一个新节点加入集群，此时又需要大量数据的SST数据传输，就有可能因此而拖垮整个集群的性能，所以在生产环境中，如果数据量较小，还可以使用SST全量数据传输，但如果数据量很大就不建议使用这种方式，可以考虑先建立主从关系，然后再加入集群。

（3）节点的数据传输方式：

SST：State Snapshot Transfer，全量数据传输

IST：Incremental State Transfer，增量数据传输

SST数据传输有xtrabackup、mysqldump和rsync三种方式，而增量数据传输就只有一种方式xtrabackup，但生产环境中一般数据量较小时，可以使用SST全量数据传输，但也只使用xtrabackup方法。

（4）GCache模块：在PXC中一个特别重要的模块，它的核心功能就是为每个节点缓存当前最新的写集。如果有新节点加入进来，就可以把新数据的增量传递给新节点，而不需要再使用SST传输方式，这样可以让节点更快地加入集群中，涉及如下参数：

gcache.size：缓存写集增量信息的大小，它的默认大小是128MB，通过wsrep_provider_options

参数设置，建议调整为2GB~4GB范围，足够的空间便于缓存更多的增量信息。

gcache.mem_size：GCache中内存缓存的大小，适度调大可以提高整个集群的性能

gcache.page_size：如果内存不够用（GCache不足），就直接将写集写入磁盘文件中

3配置

pxc1	192.168.10.10
pxc2	192.168.10.11
pxc3	192.168.10.12

备注：3个节点时间同步，没有防火墙，没有安装任何数据库。所以重置了节点

[root@pxc1 ~]# cat /etc/yum.repos.d/pxc.repo         每个节点配置pxc源
[percona]
name=percona_repo
baseurl =https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

[root@pxc1 ~]# yum install Percona-XtraDB-Cluster-57 -y 每个节点安装pxc

[root@pxc1 ~]# cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
server-id=10 每个节点不同
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[root@pxc1 ~]# grep -Ev '^$|#' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 每个节点都要修改
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.10.10,192.168.10.11,192.168.10.12 集群的所有节点地址
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.10.10 当前节点的地址
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1 节点的名字
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" 原本注释的，需要打开

4修改pxc1的数据库密码

[root@pxc1 ~]# grep 'temporary password' /var/log/mysqld.log 
2022-03-30T22:54:51.503141Z 1 [Note] A temporary password is generated for root@localhost: ;Ew9uxVkVzx3

mysql> alter user 'root'@'localhost' identified by 'enjoy';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass'; 创建3中的用户
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 授权
-> 'sstuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'wsrep%'; 查看pxc
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 6d1b50d0-b07c-11ec-aec6-be742389641f |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 3 |
| wsrep_last_committed | 3 |
| wsrep_replicated | 3 |
| wsrep_replicated_bytes | 760 |
| wsrep_repl_keys | 3 |
| wsrep_repl_keys_bytes | 96 |
| wsrep_repl_data_bytes | 463 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 155 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.500000 |
| wsrep_local_cached_downto | 1 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 100, 100 ] |
| wsrep_flow_control_interval_low | 100 |
| wsrep_flow_control_interval_high | 100 |
| wsrep_flow_control_status | OFF |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_apply_waits | 0 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 1 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 2200 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.10.10:3306 |
| wsrep_cluster_weight | 1 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 6d1adfba-b07c-11ec-a681-e67c7c5e80a1 |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 | 表明当前pxc集群只有一个节点
| wsrep_cluster_state_uuid | 6d1b50d0-b07c-11ec-aec6-be742389641f |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.55(r8b6416d) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
75 rows in set (0.00 sec)

wsrep_cluster_size表示，该Galera集群中只有一个节点

wsrep_local_state_comment 状态为Synced(4)，表示数据已同步完成(因为是第一个引导节点，无数据需要同步)。如果状态是Joiner, 意味着 SST 没有完成. 只有所有节点状态是Synced，才可以加新节点

wsrep_cluster_status为Primary，且已经完全连接并准备好

5测试新增数据库db2

mysql> create database db2;        pxc1节点
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

[root@pxc2 ~]# mysql -penjoy -e 'show databases';
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+

成功。。。。。。。。。。。。。。。

6新增pxc节点（pxc4:192.168.10.13）

[root@pxc4 ~]# cat /etc/yum.repos.d/pxc.repo 
[percona]
name=percona_repo
baseurl =https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

[root@pxc4 ~]# yum install Percona-XtraDB-Cluster-57 -y 安装pxc

[root@pxc4 ~]# grep -Ev '^$|#' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.10.10,192.168.10.11,192.168.10.12,192.168.10.13 加上pxc4地址
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.10.13 pxc4地址
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-4
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"

[root@pxc4 ~]# grep -Ev '^$|#' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=13
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
symbolic-links=0

[root@pxc4 ~]# systemctl start mysql

[root@pxc4 ~]# mysql -penjoy -e "SHOW STATUS LIKE 'wsrep_cluster_size'"; 可以看到已经有4个节点
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+

其他节点加入pxc4地址

[root@pxc1,2,3~]# grep -Ev '^$|#' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

wsrep_cluster_address=gcomm://192.168.10.10,192.168.10.11,192.168.10.12,192.168.10.13

7故障节点恢复

[root@pxc4 ~]#systemctl stop mysql    等数据恢复后重启mysql

[root@pxc4 ~]#systemctl start mysql

生产my.cnf配置优化

#打开独立表空间
innodb_file_per_table = 1
#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值
max_connections = 8000
#所有线程所打开表的数量
open_files_limit = 10240
#back_log 是操作系统在监听队列中所能保持的连接数
back_log = 300
#每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息
max_connect_errors = 1000
#每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
max_allowed_packet = 32M 
#指定一个请求的最大连接时间
wait_timeout = 10
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
sort_buffer_size = 16M 
#不带索引的全表扫描.使用的buffer的最小值
join_buffer_size = 16M 
#查询缓冲大小
query_cache_size = 128M #指定单个查询能够使用的缓冲区大小，缺省为1M
query_cache_limit = 4M    
# 设定默认的事务隔离级别
transaction_isolation = REPEATABLE-READ
# 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次
连接时被预留.
thread_stack = 512K 
# 二进制日志功能
log-bin=/data/mysqlbinlogs/
#二进制日志格式
binlog_format=row
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80%
innodb_buffer_pool_size = 24G 
#用来同步IO操作的IO线程的数量
innodb_file_io_threads = 4 #在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_thread_concurrency = 16
# 用来缓冲日志数据的缓冲区的大小
innodb_log_buffer_size = 16M
在日志组中每个日志文件的大小
innodb_log_file_size = 512M 
# 在日志组中的文件总数
innodb_log_files_in_group = 3
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
innodb_lock_wait_timeout = 120
#慢查询时长
long_query_time = 2 #将没有使用索引的查询也记录下来
log-queries-not-using-indexes

posted @ 2022-03-30 22:24 好像认识你很久了阅读(272) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· keepalived简述

· jumpserver部署

· mysql-高可用架构:MHA

· 数据库笔记-1

· MYSQL之MHA高可用集群

阅读排行：
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署

公告

昵称：好像认识你很久了
园龄： 3年6个月
粉丝： 0
关注： 1

+加关注

2025年3月

日

一

二

三

四

五

六

随笔档案

阅读排行榜

评论排行榜

1. nginx简述(2)