DAY12 - MySQL性能调优

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

# 在所有主从节点均进行清理操作：
[root@xiaoQ-01 ~]# pkill mysqld
[root@xiaoQ-01 ~]# rm -rf /data/3306/*
[root@xiaoQ-01 ~]# rm -rf /data/binlog/*
[root@xiaoQ-01 ~]# mv /etc/my.cnf /tmp
[root@xiaoQ-01 ~]# mkdir -p /data/3306/data /data/binlog
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
-- 所有数据库主从节点均进行以上清理操作；
# 主库db01配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
# 从库db02配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
# 从库db03配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
# 进行数据库所有节点初始化操作
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 启动数据库所有节点服务
[root@xiaoQ-01 ~]# /etc/init.d/mysqld start
[root@xiaoQ-02 ~]# /etc/init.d/mysqld start
[root@xiaoQ-03 ~]# /etc/init.d/mysqld start
# 重构主从关系-主库操作
db01 [(none)]>create user repl@'192.168.30.%' identified with mysql_native_password by '123456';
Quer y OK, 0 rows affected (0.01 sec)
db01 [(none)]>grant replication slave on *.* to repl@'192.168.30.%';
Quer y OK, 0 rows affected (0.00 sec)
-- 主库上创建主从复制用户信息
# 重构主从关系-从库操作
db02 [(none)]>change master to
master_host='192.168.30.101',
master_user='repl',
master_password='123456',
master_auto_position=1;
-- 表示让从库自己找寻复制同步数据的起点；
-- 在第一次启动gtid功能时，会读取从库中的binlog日志信息，根据主库uuid信息，获取从库中执行过的主库gtid信息
-- 从从库中没有执行过的主库gtid信息之后进行进行数据同步操作
db02 [(none)]> start slave;
-- 其他从库一并操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

# 创建程序命令软链接
[root@xiaoQ-01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog/usr/bin/mysqlbinlog
[root@xiaoQ-01 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
-- 所有节点均执行以上操作，因为MHA程序加载数据库命令，会默认在/usr/bin下面进行加载（会影响数据补偿和监控功能）
# 配置各节点互信
[root@xiaoQ-01 ~]# rm -rf /root/.ssh
[root@xiaoQ-01 ~]# ssh-keygen
[root@xiaoQ-01 ~]# cd /root/.ssh
[root@xiaoQ-01 ~]# mv id_rsa.pub authorized_keys
[root@xiaoQ-01 ~]# scp  -r  /root/.ssh  192.168.30.102:/root
[root@xiaoQ-01 ~]# scp  -r  /root/.ssh  192.168.30.103:/root
ssh 192.168.30.101 date
ssh 192.168.30.102 date
ssh 192.168.30.103 date
-- 各节点验证
# 安装软件程序
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
-- 所有节点安装Node软件依赖包
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
-- Manager软件安装（db03）
# 在db01主库中创建mha需要的用户
create user mha@'192.168.30.%' identified with mysql_native_password by 'mha';
grant all privileges on *.* to mha@'192.168.30.%';
-- 在主库创建完毕后，主从复制功能，核实所有从库也都有mha用户信息
# Manager配置文件准备(db03)
mkdir -p /etc/mha
-- 创建配置文件目录
mkdir -p /var/log /mha/app1
-- 创建日志目录
cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log /mha/app1/manager
-- MHA的工作日志设置
manager_workdir=/var/log /mha/app1
-- MHA的工作目录
master_binlog_dir=/data/binlog
-- 主库的binlog目录
user=mha
-- 监控用户，利用此用户连接各个节点，做心跳检测（主要是检测主库的状态）
password=mha
-- 监控密码
ping_interval=2
-- 心跳检测的间隔时间
repl_password=123456
-- 复制密码
repl_user=repl
-- 复制用户（用于告知从节点通过新主同步数据信息的用户信息）
ssh_user=root
-- ssh互信的用户（可以利用互信用户从主库scp获取binlog日志信息，便于从库进行数据信息补偿）
[server1]
-- 节点信息....
hostname=192.168.30.101
port=3306
[server2]
hostname=192.168.30.102
port=3306
candidate_master=1
[server3]
hostname=192.168.30.103
port=3306
EOF
-- 编辑mha配置文件
# MHA状态检查（db03）
masterha_check_ssh   --conf=/etc/mha/app1.cnf
Wed Dec 28 20:54:42 2022 - [info] All SSH connection tests passed successfully.
-- 在MHA管理节点，进行ssh互信功能检查，并且显示成功表示检查通过
masterha_check_repl  --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
-- 在MHA管理节点，检查主从关系与配置文件信息是否正确
# 开启MHA-manager
开启MHA(db03)：
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 &
# 查看MHA状态
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:30770) is running(0:PING_OK), master :192.168.30.101
-- 显示以上提示信息，表示MHA基础环境搭建成功了，但还不能在生产环境使用，还需要有后续的操作配置

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 &

1
2
3
4
5
6
7
8

# 监控脚本验证主节点存活方法
[root@xiaoQ-03 ~]# mysql -umha -pmha -h192.168.30.101 -e "select user();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| user()                 |
+-------------------+
| mha@xiaoQ-03 |
+-------------------+

1
2
3
4
5

# 所有从库解除主从关系操作
stop slave;
reset slave;
# 所有从库重构主从关系操作
change master to ...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 上传MHA所需的脚本文件
[root@xiaoQ-03 ~]# cd /usr/local/bin/
[root@xiaoQ-03 bin]# chmod +x /usr/local/bin/*
# 修改MHA脚本文件的信息
[root@xiaoQ-03 bin]# cp master_ip_failover master_ip_failover.bak
[root@xiaoQ-03 bin]# dos2unix /usr/local/bin/*
[root@xiaoQ-03 bin]# vim master_ip_failover
13 my $vip = '192.168.30.110/24';
14 my $key = '1';
15 my $if = 'eth0';
16 my $ssh_start_vip = "/sbin/ifconfig $if:$key $vip";
17 my $ssh_stop_vip = "/sbin/ifconfig $if:$key down";
18 my $ssh_Bcast_arp= "/sbin/arping -I $if -c 3 -A 192.168.30.110";
# 修改配置文件
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf
master_ip_failover_script=/usr/local/bin/master_ip_failover
# 重启MHA服务
[root@xiaoQ-03 bin]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 bin]# 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 &
# 手工在主库上添加VIP
[root@xiaoQ-03 bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:103046) is running(0:PING_OK), master :192.168.30.101
-- 核实此时的MHA的主库节点
ifconfig eth0:1 192.168.30.110/24
-- 在主库节点手工添加vip地址信息
# 进行VIP地址连接测试
-- 可以使用navcat软件，连接MHA的vip地址，查看所连主机信息是否为主节点，当故障转移后可以核实VIP地址是否持续连接；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

# 准备脚本文件
[root@xiaoQ-03 bin]# cp send_report send_report.bak
28 my $smtp='smtp.qq.com';
-- smtp服务器地址域名
29 my $mail_from='330882721@qq.com';
-- 发件箱信息配置
30 my $mail_user='330882721';
-- 用户名 QQ号
31 my $mail_pass='ypokkranqlgkcbba';
-- 邮箱授权码
32 my $mail_to='330882721@qq.com';
or
my $mail_to=['to1@qq.com','to2@qq.com'];
-- 收件箱信息配置
# 修改配置文件
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf
report_script=/usr/local/bin/send_report
# 重启MHA服务
[root@xiaoQ-03 bin]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 bin]# 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

# 创建日志存放目录
[root@xiaoQ-03 ~]# mkdir -p /data/binlog_server/
[root@xiaoQ-03 ~]# chown -R mysql.mysql /data/*
[root@xiaoQ-03 ~]# cd /data/binlog_server
[root@xiaoQ-03 binlog_server]# mysql -e "show slave status\G"|grep "Master_Log"
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1201
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 1201
-- 拉取日志的起点，需要按照目前从库的已经获取到的二进制日志点为起点
[root@xiaoQ-03 binlog_server]# mysqlbinlog -R --host=192.168.30.101 --user=mha --password=mha --raw --stop-never mysql-bin.000002 &
# 编写配置文件信息
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf
[binlog1]
no_master=1
hostname=192.168.30.103
master_binlog_dir=/data/binlog_server/
# 重启MHA服务
[root@xiaoQ-03 bin]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 bin]# 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 确认目前的MHA的状态是良好的
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:30770) is running(0:PING_OK), master :192.168.30.101
# 模拟DB01数据库节点宕机
[root@xiaoQ-01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL........... SUCCESS!
# 监控DB03日志信息的变化
[root@xiaoQ-03 ~]# tail -f /var/log /mha/app1/manager
Mon Jan  2 15:32:26 2023 - [warning] Got error on MySQL select ping: 1053 (Server shutdown in progress)
Mon Jan  2 15:32:26 2023 - [info] Executing SSH check script: exit 0
Mon Jan  2 15:32:26 2023 - [info] HealthCheck: SSH to 192.168.30.101 is reachable.
Mon Jan  2 15:32:28 2023 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.30.101' (111))
... 省略部分信息...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 日志信息分析
[waring] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.30.101' (111))
[waring] Connection failed 2 time(s)..
[waring] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.30.101' (111))
[waring] Connection failed 3 time(s)..
[waring] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.30.101' (111))
[waring] Connection failed 4 time(s)..
[waring] Master is not reachable from health checker!
[waring] Master 192.168.30.101(192.168.30.101:3306) is not reachable!
-- 对故障主节点进行4次健康检查，主节点数据库服务仍旧无法连接，即判定主节点故障

1
2
3
4
5
6
7

[info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and tr ying to connect to all servers
to check server status..
[info] Reading application default configuration from /etc/mha/app1.cnf
....
[info] Starting master failover
....
[info] ** Phase 1: Configuration Check Phase completed.

1
2
3

[info] * Phase 2: Dead Master Shutdown Phase..
[info] Forcing shutdown so that applications never connect to the current master..
Disabling the VIP on old master : 192.168.30.101

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

[info] * Phase 3: Master Recovery Phase..
[info] * Phase 3.1: Getting Latest Slaves Phase..
....
[info] * Phase 3.3: Determining New Master Phase..
[info] New master is 192.168.30.102(192.168.30.102:3306)
[info] Starting master failover..
[info] * Phase 3.3: New Master Recovery Phase..
[info] Executing binlog save command: save_binar y_logs --command=save --start_file=mysql-bin.000002  --start_pos=1201 --
output_file=/var/tmp/saved_binlog_binlog1_20230102153233.binlog --handle_raw_binlog=0 --skip_filter=1 --disable_log_bin=0 --manager_version=0.58 --
oldest_version=8.0.26  --binlog_dir=/data/binlog_server/
[info] Additional events were not found from the binlog server. No need to save.
Enabling the VIP - 192.168.30.110/24 on the new master - 192.168.30.102
Mon Jan  2 15:32:36 2023 - [info]  OK.
Mon Jan  2 15:32:36 2023 - [info] ** Finished master recovery successfully.
[info] * Phase 3: Master Recovery Phase completed.

1
2
3
4
5
6

[info] * Phase 4: Slaves Recovery Phase..
[info] * Phase 4.1: Starting Slaves in parallel..
[info]  Resetting slave 192.168.30.103(192.168.30.103:3306) and starting replication from the new master 192.168.30.102(192.168.30.102:3306)..
[info]  Executed CHANGE MASTER.
[info]  Slave started.
[info]  All new slave servers recovered successfully.

1
2
3
4

[info] * Phase 5: New master cleanup phase..
----- Failover Report -----
Master failover to 192.168.30.102(192.168.30.102:3306) completed successfully.
Mon Jan  2 15:32:37 2023 - [info] Sending mail..

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 获取新主从关系
[root@xiaoQ-03 ~]# mysql -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.30.102
Master_User : repl
Master_Port: 3306
# 查看MHA配置文件信息
[root@xiaoQ-03 ~]# cat /etc/mha/app1.cnf
[binlog1]
hostname=192.168.30.103
master_binlog_dir=/data/binlog_server/
no_master=1
[server default]
manager_log=/var/log /mha/app1/manager
manager_workdir=/var/log /mha/app1
master_binlog_dir=/data/binlog
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha
ping_interval=2
repl_password=123456
repl_user=repl
report_script=/usr/local/bin/send_report
ssh_user=root
user=mha
[server2]
hostname=192.168.30.102
port=3306
[server3]
hostname=192.168.30.103
port=3306
-- 故障节点信息已从配置文件中清理

1
2
3
4

[root@xiaoQ-03 binlog_server]# ps -ef|grep mha
root     109328   1798  0 15:48 pts/0    00:00:00 grep --color=auto mha
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

1
2
3
4
5
6
7

# 检查节点数据库运行状态
[root@xiaoQ-01 ~]# /etc/init.d/mysqld status
ERROR! MySQL is not running
-- 发现数据库服务有异常节点进行恢复
[root@xiaoQ-01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
-- 恢复DB01异常数据库服务节点

1
2
3
4
5
6
7
8

[root@xiaoQ-03 ~]# mysql -e "show slave status\G"|grep "Master_Host"
Master_Host: 192.168.30.102
-- 确认切换后的新主库信息
[root@xiaoQ-02 ~]# /etc/init.d/mysqld status
SUCCESS! MySQL running (27332)
-- 在DB02上核实节点运行的状态
[root@xiaoQ-01 ~]# mysql -e "show slave status\G"|grep "Master_Host"
-- 在DB01上无法获取主从关系信息，需要修复DB01主从，最终实现1主2从效果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 在DB01上修复主从：
[root@xiaoQ-01 ~]# mysql
db02 [(none)]>change master to
master_host='192.168.30.102',
master_user='repl',
master_password='123456',
master_auto_position=1;
db02 [(none)]> start slave;
# 在DB01上核实主从关系
[root@xiaoQ-01 ~]# mysql -e "show slave status\G"|grep "Master_Host"
Master_Host: 192.168.30.102

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

[root@xiaoQ-02 ~]# ip a s eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:61:cd:06 brd ff:ff:ff:ff:ff:ff
inet 192.168.30.102/24 brd 192.168.30.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 192.168.30.110/24 brd 192.168.30.255 scope global secondar y eth0:1
valid_lft forever preferred_lft forever
inet6 fe80::560c:13cd:6107:b0de/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::727b:5d03:94c0:9382/64 scope link noprefixroute
valid_lft forever preferred_lft forever
ifconfig eth0:1 192.168.30.110/24
-- 如果上面vip漂移失败，出现问题可以手工添加VIP

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# 检查binlog_server状态
[root@xiaoQ-03 ~]# ps -ef|grep mysqlbinlog
-- binlog_server日志同步进程消失
# 修复binlog_server状态
[root@xiaoQ-03 ~]# rm -rf /data/binlog_server/*
[root@xiaoQ-03 ~]# cd /data/binlog_server/
[root@xiaoQ-03 binlog_server]# mysql -e "show slave status\G"|grep "Master_Log"
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1229
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 1229
[root@xiaoQ-03 binlog_server]# mysqlbinlog -R --host=192.168.30.102 --user=mha --password=mha --raw --stop-never mysql-bin.000002 &
[root@xiaoQ-03 binlog_server]# ll
总用量 4
-rw-r----- 1 root root 1229 1月   2 16:28 mysql-bin.000002

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 确认核实配置文件中三个节点信息
[root@xiaoQ-03 binlog_server]# cat /etc/mha/app1.cnf
[binlog1]
hostname=192.168.30.103
master_binlog_dir=/data/binlog_server/
no_master=1
[server default]
manager_log=/var/log /mha/app1/manager
manager_workdir=/var/log /mha/app1
master_binlog_dir=/data/binlog
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha
ping_interval=2
repl_password=123456
repl_user=repl
report_script=/usr/local/bin/send_report
ssh_user=root
user=mha
[server2]
hostname=192.168.30.102
port=3306
[server3]
hostname=192.168.30.103
port=3306
[server1]
hostname=192.168.30.101
port=3306
-- 添加DB01故障节点到配置文件中
[root@xiaoQ-03 ~]# masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=192.168.30.101 --block=server1 --params="port=3306"
-- 利用命令脚本添加新的节点信息
[root@xiaoQ-03 ~]# masterha_conf_host --command=delete --conf=/etc/mha/app1.cnf --block=server1
-- 利用命令脚本删除指定的节点信息

1
2
3
4

[root@xiaoQ-03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Mon Jan  2 16:42:35 2023 - [info] All SSH connection tests passed successfully.
[root@xiaoQ-03 binlog_server]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.

1
2
3
4

[root@xiaoQ-03 ~]# 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 &
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:109463) is running(0:PING_OK), master :192.168.30.102

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

# 关闭MHA服务程序
masterha_stop --conf=/etc/mha/app1.cnf
-- 关闭mha程序是保证手工切换时，不会受到mha自动切换的影响
# 执行MHA手工切换
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.30.101 --orig_master_is_new_slave --
running_updates_limit=10000
...省略部分信息...
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.30.102(192.168.30.102:3306)? (YES/no):
-- 以上说明信息，表示在进行切换之前，在原有主库节点执行FLUSH NO_WRITE_TO_BINLOG TABLES这个命令
-- 此命令表示，关闭所有打开的表，强制关闭所有正在使用的表，不写入binlog；
-- 因为此时VIP还没有漂移，表示禁止原主库继续写入数据信息
# 关闭原主库的写入功能
db02 [(none)]>FLUSH NO_WRITE_TO_BINLOG TABLES;
Quer y OK, 0 rows affected (0.01 sec)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.30.102(192.168.30.102:3306)? (YES/no):
yes
Starting master switch from 192.168.30.102(192.168.30.102:3306) to 192.168.30.101(192.168.30.101:3306)? (yes/NO): yes
-- 进行再一次核实确认，是否进行手工切换
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it
ok to proceed? (yes/NO): yes
-- 表示master_ip_online_change_script此脚本没有定义，如果没有禁止当前主库写入的禁止，业务应用仍旧访问当前主库
-- 因为此时VIP还没有进行转移；
[info] Switching master to 192.168.30.101(192.168.30.101:3306) completed successfully.
-- 提示切换完成
# 进行MHA切换核验
[root@xiaoQ-03 ~]# 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 &
-- 重新启动MHA程序
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
[1]+  退出 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
-- 检查状态失败，mha没有启动成功，因为vip信息并没有进行有效漂移
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.30.102 --orig_master_is_new_slave --
running_updates_limit=10000
-- 临时先切换回原有主节点，恢复MHA服务状态
[root@xiaoQ-03 ~]# 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] 65799
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:65799) is running(0:PING_OK), master :192.168.30.102
-- mha服务状态恢复

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# 编写MHA手工切换脚本文件：
[root@xiaoQ-03 ~]# cd /usr/local/bin/
[root@xiaoQ-03 bin]# cp master_ip_online_change master_ip_online_change.bak
[root@xiaoQ-03 bin]# vim master_ip_online_change
21 my $vip = "192.168.30.110";
22 my $key = "1";
23 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
24 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key $vip down";
25 my $ssh_Bcast_arp= "/sbin/arping -I eth0 -c 3 -A 192.168.30.110";
# 修改MHA服务程序配置文件：
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
# 关闭MHA服务程序进行核查：
[root@xiaoQ-03 ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
[root@xiaoQ-03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
# 进行MHA服务手工在线切换：
[root@xiaoQ-03 ~]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.30.101 --orig_master_is_new_slave --
running_updates_limit=10000
...省略部分信息...
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.30.102(192.168.30.102:3306)? (YES/no):
yes
-- FLUSH NO_WRITE_TO_BINLOG TABLES 命令在原有主库节点需要再执行一次；
Starting master switch from 192.168.30.102(192.168.30.102:3306) to 192.168.30.101(192.168.30.101:3306)? (yes/NO): yes
Sat Jan  7 13:48:50 2023 - [info] Switching master to 192.168.30.101(192.168.30.101:3306) completed successfully.
# 重构binlogserver功能
[root@xiaoQ-03 ~]# cd /data/binlog_server/
[root@xiaoQ-03 binlog_server]# rm -rf ./*
[root@xiaoQ-03 binlog_server]# mysql -e "show slave status\G"|grep "Master_Log"
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 236
Relay_Master_Log_File: mysql-bin.000005
Exec_Master_Log_Pos: 236
[root@xiaoQ-03 binlog_server]# mysqlbinlog -R --host=192.168.30.101 --user=mha --password=mha --raw --stop-never mysql-bin.000005 &
-- 此功能不进行重新配置，会导致MHA服务无法正常启动（若已经启动过，可以将进程杀死，重新启动）
# 进行MHA服务手工切换核验：
[root@xiaoQ-03 ~]# 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 &
-- 重新启动MHA程序
[root@xiaoQ-03 binlog_server]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:17462) is running(0:PING_OK), master :192.168.30.101

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 上传安装软件程序
[root@xiaoQ-03 ~]# rpm -ivh proxysql-2.4.6-1-centos7.x86_64.rpm
警告：proxysql-2.4.6-1-centos7.x86_64.rpm: V4 RSA/SHA512 Signature, 密钥 ID 8217c97e: NOKEY
准备中...                          ################################# [100%]
# 启动运行软件程序
[root@xiaoQ-03 ~]# systemctl start proxysql
[root@xiaoQ-03 ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0            0         0.0.0.0:6032                  0.0.0.0:*               LISTEN      83020/proxysql
tcp        0            0         0.0.0.0:6033                  0.0.0.0:*               LISTEN      83020/proxysql
-- 启动生成的6032端口为管理端口，用于配置数据库中间件的功能信息连接此端口
-- 启动生成的6033端口为访问端口，用于提供对外的业务访问此端口

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

# 连接进入到proxySQL管理终端
[root@xiaoQ-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [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 1
Server version: 5.5.30 (ProxySQL Admin Module)
# 查看proxySQL终端数据库信息
>show databases;
+-----+-----------------+----------------------------------------------+
| seq | name              | file                                                         |
+-----+-----------------+----------------------------------------------+
| 0     | main               |                                                               |
| 2     | disk                | /var/lib/proxysql/proxysql.db            |
| 3     | stats               |                                                               |
| 4     | monitor          |                                                               |
| 5     | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+-----------------+----------------------------------------------+
5 rows in set (0.00 sec)
-- 不需要use到相应数据库中进行操作，可以操作的数据表信息如下
# 查看proxySQL终端数据表信息
>show tables ;
+-----------------------------------------------------------------+
| tables
+-----------------------------------------------------------------+
| global_variables
| mysql_aws_aurora_hostgroups
| mysql_collations
| mysql_firewall_whitelist_rules
| mysql_firewall_whitelist_sqli_fingerprints
| mysql_firewall_whitelist_users
| mysql_galera_hostgroups
| mysql_group_replication_hostgroups
| mysql_query_rules
| mysql_query_rules_fast_routing
| mysql_replication_hostgroups
| mysql_servers
| mysql_users
| proxysql_servers
| restapi_routes
| runtime_checksums_values
| runtime_global_variables
| runtime_mysql_aws_aurora_hostgroups
| runtime_mysql_firewall_whitelist_rules
| runtime_mysql_firewall_whitelist_sqli_fingerprints
| runtime_mysql_firewall_whitelist_users
| runtime_mysql_galera_hostgroups
| runtime_mysql_group_replication_hostgroups
| runtime_mysql_query_rules
| runtime_mysql_query_rules_fast_routing
| runtime_mysql_replication_hostgroups
| runtime_mysql_servers
| runtime_mysql_users
| runtime_proxysql_servers
| runtime_restapi_routes
| runtime_scheduler
| scheduler
+-----------------------------------------------------------------+
32 rows in set (0.00 sec)
-- 表名以runtiem_开头的表示proxySQL服务中当前运行的配置内容，不能直接修改，不带runtime是下文图中mem相关的配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

# 01 user相关配置
LOAD MYSQL USERS TO RUNTIME;
-- MEM加载到runtime
SAVE MYSQL USERS TO MEMORY;
-- RUNTIME保存至MEM
LOAD MYSQL USERS FROM DISK;
-- DISK加载到MEM
SAVE MYSQL USERS TO DISK;
-- MEM保存至DISK
LOAD MYSQL USERS FROM CONFIG
-- CFG加载到MEM
# 02 server相关配置
LOAD MYSQL SERVERS TO RUNTIME;
-- MEM加载到RUNTIME
SAVE MYSQL SERVERS TO MEMORY;
-- RUNTIME保存至MEM
LOAD MYSQL SERVERS FROM DISK;
-- DISK加载到MEM
SAVE MYSQL SERVERS TO DISK;
-- MEM保存至DISK
LOAD MYSQL SERVERS FROM CONFIG
-- CFG加载到MEM
# 03 MYSQL QUERY RULES相关配置
LOAD MYSQL QUERY RULES TO RUNTIME;
-- MEM加载到RUNTIME
SAVE MYSQL QUERY RULES TO MEMORY;
-- RUNTIME保存至MEM
LOAD MYSQL QUERY RULES FROM DISK;
-- DISK加载到MEM
SAVE MYSQL QUERY RULES TO DISK;
-- MEM保存至DISK
LOAD MYSQL QUERY RULES FROM CONFIG
-- CFG加载到MEM
# 03 MYSQL VARIABLES相关配置
LOAD MYSQL VARIABLES TO RUNTIME;
-- MEM加载到RUNTIME
SAVE MYSQL VARIABLES TO MEMORY;
-- RUNTIME保存至MEM
LOAD MYSQL VARIABLES FROM DISK;
-- DISK加载到MEM
SAVE MYSQL VARIABLES TO DISK;
-- MEM保存至DISK
LOAD MYSQL VARIABLES FROM CONFIG
-- CFG加载到MEM

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

db03 [(none)]>insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
Quer y OK, 1 row affected (0.00 sec)
db03 [(none)]>save mysql servers to disk;
Quer y OK, 0 rows affected (0.01 sec)
db03 [(none)]>load mysql servers to runtime;
Quer y OK, 0 rows affected (0.00 sec)
db03 [(none)]>select * from mysql_replication_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
reader_hostgroup: 20
check_type: read_only
comment: proxy
1 row in set (0.00 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

db03 [(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.30.110',3306);
Quer y OK, 1 row affected (0.00 sec)
db03 [(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.30.102',3306);
Quer y OK, 1 row affected (0.00 sec)
db03 [(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.30.103',3306);
Quer y OK, 1 row affected (0.00 sec)
db03 [(none)]>save mysql servers to disk;
Quer y OK, 0 rows affected (0.01 sec)
db03 [(none)]>load mysql servers to runtime;
Quer y OK, 0 rows affected (0.00 sec)
db03 [(none)]>select * from mysql_servers\G;
*************************** 1. row ***************************
hostgroup_id: 20
hostname: 192.168.30.102
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 20
hostname: 192.168.30.103
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 10
hostname: 192.168.30.110
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
3 rows in set (0.00 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

# 主库创建监控用户
db01 [(none)]>create user monitor@'%' identified with mysql_native_password by '123';
Quer y OK, 0 rows affected (0.02 sec)
db01 [(none)]>grant replication client on *.* to monitor@'%';
Quer y OK, 0 rows affected (0.01 sec)
# 在proxysql中修改variables表配置信息
db03 [(none)]>set mysql-monitor_username='monitor';
Quer y OK, 1 row affected (0.00 sec)
db03 [(none)]>set mysql-monitor_password='123';
Quer y OK, 1 row affected (0.00 sec)
-- 以上变量信息修改为方法一
db03 [(none)]>update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
Quer y OK, 1 row affected (0.00 sec)
db03 [(none)]>update global_variables set variable_value='123' where variable_name='mysql-monitor_password';
Quer y OK, 1 row affected (0.00 sec)
-- 以上变量信息修改为方法一
db03 [(none)]>load mysql variables to runtime;
Quer y OK, 0 rows affected (0.01 sec)
db03 [(none)]>save mysql variables to disk;
Quer y OK, 154 rows affected (0.00 sec)
# 检查核实配置信息
db03 [(none)]>select @@mysql-monitor_username\G
*************************** 1. row ***************************
@@mysql-monitor_username: monitor
1 row in set (0.00 sec)
db03 [(none)]>select @@mysql-monitor_password\G
*************************** 1. row ***************************
@@mysql-monitor_password: 123
1 row in set (0.00 sec)
# 查询监控日志信息
db03 [(none)]>select * from mysql_server_connect_log;
+-------------------+------+-------------------------+---------------------------------+----------------------------------------+
| hostname          | port | time_start_us          | connect_success_time_us | connect_error                             |
+-------------------+------+-------------------------+---------------------------------+----------------------------------------+
| 192.168.30.110 | 3306 | 1674026545375939 | 2831                                     | NULL                                             |
| 192.168.30.103 | 3306 | 1674026546137911 | 1480                                     | NULL                                             |
| 192.168.30.102 | 3306 | 1674026546899730 | 3781                                     | NULL                                             |
-- 检查确认所有节点的连接访问情况
db03 [(none)]>select * from mysql_server_ping_log;
+-------------------+------+-------------------------+-----------------------------+---------------+
| hostname          | port | time_start_us          | ping_success_time_us | ping_error |
+-------------------+------+-------------------------+-----------------------------+---------------+
| 192.168.30.102 | 3306 | 1674026696004217 | 1139                               | NULL           |
| 192.168.30.103 | 3306 | 1674026696095455 | 194                                 | NULL           |
| 192.168.30.110 | 3306 | 1674026696186794 | 1466                               | NULL           |
-- 检查确认所有节点的网络连通情况
db03 [(none)]>select * from mysql_server_read_only_log limit 3;
+-------------------+------+-------------------------+----------------------+-------------+--------+
| hostname          | port | time_start_us          | success_time_us | read_only | error  |
+-------------------+------+-------------------------+----------------------+-------------+--------+
| 192.168.30.110 | 3306 | 1674027579464285 | 1325                      | 0                | NULL  |
| 192.168.30.102 | 3306 | 1674027579479777 | 1743                      | 1                | NULL  |
| 192.168.30.103 | 3306 | 1674027579494993 | 308                        | 1                | NULL  |
-- 检查确认所有节点的只读状态信息（获取主库或从库主机信息）
db03 [(none)]>select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)
-- 检查确认所有节点的主从延时情况