mysql高可用集群之MHA详细搭建过程
程序员文章站
2024-01-31 23:01:52
...
一、MHA简介
1、MHA
是一套优秀的实现mysql高可用的解决方案；
数据库的自动故障切换操作能做到在0--30秒之内；
MHA能确保在故障切换过程中保证数据的一致性，以达到真正意义上的高可用。
2、MHA的组成
MHA Mangaer(管理节点)：可以单独部署在一*立的机器上，管理其他节点，也可以部署在一台slave节点上，能管理多套mysql集群。
MHA Node(数据节点)：运行在每台mysql服务器上。
3、MHA集群架构
如下图所示，一个MHA Manager管理了三套mysql集群。
4、MHA工作过程
Manager定时探测集群中的master节点，当master故障时，Manager自动将拥有最新数据的slave提升为新的master
关键点：
从岩机崩溃的master保存二进制日志事件
识别最新更新的slave
应用中继日志(relay log) 到其他的slave
应用从master保存的二进制日志事件
提升一个slave为新的master
其他的slave连接最新的master
5、详细搭建过程
说明：本次搭建使用4台机器，一台主库，一台备用主库，一台从库，一台部署Manager管理节点，系统采用centos7.4
| 用途 | IP地址 | 主机名 |
| 主库 |
192.168.2.100 vip 192.168.2.101 设置在eth0:0上 |
vmctl2 |
| 备用主库 | 192.168.2.150 | vmctl3 |
| 从库 | 192.168.2.200 | vmctl4 |
| MHA Manager | 192.168.2.210 | vmctl6 |
1、主库 192.168.2.100 操作
安装数据库mysql-5.7.28
#用逻辑卷来做安装mysql 存放数据的目录，方便后期空间的扩容
[aaa@qq.com ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 10G 0 disk
├─sda1 8:1 0 296M 0 part /boot
├─sda2 8:2 0 8.7G 0 part /
└─sda3 8:3 0 1G 0 part [SWAP]
sdb 8:16 0 6G 0 disk
sr0 11:0 1 1024M 0 rom
[aaa@qq.com ~]# vgcreate centos /dev/sdb #创建卷组centos
Physical volume "/dev/sdb" successfully created.
Volume group "centos" successfully created
[aaa@qq.com ~]# vgs #查看卷组
VG #PV #LV #SN Attr VSize VFree
centos 1 0 0 wz--n- <6.00g <6.00g
[aaa@qq.com ~]# lvcreate -L 4G -n data centos #创建逻辑卷data，大小是4G
Logical volume "data" created.
[aaa@qq.com ~]# lvs #查看逻辑卷
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
data centos -wi-a----- 4.00g
[aaa@qq.com ~]# mkfs.ext4 /dev/centos/data #格式化逻辑卷data为ext4文件系统
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
262144 inodes, 1048576 blocks
52428 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1073741824
32 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
[aaa@qq.com ~]#
[aaa@qq.com ~]# blkid #查看UUID和文件系统
/dev/sda1: UUID="0e493985-9b58-47c4-8200-95a8fa18a31b" TYPE="xfs"
/dev/sda2: UUID="88da310c-65e5-4823-bf60-4f3cdaffbb00" TYPE="xfs"
/dev/sda3: UUID="0c337c1f-cf74-42bc-ab22-59c9e78c8e52" TYPE="swap"
/dev/sdb: UUID="5cjJ8W-Zhg6-1mOy-2kmf-bvhY-xOL5-mdGtR8" TYPE="LVM2_member"
/dev/mapper/centos-data: UUID="bb752efc-134f-4bee-9598-8215d0b6eb4d" TYPE="ext4"
[aaa@qq.com ~]# mkdir -p /data/mysql #创建mysql安装目录
[aaa@qq.com ~]# useradd -s /sbin/nologin mysql #创建mysql用户，且不能登录系统
[aaa@qq.com ~]# chown -R mysql:mysql /data #安装目录的所有者，所属组设置为mysql用户
[aaa@qq.com ~]# ls -ld /data/
drwxr-xr-x 3 mysql mysql 19 Jun 30 04:05 /data/
[aaa@qq.com ~]# ls -ld /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Jun 30 04:05 /data/mysql/
[aaa@qq.com ~]# vim /etc/fstab #修改配置文件，永久挂载
/dev/centos/data /data/mysql ext4 defaults 0 0
[aaa@qq.com ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 10G 0 disk
├─sda1 8:1 0 296M 0 part /boot
├─sda2 8:2 0 8.7G 0 part /
└─sda3 8:3 0 1G 0 part [SWAP]
sdb 8:16 0 6G 0 disk
└─centos-data 253:0 0 4G 0 lvm /data/mysql
sr0 11:0 1 1024M 0 rom
[aaa@qq.com bin]# ls /data/mysql/
lost+found
[aaa@qq.com bin]# rm -rf /data/mysql/*
#安装mysql以主库为例
#卸载mariadb相关软件包
[aaa@qq.com ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[aaa@qq.com ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
[aaa@qq.com ~]# rpm -qa | grep mariadb
[aaa@qq.com mnt]# ls
mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[aaa@qq.com mnt]# tar -xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C ./ #解压按装包
[aaa@qq.com mnt]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.28 #修改名称并移动到/usr/local/下
[aaa@qq.com mnt]# vim /etc/my.cnf #创建编辑mysql配置文件
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.28
datadir=/data/mysql
socket/tmp/mysql.sock #必须放在tmp目录下，否则需要建软连接到tmp
log-error=/data/mysql/mysql-err.log
pid-file=/data/mysql/mysql.pid
#开启binlog日志，且设置成mixed方式
server_id=100
log-bin=master100
binlog-format="mixed"
#开启半同步复制
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
#初始化
[aaa@qq.com mnt]# cd /usr/local/mysql-5.7.28/bin/
[aaa@qq.com bin]# ./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql-5.7.28 --datadir=/data/mysql --user=mysql --initialize
#查看mysql日志，获取密码aaa@qq.com: oW;?a(L:<4gK
[aaa@qq.com bin]# cat /data/mysql/mysql-err.log
2020-06-30T11:39:44.108354Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-30T11:39:44.298033Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-30T11:39:44.348095Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-30T11:39:44.411347Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 64b543b9-bac6-11ea-94ca-000c29e70d43.
2020-06-30T11:39:44.412375Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-30T11:39:45.223278Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-30T11:39:45.351193Z 1 [Note] A temporary password is generated for aaa@qq.com: oW;?a(L:<4gK
#启动mysql
[aaa@qq.com bin]# cp -r /usr/local/mysql-5.7.28/support-files/mysql.server /etc/init.d/mysql
[aaa@qq.com bin]# service mysql start
Starting MySQL. SUCCESS!
#建软连接方便命令行使用mysql命令
[aaa@qq.com bin]# ln -s /usr/local/mysql-5.7.28/bin/mysql /sbin/mysql
#登录mysql，首次登录强制让改密码。
[aaa@qq.com ~]# mysql -uroot -p'oW;?a(L:<4gK'
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 2
Server version: 5.7.28-log
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (10.01 sec)
#查看binlog日志是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/master100 |
| log_bin_index | /data/mysql/master100.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)
#或者在mysql安装目录查看是否有binlog日志文件 master100.*
[aaa@qq.com ~]# ls /data/mysql/
auto.cnf ib_buffer_pool master100.000001 mysql.pid server-key.pem
ca-key.pem ibdata1 master100.000002 performance_schema sys
ca.pem ib_logfile0 master100.index private_key.pem
client-cert.pem ib_logfile1 mysql public_key.pem
client-key.pem ibtmp1 mysql-err.log server-cert.pem
[aaa@qq.com ~]#
#查看binlog文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| master100.000001 | 177 |
| master100.000002 | 398 |
+------------------+-----------+
2 rows in set (0.00 sec)
#修改root用户的host字段，实现root用户可以在任意机器上登录数据库
mysql> update mysql.user set host='%' where user=root;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repluser | % | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
2、备用主库192.168.2.200 操作
数据库配置
#数据库配置基本和192.168.2.100相同
#不同点，binlog日志配置不同，server_id不能相同，日志名称log-bin不能相同
[aaa@qq.com mysql-5.7.28]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.28
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql-err.log
pid-file=/data/mysql/mysql.pid
#开启binlog日志，且设置成mixed方式
server_id=200
log-bin=master200
binlog-format="mixed"
#开启半同步复制
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
#修改root用户的host字段，实现root用户可以在任意机器上登录数据库
mysql> update mysql.user set host='%' where user=root;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repluser | % | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
3、从库192.168.2.150 操作
数据库配置
#192.168.2.150是用来单纯做从库的，不竞选主库，所以不用开binlog日志和半同步复制
[aaa@qq.com mysql-5.7.28]# vim /etc/my.cnf
[aaa@qq.com mysql-5.7.28]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.28
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql-err.log
pid-file=/data/mysql/mysql.pid
server_id=150
[aaa@qq.com mysql-5.7.28]#
#修改root用户的host字段，实现root用户可以在任意机器上登录数据库
mysql> update mysql.user set host='%' where user=root;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repluser | % | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)4、配置所有数据节点主机之间可以互相以ssh**对方式认证登陆，管理节点能免密登录所有数据库
#192.168.2.100生成**对，传给备用主库和从库
[aaa@qq.com ~]# ssh-******
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:MDeLm9ZcUVv5oV5ftEHEfYcCOSrgl4GhHpZ2kI1TOCA aaa@qq.com
The key's randomart image is:
+---[RSA 2048]----+
|E..*oo .o. ==.|
|. *++ . o..oo+=|
| *+..ooo......o*|
| + o. +=.o . . oo|
| . ...S . . . o|
| = . . .|
| + o |
| . |
| |
+----[SHA256]-----+
[aaa@qq.com ~]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.200 (192.168.2.200)' can't be established.
ECDSA key fingerprint is SHA256:XfbIXfSIRyaMZndoY6Qmc4v3GxH7l2pvztFmEqUyqDM.
ECDSA key fingerprint is MD5:0f:79:60:ab:fb:13:29:67:0e:ef:76:b3:13:14:0c:3c.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com ~]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:2vDXHe6pnDdXt1gaFChEztQfXCwDqSCANGv5SaPErAQ.
ECDSA key fingerprint is MD5:88:b4:22:0f:09:7f:79:b5:1f:b4:9e:ae:bf:85:84:02.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com ~]#
#192.168.2.200生成**对，传给主库和从库
[aaa@qq.com mysql-5.7.28]# ssh-******
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:zWZrVpDeiFNUB84vAAhw8Qckha1+w/Pu6nvB3wfX3h4 aaa@qq.com
The key's randomart image is:
+---[RSA 2048]----+
| ..=B+.. ..o.. |
| ..oo. o + . |
| .. . = o |
| . . * = . |
| . . .S B + .. |
| . = o+ o... .|
| . + o+. o E.|
| oo. . . +|
| .+*o . ..|
+----[SHA256]-----+
[aaa@qq.com mysql-5.7.28]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.100 (192.168.2.100)' can't be established.
ECDSA key fingerprint is SHA256:ejSnqTT/C8uKQr727EPAt6qYcxQLu4zsJusDoyA/X3U.
ECDSA key fingerprint is MD5:eb:c6:14:f6:17:49:1b:d8:76:61:52:22:7a:be:26:b5.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com mysql-5.7.28]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:2vDXHe6pnDdXt1gaFChEztQfXCwDqSCANGv5SaPErAQ.
ECDSA key fingerprint is MD5:88:b4:22:0f:09:7f:79:b5:1f:b4:9e:ae:bf:85:84:02.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com mysql-5.7.28]#
#192.168.2.150生成**对，传给主库和备用主库
[aaa@qq.com mysql-5.7.28]# ssh-******
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Vsn6SfIaEiNjz/cfdt/IkYwOks9LizCsoSM0Tbtj6NA aaa@qq.com
The key's randomart image is:
+---[RSA 2048]----+
| |
| . . |
| + |
| . o |
| o = o S . |
| + + * + * . o . |
|o E o B = * + = |
|o..= o = X = + +.|
|.oo.o o *oo o o|
+----[SHA256]-----+
[aaa@qq.com mysql-5.7.28]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.100 (192.168.2.100)' can't be established.
ECDSA key fingerprint is SHA256:ejSnqTT/C8uKQr727EPAt6qYcxQLu4zsJusDoyA/X3U.
ECDSA key fingerprint is MD5:eb:c6:14:f6:17:49:1b:d8:76:61:52:22:7a:be:26:b5.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com mysql-5.7.28]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.200 (192.168.2.200)' can't be established.
ECDSA key fingerprint is SHA256:XfbIXfSIRyaMZndoY6Qmc4v3GxH7l2pvztFmEqUyqDM.
ECDSA key fingerprint is MD5:0f:79:60:ab:fb:13:29:67:0e:ef:76:b3:13:14:0c:3c.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com mysql-5.7.28]#
#管理节点192.168.2.210生成**对，传给3台数据库
[aaa@qq.com ~]# ssh-******
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:+sdKQA2lhHEJOr8x++71WT8EjBDEIenoOSDpJH3hDwg aaa@qq.com
The key's randomart image is:
+---[RSA 2048]----+
| o+=*=o |
|E .ooo=o |
| +oo +o .. o |
|+.+o=.. . o |
|+. +++. S . |
| . +=.o . |
| o.. o. .. |
| . + .oo .. |
| o+ ooo .. |
+----[SHA256]-----+
[aaa@qq.com ~]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.100 (192.168.2.100)' can't be established.
ECDSA key fingerprint is SHA256:ejSnqTT/C8uKQr727EPAt6qYcxQLu4zsJusDoyA/X3U.
ECDSA key fingerprint is MD5:eb:c6:14:f6:17:49:1b:d8:76:61:52:22:7a:be:26:b5.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com ~]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.200 (192.168.2.200)' can't be established.
ECDSA key fingerprint is SHA256:XfbIXfSIRyaMZndoY6Qmc4v3GxH7l2pvztFmEqUyqDM.
ECDSA key fingerprint is MD5:0f:79:60:ab:fb:13:29:67:0e:ef:76:b3:13:14:0c:3c.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com ~]# ssh-copy-id aaa@qq.com
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:2vDXHe6pnDdXt1gaFChEztQfXCwDqSCANGv5SaPErAQ.
ECDSA key fingerprint is MD5:88:b4:22:0f:09:7f:79:b5:1f:b4:9e:ae:bf:85:84:02.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
aaa@qq.com's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'aaa@qq.com'"
and check to make sure that only the key(s) you wanted were added.
[aaa@qq.com ~]#
5、主库192.168.2.100 创建主从同步用户repluser，密码设置为654321
[aaa@qq.com ~]# mysql -uroot -p'123456'
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 3
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global relay_log_purge=off;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to aaa@qq.com"%" identified by "654321";
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| master100.000002 | 685 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> 6、备用主库192.168.2.200，因为是备用的主库，所以需要创建和主库一样的主从用户repluser，密码设置为654321，并且指定主库的信息，配置成主库的从库，IO和SQL线程是yes状态，证明配置成从库了
[aaa@qq.com ~]# mysql -uroot -p'123456'
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 3
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global relay_log_purge=off;
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to aaa@qq.com"%" identified by "654321";
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> change master to master_host="192.168.2.100",master_user="repluser",master_password="654321",master_log_file="master100.000002",master_log_pos=685
-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master100.000002
Read_Master_Log_Pos: 685
Relay_Log_File: vmctl3-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master100.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: 685
Relay_Log_Space: 528
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: 100
Master_UUID: 64b543b9-bac6-11ea-94ca-000c29e70d43
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)
mysql>
7、从库192.168.2.150，因为是单纯的从库，不会成为主库，所以不需要创建repluser用户，只需要配置成主库的从库即可
[aaa@qq.com ~]# mysql -uroot -p'123456'
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 3
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host="192.168.2.100",master_user="repluser",master_password="654321",master_log_file="master100.000002",master_log
-> ;
Query OK, 0 rows affected, 2 warnings (0.06 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: 192.168.2.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master100.000002
Read_Master_Log_Pos: 685
Relay_Log_File: vmctl4-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master100.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: 685
Relay_Log_Space: 528
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: 100
Master_UUID: 64b543b9-bac6-11ea-94ca-000c29e70d43
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)
mysql>
8、可以在主库创建一个库表插入几行数据，测试主从是否配置成功，经测试主从配置成功。
#主库上操作添加测试数据
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| vmctl2 |
+------------+
1 row in set (0.00 sec)
mysql> create database gamedb;
Query OK, 1 row affected (0.00 sec)
mysql> create table gamedb.t1 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into gamedb.t1 values(9);
Query OK, 1 row affected (0.03 sec)
mysql> insert into gamedb.t1 values(99);
Query OK, 1 row affected (0.00 sec)
mysql> select * from gamedb.t1;
+------+
| id |
+------+
| 9 |
| 99 |
+------+
2 rows in set (0.00 sec)
mysql>
#备用主库查看是否有测试数据
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| vmctl3 |
+------------+
1 row in set (0.00 sec)
mysql> select * from gamedb.t1;
+------+
| id |
+------+
| 9 |
| 99 |
+------+
2 rows in set (0.00 sec)
mysql>
#从库上查看是否有测试数据
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| vmctl4 |
+------------+
1 row in set (0.00 sec)
mysql> select * from gamedb.t1;
+------+
| id |
+------+
| 9 |
| 99 |
+------+
2 rows in set (0.00 sec)
mysql>
9、MHA配置
#在3台数据库服务器上安装mha-node包
#在管理节点上安装mha-node和mha-manager
#安装依赖
[aaa@qq.com mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI
#安装mha-node包
[aaa@qq.com mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-node-0.56-0.el6 ################################# [100%]
[aaa@qq.com mha-soft-student]#
[aaa@qq.com mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI
[aaa@qq.com mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-node-0.56-0.el6 ################################# [100%]
[aaa@qq.com mha-soft-student]#
[aaa@qq.com mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI
[aaa@qq.com mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-node-0.56-0.el6 ################################# [100%]
[aaa@qq.com mha-soft-student]#
#管理节点安装mha-node
[aaa@qq.com mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI
[aaa@qq.com mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-node-0.56-0.el6 ################################# [100%]
#安装mha-manager
#安装依赖
[aaa@qq.com mha4mysql-manager-0.56]# yum -y install perl-ExtUtils-* perl-CPAN-*
[aaa@qq.com mha4mysql-manager-0.56]# ls
AUTHORS bin COPYING debian inc lib Makefile.PL MANIFEST META.yml README rpm samples t tests
[aaa@qq.com mha4mysql-manager-0.56]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...missing.
- Log::Dispatch ...missing.
- Parallel::ForkManager ...missing.
- MHA::NodeConst ...loaded. (0.56)
==> Auto-install the 3 mandatory module(s) from CPAN? [y]
#出现这种情况证明，缺少依赖，按照提示安装依赖即可，直到所有的都变成 loaded状态
[aaa@qq.com mha4mysql-manager-0.56]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...loaded. (2.14)
- Log::Dispatch ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst ...loaded. (0.56)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
[aaa@qq.com mha4mysql-manager-0.56]#make
[aaa@qq.com mha4mysql-manager-0.56]#make install编辑管理节点主机主配置文件
[aaa@qq.com ~]# mkdir /etc/mha_manager
[aaa@qq.com ~]# cp /mnt/mha-soft-student/mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha_manager/
[aaa@qq.com mha_manager]# pwd
/etc/mha_manager
[aaa@qq.com mha_manager]# vim app1.cnf
[server default]
manager_workdir=/etc/mha_manager
manager_log=/etc/mha_manager/manager.log
master_binlog_dir=/data/mysql #一定要写行不然后面会报错
master_ip_failover_script=/usr/local/bin/master_ip_failover #自动切换脚本，检查主从同步时需要注释掉这行
ssh_user=root #ssh用户
ssh_port=22
repl_user=repluser #主从用户
repl_password=654321 #主从用户的密码
user=root #数据库root用户
password=123456 #数据库root用户密码
[server1]
hostname=192.168.2.100
candidate_master=1
[server2]
hostname=192.168.2.200
candidate_master=1
[server3]
hostname=192.168.2.150
no_master=1 #不竞选主库
[aaa@qq.com mha_manager]# touch manager.log
MHA的配置检查测试
#检查ssh免密配置，successfully 表示配置成功
[aaa@qq.com mha_manager]# masterha_check_ssh --conf /etc/mha_manager/app1.cnf
Tue Jun 30 16:23:42 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jun 30 16:23:42 2020 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Tue Jun 30 16:23:42 2020 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
Tue Jun 30 16:23:42 2020 - [info] Starting SSH connection tests..
Tue Jun 30 16:23:43 2020 - [debug]
Tue Jun 30 16:23:42 2020 - [debug] Connecting via SSH from aaa@qq.com(192.168.2.100:22) to aaa@qq.com(192.168.2.200:22)..
Tue Jun 30 16:23:43 2020 - [debug] ok.
Tue Jun 30 16:23:43 2020 - [debug] Connecting via SSH from aaa@qq.com(192.168.2.100:22) to aaa@qq.com(192.168.2.150:22)..
Tue Jun 30 16:23:43 2020 - [debug] ok.
Tue Jun 30 16:23:44 2020 - [debug]
Tue Jun 30 16:23:43 2020 - [debug] Connecting via SSH from aaa@qq.com(192.168.2.200:22) to aaa@qq.com(192.168.2.100:22)..
Tue Jun 30 16:23:43 2020 - [debug] ok.
Tue Jun 30 16:23:43 2020 - [debug] Connecting via SSH from aaa@qq.com(192.168.2.200:22) to aaa@qq.com(192.168.2.150:22)..
Tue Jun 30 16:23:44 2020 - [debug] ok.
Tue Jun 30 16:23:45 2020 - [debug]
Tue Jun 30 16:23:43 2020 - [debug] Connecting via SSH from aaa@qq.com(192.168.2.150:22) to aaa@qq.com(192.168.2.100:22)..
Tue Jun 30 16:23:44 2020 - [debug] ok.
Tue Jun 30 16:23:44 2020 - [debug] Connecting via SSH from aaa@qq.com(192.168.2.150:22) to aaa@qq.com(192.168.2.200:22)..
Tue Jun 30 16:23:44 2020 - [debug] ok.
Tue Jun 30 16:23:45 2020 - [info] All SSH connection tests passed successfully.
[aaa@qq.com mha_manager]#
#检查主从同步时把app1.cnf文件中的此配置项#master_ip_failover_script=/usr/local/bin/master_ip_failover注释掉，不然检查失败。
#MySQL Replication Health is OK. 表示主从没问题
[aaa@qq.com mha_manager]# masterha_check_repl --conf /etc/mha_manager/app1.cnf
Tue Jun 30 17:38:13 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jun 30 17:38:13 2020 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Tue Jun 30 17:38:13 2020 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
Tue Jun 30 17:38:13 2020 - [info] MHA::MasterMonitor version 0.56.
Tue Jun 30 17:38:14 2020 - [info] GTID failover mode = 0
Tue Jun 30 17:38:14 2020 - [info] Dead Servers:
Tue Jun 30 17:38:14 2020 - [info] Alive Servers:
Tue Jun 30 17:38:14 2020 - [info] 192.168.2.100(192.168.2.100:3306)
Tue Jun 30 17:38:14 2020 - [info] 192.168.2.200(192.168.2.200:3306)
Tue Jun 30 17:38:14 2020 - [info] 192.168.2.150(192.168.2.150:3306)
Tue Jun 30 17:38:14 2020 - [info] Alive Slaves:
Tue Jun 30 17:38:14 2020 - [info] 192.168.2.200(192.168.2.200:3306) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Tue Jun 30 17:38:14 2020 - [info] Replicating from 192.168.2.100(192.168.2.100:3306)
Tue Jun 30 17:38:14 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Jun 30 17:38:14 2020 - [info] 192.168.2.150(192.168.2.150:3306) Version=5.7.28 (oldest major version between slaves) log-bin:disabled
Tue Jun 30 17:38:14 2020 - [info] Replicating from 192.168.2.100(192.168.2.100:3306)
Tue Jun 30 17:38:14 2020 - [info] Not candidate for the new Master (no_master is set)
Tue Jun 30 17:38:14 2020 - [info] Current Alive Master: 192.168.2.100(192.168.2.100:3306)
Tue Jun 30 17:38:14 2020 - [info] Checking slave configurations..
Tue Jun 30 17:38:14 2020 - [info] read_only=1 is not set on slave 192.168.2.200(192.168.2.200:3306).
Tue Jun 30 17:38:14 2020 - [info] read_only=1 is not set on slave 192.168.2.150(192.168.2.150:3306).
Tue Jun 30 17:38:14 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.2.150(192.168.2.150:3306).
Tue Jun 30 17:38:14 2020 - [warning] log-bin is not set on slave 192.168.2.150(192.168.2.150:3306). This host cannot be a master.
Tue Jun 30 17:38:14 2020 - [info] Checking replication filtering settings..
Tue Jun 30 17:38:14 2020 - [info] binlog_do_db= , binlog_ignore_db=
Tue Jun 30 17:38:14 2020 - [info] Replication filtering check ok.
Tue Jun 30 17:38:14 2020 - [info] GTID (with auto-pos) is not supported
Tue Jun 30 17:38:14 2020 - [info] Starting SSH connection tests..
Tue Jun 30 17:38:17 2020 - [info] All SSH connection tests passed successfully.
Tue Jun 30 17:38:17 2020 - [info] Checking MHA Node version..
Tue Jun 30 17:38:17 2020 - [info] Version check ok.
Tue Jun 30 17:38:17 2020 - [info] Checking SSH publickey authentication settings on the current master..
Tue Jun 30 17:38:17 2020 - [info] HealthCheck: SSH to 192.168.2.100 is reachable.
Tue Jun 30 17:38:18 2020 - [info] Master MHA Node version is 0.56.
Tue Jun 30 17:38:18 2020 - [info] Checking recovery script configurations on 192.168.2.100(192.168.2.100:3306)..
Tue Jun 30 17:38:18 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=master100.000002
Tue Jun 30 17:38:18 2020 - [info] Connecting to aaa@qq.com(192.168.2.100:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql, up to master100.000002
Tue Jun 30 17:38:18 2020 - [info] Binlog setting check done.
Tue Jun 30 17:38:18 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Jun 30 17:38:18 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.2.200 --slave_ip=192.168.2.200 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.28-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Tue Jun 30 17:38:18 2020 - [info] Connecting to aaa@qq.com(192.168.2.200:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to vmctl3-relay-bin.000002
Temporary relay log file is /data/mysql/vmctl3-relay-bin.000002
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.
Tue Jun 30 17:38:18 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.2.150 --slave_ip=192.168.2.150 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.28 --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Tue Jun 30 17:38:18 2020 - [info] Connecting to aaa@qq.com(192.168.2.150:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to vmctl4-relay-bin.000002
Temporary relay log file is /data/mysql/vmctl4-relay-bin.000002
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.
Tue Jun 30 17:38:19 2020 - [info] Slaves settings check done.
Tue Jun 30 17:38:19 2020 - [info]
192.168.2.100(192.168.2.100:3306) (current master)
+--192.168.2.200(192.168.2.200:3306)
+--192.168.2.150(192.168.2.150:3306)
Tue Jun 30 17:38:19 2020 - [info] Checking replication health on 192.168.2.200..
Tue Jun 30 17:38:19 2020 - [info] ok.
Tue Jun 30 17:38:19 2020 - [info] Checking replication health on 192.168.2.150..
Tue Jun 30 17:38:19 2020 - [info] ok.
Tue Jun 30 17:38:19 2020 - [warning] master_ip_failover_script is not defined.
Tue Jun 30 17:38:19 2020 - [warning] shutdown_script is not defined.
Tue Jun 30 17:38:19 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[aaa@qq.com mha_manager]#
#启动mha，没有在后台运行，占用一个终端窗口
[aaa@qq.com mha_manager]# masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover
Tue Jun 30 17:44:01 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jun 30 17:44:01 2020 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Tue Jun 30 17:44:01 2020 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
#再开一个终端查看状态
[aaa@qq.com ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf
app1 (pid:37790) is running(0:PING_OK), master:192.168.2.100
[aaa@qq.com ~]#
#停服务，查看状态
[aaa@qq.com ~]# masterha_stop --conf=/etc/mha_manager/app1.cnf
Stopped app1 successfully.
[aaa@qq.com ~]#
[aaa@qq.com ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf
app1 is stopped(2:NOT_RUNNING).
[aaa@qq.com ~]#测试高可用
#复制故障转移脚本到/usr/local/bin/下
[aaa@qq.com mha_manager]# cp /mnt/mha-soft-student/mha4mysql-manager-0.56/samples/scripts/master_ip_failover /usr/local/bin/
[aaa@qq.com mha_manager]# vim /usr/local/bin/master_ip_failover
#在$new_master_password ); 下面添加如下内容，33左右，还需要修改其他几个地方！！！
my $vip = '192.168.2.101/24'; # Virtual IP
my $key = "0";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
#在主库上手动绑定VIP地址(mha_manager服务不负责部署 只负责故障转移，所以服务启动之前，主库的VIP地址要手动配置)
[aaa@qq.com ~]# ifconfig eth0:0 192.168.2.101/24
验证高可用
#去掉app1.cnf文件master_ip_failover_script=/usr/local/bin/master_ip_failover 这一行的注释，启动服务
[aaa@qq.com mha_manager]# masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover
Tue Jun 30 18:17:49 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jun 30 18:17:49 2020 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Tue Jun 30 18:17:49 2020 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
#查看状态
[aaa@qq.com ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf
app1 (pid:38662) is running(0:PING_OK), master:192.168.2.100
[aaa@qq.com ~]#
#故障切换前的各个状态
#用VIP连接数据库
[aaa@qq.com ~]# mysql -h 192.168.2.101 -uroot -p'123456'
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 36
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| vmctl2 |
+------------+
1 row in set (0.00 sec)
mysql>
#主库上有VIP
aaa@qq.com ~]# ifconfig | head -12
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.100 netmask 255.255.255.0 broadcast 192.168.2.255
inet6 fe80::20c:29ff:fee7:d43 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:e7:0d:43 txqueuelen 1000 (Ethernet)
RX packets 530278 bytes 764431757 (729.0 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 40522 bytes 3774157 (3.5 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.101 netmask 255.255.255.0 broadcast 192.168.2.255
ether 00:0c:29:e7:0d:43 txqueuelen 1000 (Ethernet)
[aaa@qq.com ~]#
#备用主库上没有VIP
[aaa@qq.com mha-soft-student]# clear
[aaa@qq.com mha-soft-student]# ifconfig | head -12
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.200 netmask 255.255.255.0 broadcast 192.168.2.255
inet6 fe80::20c:29ff:fecb:9c00 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:cb:9c:00 txqueuelen 1000 (Ethernet)
RX packets 529744 bytes 764368340 (728.9 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 39617 bytes 3581843 (3.4 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
[aaa@qq.com mha-soft-student]#
#从库的从库信息
[aaa@qq.com ~]# mysql -uroot -p123456 -e "show slave status\G" | head -13
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master100.000002
Read_Master_Log_Pos: 2466
Relay_Log_File: vmctl4-relay-bin.000002
Relay_Log_Pos: 2101
Relay_Master_Log_File: master100.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[aaa@qq.com ~]#
#手动停掉主库的mysql服务，测试高可用故障切换
[aaa@qq.com ~]# service mysql status
SUCCESS! MySQL running (2988)
[aaa@qq.com ~]# service mysql stop
Shutting down MySQL............ SUCCESS!
[aaa@qq.com ~]#
[aaa@qq.com ~]# service mysql status
ERROR! MySQL is not running
[aaa@qq.com ~]#
#查看manager状态
[aaa@qq.com ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf #故障前
app1 (pid:38662) is running(0:PING_OK), master:192.168.2.100
[aaa@qq.com ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf #故障后
app1 is stopped(2:NOT_RUNNING).
[aaa@qq.com ~]#
#查看连接信息
mysql> select @@hostname; #故障前
+------------+
| @@hostname |
+------------+
| vmctl2 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@hostname; #切换中，要快点查看，否则查看不到这个状态
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
mysql> select @@hostname; #故障后
+------------+
| @@hostname |
+------------+
| vmctl3 |
+------------+
1 row in set (0.01 sec)
#查看主库是否还有VIP
[aaa@qq.com ~]# service mysql status
ERROR! MySQL is not running
[aaa@qq.com ~]# ifconfig | head -12
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.100 netmask 255.255.255.0 broadcast 192.168.2.255
inet6 fe80::20c:29ff:fee7:d43 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:e7:0d:43 txqueuelen 1000 (Ethernet)
RX packets 530697 bytes 764481948 (729.0 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 40817 bytes 3821074 (3.6 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
[aaa@qq.com ~]#
#查看备用主库(也就是当前的主库)，是否有VIP
[aaa@qq.com mha-soft-student]# ifconfig | head -12
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.200 netmask 255.255.255.0 broadcast 192.168.2.255
inet6 fe80::20c:29ff:fecb:9c00 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:cb:9c:00 txqueuelen 1000 (Ethernet)
RX packets 530068 bytes 764413227 (729.0 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 39905 bytes 3687723 (3.5 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.101 netmask 255.255.255.0 broadcast 192.168.2.255
ether 00:0c:29:cb:9c:00 txqueuelen 1000 (Ethernet)
[aaa@qq.com mha-soft-student]#
#查看从库的从库信息，是否指向新的主库
[aaa@qq.com ~]# mysql -uroot -p123456 -e "show slave status\G" | head -13 #挂账前
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master100.000002
Read_Master_Log_Pos: 2466
Relay_Log_File: vmctl4-relay-bin.000002
Relay_Log_Pos: 2101
Relay_Master_Log_File: master100.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[aaa@qq.com ~]#
[aaa@qq.com ~]# mysql -uroot -p123456 -e "show slave status\G" | head -13 #故障后
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master200.000002
Read_Master_Log_Pos: 1023
Relay_Log_File: vmctl4-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master200.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[aaa@qq.com ~]#
#从各个角度查看，证明实现了高可用
检查ssh免密登录，主从时，遇到的相关报错及解决办法
报错1
报错信息：Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.
解决办法：
ln -s /usr/local/mysql-5.7.28/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql-5.7.28/bin/mysql /usr/local/bin/mysql
报错2
报错信息：Failed to save binary log: Binlog not found from /var/lib/mysql,/var/log/mysql! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master" correctly in the MHA Manager's configuration file and try again.
at /usr/bin/save_binary_logs line 123.
解决办法：
在 /etc/mha_manager/app1.cnf
添加 master_binlog_dir=/data/mysql
上一篇： springcloud学习笔记之负载均衡
下一篇： 显示器亮点、暗点和坏点检测的方法详解