MySQL学习笔记
MHA高可用工作原理 1.监控节点，通过配置文件获取所有节点信息 2.主库宕机 1）如果判断从库GTID或者position号，数据有差距，最接近于master的从库会选为备选主 2）如果两个从库数据一致，按照配置文件的顺序进行选择备选主 3）如果设定有权重，按照权重强制指定备选主（默认如果一个slave落后master100M的话，即使有权重也会失效，如果配合check_repl_delay=0的话，即使落后100M，也强制选定为备主） 3.数据补偿 1）当ssh能连接时，从库对比主库GTID或者position号，立即将二进制日志保存至各个节点，并且应用 2）当ssh不能连接时，对比从库之间的relaylog的差异 4.failover 将被选主身份切换，并且对外提供服务，其余从库和新的主库建立主从 5.VIP漂移 配置vip，自动切换到新主库上，直接配置在配置文件中，需要一个脚本 6.binlog server 二次数据补偿，一台额外的机器，实时同步主库的binlog，直接配置在配置文件中
配置
node节点：db01（10.0.0.51），db02（10.0.0.52），db03（10.0.0.53）
manager节点：m01（10.0.0.61）
搭建好一主两从，我选db01为主，并且都以GTID复制
关键程序进行软连接，我是通过tar包安装的MySQL5.7.20
ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /app/mysql/bin/mysql /usr/bin/mysql
配置互信
[root@m01 ~]# ssh-keygen
[root@m01 ~]# cd /root/.ssh/
[root@m01 .ssh]# mv id_rsa.pub authorized_keys
[root@m01 .ssh]# scp -r /root/.ssh 10.0.0.51:/root
[root@m01 .ssh]# scp -r /root/.ssh 10.0.0.52:/root
[root@m01 .ssh]# scp -r /root/.ssh 10.0.0.53:/root
所有节点安装node依赖包
[root@m01 .ssh]# yum install perl-DBD-MySQL -y
[root@m01 .ssh]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
在主库中创建mha专用用户
db01 [(none)]> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';
db01 [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| aaaa | 10.0.0.% |
| mha | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)
node节点安装结束
下载manager依赖并安装
[root@m01 ~]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
[root@m01 ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
准备配置文件目录
[root@m01 ~]# mkdir -p /etc/mha
创建日志目录
[root@m01 ~]# mkdir -p /var/log/mha/app1
编辑MHA配置文件
[root@m01 ~]# vim /etc/mha/app1.cnf
[binlog1]
hostname=10.0.0.61 binlog server要放在哪儿
master_binlog_dir=/data/binlog2 日志存放路径
no_master=1
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql/binlog
master_ip_failover_script=/usr/local/bin/master_ip_failover vip脚本的存放路径
password=mha
ping_interval=2
repl_password=123 主从复制专用用户密码
repl_user=aaaa 主从复制专用用户名
ssh_user=root
user=mha
[server1]
hostname=10.0.0.51
port=3306
[server2]
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306
创建binlog server路径
mkdir /data/binlog2
chown -R mysql.mysql /data/*
拉取日志
[root@m01 ~]# cd /data/binlog2/
[root@m01 ~]# mysqlbinlog -R --host=10.0.0.51 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
[root@m01 binlog2]# ll
total 696
-rw-r----- 1 mysql mysql 690968 Jul 4 09:01 mysql-bin.000001
-rw-r----- 1 mysql mysql 194 Jul 4 09:01 mysql-bin.000002
-rw-r----- 1 mysql mysql 954 Jul 4 09:01 mysql-bin.000003
-rw-r----- 1 mysql mysql 371 Jul 4 09:01 mysql-bin.000004
-rw-r----- 1 mysql mysql 257 Jul 4 09:01 mysql-bin.000005
-rw-r----- 1 mysql mysql 234 Jul 4 09:01 mysql-bin.000006
上传vip脚本到/usr/local/bin/
[root@m01 binlog2]# cd /usr/local/bin/
[root@m01 bin]# ll
-rwxr-xr-x 1 root root 2166 Jul 3 17:33 master_ip_failover
[root@m01 bin]# chmod +x master_ip_failover
第一次使用vip要手动在主机创建
[root@db01 ~]# ifconfig eth0:1 10.0.0.55/24在主库
manager配置完成，这里直接把MHA的binlog server 和VIP功能加进去了
启动前检测所有节点
[root@m01 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Thu Jul 4 09:56:22 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul 4 09:56:22 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jul 4 09:56:22 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Jul 4 09:56:22 2019 - [info] Starting SSH connection tests..
Thu Jul 4 09:56:23 2019 - [debug]
Thu Jul 4 09:56:22 2019 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22)..
Thu Jul 4 09:56:23 2019 - [debug] ok.
Thu Jul 4 09:56:23 2019 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.53(10.0.0.53:22)..
Thu Jul 4 09:56:23 2019 - [debug] ok.
Thu Jul 4 09:56:24 2019 - [debug]
Thu Jul 4 09:56:23 2019 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22)..
Thu Jul 4 09:56:23 2019 - [debug] ok.
Thu Jul 4 09:56:23 2019 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.53(10.0.0.53:22)..
Thu Jul 4 09:56:24 2019 - [debug] ok.
Thu Jul 4 09:56:25 2019 - [debug]
Thu Jul 4 09:56:23 2019 - [debug] Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.51(10.0.0.51:22)..
Thu Jul 4 09:56:24 2019 - [debug] ok.
Thu Jul 4 09:56:24 2019 - [debug] Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.52(10.0.0.52:22)..
Thu Jul 4 09:56:24 2019 - [debug] ok.
Thu Jul 4 09:56:25 2019 - [info] All SSH connection tests passed successfully.
检测主从状态
[root@m01 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
一切正常，启动MHA
[root@m01 ~]# 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@m01 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:1748) is running(0:PING_OK), master:10.0.0.51
模拟故障
把主库kill掉
[root@db01 ~]# pkill mysqld
在db03上查看状态，自动把db02当做主库并自动构建主从
db03 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52
Master_User: aaaa
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 234
Relay_Log_File: db03-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
查看db02的vip
[root@db02 ~]# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:90:b8:9d brd ff:ff:ff:ff:ff:ff
inet 10.0.0.52/24 brd 10.0.0.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:1
恢复
启动db01mysql，构建主从，db02为主
[root@db01 ~]# systemctl start mysqld.service
我们可以在mha日志内找到db03的切换主库的命令，可以直接复制过来
[root@m01 ~]# cat /var/log/mha/app1/manager |grep -n 'CHANGE MASTER'
189:Wed Jul 3 21:13:47 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='aaaa', MASTER_PASSWORD='xxx';
215:Wed Jul 3 21:13:47 2019 - [info] Executed CHANGE MASTER.
486:Thu Jul 4 09:59:39 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='aaaa', MASTER_PASSWORD='xxx';
512:Thu Jul 4 09:59:39 2019 - [info] Executed CHANGE MASTER.
486行
db01 [(none)]>CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='aaaa', MASTER_PASSWORD='123';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db01 [(none)]>start slave;
Query OK, 0 rows affected (0.00 sec)
db01 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52
Master_User: aaaa
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 234
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MHA挂掉，binlog server也要重新布置，把原来的日志删掉，重新拉取新主库的日志
[root@m01 binlog2]# rm -rf /data/binlog2/*
[root@m01 binlog2]# mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
[root@m01 binlog2]# ll
total 700
-rw-r----- 1 root root 690913 Jul 4 10:12 mysql-bin.000001
-rw-r----- 1 root root 1108 Jul 4 10:12 mysql-bin.000002
-rw-r----- 1 root root 281 Jul 4 10:12 mysql-bin.000003
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000004
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000005
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000006
-rw-r----- 1 root root 234 Jul 4 10:12 mysql-bin.000007
进行切换后，配置文件内自动清除发生故障的sever，所以启动之前添加配置文件server
准备完毕，启动MHA
[root@m01 ~]# 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 &
[2] 4060
[root@m01 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4060) is running(0:PING_OK), master:10.0.0.52
检查binlog server
db02 [(none)]>db02 [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000008 | 234 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-17,
c74ed2c4-998e-11e9-b9ee-000c2990b89d:1 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
db02 [(none)]>flush logs;
Query OK, 0 rows affected (0.00 sec)
db02 [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000009 | 234 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-17,
c74ed2c4-998e-11e9-b9ee-000c2990b89d:1 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
已复制成功
[root@m01 ~]# ll /data/binlog2/
total 708
-rw-r----- 1 root root 690913 Jul 4 10:12 mysql-bin.000001
-rw-r----- 1 root root 1108 Jul 4 10:12 mysql-bin.000002
-rw-r----- 1 root root 281 Jul 4 10:12 mysql-bin.000003
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000004
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000005
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000006
-rw-r----- 1 root root 281 Jul 4 10:16 mysql-bin.000007
-rw-r----- 1 root root 281 Jul 4 10:16 mysql-bin.000008
-rw-r----- 1 root root 234 Jul 4 10:16 mysql-bin.000009
至此，MHA的高可用功能，binlog server功能和vip漂移功能，部署并测试成功
把主库kill掉 [root@db01 ~]# pkill mysqld 在db03上查看状态，自动把db02当做主库并自动构建主从 db03 [(none)]>show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.52 Master_User: aaaa Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 234 Relay_Log_File: db03-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes 查看db02的vip [root@db02 ~]# ip a 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:90:b8:9d brd ff:ff:ff:ff:ff:ff inet 10.0.0.52/24 brd 10.0.0.255 scope global noprefixroute eth0 valid_lft forever preferred_lft forever inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:1
恢复
启动db01mysql，构建主从，db02为主
[root@db01 ~]# systemctl start mysqld.service
我们可以在mha日志内找到db03的切换主库的命令，可以直接复制过来
[root@m01 ~]# cat /var/log/mha/app1/manager |grep -n 'CHANGE MASTER'
189:Wed Jul 3 21:13:47 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='aaaa', MASTER_PASSWORD='xxx';
215:Wed Jul 3 21:13:47 2019 - [info] Executed CHANGE MASTER.
486:Thu Jul 4 09:59:39 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='aaaa', MASTER_PASSWORD='xxx';
512:Thu Jul 4 09:59:39 2019 - [info] Executed CHANGE MASTER.
486行
db01 [(none)]>CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='aaaa', MASTER_PASSWORD='123';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db01 [(none)]>start slave;
Query OK, 0 rows affected (0.00 sec)
db01 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52
Master_User: aaaa
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 234
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MHA挂掉，binlog server也要重新布置，把原来的日志删掉，重新拉取新主库的日志
[root@m01 binlog2]# rm -rf /data/binlog2/*
[root@m01 binlog2]# mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
[root@m01 binlog2]# ll
total 700
-rw-r----- 1 root root 690913 Jul 4 10:12 mysql-bin.000001
-rw-r----- 1 root root 1108 Jul 4 10:12 mysql-bin.000002
-rw-r----- 1 root root 281 Jul 4 10:12 mysql-bin.000003
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000004
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000005
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000006
-rw-r----- 1 root root 234 Jul 4 10:12 mysql-bin.000007
进行切换后，配置文件内自动清除发生故障的sever，所以启动之前添加配置文件server
准备完毕，启动MHA
[root@m01 ~]# 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 &
[2] 4060
[root@m01 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4060) is running(0:PING_OK), master:10.0.0.52
检查binlog server
db02 [(none)]>db02 [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000008 | 234 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-17,
c74ed2c4-998e-11e9-b9ee-000c2990b89d:1 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
db02 [(none)]>flush logs;
Query OK, 0 rows affected (0.00 sec)
db02 [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000009 | 234 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-17,
c74ed2c4-998e-11e9-b9ee-000c2990b89d:1 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
已复制成功
[root@m01 ~]# ll /data/binlog2/
total 708
-rw-r----- 1 root root 690913 Jul 4 10:12 mysql-bin.000001
-rw-r----- 1 root root 1108 Jul 4 10:12 mysql-bin.000002
-rw-r----- 1 root root 281 Jul 4 10:12 mysql-bin.000003
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000004
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000005
-rw-r----- 1 root root 257 Jul 4 10:12 mysql-bin.000006
-rw-r----- 1 root root 281 Jul 4 10:16 mysql-bin.000007
-rw-r----- 1 root root 281 Jul 4 10:16 mysql-bin.000008
-rw-r----- 1 root root 234 Jul 4 10:16 mysql-bin.000009
至此，MHA的高可用功能，binlog server功能和vip漂移功能，部署并测试成功