失眠网 > 基于keepalived+GTID半同步主从复制的高可用MySQL集群

基于keepalived+GTID半同步主从复制的高可用MySQL集群

时间：2022-04-17 21:20:58

项目拓扑图

项目名称

项目环境

项目描述

ip地址规划

项目步骤

一.安装好8台全新的centos7.9的系统，关闭firewalld和selinux，配置每台主机的静态ip地址，设置每台主机对应的主机名。

1.关闭firewalld

2.关闭seLinux

3.配置每台主机静态ip地址（桥接模式）

4.设置每台主机对应的主机名

二.部署ansible服务器，配置SSH免密通道（单向），编写主机清单，使用ansible以二进制方式通过脚本一键安装MySQL。

1.安装epel源和ansible

2.建立免密通道，在ansible主机上生成密钥对

3.上传公钥到MySQL集群服务器的root用户家目录下

4.验证是否实现免密码密钥认证

5.编写主机清单

6.测试

7.MySQL官网下载mysql的二进制包，编写好一键安装脚本

三.在master服务器和ansible服务器之间建立双向的免密通道，方便同步数据。

四.部署4台MySQL服务器，一台master服务器，2台slave服务器，一台delay-backup延迟服务器，安装半同步相关的插件。

五.使用mysqldump在master服务器上导出基础数据，scp远程同步到ansible服务器，通过ansible服务器下发到salve服务器。

六.在slave服务器上使用mysql导入master服务器的基础数据。

1.slave服务器上直接导入

2.使用ansible（shell模块）导入基础数据

七.MySQL集群开启GTID功能，启动主从复制服务，配置好延迟备份服务器，从slave-1上拿二进制日志。

1.安装插件

master服务器上操作

salve服务器上操作

使用ansible的playbook来下载插件和检查插件是否安装

2.修改master和salve配置文件

在master上操作

3.在slave-1上操作（即是slave也是master）

4.在slave-2上操作

5.配置好延迟备份服务器，从slave-1上拿二进制日志。

6.测试

八.部署mysql的failover插件（MHA），实现自动的故障切换，如果master宕机，能自动提升其中一台slave为新的master，其他slave到新的master上获得二进制日志。

0.前提条件（准备好rpm包）

MySQL集群都安装mha4mysql-node

1.下载该软件需要的依赖包

2.通过ansible使用rpm安装rpm包

3.管理节点安装mha4mysql-manager，延迟服务器同时是管理节点

4.建立免密通道

5.创建监控用户（一台master和2台slave都要创建，delay-backup服务器不用）

配置MHA

1.创建工作目录

2.在/usr/local/bin目录下创建脚本master_ip_failover（故障切换脚本，切换master的VIP地址）

3.给脚本可执行权限

4.在/etc/masterha下创建配置文件f

5.检查管理节点到所有Node节点的ssh连接状态

6.检查复制环境。

7.检查管理节点的状态

8.开启管理节点监控

9.关闭master服务器的mysqld

10.管理节点查看配置文件，会发现[server1]模块和user=root被删除了。

九.在master上创建一个计划任务每天2:30进行数据库的备份，编写备份脚本，备份文件包含当天的日期，使用rsync+sersync远程同步到ansible服务器。

编写脚本

部署rsync+sersync

1.ansible服务器操作

2.master服务器操作

3.实现自动同步

4.查看rsync的帮助文档

十.部署两台安装了mysqlrouter中间件软件的服务器，实现读写分离和高可用功能。

十一.在两台mysqlrouter服务器上安装keepalived软件，配置2个vrrp实例，互为主备，来实现双vip的高可用功能。

十二.使用dns实现负载均衡，在云平台（阿里云、腾迅云等）购买域名，然后在域名里添加两条A记录，同一个域名对应着2个vip。

十三.使用压力测试软件（sysbench或tcpp）对整个mysql集群进行压力测试。

使用sysbench进行测试

mysql性能测试工具——tpcc-mysql

项目架构图

项目名称

基于keepalived（双vip）+ GTID 半同步主从复制的高可用MySQL集群。

项目环境

8台服务器（2G，2核）centos7.9、mysql5.7.41、mysqlrouter8.0.33、keepalived1.3.5、ansible2.9.27、sysbench-1.0.17。

项目描述

目的是构建一个高可用的能实现读写分离的高性能MySQL集群，确保业务的稳定同时能批量的去部署和管理整个集群。

ip地址规划

项目步骤

一.安装好8台全新的centos7.9的系统，关闭firewalld和selinux，配置每台主机的静态ip地址，设置每台主机对应的主机名。

1.关闭firewalld

# 立即关闭firewalldsystemctl stop firewalld# 设置firewalld开启不启动systemctl disable firewalld# 查看firewalld的状态systemctl status firewalld

2.关闭seLinux

# 临时关闭seLinuxsetenforce 0# 永久关闭selinuxsed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config# 查看seLinuxgetenforce

3.配置每台主机静态ip地址（桥接模式）

[root@ansible ~]# cat static_ip.sh #!/bin/bash# 获得网段号net_seg=$(ip a|grep "ens33$"|awk '{print $2}'|cut -d "/" -f 1|awk -F"." '{print $3}')# 获得主机号host_seg=$(ip a|grep "ens33$"|awk '{print $2}'|cut -d "/" -f 1|awk -F"." '{print $4}')# 进入/etc/sysconfig/network-scripts/cd /etc/sysconfig/network-scripts/#清空>ifcfg-ens33#编写ifcfg-ens33cat >> ifcfg-ens33 << EOFBOOTPROTO=noneNAME=ens33 DEVICE=ens33ONBOOT=yes IPADDR=192.168.$net_seg.$host_segGATEWAY=192.168.$net_seg.1 NETMASK=255.255.255.0 DNS2=114.114.114.114 EOF#重启服务service network restart#查看ip地址ip a

4.设置每台主机对应的主机名

hostnamectl set-hostname ansiblehostnamectl set-hostname masterhostnamectl set-hostname slave-1hostnamectl set-hostname slave-2hostnamectl set-hostname delay-backuphostnamectl set-hostname mysqlrouter-1hostnamectl set-hostname mysqlrouter-2hostnamectl set-hostname test-clientsu - root

二.部署ansible服务器，配置SSH免密通道（单向），编写主机清单，使用ansible以二进制方式通过脚本一键安装MySQL。

1.安装epel源和ansible

yum install epel-release -yyum install ansible -y

2.建立免密通道，在ansible主机上生成密钥对

[root@ansible .ssh]# ssh-keygen -t rsaGenerating 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:RwPsvRYZ/cRdqUv2JVlFKQovTUsDb+3B+27izeinC8c root@ansibleThe key's randomart image is:+---[RSA 2048]----+| ..... . oO|| .oo++.ooo|| . .O=+*oo || .o=*.+* .|| S ooooo..|| .o .... || . . E . || o.=o || o**+ |+----[SHA256]-----+[root@ansible .ssh]# lsid_rsa id_rsa.pub

3.上传公钥到MySQL集群服务器的root用户家目录下

ssh-copy-id -i id_rsa.pub root@192.168.0.11ssh-copy-id -i id_rsa.pub root@192.168.0.17ssh-copy-id -i id_rsa.pub root@192.168.0.12ssh-copy-id -i id_rsa.pub root@192.168.0.13

4.验证是否实现免密码密钥认证

[root@ansible .ssh]# ssh root@192.168.0.11Last login: Tue Aug 1 11:12:38 [root@master ~]# exit登出Connection to 192.168.0.11 closed.[root@ansible .ssh]# ssh root@192.168.0.17Last login: Tue Aug 1 11:12:58 [root@slave-1 ~]# exit登出Connection to 192.168.0.17 closed.[root@ansible .ssh]# ssh root@192.168.0.12Last login: Tue Aug 1 11:13:07 [root@slave-2 ~]# exit登出Connection to 192.168.0.12 closed.[root@ansible .ssh]# ssh root@192.168.0.13Last login: Tue Aug 1 11:13:23 [root@delay-backup ~]# exit登出Connection to 192.168.0.13 closed.

5.编写主机清单

[root@ansible .ssh]# cd /etc/ansible[root@ansible ansible]# lsansible.cfg hosts roles[root@ansible ansible]# vim hosts [db]192.168.0.11192.168.0.17192.168.0.12192.168.0.13[slave]192.168.0.17192.168.0.12192.168.0.13

6.测试

[root@ansible ansible]# ansible db -m shell -a "ip add"192.168.0.17 | CHANGED | rc=0 >>1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:a0:46:1c brd ff:ff:ff:ff:ff:ffinet 192.168.0.17/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 85265sec preferred_lft 85265secinet6 fe80::cbd1:6bd3:108f:e86e/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::3d73:75d:9f7a:924c/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::e027:ebc3:6ae5:d5e7/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever192.168.0.12 | CHANGED | rc=0 >>1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:6b:0e:a9 brd ff:ff:ff:ff:ff:ffinet 192.168.0.12/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 83115sec preferred_lft 83115secinet6 fe80::cbd1:6bd3:108f:e86e/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::3d73:75d:9f7a:924c/64 scope link noprefixroute valid_lft forever preferred_lft forever192.168.0.13 | CHANGED | rc=0 >>1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:fe:7a:e0 brd ff:ff:ff:ff:ff:ffinet 192.168.0.13/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 83157sec preferred_lft 83157secinet6 fe80::cbd1:6bd3:108f:e86e/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::3d73:75d:9f7a:924c/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::e027:ebc3:6ae5:d5e7/64 scope link noprefixroute valid_lft forever preferred_lft forever192.168.0.11 | CHANGED | rc=0 >>1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:90:24:d3 brd ff:ff:ff:ff:ff:ffinet 192.168.0.11/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 85257sec preferred_lft 85257secinet6 fe80::cbd1:6bd3:108f:e86e/64 scope link noprefixroute valid_lft forever preferred_lft forever

7.MySQL官网下载mysql的二进制包，编写好一键安装脚本

[root@ansible ~]# lsmysql-5.7.41-linux-glibc2.12-x86_64.tar.gz onekey_install_mysql_binary_v2.sh[root@ansible ~]# cat onekey_install_mysql_binary_v2.sh #!/bin/bash#解决软件的依赖关系yum install cmake ncurses-devel gcc gcc-c++ vim lsof bzip2 openssl-devel ncurses-compat-libs -y#解压mysql二进制安装包tar xf mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz#移动mysql解压后的文件到/usr/local下改名叫mysqlmv mysql-5.7.41-linux-glibc2.12-x86_64 /usr/local/mysql#新建组和用户 mysqlgroupadd mysql#mysql这个用户的shell 是/bin/false 属于mysql组 useradd -r -g mysql -s /bin/false mysql#关闭firewalld防火墙服务，并且设置开机不要启动systemctl stop firewalldsystemctl disable firewalld#临时关闭selinuxsetenforce 0#永久关闭selinuxsed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config#新建存放数据的目录mkdir /data/mysql -p#修改/data/mysql目录的权限归mysql用户和mysql组所有，这样mysql用户可以对这个文件夹进行读写了chown mysql:mysql /data/mysql/#只是允许mysql这个用户和mysql组可以访问，其他人都不能访问chmod 750 /data/mysql/#进入/usr/local/mysql/bin目录cd /usr/local/mysql/bin/#初始化mysql./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql &>passwd.txt#让mysql支持ssl方式登录的设置./mysql_ssl_rsa_setup --datadir=/data/mysql/#获得临时密码，$NF表示最后一个字段，命令替换：$(命令)tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')# 修改PATH变量，加入mysql bin目录的路径#临时修改PATH变量的值export PATH=/usr/local/mysql/bin/:$PATH#重新启动linux系统后也生效，永久修改echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc#复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqldcp ../support-files/mysql.server /etc/init.d/mysqld#修改/etc/init.d/mysqld脚本文件里的datadir目录的值sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld#生成/etc/f配置文件cat >/etc/f <<EOF[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8[mysql]auto-rehashprompt=\\u@\\d \\R:\\m mysql>EOF#修改内核的open file的数量ulimit -n 1000000#设置开机启动的时候配置也生效echo "ulimit -n 1000000" >>/etc/rc.localchmod +x /etc/rc.d/rc.local#启动mysqld进程service mysqld start#将mysqld添加到linux系统里服务管理名单里/sbin/chkconfig --add mysqld#设置mysqld服务开机启动/sbin/chkconfig mysqld on#初次修改密码需要使用--connect-expired-password 选项#-e 后面接的表示是在mysql里需要执行命令 execute 执行#set password='Sanchuang123#'; 修改root用户的密码为Sanchuang123#mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='Sanchuang123#';"#检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。mysql -uroot -p'Sanchuang123#' -e "show databases;"

三.在master服务器和ansible服务器之间建立双向的免密通道，方便同步数据。

[root@master ~]# ssh-keygen -t rsaGenerating 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:M/ZdAJaMqeVF+2C7ZC4LoJs+suYNRRKHqY66kBVDKn0 root@masterThe key's randomart image is:+---[RSA 2048]----+| .+.=+.|| *o+.+o||+.+.E + .+ . ||o o+ . .. + . ||o ... S + . . ||.+.. . . B o . ||+.. . . + . ||+.+o . o ||=*+o . |+----[SHA256]-----+[root@master ~]# cd .ssh[root@master .ssh]# lsauthorized_keys id_rsa id_rsa.pub[root@master .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.0.14/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa.pub"The authenticity of host '192.168.0.14 (192.168.0.14)' can't be established.ECDSA key fingerprint is SHA256:l7LRfACELrI6mU2XvYaCz+sDBWiGkYnAecPgnxJxdvE.ECDSA key fingerprint is MD5:b6:f7:e1:c5:23:24:5c:16:1f:66:42:ba:80:a6:3c:fd.Are you sure you want to continue connecting (yes/no)? yes/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keysroot@192.168.0.14's password: Number of key(s) added: 1Now try logging into the machine, with: "ssh 'root@192.168.0.14'"and check to make sure that only the key(s) you wanted were added.[root@master .ssh]# ssh root@192.168.0.14Last login: Tue Aug 1 11:12:29 [root@ansible ~]# exit登出Connection to 192.168.0.14 closed.[root@master .ssh]#

四.部署4台MySQL服务器，一台master服务器，2台slave服务器，一台delay-backup延迟服务器，安装半同步相关的插件。

[root@ansible ~]# ansible db -m copy -a "src=/root/onekey_install_mysql_binary_v2.sh dest=/root/ "192.168.0.11 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "4d0492d9e7313f84092c386da4e8e4971779ec36", "dest": "/root/onekey_install_mysql_binary_v2.sh", "gid": 0, "group": "root", "md5sum": "0a5d406e52205a0274cd89f9167d6610", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 3044, "src": "/root/.ansible/tmp/ansible-tmp-1690860872.1-12092-6885179297673/source", "state": "file", "uid": 0}192.168.0.13 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "4d0492d9e7313f84092c386da4e8e4971779ec36", "dest": "/root/onekey_install_mysql_binary_v2.sh", "gid": 0, "group": "root", "md5sum": "0a5d406e52205a0274cd89f9167d6610", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 3044, "src": "/root/.ansible/tmp/ansible-tmp-1690860872.46-12098-124732116956579/source", "state": "file", "uid": 0}192.168.0.17 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "4d0492d9e7313f84092c386da4e8e4971779ec36", "dest": "/root/onekey_install_mysql_binary_v2.sh", "gid": 0, "group": "root", "md5sum": "0a5d406e52205a0274cd89f9167d6610", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 3044, "src": "/root/.ansible/tmp/ansible-tmp-1690860872.16-12094-264748168418124/source", "state": "file", "uid": 0}192.168.0.12 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "4d0492d9e7313f84092c386da4e8e4971779ec36", "dest": "/root/onekey_install_mysql_binary_v2.sh", "gid": 0, "group": "root", "md5sum": "0a5d406e52205a0274cd89f9167d6610", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 3044, "src": "/root/.ansible/tmp/ansible-tmp-1690860872.43-12096-172851883633782/source", "state": "file", "uid": 0}[root@ansible ~]# [root@ansible ~]# ansible db -m copy -a "src=/root/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz dest=/root/ "192.168.0.13 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "08b13fb151cf83d81e1254d42d522587730b84ad", "dest": "/root/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz", "gid": 0, "group": "root", "md5sum": "501a7f6f25246b178fef90321391891c", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 678018165, "src": "/root/.ansible/tmp/ansible-tmp-1690860896.32-12194-189907039350318/source", "state": "file", "uid": 0}192.168.0.11 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "08b13fb151cf83d81e1254d42d522587730b84ad", "dest": "/root/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz", "gid": 0, "group": "root", "md5sum": "501a7f6f25246b178fef90321391891c", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 678018165, "src": "/root/.ansible/tmp/ansible-tmp-1690860896.26-12189-112504332061161/source", "state": "file", "uid": 0}192.168.0.12 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "08b13fb151cf83d81e1254d42d522587730b84ad", "dest": "/root/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz", "gid": 0, "group": "root", "md5sum": "501a7f6f25246b178fef90321391891c", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 678018165, "src": "/root/.ansible/tmp/ansible-tmp-1690860896.29-12193-195272794525868/source", "state": "file", "uid": 0}192.168.0.17 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "08b13fb151cf83d81e1254d42d522587730b84ad", "dest": "/root/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz", "gid": 0, "group": "root", "md5sum": "501a7f6f25246b178fef90321391891c", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 678018165, "src": "/root/.ansible/tmp/ansible-tmp-1690860896.26-12191-279069254403413/source", "state": "file", "uid": 0}[root@ansible ~]# ansible db -m shell -a 'bash /root/onekey_install_mysql_binary_v2.sh'192.168.0.13 | CHANGED | rc=0 >>已加载插件：fastestmirrorLoading mirror speeds from cached hostfile* base: mirrors.* extras: mirrors.* updates: mirrors.软件包 cmake-2.8.12.2-2.el7.x86_64 已安装并且是最新版本软件包 ncurses-devel-5.9-14.0511.el7_4.x86_64 已安装并且是最新版本软件包 gcc-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 gcc-c++-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 2:vim-enhanced-7.4.629-8.el7_9.x86_64 已安装并且是最新版本软件包 lsof-4.87-6.el7.x86_64 已安装并且是最新版本软件包 bzip2-1.0.6-13.el7.x86_64 已安装并且是最新版本软件包 1:openssl-devel-1.0.2k-26.el7_9.x86_64 已安装并且是最新版本没有可用软件包 ncurses-compat-libs。无须任何处理Starting MySQL.. SUCCESS! Databaseinformation_schemamysqlperformance_schemasysLogging to '/data/mysql/delay-backup.err'.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.192.168.0.17 | CHANGED | rc=0 >>已加载插件：fastestmirrorLoading mirror speeds from cached hostfile* base: mirrors.* extras: mirrors.* updates: mirrors.软件包 cmake-2.8.12.2-2.el7.x86_64 已安装并且是最新版本软件包 ncurses-devel-5.9-14.0511.el7_4.x86_64 已安装并且是最新版本软件包 gcc-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 gcc-c++-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 2:vim-enhanced-7.4.629-8.el7_9.x86_64 已安装并且是最新版本软件包 lsof-4.87-6.el7.x86_64 已安装并且是最新版本软件包 bzip2-1.0.6-13.el7.x86_64 已安装并且是最新版本软件包 1:openssl-devel-1.0.2k-26.el7_9.x86_64 已安装并且是最新版本没有可用软件包 ncurses-compat-libs。无须任何处理Starting MySQL... SUCCESS! Databaseinformation_schemamysqlperformance_schemasysLogging to '/data/mysql/slave-1.err'.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.192.168.0.12 | CHANGED | rc=0 >>已加载插件：fastestmirrorLoading mirror speeds from cached hostfile* base: * extras: * updates: 软件包 cmake-2.8.12.2-2.el7.x86_64 已安装并且是最新版本软件包 ncurses-devel-5.9-14.0511.el7_4.x86_64 已安装并且是最新版本软件包 gcc-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 gcc-c++-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 2:vim-enhanced-7.4.629-8.el7_9.x86_64 已安装并且是最新版本软件包 lsof-4.87-6.el7.x86_64 已安装并且是最新版本软件包 bzip2-1.0.6-13.el7.x86_64 已安装并且是最新版本软件包 1:openssl-devel-1.0.2k-26.el7_9.x86_64 已安装并且是最新版本没有可用软件包 ncurses-compat-libs。无须任何处理Starting MySQL.. SUCCESS! Databaseinformation_schemamysqlperformance_schemasysLogging to '/data/mysql/slave-2.err'.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.192.168.0.11 | CHANGED | rc=0 >>已加载插件：fastestmirrorLoading mirror speeds from cached hostfile* base: mirrors.* extras: mirrors.* updates: mirrors.软件包 cmake-2.8.12.2-2.el7.x86_64 已安装并且是最新版本软件包 ncurses-devel-5.9-14.0511.el7_4.x86_64 已安装并且是最新版本软件包 gcc-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 gcc-c++-4.8.5-44.el7.x86_64 已安装并且是最新版本软件包 2:vim-enhanced-7.4.629-8.el7_9.x86_64 已安装并且是最新版本软件包 lsof-4.87-6.el7.x86_64 已安装并且是最新版本软件包 bzip2-1.0.6-13.el7.x86_64 已安装并且是最新版本软件包 1:openssl-devel-1.0.2k-26.el7_9.x86_64 已安装并且是最新版本没有可用软件包 ncurses-compat-libs。无须任何处理Starting MySQL.. SUCCESS! Databaseinformation_schemamysqlperformance_schemasysLogging to '/data/mysql/master.err'.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.

五.使用mysqldump在master服务器上导出基础数据，scp远程同步到ansible服务器，通过ansible服务器下发到salve服务器。

[root@master ~]# mysql -uroot -p"Sanchuang123#"-bash: mysql: 未找到命令[root@master ~]# which mysql/usr/bin/which: no mysql in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)# 切换用户，重启加载环境变量[root@master ~]# su - root上一次登录：二 8月 1 11:42:46 CST 从 192.168.0.14pts/1 上[root@master ~]# which mysql/usr/local/mysql/bin/mysql# 建库建表，插入一些数据[root@master ~]# mysql -uroot -p"Sanchuang123#"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 6Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@(none) 11:49 mysql>CREATE DATABASE TENNIS default character set utf8;Query OK, 1 row affected (0.00 sec)root@(none) 11:49 mysql>use TENNIS;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@TENNIS 11:50 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || mysql || performance_schema || sys|+--------------------+5 rows in set (0.00 sec)root@TENNIS 11:50 mysql>CREATE TABLE TEAMS-> (TEAMNO INTEGERNOT NULL,->PLAYERNO INTEGERNOT NULL,->DIVISION CHAR(6)NOT NULL,->PRIMARY KEY (TEAMNO) )-> ;Query OK, 0 rows affected (0.01 sec)root@TENNIS 11:50 mysql>INSERT INTO TEAMS VALUES (1, 6, 'first'),(2, 27, 'second');Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0root@TENNIS 11:50 mysql>select * from TEAMS;+--------+----------+----------+| TEAMNO | PLAYERNO | DIVISION |+--------+----------+----------+|1 | 6 | first ||2 | 27 | second |+--------+----------+----------+2 rows in set (0.00 sec)root@TENNIS 11:51 mysql>exitBye# master服务器上导出基础数据[root@master ~]# mysqldump -uroot -p'Sanchuang123#' --all-databases > /root/all_db.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@master ~]# lsall_db.sql# scp远程同步到ansible服务器root@master ~]# scp /root/all_db.sql root@192.168.0.14:/root/all_db.sql100% 874KB 24.1MB/s 00:00 [root@master ~]# #通过ansible下发到salve服务器[root@ansible ~]# lsall_db.sql anaconda-ks.cfg mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz onekey_install_mysql_binary_v2.sh static_ip.sh[root@ansible ~]# ansible db -m copy -a "src=/root/all_db.sql dest=/root/ "192.168.0.11 | SUCCESS => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": false, "checksum": "b23510fc2d56012aa8a06c08517c38fcd0b85b7b", "dest": "/root/all_db.sql", "gid": 0, "group": "root", "mode": "0644", "owner": "root", "path": "/root/all_db.sql", "secontext": "unconfined_u:object_r:admin_home_t:s0", "size": 895179, "state": "file", "uid": 0}192.168.0.12 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "b23510fc2d56012aa8a06c08517c38fcd0b85b7b", "dest": "/root/all_db.sql", "gid": 0, "group": "root", "md5sum": "0f0a31a6514212b0735dd62aac19e930", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 895179, "src": "/root/.ansible/tmp/ansible-tmp-1690862468.66-12380-216585534347340/source", "state": "file", "uid": 0}192.168.0.17 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "b23510fc2d56012aa8a06c08517c38fcd0b85b7b", "dest": "/root/all_db.sql", "gid": 0, "group": "root", "md5sum": "0f0a31a6514212b0735dd62aac19e930", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 895179, "src": "/root/.ansible/tmp/ansible-tmp-1690862468.48-12378-85743988533689/source", "state": "file", "uid": 0}192.168.0.13 | CHANGED => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": true, "checksum": "b23510fc2d56012aa8a06c08517c38fcd0b85b7b", "dest": "/root/all_db.sql", "gid": 0, "group": "root", "md5sum": "0f0a31a6514212b0735dd62aac19e930", "mode": "0644", "owner": "root", "secontext": "system_u:object_r:admin_home_t:s0", "size": 895179, "src": "/root/.ansible/tmp/ansible-tmp-1690862468.71-12382-262895436169053/source", "state": "file", "uid": 0}

六.在slave服务器上使用mysql导入master服务器的基础数据。

1.slave服务器上直接导入

[root@slave-1 ~]# mysql -uroot -p"Sanchuang123#" <all_db.sqlmysql: [Warning] Using a password on the command line interface can be insecure.[root@slave-1 ~]# mysql -uroot -p"Sanchuang123#"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 5Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@(none) 12:02 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || mysql || performance_schema || sys|+--------------------+5 rows in set (0.00 sec)root@(none) 12:02 mysql>use TENNIS;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@TENNIS 12:02 mysql>select * from TEAMS;+--------+----------+----------+| TEAMNO | PLAYERNO | DIVISION |+--------+----------+----------+|1 | 6 | first ||2 | 27 | second |+--------+----------+----------+2 rows in set (0.00 sec)root@TENNIS 12:03 mysql>exitBye[root@slave-1 ~]#

2.使用ansible（shell模块）导入基础数据

[root@ansible ansible]# ansible slave -m shell -a 'mysql -uroot -p"Sanchuang123#" </root/all_db.sql'192.168.0.13 | CHANGED | rc=0 >>mysql: [Warning] Using a password on the command line interface can be insecure.192.168.0.17 | CHANGED | rc=0 >>mysql: [Warning] Using a password on the command line interface can be insecure.192.168.0.12 | CHANGED | rc=0 >>mysql: [Warning] Using a password on the command line interface can be insecure.[root@ansible ansible]# 验证slave服务器上是否有数据[root@slave-2 ~]# mysql -uroot -p"Sanchuang123#"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 5Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@(none) 12:04 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || mysql || performance_schema || sys|+--------------------+5 rows in set (0.01 sec)root@(none) 12:05 mysql>use TENNIS;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@TENNIS 12:05 mysql>select * from TEAMS;+--------+----------+----------+| TEAMNO | PLAYERNO | DIVISION |+--------+----------+----------+|1 | 6 | first ||2 | 27 | second |+--------+----------+----------+2 rows in set (0.00 sec)root@TENNIS 12:05 mysql>exitBye[root@slave-2 ~]#

七.MySQL集群开启GTID功能，启动主从复制服务，配置好延迟备份服务器，从slave-1上拿二进制日志。

1.安装插件

master服务器上操作

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

salve服务器上操作

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

使用ansible的playbook来下载插件和检查插件是否安装

[root@ansible ~]# vim slave.yaml [root@ansible ~]# cat slave.yaml - hosts: slaveremote_user: roottasks:- name: install pluginshell: mysql -uroot -p'Sanchuang123#' -e "INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';"- name: check pluginshell: mysql -uroot -p'Sanchuang123#' -e "SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';"[root@ansible ~]# ansible-playbook --syntax-check slave.yaml playbook: slave.yaml[root@ansible ~]# ansible-playbook slave.yaml PLAY [slave] ****************************************************************************************************************************************************************************************************TASK [Gathering Facts] ******************************************************************************************************************************************************************************************ok: [192.168.0.13]ok: [192.168.0.17]ok: [192.168.0.12]TASK [install plugin] *******************************************************************************************************************************************************************************************changed: [192.168.0.17]changed: [192.168.0.12]changed: [192.168.0.13]TASK [check plugin] *********************************************************************************************************************************************************************************************changed: [192.168.0.12]changed: [192.168.0.17]changed: [192.168.0.13]PLAY RECAP ******************************************************************************************************************************************************************************************************192.168.0.12: ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 192.168.0.13: ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 192.168.0.17: ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 [root@ansible ~]#

2.修改master和salve配置文件

在master上操作

[root@master ~]# cat /etc/f[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8#开启二进制日志log_binserver_id = 1#开启半同步功能rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000 # 1 second#开启GTID功能gtid-mode=ONenforce-gtid-consistency=ON[mysql]auto-rehashprompt=\u@\d \R:\m mysql># 重启服务[root@master ~]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@master ~]# ps aux|grep mysqldroot 2755 0.0 0.0 11824 1608 pts/0 S 12:16 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pidmysql2983 1.4 11.0 1554840 206728 pts/0 Sl 12:16 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master.err --open-files-limit=8192 --pid-file=/data/mysql/master.pid --socket=/data/mysql/mysql.sock --port=3306root 3014 0.0 0.0 112824 988 pts/0 S+ 12:16 0:00 grep --color=auto mysqld# 在master上新建一个授权用户，给slave来复制二进制日志grant replication slave on *.* to 'slave'@'192.168.0.%' identified by 'Sanchuang123#';# 刷新权限root@(none) 12:21 mysql>flush privileges;Query OK, 0 rows affected (0.01 sec)#清空二进制日志root@(none) 12:17 mysql>reset master;Query OK, 0 rows affected (0.00 sec)root@(none) 12:20 mysql>show master status;+-------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |154 | | | |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

3.在slave-1上操作（即是slave也是master）

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';[root@slave-1 ~]# cat /etc/f[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8#开启二进制日志log_binserver_id = 2#开启半同步功能rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000 # 1 secondrpl_semi_sync_slave_enabled=1log_slave_updates=ON#开启GTID功能gtid-mode=ONenforce-gtid-consistency=ON[mysql]auto-rehashprompt=\u@\d \R:\m mysql>[root@slave-1 ~]# # 重启服务service mysqld restart# 在salve-1上新建一个授权用户，给delay-backup来复制二进制日志grant replication slave on *.* to 'slave'@'192.168.0.%' identified by 'Sanchuang123#';#清空二进制日志reset slave all;#填写master信息change master to master_host='192.168.0.11', master_user='slave',master_password='Sanchuang123#',master_port=3306,master_auto_position=1;# 开启slaveroot@(none) 12:23 mysql>start slave;Query OK, 0 rows affected (0.00 sec)root@(none) 12:23 mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.11Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_Log_Pos: 154Relay_Log_File: slave-1-relay-bin.000002Relay_Log_Pos: 321Relay_Master_Log_File: master-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 530Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c97dec66-301d-11ee-b9a8-000c299024d3Master_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

4.在slave-2上操作

[root@slave-2 ~]# cat /etc/f[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8#开启二进制日志log_binserver_id = 3#开启半同步功能rpl_semi_sync_slave_enabled=1log_slave_updates=ON#开启GTID功能gtid-mode=ONenforce-gtid-consistency=ON[mysql]auto-rehashprompt=\u@\d \R:\m mysql>[root@slave-2 ~]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS![root@slave-2 ~]# mysql -uroot -p"Sanchuang123#"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 2Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@(none) 12:46 mysql>change master to master_host='192.168.0.11', -> master_user='slave',-> master_password='Sanchuang123#',-> master_port=3306,-> master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.01 sec)root@(none) 12:47 mysql>start slave;Query OK, 0 rows affected (0.00 sec)root@(none) 12:47 mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.11Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_Log_Pos: 600Relay_Log_File: slave-2-relay-bin.000002Relay_Log_Pos: 569Relay_Master_Log_File: master-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 600Relay_Log_Space: 778Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c97dec66-301d-11ee-b9a8-000c299024d3Master_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: c97dec66-301d-11ee-b9a8-000c299024d3:2Executed_Gtid_Set: c97dec66-301d-11ee-b9a8-000c299024d3:1-2Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

5.配置好延迟备份服务器，从slave-1上拿二进制日志。

[root@delay-backup ~]# cat /etc/f[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8server_id = 4#开启半同步功能rpl_semi_sync_slave_enabled=1log_slave_updates=ON#开启GTID功能gtid-mode=ONenforce-gtid-consistency=ON[mysql]auto-rehashprompt=\u@\d \R:\m mysql>[root@delay-backup ~]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! change master to master_host='192.168.0.17', master_user='slave',master_password='Sanchuang123#',master_port=3306,master_auto_position=1;change master to master_delay = 600;root@(none) 14:56 mysql>change master to master_host='192.168.0.17', -> master_user='slave',-> master_password='Sanchuang123#',-> master_port=3306,-> master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.02 sec)root@(none) 14:57 mysql>change master to master_delay = 600;Query OK, 0 rows affected (0.01 sec)root@(none) 14:57 mysql>start slave;Query OK, 0 rows affected (0.00 sec)root@(none) 14:57 mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.17Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: slave-1-bin.000002Read_Master_Log_Pos: 488Relay_Log_File: delay-backup-relay-bin.000003Relay_Log_Pos: 411Relay_Master_Log_File: slave-1-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 194Relay_Log_Space: 1440Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 125Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2Master_UUID: c2055faa-301d-11ee-b63a-000c29a0461cMaster_Info_File: /data/mysql/master.infoSQL_Delay: 600SQL_Remaining_Delay: 475Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed eventMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: c2055faa-301d-11ee-b63a-000c29a0461c:1,c97dec66-301d-11ee-b9a8-000c299024d3:1-2Executed_Gtid_Set: c97dec66-301d-11ee-b9a8-000c299024d3:1-2Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

注意：如果 Slave_IO_Running、Slave_SQL_Running的状态是 NO

需要检查

1.检查配置文件是否打错
2.修改配置文件后是否重启MySQL服务
3.可能slave上的GTID编号比master上的还大

如果是第三种情况，清空二进制日志。

# master 上操作# 清空二进制日志reset master;# slave上操作# 停止slavestop slave;# 清空二进制日志reset slave all;# 开启slavestart slave;

6.测试

# master上操作root@(none) 14:59 mysql>create database han;Query OK, 1 row affected (0.00 sec)root@(none) 14:59 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || han|| mysql || performance_schema || sys|+--------------------+6 rows in set (0.00 sec)# slave-1和slave-2上查看root@(none) 14:59 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || han|| mysql || performance_schema || sys|+--------------------+6 rows in set (0.00 sec)root@(none) 14:59 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || han|| mysql || performance_schema || sys|+--------------------+6 rows in set (0.01 sec)# delay-backup上查看root@(none) 15:00 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || mysql || performance_schema || sys|+--------------------+5 rows in set (0.00 sec)

八.部署mysql的failover插件（MHA），实现自动的故障切换，如果master宕机，能自动提升其中一台slave为新的master，其他slave到新的master上获得二进制日志。

0.前提条件（准备好rpm包）

mha4mysql-node-0.56-0.el6.noarch.rpmmha4mysql-manager-0.56-0.el6.noarch.rpm

MySQL集群都安装mha4mysql-node

1.下载该软件需要的依赖包

yum install epel-release -yyum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

2.通过ansible使用rpm安装rpm包

ansible db -m shell -a 'rpm -ivh /root/mha4mysql-node-0.56-0.el6.noarch.rpm'

3.管理节点安装mha4mysql-manager，延迟服务器同时是管理节点

[root@delay-backup ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 准备中...################################# [100%]正在升级/安装...1:mha4mysql-node-0.56-0.el6 ################################# [100%]# 查看Node工具包[root@delay-backup ~]# ll /usr/bin/{app*,filter*,purge*,save*}-rwxr-xr-x. 1 root root 16367 4月 1 /usr/bin/apply_diff_relay_logs-rwxr-xr-x. 1 root root 4807 4月 1 /usr/bin/filter_mysqlbinlog-rwxr-xr-x. 1 root root 8261 4月 1 /usr/bin/purge_relay_logs-rwxr-xr-x. 1 root root 7525 4月 1 /usr/bin/save_binary_logs[root@delay-backup ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 准备中...################################# [100%]正在升级/安装...1:mha4mysql-manager-0.56-0.el6################################# [100%]

4.建立免密通道

管理节点delay-backup要能免密登录主从复制的所有服务器，而不需要反向免密登录。主从复制的所有服务器之间要能免密登录。

ssh-keygen -t rsassh-copy-id -i id_rsa.pub root@192.168.0.%

5.创建监控用户（一台master和2台slave都要创建，delay-backup服务器不用）

grant all privileges on *.* to 'monitor'@'192.168.0.%' identified by '123456';# 刷新权限flush privileges;

配置MHA

1.创建工作目录

mkdir /etc/masterhamkdir -p /var/log/masterha/app1

2.在/usr/local/bin目录下创建脚本master_ip_failover（故障切换脚本，切换master的VIP地址）

cd /usr/local/bin[root@delay-backup bin]# vimmaster_ip_failover#!/usr/bin/env perluse strict;use warnings FATAL =>'all';use Getopt::Long;my ($command,$ssh_user, $orig_master_host, $orig_master_ip,$orig_master_port, $new_master_host, $new_master_ip, $new_master_port);my $vip = '192.168.0.200/24'; #这里需要改，漂移的VIPmy $key = "1";my $ssh_start_vip = "/sbin/ip a add $vip dev ens33:$key";#这两行需要修改，一行是临时添加IP，一行是临时删除IPmy $ssh_stop_vip = "/sbin/ip a del $vip dev ens33:$key";#注意能够使用的命令是ip还是ifconfig，以及网卡名称my $exit_code = 0;GetOptions('command=s'=> \$command,'ssh_user=s' => \$ssh_user,'orig_master_host=s' => \$orig_master_host,'orig_master_ip=s' => \$orig_master_ip,'orig_master_port=i' => \$orig_master_port,'new_master_host=s' => \$new_master_host,'new_master_ip=s' => \$new_master_ip,'new_master_port=i' => \$new_master_port,);exit &main();sub main {#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";if ( $command eq "stop" || $command eq "stopssh" ) {my $exit_code = 1;eval {print "\n\n\n***************************************************************\n";print "Disabling the VIP - $vip on old master: $orig_master_host\n";print "***************************************************************\n\n\n\n";&stop_vip();$exit_code = 0;};if ($@) {warn "Got Error: $@\n";exit $exit_code;}exit $exit_code;}elsif ( $command eq "start" ) {my $exit_code = 10;eval {print "\n\n\n***************************************************************\n";print "Enabling the VIP - $vip on new master: $new_master_host \n";print "***************************************************************\n\n\n\n";&start_vip();$exit_code = 0;};if ($@) {warn $@;exit $exit_code;}exit $exit_code;}elsif ( $command eq "status" ) {print "Checking the Status of the script.. OK \n";`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;exit 0;}else {&usage();exit 1;}}# A simple system call that enable the VIP on the new mastersub start_vip() {`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;}# A simple system call that disable the VIP on the old_mastersub stop_vip() {`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;}sub usage {print "Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=port –new_master_host=host –new_master_ip=ip –new_master_port=port\n";}

3.给脚本可执行权限

chmod 777 /usr/local/bin/master_ip_failover

4.在/etc/masterha下创建配置文件f

[root@delay-backup bin]# vim /etc/masterha/f[server default]# manager工作目录manager_workdir=/var/log/masterha/app1# manager日志目录manager_log=/var/log/masterha/app1/manager.log# MySQL的数据目录master_binlog_dir=/data/mysql/# 脚本（需要自己编写）master_ip_failover_script=/usr/local/bin/master_ip_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_change# MySQL管理帐号和密码user=monitorpassword=123456# 监控间隔(秒)ping_interval=1remote_workdir=/tmp# 复制帐号和密码repl_user=xiaohrepl_password=Sanchuang123#report_script=/usr/local/send_report#故障发生后关闭主机的脚本,不是必须的,但是你要设置为空shutdown_script=""ssh_user=root#master节点[server1]hostname=192.168.0.11port=3306#salve节点[server2]hostname=192.168.0.17port=3306#设置为候选master，发生主从切换将会优先将此从库提升为主库，即使这个主库不是集群中事件最新的slave#候选在切换的过程中一定是新的master,一定程度上也是可以加快切换的参数candidate_master=1check_repl_delay=0[server3]hostname=192.168.0.12port=3306

5.检查管理节点到所有Node节点的ssh连接状态

[root@delay-backup .ssh]# masterha_check_ssh --conf=/etc/masterha/fMon Jul 31 21:35:26 - [warning] Global configuration file /etc/f not found. Skipping.Mon Jul 31 21:35:26 - [info] Reading application default configuration from /etc/masterha/f..Mon Jul 31 21:35:26 - [info] Reading server configuration from /etc/masterha/f..Mon Jul 31 21:35:26 - [info] Starting SSH connection tests..Mon Jul 31 21:35:28 - [debug] Mon Jul 31 21:35:26 - [debug] Connecting via SSH from root@192.168.2.221(192.168.2.221:22) to root@192.168.2.222(192.168.2.222:22)..Mon Jul 31 21:35:27 - [debug] ok.Mon Jul 31 21:35:27 - [debug] Connecting via SSH from root@192.168.2.221(192.168.2.221:22) to root@192.168.2.129(192.168.2.129:22)..Mon Jul 31 21:35:27 - [debug] ok.Mon Jul 31 21:35:29 - [debug] Mon Jul 31 21:35:27 - [debug] Connecting via SSH from root@192.168.2.222(192.168.2.222:22) to root@192.168.2.221(192.168.2.221:22)..Mon Jul 31 21:35:27 - [debug] ok.Mon Jul 31 21:35:27 - [debug] Connecting via SSH from root@192.168.2.222(192.168.2.222:22) to root@192.168.2.129(192.168.2.129:22)..Mon Jul 31 21:35:28 - [debug] ok.Mon Jul 31 21:35:29 - [debug] Mon Jul 31 21:35:27 - [debug] Connecting via SSH from root@192.168.2.129(192.168.2.129:22) to root@192.168.2.221(192.168.2.221:22)..Mon Jul 31 21:35:28 - [debug] ok.Mon Jul 31 21:35:28 - [debug] Connecting via SSH from root@192.168.2.129(192.168.2.129:22) to root@192.168.2.222(192.168.2.222:22)..Mon Jul 31 21:35:28 - [debug] ok.Mon Jul 31 21:35:29 - [info] All SSH connection tests passed successfully.

6.检查复制环境。

注意：报Binlog setting check failed!，可能是master服务器保存二进制日志文件地址填写错误。

[root@delay-backup .ssh]# masterha_check_repl --conf=/etc/masterha/fMon Jul 31 21:35:59 - [warning] Global configuration file /etc/f not found. Skipping.Mon Jul 31 21:35:59 - [info] Reading application default configuration from /etc/masterha/f..Mon Jul 31 21:35:59 - [info] Reading server configuration from /etc/masterha/f..Mon Jul 31 21:35:59 - [info] MHA::MasterMonitor version 0.56.Mon Jul 31 21:36:01 - [info] GTID failover mode = 1Mon Jul 31 21:36:01 - [info] Dead Servers:Mon Jul 31 21:36:01 - [info] Alive Servers:Mon Jul 31 21:36:01 - [info] 192.168.2.221(192.168.2.221:3306)Mon Jul 31 21:36:01 - [info] 192.168.2.222(192.168.2.222:3306)Mon Jul 31 21:36:01 - [info] 192.168.2.129(192.168.2.129:3306)Mon Jul 31 21:36:01 - [info] Alive Slaves:Mon Jul 31 21:36:01 - [info] 192.168.2.222(192.168.2.222:3306) Version=5.7.41-log (oldest major version between slaves) log-bin:enabledMon Jul 31 21:36:01 - [info]GTID ONMon Jul 31 21:36:01 - [info]Replicating from 192.168.2.221(192.168.2.221:3306)Mon Jul 31 21:36:01 - [info]Primary candidate for the new Master (candidate_master is set)Mon Jul 31 21:36:01 - [info] 192.168.2.129(192.168.2.129:3306) Version=5.7.41-log (oldest major version between slaves) log-bin:enabledMon Jul 31 21:36:01 - [info]GTID ONMon Jul 31 21:36:01 - [info]Replicating from 192.168.2.221(192.168.2.221:3306)Mon Jul 31 21:36:01 - [info] Current Alive Master: 192.168.2.221(192.168.2.221:3306)Mon Jul 31 21:36:01 - [info] Checking slave configurations..Mon Jul 31 21:36:01 - [info] read_only=1 is not set on slave 192.168.2.222(192.168.2.222:3306).Mon Jul 31 21:36:01 - [info] read_only=1 is not set on slave 192.168.2.129(192.168.2.129:3306).Mon Jul 31 21:36:01 - [info] Checking replication filtering settings..Mon Jul 31 21:36:01 - [info] binlog_do_db= , binlog_ignore_db= Mon Jul 31 21:36:01 - [info] Replication filtering check ok.Mon Jul 31 21:36:01 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln393] 192.168.2.129(192.168.2.129:3306): User xiaoh does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.Mon Jul 31 21:36:01 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 1403.Mon Jul 31 21:36:01 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.Mon Jul 31 21:36:01 - [info] Got exit code 1 (Not master dead).MySQL Replication Health is NOT OK!

7.检查管理节点的状态

[root@delay-backup .ssh]# masterha_check_status --conf=/etc/masterha/fapp1 is stopped(2:NOT_RUNNING).

8.开启管理节点监控

[root@delay-backup .ssh]# nohup masterha_manager --conf=/etc/masterha/f --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &[2] 4243[root@delay-backup .ssh]# masterha_check_status --conf=/etc/masterha/fapp1 is stopped(2:NOT_RUNNING).# 关闭管理节点监控（现在不操作，实验结束后可执行）[root@delay-backup .ssh]# masterha_stop --conf=/etc/masterha/f MHA Manager is not running on app1(2:NOT_RUNNING).[2]- 退出 1nohup masterha_manager --conf=/etc/masterha/f --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1

9.关闭master服务器的mysqld

[root@master .ssh]# systemctl stop mysqld# 查看进程[root@master .ssh]# ps aux|grep mysqldroot18525 0.0 0.0 112824 988 pts/0 S+ 21:39 0:00 grep --color=auto mysqld

10.管理节点查看配置文件，会发现[server1]模块和user=root被删除了。

[root@delay-backup .ssh]# cat /etc/masterha/f[server default]manager_workdir=/var/log/masterha/app1manager_log=/var/log/masterha/app1/manager.logmaster_binlog_dir=/data/mysql/master_ip_failover_script=/usr/local/bin/master_ip_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_changepassword=123456ping_interval=1remote_workdir=/tmprepl_user=xiaohrepl_password=Sanchuang123#report_script=/usr/local/send_reportshutdown_script=""ssh_user=root[server2]hostname=192.168.0.17port=3306candidate_master=1check_repl_delay=0[server3]hostname=192.168.0.12port=3306root@(none) 21:41 mysql>show master status\G;*************************** 1. row ***************************File: slave-bin.000001Position: 1353Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 11039023-2ede-11ee-a775-000c29e03d59:1-3,985f2117-2ede-11ee-a327-000c2962e8d0:1-31 row in set (0.00 sec)ERROR: No query specified

九.在master上创建一个计划任务每天2:30进行数据库的备份，编写备份脚本，备份文件包含当天的日期，使用rsync+sersync远程同步到ansible服务器。

编写脚本

# 编写脚本[root@master ~]# cat backup_db.sh #!/bin/bash#新建一个目录mkdir -p /backup/#导出数据库的所有数据mysqldump -uroot -p'Sanchuang123#' --all-databases > /backup/$(date +%Y%m%d%H%M%S)-all_db.sql# 创建计划任务[root@master ~]# crontab -eno crontab for root - using an empty onecrontab: installing new crontab[root@master ~]# crontab -l30 2 * * * bash /backup/backup_db.sh[root@master ~]# mkdir backup[root@master ~]# mv backup_db.sh /backup[root@master ~]# cd /backup[root@master backup]# lsbackup_db.sh

部署rsync+sersync

1.ansible服务器操作

# 1、关闭 selinux setenforce 0vim /etc/selinux/configSELINUX=disabled # 修改# 2、关闭防火墙systemctl stop firewalld# 3、安装rsync服务端软件[root@ansible ~]# yum install rsync xinetd -y# 4.设置开启启动[root@ansible ~]# vim /etc/rc.d/rc.local/usr/bin/rsync --daemon --config=/etc/rsyncd.conf# 5.给予可执行权限，否则重启不执行[root@ansible ~]# chmod +x /etc/rc.d/rc.local [root@ansible ~]# ll /etc/rc.d/rc.local -rwxr-xr-x. 1 root root 523 8月 1 15:08 /etc/rc.d/rc.local# 6.创建rsyncd.conf配置文件[root@ansible ~]# vim /etc/rsyncd.conf uid = rootgid = rootuse chroot = yesmax connections = 0log file = /var/log/rsyncd.logpid file = /var/run/rsyncd.pid lock file = /var/run/rsync.lock secrets file = /etc/rsync.pass motd file = /etc/rsyncd.Motd[back_data] #配置项名称（自定义）path = /backup#备份文件存储地址comment = A directory in which data is storedignore errors = yesread only = nohosts allow = 192.168.0.11 #允许的ip地址(数据源服务器地址)[root@ansible ~]# mkdir -p /backup# 7.创建用户认证文件配置文件，添加以下内容，添加允许传输用户和密码[root@ansible ~]# vim /etc/rsync.pass[root@ansible ~]# cat /etc/rsync.pass sc:sc123456 # 格式，用户名:密码，可以设置多个，每行一个用户名:密码# 8.设置文件权限chmod 600 /etc/rsyncd.conf #设置文件所有者读取、写入权限chmod 600 /etc/rsync.pass #设置文件所有者读取、写入权限[root@ansible ~]# ll /etc/rsync.pass-rw-------. 1 root root 12 8月 1 15:11 /etc/rsync.pass[root@ansible ~]# ll /etc/rsyncd.conf-rw-------. 1 root root 928 8月 1 15:10 /etc/rsyncd.conf# 9.启动rsync、xinetd[root@ansible ~]# /usr/bin/rsync --daemon --config=/etc/rsyncd.conf[root@ansible ~]# ps aux|grep rsyncroot12800 0.0 0.0 114852 572 ? Ss 15:13 0:00 /usr/bin/rsync --daemon --config=/etc/rsyncd.confroot12802 0.0 0.0 112824 980 pts/0 S+ 15:13 0:00 grep --color=auto rsync[root@ansible ~]# systemctl start xinetd[root@ansible ~]# ps aux |grep xinetdroot17488 0.0 0.0 25044 588 ? Ss 00:35 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pidroot17491 0.0 0.0 112824 984 pts/0 S+ 00:36 0:00 grep --color=auto xinetd# 10.查看rsync监听的端口号[root@ansible ~]# netstat -anplut|grep rsync-bash: netstat: 未找到命令[root@ansible ~]# yum install net-tools -y[root@ansible ~]# netstat -anplut|grep rsynctcp 00 0.0.0.0:873 0.0.0.0:*LISTEN12800/rsync tcp6 00 :::873 :::*LISTEN12800/rsync[root@ansible ~]# ss -anpult|grep rsynctcp LISTEN05 *:873 *:* users:(("rsync",pid=12800,fd=4))tcp LISTEN05[::]:873[::]:* users:(("rsync",pid=12800,fd=5))

2.master服务器操作

# 1.检查firewalld和selinux是否关闭systemctl status firewalld getenforce# 2.安装rsync软件[root@master ~]# yum install rsync xinetd -y# 3.设置开启启动[root@master ~]# vim /etc/rc.d/rc.local/usr/bin/rsync --daemon --config=/etc/rsyncd.conf# 4.给予可执行权限，否则重启不执行[root@master ~]# chmod +x /etc/rc.d/rc.local [root@master backup]# ll /etc/rc.d/rc.local -rwxr-xr-x. 1 root root 541 8月 1 15:15 /etc/rc.d/rc.local# 5.创建rsyncd.conf配置文件[root@master ~]# vim /etc/rsyncd.conflog file = /var/log/rsyncd.logpid file = /var/run/rsyncd.pidlock file = /var/run/rsync.lockmotd file = /etc/rsyncd.Motd[Sync]comment = Syncuid = rootgid = rootport= 873# 6.启动xinetd（CentOS中是以xinetd来管理rsync服务的）[root@master ~]# systemctl start xinetd [root@master ~]# ps aux|grep xinetdroot16076 0.0 0.0 25044 584 ? Ss 15:15 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pidroot16089 0.0 0.0 112824 988 pts/0 S+ 15:17 0:00 grep --color=auto xinetd# 7.创建认证密码文件[root@master ~]# vim /etc/passwd.txt #编辑文件，添加以下内容，该密码应与目标服务器中的/etc/rsync.pass中的密码一致 [root@master ~]# cat /etc/passwd.txt sc123456[root@master ~]# chmod 600 /etc/passwd.txt #设置文件权限，只设置文件所有者具有读取、写入权限即可 [root@master ~]# ll /etc/passwd.txt-rw-------. 1 root root 9 8月 1 15:18 /etc/passwd.txt# 8.测试数据同步master服务器到ansible服务器之间的数据同步[root@master backup]# rsync -avH --port=873 --progress --delete /backup root@192.168.0.14::back_data --password-file=/etc/passwd.txtsending incremental file listbackup/backup/backup_db.sh204 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=1/4)sent 1035 bytes received 77 bytes 8,224.00 bytes/sectotal size is 1762 speedup is 0.41

3.实现自动同步

# inotify已经默认在内核里安装了，不需要安装# 1、修改inotify默认参数[root@master ~]#sysctl -w fs.inotify.max_queued_events="99999999"fs.inotify.max_queued_events = 99999999[root@master ~]# sysctl -w fs.inotify.max_user_watches="99999999"fs.inotify.max_user_watches = 99999999[root@master ~]# sysctl -w fs.inotify.max_user_instances="65535"fs.inotify.max_user_instances = 65535[root@master ~]# vim /etc/sysctl.conf fs.inotify.max_queued_events=99999999fs.inotify.max_user_watches=99999999fs.inotify.max_user_instances=65535# 2、安装sersync[root@master ~]# wget /downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz#解压[root@master ~]# tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz #移动目录到/usr/local/sersync[root@master ~]# mv GNU-Linux-x86/ /usr/local/sersync#进入sersync安装目录[root@master ~]# cd /usr/local/sersync/[root@master sersync]# lsconfxml.xml sersync2# 备份配置文件[root@master sersync]# cp confxml.xml confxml.xml.bak[root@masterl sersync]# cp confxml.xml data_configxml.xml[root@master sersync]# lsconfxml.xml confxml.xml.bak data_configxml.xml sersync2# 3、修改配置文件data_configxml.xml [root@master sersync]# vim data_configxml.xml# 第24行后的配置<localpath watch="/backup"> # 本地数据源路径 <remote ip="192.168.0.14" name="back_data"/> # 备份服务器地址信息</localpath><rsync><commonParams params="-artuz"/><auth start="true" users="root" passwordfile="/etc/passwd.txt"/> # 启用身份验证，密码文件路径"/etc/passwd.txt"<userDefinedPort start="false" port="874"/><timeout start="false" time="100"/><ssh start="false"/></rsync># 4、修改环境变量[root@master sersync]# PATH=/usr/local/sersync/:$PATH[root@master sersync]# echo 'PATH=/usr/local/sersync/:$PATH' >>/root/.bashrc # 5.启动服务[root@master sersync]# sersync2 -d -r -o /usr/local/sersync/data_configxml.xml# 6、设置sersync开机自动执行[root@master backup]# vim /etc/rc.local /usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/data_configxml.xml

4.查看rsync的帮助文档

[root@master ~]# rsync --helprsync version 3.1.2 protocol version 31Copyright (C) 1996- by Andrew Tridgell, Wayne Davison, and others.Web site: /Capabilities:64-bit files, 64-bit inums, 64-bit timestamps, 64-bit long ints,socketpairs, hardlinks, symlinks, IPv6, batchfiles, inplace,append, ACLs, xattrs, iconv, symtimes, preallocrsync comes with ABSOLUTELY NO WARRANTY. This is free software, and youare welcome to redistribute it under certain conditions. See the GNUGeneral Public Licence for details.rsync is a file transfer program capable of efficient remote updatevia a fast differencing algorithm.Usage: rsync [OPTION]... SRC [SRC]... DESTor rsync [OPTION]... SRC [SRC]... [USER@]HOST:DESTor rsync [OPTION]... SRC [SRC]... [USER@]HOST::DESTor rsync [OPTION]... SRC [SRC]... rsync://[USER@]HOST[:PORT]/DESTor rsync [OPTION]... [USER@]HOST:SRC [DEST]or rsync [OPTION]... [USER@]HOST::SRC [DEST]or rsync [OPTION]... rsync://[USER@]HOST[:PORT]/SRC [DEST]The ':' usages connect via remote shell, while '::' & 'rsync://' usages connectto an rsync daemon, and require SRC or DEST to start with a module name.Options-v, --verboseincrease verbosity--info=FLAGS fine-grained informational verbosity--debug=FLAGS fine-grained debug verbosity--msgs2stderr special output handling for debugging-q, --quiet suppress non-error messages--no-motdsuppress daemon-mode MOTD (see manpage caveat)-c, --checksum skip based on checksum, not mod-time & size-a, --archivearchive mode; equals -rlptgoD (no -H,-A,-X)--no-OPTION turn off an implied OPTION (e.g. --no-D)-r, --recursive recurse into directories-R, --relative use relative path names--no-implied-dirs don't send implied dirs with --relative-b, --backupmake backups (see --suffix & --backup-dir)--backup-dir=DIR make backups into hierarchy based in DIR--suffix=SUFFIX set backup suffix (default ~ w/o --backup-dir)-u, --updateskip files that are newer on the receiver--inplaceupdate destination files in-place (SEE MAN PAGE)--appendappend data onto shorter files--append-verify like --append, but with old data in file checksum-d, --dirs transfer directories without recursing-l, --links copy symlinks as symlinks-L, --copy-links transform symlink into referent file/dir--copy-unsafe-linksonly "unsafe" symlinks are transformed--safe-links ignore symlinks that point outside the source tree--munge-links munge symlinks to make them safer (but unusable)-k, --copy-dirlinks transform symlink to a dir into referent dir-K, --keep-dirlinks treat symlinked dir on receiver as dir-H, --hard-links preserve hard links-p, --perms preserve permissions-E, --executability preserve the file's executability--chmod=CHMOD affect file and/or directory permissions-A, --acls preserve ACLs (implies --perms)-X, --xattrspreserve extended attributes-o, --owner preserve owner (super-user only)-g, --group preserve group--devicespreserve device files (super-user only)--copy-devicescopy device contents as regular file--specials preserve special files-Dsame as --devices --specials-t, --times preserve modification times-O, --omit-dir-times omit directories from --times-J, --omit-link-times omit symlinks from --times--super receiver attempts super-user activities--fake-super store/recover privileged attrs using xattrs-S, --sparsehandle sparse files efficiently--preallocate allocate dest files before writing them-n, --dry-runperform a trial run with no changes made-W, --whole-file copy files whole (without delta-xfer algorithm)-x, --one-file-system don't cross filesystem boundaries-B, --block-size=SIZE force a fixed checksum block-size-e, --rsh=COMMAND specify the remote shell to use--rsync-path=PROGRAM specify the rsync to run on the remote machine--existing skip creating new files on receiver--ignore-existing skip updating files that already exist on receiver--remove-source-files sender removes synchronized files (non-dirs)--del an alias for --delete-during--deletedelete extraneous files from destination dirs--delete-before receiver deletes before transfer, not during--delete-during receiver deletes during the transfer--delete-delayfind deletions during, delete after--delete-afterreceiver deletes after transfer, not during--delete-excluded also delete excluded files from destination dirs--ignore-missing-args ignore missing source args without error--delete-missing-args delete missing source args from destination--ignore-errors delete even if there are I/O errors--force force deletion of directories even if not empty--max-delete=NUM don't delete more than NUM files--max-size=SIZE don't transfer any file larger than SIZE--min-size=SIZE don't transfer any file smaller than SIZE--partialkeep partially transferred files--partial-dir=DIR put a partially transferred file into DIR--delay-updates put all updated files into place at transfer's end-m, --prune-empty-dirsprune empty directory chains from the file-list--numeric-ids don't map uid/gid values by user/group name--usermap=STRING custom username mapping--groupmap=STRING custom groupname mapping--chown=USER:GROUPsimple username/groupname mapping--timeout=SECONDS set I/O timeout in seconds--contimeout=SECONDS set daemon connection timeout in seconds-I, --ignore-timesdon't skip files that match in size and mod-time-M, --remote-option=OPTION send OPTION to the remote side only--size-only skip files that match in size--modify-window=NUMcompare mod-times with reduced accuracy-T, --temp-dir=DIRcreate temporary files in directory DIR-y, --fuzzy find similar file for basis if no dest file--compare-dest=DIRalso compare destination files relative to DIR--copy-dest=DIR ... and include copies of unchanged files--link-dest=DIR hardlink to files in DIR when unchanged-z, --compress compress file data during the transfer--compress-level=NUM explicitly set compression level--skip-compress=LIST skip compressing files with a suffix in LIST-C, --cvs-exclude auto-ignore files the same way CVS does-f, --filter=RULE add a file-filtering RULE-Fsame as --filter='dir-merge /.rsync-filter'repeated: --filter='- .rsync-filter'--exclude=PATTERN exclude files matching PATTERN--exclude-from=FILEread exclude patterns from FILE--include=PATTERN don't exclude files matching PATTERN--include-from=FILEread include patterns from FILE--files-from=FILE read list of source-file names from FILE-0, --from0 all *-from/filter files are delimited by 0s-s, --protect-argsno space-splitting; only wildcard special-chars--address=ADDRESS bind address for outgoing socket to daemon--port=PORT specify double-colon alternate port number--sockopts=OPTIONSspecify custom TCP options--blocking-io use blocking I/O for the remote shell--stats give some file-transfer stats-8, --8-bit-outputleave high-bit chars unescaped in output-h, --human-readable output numbers in a human-readable format--progress show progress during transfer-Psame as --partial --progress-i, --itemize-changes output a change-summary for all updates--out-format=FORMAToutput updates using the specified FORMAT--log-file=FILE log what we're doing to the specified FILE--log-file-format=FMT log updates using the specified FMT--password-file=FILE read daemon-access password from FILE--list-only list the files instead of copying them--bwlimit=RATElimit socket I/O bandwidth--outbuf=N|L|Bset output buffering to None, Line, or Block--write-batch=FILEwrite a batched update to FILE--only-write-batch=FILE like --write-batch but w/o updating destination--read-batch=FILE read a batched update from FILE--protocol=NUMforce an older protocol version to be used--iconv=CONVERT_SPEC request charset conversion of filenames--checksum-seed=NUMset block/file checksum seed (advanced)-4, --ipv4 prefer IPv4-6, --ipv6 prefer IPv6--versionprint version number(-h) --help show this help (-h is --help only if used alone)Use "rsync --daemon --help" to see the daemon-mode command-line options.Please see the rsync(1) and rsyncd.conf(5) man pages for full documentation.See / for updates, bug reports, and answers

十.部署两台安装了mysqlrouter中间件软件的服务器，实现读写分离和高可用功能。

1.去官方网站下载rpm包，使用xftp上传到Linux里[root@mysqlrouter-1 ~]# lsanaconda-ks.cfg mysql-router-community-8.0.33-1.el7.x86_64.rpm[root@mysqlrouter-1 ~]# scp mysql-router-community-8.0.33-1.el7.x86_64.rpm root@192.168.0.16:/root/The authenticity of host '192.168.0.16 (192.168.0.16)' can't be established.ECDSA key fingerprint is SHA256:l7LRfACELrI6mU2XvYaCz+sDBWiGkYnAecPgnxJxdvE.ECDSA key fingerprint is MD5:b6:f7:e1:c5:23:24:5c:16:1f:66:42:ba:80:a6:3c:fd.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '192.168.0.16' (ECDSA) to the list of known hosts.root@192.168.0.16's password: mysql-router-community-8.0.33-1.el7.x86_64.rpm 100% 5113KB 10.8MB/s 00:00 [root@mysqlrouter-1 ~]# [root@mysqlrouter-2 ~]# lsanaconda-ks.cfg mysql-router-community-8.0.33-1.el7.x86_64.rpm2.安装[root@mysqlrouter-1 ~]# rpm -ivh mysql-router-community-8.0.33-1.el7.x86_64.rpm 警告：mysql-router-community-8.0.33-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY准备中...################################# [100%]正在升级/安装...1:mysql-router-community-8.0.33-1.e################################# [100%][root@mysqlrouter-2 ~]# rpm -ivh mysql-router-community-8.0.33-1.el7.x86_64.rpm 警告：mysql-router-community-8.0.33-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY准备中...################################# [100%]正在升级/安装...1:mysql-router-community-8.0.33-1.e################################# [100%]3.修改配置文件[root@mysql-router-1 ~]# cd /etc/mysqlrouter/ # 进入存放配置文件的目录[root@mysql-router-1 mysqlrouter]# lsmysqlrouter.conf[root@mysqlrouter-1 mysqlrouter]# vim mysqlrouter.conf [root@mysqlrouter-1 mysqlrouter]# cat mysqlrouter.conf |grep -v "^#"[DEFAULT]logging_folder = /var/log/mysqlrouterruntime_folder = /run/mysqlrouterconfig_folder = /etc/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 192.168.0.15:7001destinations = 192.168.0.17:3306,192.168.0.12:3306,192.168.0.13:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 192.168.0.15:7002destinations = 192.168.0.11:3306mode = read-writeconnect_timeout = 1[keepalive]interval = 604.启动MySQL router服务[root@mysql-router-1 ~]# service mysqlrouter startRedirecting to /bin/systemctl start mysqlrouter.servicemysqlrouter监听了7001和7002端口[root@mysql-router-1 ~]# netstat -anplut|grep mysqltcp 00 192.168.2.106:70010.0.0.0:*LISTEN2258/mysqlrouter tcp 00 192.168.2.106:70020.0.0.0:*LISTEN2258/mysqlrouter 5.在master上创建2个账号，测试读写分离root@(none) 15:34 mysql>grant all on *.* to 'write'@'%' identified by 'Sanchuang123#';Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 15:35 mysql>grant select on *.* to 'read'@'%' identified by 'Sanchuang123#';Query OK, 0 rows affected, 1 warning (0.01 sec)6.在客户端上测试读写分离的效果，使用2个测试账号实现读功能[root@sc ~]# mysql -h 192.168.0.15 -P 7001 -u read -p'Sanchuang123#'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 5Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.read@(none) 16:45 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys|| wang|+--------------------+5 rows in set (0.02 sec)read@(none) 16:45 mysql>use wang;Database changedread@wang 16:45 mysql>show tables;Empty set (0.01 sec)read@wang 16:45 mysql>create table t1;ERROR 1142 (42000): CREATE command denied to user 'read'@'192.168.2.223' for table 't1'read@wang 16:45 mysql>实现写功能[root@delay-backup ~]# mysql -h 192.168.0.15 -P 7002 -uwrite -p'Sanchuang123#'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 7Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.write@(none) 16:46 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || mysql || performance_schema || sys|| wang|+--------------------+6 rows in set (0.01 sec)write@(none) 16:46 mysql>use wang;Database changedwrite@wang 16:46 mysql>create table t1(id int,name varchar(10));Query OK, 0 rows affected (0.03 sec)write@wang 16:46 mysql>show tables;+----------------+| Tables_in_wang |+----------------+| t1 |+----------------+1 row in set (0.00 sec)write@wang 16:46 mysql>insert into t1 values(1,'hello'),(2,'world');Query OK, 2 rows affected (0.06 sec)Records: 2 Duplicates: 0 Warnings: 0write@wang 16:47 mysql>desc t1;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11)| YES || NULL | || name | varchar(10) | YES || NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.02 sec)write@wang 16:47 mysql>select * from t1;+------+-------+| id | name |+------+-------+| 1 | hello || 2 | world |+------+-------+2 rows in set (0.00 sec)实现高可用功能[root@mysqlrouter-1 mysqlrouter]# vim mysqlrouter.conf [root@mysqlrouter-1 mysqlrouter]# cat mysqlrouter.conf |grep -v "^#"[DEFAULT]logging_folder = /var/log/mysqlrouterruntime_folder = /run/mysqlrouterconfig_folder = /etc/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 0.0.0.0:7001destinations = 192.168.0.17:3306,192.168.0.12:3306,192.168.0.13:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 0.0.0.0:7002destinations = 192.168.0.11:3306mode = read-writeconnect_timeout = 1[keepalive]interval = 60[root@mysqlrouter-1 ~]# service mysqlrouter restartRedirecting to /bin/systemctl restart mysqlrouter.service[root@mysqlrouter-1 ~]# ss -anplut|grep mysqlroutertcp LISTEN0128 *:7001 *:* users:(("mysqlrouter",pid=11961,fd=11))tcp LISTEN0128 *:7002 *:* users:(("mysqlrouter",pid=11961,fd=10))[root@mysqlrouter-2 mysqlrouter]# cat mysqlrouter.conf |grep -v "^#"[DEFAULT]logging_folder = /var/log/mysqlrouterruntime_folder = /run/mysqlrouterconfig_folder = /etc/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 0.0.0.0:7001destinations = 192.168.0.17:3306,192.168.0.12:3306,192.168.0.13:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 0.0.0.0:7002destinations = 192.168.0.11:3306mode = read-writeconnect_timeout = 1[keepalive]interval = 60[root@mysqlrouter-2 mysqlrouter]# service mysqlrouter restartRedirecting to /bin/systemctl restart mysqlrouter.service[root@mysqlrouter-2 mysqlrouter]# ss -anplut|grep mysqlroutertcp LISTEN0128 *:7001 *:* users:(("mysqlrouter",pid=2238,fd=14))tcp LISTEN0128 *:7002 *:* users:(("mysqlrouter",pid=2238,fd=15))测试高可用[root@test-client ~]# mysql -h 192.168.0.15 -P 7002 -uwrite -p'Sanchuang123#'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 31Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.write@(none) 16:54 mysql>exitBye[root@test-client ~]# mysql -h 192.168.0.15 -P 7001 -uread -p'Sanchuang123#'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 5Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.read@(none) 16:54 mysql>exitBye

十一.在两台mysqlrouter服务器上安装keepalived软件，配置2个vrrp实例，互为主备，来实现双vip的高可用功能。

# 1.安装keepalivedyum install keepalived -y# 2.修改配置文件[root@mysqlrouter-1 ~]# cd /etc/keepalived/[root@mysqlrouter-1 keepalived]# lskeepalived.conf[root@mysqlrouter-1 keepalived]# vim keepalived.conf [root@mysqlrouter-1 keepalived]# cat keepalived.conf ! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0}vrrp_instance VI_1 {state MASTERinterface ens33virtual_router_id 80priority 200advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.0.185}}vrrp_instance VI_2 {state backupinterface ens33virtual_router_id 100priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.0.186}}[root@mysqlrouter-1 keepalived]# service keepalived startRedirecting to /bin/systemctl start keepalived.service[root@mysqlrouter-1 keepalived]# ip a1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:3c:cd:c3 brd ff:ff:ff:ff:ff:ffinet 192.168.0.15/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 66132sec preferred_lft 66132secinet 192.168.0.185/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::cbd1:6bd3:108f:e86e/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::3d73:75d:9f7a:924c/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::e027:ebc3:6ae5:d5e7/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever[root@mysqlrouter-2 ~]# cd /etc/keepalived/[root@mysqlrouter-2 keepalived]# lskeepalived.conf[root@mysqlrouter-1 keepalived]# vim keepalived.conf [root@mysqlrouter-2 keepalived]# cat keepalived.conf ! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0}vrrp_instance VI_1 {state backupinterface ens33virtual_router_id 80priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.0.185}}vrrp_instance VI_2 {state masterinterface ens33virtual_router_id 100priority 200advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.0.186}}[root@mysqlrouter-2 keepalived]# service keepalived startRedirecting to /bin/systemctl start keepalived.service[root@mysqlrouter-2 keepalived]# ip a1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:97:5c:6f brd ff:ff:ff:ff:ff:ffinet 192.168.0.16/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 66196sec preferred_lft 66196secinet 192.168.0.186/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe97:5c6f/64 scope link valid_lft forever preferred_lft forever# 验证vip漂移[root@mysqlrouter-1 keepalived]# service keepalived stopRedirecting to /bin/systemctl stop keepalived.service[root@mysqlrouter-2 keepalived]# ip a1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:97:5c:6f brd ff:ff:ff:ff:ff:ffinet 192.168.0.16/24 brd 192.168.0.255 scope global noprefixroute dynamic ens33valid_lft 66086sec preferred_lft 66086secinet 192.168.0.186/32 scope global ens33valid_lft forever preferred_lft foreverinet 192.168.0.185/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe97:5c6f/64 scope link valid_lft forever preferred_lft forever# 测试使用vip连接[root@test-client ~]# mysql -h 192.168.0.186 -P 7002 -uwrite -p'Sanchuang123#'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 30Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.write@(none) 16:53 mysql>exitBye[root@test-client ~]# mysql -h 192.168.0.185 -P 7001 -uread -p'Sanchuang123#'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 7Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.read@(none) 16:53 mysql>exitBye

十二.使用dns实现负载均衡，在云平台（阿里云、腾迅云等）购买域名，然后在域名里添加两条A记录，同一个域名对应着2个vip。

十三.使用压力测试软件（sysbench或tcpp）对整个mysql集群进行压力测试。

使用sysbench进行测试

# 1.下载sysbenchyum install sysbench -y# 2.准备测试所用的表sysbench --mysql-host=192.168.0.15 \--mysql-port=7002 \--mysql-user=write \--mysql-password=Sanchuang123# \/usr/share/sysbench/oltp_common.lua \--tables=10 \--table_size=100000 \prepare[root@test-client ~]# sysbench --mysql-host=192.168.0.15--mysql-port=7002--mysql-user=write--mysql-password=Sanchuang123#/usr/share/sysbench/oltp_common.lua--tables=10--table_size=100000preparesysbench 1.0.17 (using system LuaJIT 2.0.4)Creating table 'sbtest1'...Inserting 100000 records into 'sbtest1'Creating a secondary index on 'sbtest1'...Creating table 'sbtest2'...Inserting 100000 records into 'sbtest2'Creating a secondary index on 'sbtest2'...Creating table 'sbtest3'...Inserting 100000 records into 'sbtest3'Creating a secondary index on 'sbtest3'...Creating table 'sbtest4'...Inserting 100000 records into 'sbtest4'Creating a secondary index on 'sbtest4'...Creating table 'sbtest5'...Inserting 100000 records into 'sbtest5'Creating a secondary index on 'sbtest5'...Creating table 'sbtest6'...Inserting 100000 records into 'sbtest6'Creating a secondary index on 'sbtest6'...Creating table 'sbtest7'...Inserting 100000 records into 'sbtest7'Creating a secondary index on 'sbtest7'...Creating table 'sbtest8'...Inserting 100000 records into 'sbtest8'Creating a secondary index on 'sbtest8'...Creating table 'sbtest9'...Inserting 100000 records into 'sbtest9'Creating a secondary index on 'sbtest9'...Creating table 'sbtest10'...Inserting 100000 records into 'sbtest10'Creating a secondary index on 'sbtest10'...[root@test-client ~]# # 测试完后，可以清除这10个表（现在不做）sysbench --mysql-host=192.168.0.15 \--mysql-port=7002 \--mysql-user=write \--mysql-password=Sanchuang123# \/usr/share/sysbench/oltp_common.lua \--tables=10 \cleanup# 3.数据库测试sysbench --threads=4 \--time=20 \--report-interval=5 \--mysql-host=192.168.0.15 \--mysql-port=7002 \--mysql-user=write \--mysql-password=Sanchuang123# \/usr/share/sysbench/oltp_read_write.lua \--tables=10 \--table_size=10000 \run[root@test-client ~]# sysbench --threads=4 \>--time=20 \>--report-interval=5 \>--mysql-host=192.168.0.15 \>--mysql-port=7002 \>--mysql-user=write \>--mysql-password=Sanchuang123# \>/usr/share/sysbench/oltp_read_write.lua \>--tables=10 \>--table_size=10000 \>runsysbench 1.0.17 (using system LuaJIT 2.0.4)Running the test with following options:Number of threads: 4Report intermediate results every 5 second(s)Initializing random number generator from current timeInitializing worker threads...Threads started![ 5s ] thds: 4 tps: 5.59 qps: 122.20 (r/w/o: 87.85/22.36/11.98) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00[ 10s ] thds: 4 tps: 0.80 qps: 11.81 (r/w/o: 7.00/3.20/1.60) lat (ms,95%): 3326.55 err/s: 0.00 reconn/s: 0.00[ 15s ] thds: 4 tps: 0.00 qps: 2.20 (r/w/o: 2.20/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00[ 20s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00[ 25s ] thds: 4 tps: 0.00 qps: 0.20 (r/w/o: 0.20/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00[ 30s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00[ 35s ] thds: 4 tps: 0.00 qps: 2.40 (r/w/o: 2.00/0.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00[ 40s ] thds: 4 tps: 0.00 qps: 0.40 (r/w/o: 0.20/0.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00SQL statistics:queries performed:read: 504write: 144other: 72total: 720transactions: 36(0.88 per sec.)queries: 720 (17.65 per sec.)ignored errors: 0(0.00 per sec.)reconnects:0(0.00 per sec.)General statistics:total time:40.7895stotal number of events: 36Latency (ms):min: 174.28avg: 4508.50max: 35015.5395th percentile:35191.04sum: 162305.92Threads fairness:events (avg/stddev): 9.0000/0.00execution time (avg/stddev): 40.5765/0.12[root@test-client ~]# # 测试连接中间件服务器的时候，一定要去写的端口（7002），不然会导致写数据从服务器上，因为有2个从服务器，会轮询调度到不同机器上，会出现找不到数据库。

mysql性能测试工具——tpcc-mysql

注意：使用tpcc（需要安装MySQL）

# 下载源码包，解压安装[root@test-client ~]# yum install wget -y[root@test-client ~]# wget /wp-content/uploads//09/tpcc-mysql-src.tgz---08-01 16:48:15-- /wp-content/uploads//09/tpcc-mysql-src.tgz正在解析主机 ()... 43.133.241.44正在连接 ()|43.133.241.44|:80... 已连接。已发出 HTTP 请求，正在等待回应... 301 Moved Permanently位置：/wp-content/uploads//09/tpcc-mysql-src.tgz [跟随至新的 URL]---08-01 16:48:15-- /wp-content/uploads//09/tpcc-mysql-src.tgz正在连接 ()|43.133.241.44|:443... 已连接。已发出 HTTP 请求，正在等待回应... 200 OK长度：176013 (172K) [application/octet-stream]正在保存至: “tpcc-mysql-src.tgz”100%[=======================================================================================================================================================================>] 176,01316.3KB/s 用时 11s -08-01 16:48:29 (16.3 KB/s) - 已保存 “tpcc-mysql-src.tgz” [176013/176013])[root@test-client ~]# mkdir tpcc[root@test-client ~]# mv tpcc-mysql-src.tgz tpcc[root@test-client ~]# cd tpcc[root@test-client tpcc]# tar xf tpcc-mysql-src.tgz [root@test-client tpcc]# cd tpcc-mysql[root@test-client tpcc-mysql]# cd src[root@test-client src]# lsdelivery.c main.c ordstat.crthist.c sequence.h spt_proc.h trans_if.hdriver.c Makefile parse_port.h rthist.h slev.csupport.cload.cneword.c payment.csequence.c spt_proc.c tpc.h[root@test-client src]# makecc -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_loadcc -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c -w -O2 -g -I. `mysql_config --include` -c c main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start[root@test-client src]# cd ..[root@test-client tpcc-mysql]# ll总用量 276-rw-r--r-- 1 root root 1621 9月 14 add_fkey_idx.sql-rw-r--r-- 1 root root 317 9月 14 count.sql-rw-r--r-- 1 root root 3105 9月 14 create_table.sql-rw-r--r-- 1 root root 763 9月 14 drop_cons.sql-rw-r--r-- 1 root root 477 9月 14 load.sh-rw-r--r-- 1 root root 851 9月 14 READMEdrwxr-xr-x 2 root root92 9月 14 schema2drwxr-xr-x 5 root root 4096 9月 14 scriptsdrwxr-xr-x 2 root root 4096 8月 1 16:49 src-rwxr-xr-x 1 root root 72528 8月 1 16:49 tpcc_load-rwxr-xr-x 1 root root 175240 8月 1 16:49 tpcc_start# 之后会生成两个二进制工具tpcc_load（提供初始化数据的功能）和tpcc_start(进行压力测试)# tpcc测试前准备，初始化数据库[root@test-client ~]# mysqladmin -uwrite -p'Sanchuang123#' -h 192.168.0.186 -P 7002 create tpcc mysqladmin: [Warning] Using a password on the command line interface can be insecure.# master上查看root@(none) 16:42 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || TENNIS || han|| mysql || performance_schema || sbtest || sys|| tpcc|+--------------------+8 rows in set (0.00 sec)# master服务器需要导入表到tpcc库里mysql -uwrite -p'Sanchuang123#' -h 192.168.0.185 -P 7002 tpcc < create_table.sql# 创建测试用的表mysql -uwrite -p'Sanchuang123#' -h 192.168.0.186 -P 7002 tpcc < add_fkey_idx.sql# 创建FK和索引[root@test-client tpcc-mysql]# pwd/root/tpcc/tpcc-mysql[root@test-client tpcc-mysql]# lsadd_fkey_idx.sql count.sql create_table.sql drop_cons.sql load.sh README schema2 scripts src tpcc_load tpcc_start[root@test-client tpcc-mysql]# mysql -uwrite -p'Sanchuang123#' -h 192.168.0.185 -P 7002 tpcc < create_table.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@test-client tpcc-mysql]# mysql -uwrite -p'Sanchuang123#' -h 192.168.0.186 -P 7002 tpcc < add_fkey_idx.sqlmysql: [Warning] Using a password on the command line interface can be insecure.# 加载数据[root@test-client tpcc-mysql]# ./tpcc_load 192.168.0.185:7002 tpcc write 'Sanchuang123#' 150./tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

./tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

问题原因：系统找不到此动态库
解决办法：将动态库下载到linux下

# 将此库复制到此目录[root@test-client tpcc-mysql]# cp /usr/local/mysql/lib/libmysqlclient.so.20 /usr/local/lib/ # 系统根据这个文件找的动态库，添加/usr/local/lib/ [root@test-client tpcc-mysql]# vim /etc/ld.so.conf[root@test-client tpcc-mysql]# cat /etc/ld.so.confinclude ld.so.conf.d/*.conf/usr/local/lib/ # 执行ldconfig[root@test-client tpcc-mysql]# ldconfig

加载数据

[root@test-client tpcc-mysql]# ./tpcc_load 192.168.0.185:7002 tpcc write 'Sanchuang123#' 150**************************************** ###easy### TPC-C Data Loader ****************************************<Parameters>[server]: 192.168.0.185[port]: 7002[DBname]: tpcc[user]: write[pass]: Sanchuang123#[warehouse]: 150TPCC Data Load Started...Loading Item .................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Item Done. Loading Warehouse Loading Stock Wid=1.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=2.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=3.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=4.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=5.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=6.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=7.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=8.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=9.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=10.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000.................................................. 50000.................................................. 55000.................................................. 60000.................................................. 65000.................................................. 70000.................................................. 75000.................................................. 80000.................................................. 85000.................................................. 90000.................................................. 95000.................................................. 100000Stock Done.Loading DistrictLoading Stock Wid=11.................................................. 5000.................................................. 10000.................................................. 15000.................................................. 20000.................................................. 25000.................................................. 30000.................................................. 35000.................................................. 40000.................................................. 45000# 查看消耗的资源情况[root@master ~]# toptop - 17:34:47 up 6:32, 3 users, load average: 0.00, 0.01, 0.05Tasks: 117 total, 1 running, 116 sleeping, 0 stopped, 0 zombie%Cpu(s): 0.0 us, 6.2 sy, 0.0 ni, 93.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 stKiB Mem : 1863032 total, 74020 free, 697260 used, 1091752 buff/cacheKiB Swap: 2097148 total, 2093300 free,3848 used. 996176 avail Mem PID USERPR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2983 mysql20 0 2037164 486100 14432 S 6.2 26.1 1:02.77 mysqld 16299 root20 0 162108 2208 1540 R 6.2 0.1 0:00.01 top1 root20 0 128188 5544 2904 S 0.0 0.3 0:04.08 systemd 2 root20 0 000 S 0.0 0.0 0:00.03 kthreadd 4 root 0 -20 000 S 0.0 0.0 0:00.00 kworker/0:0H 6 root20 0 000 S 0.0 0.0 0:10.07 ksoftirqd/0 7 rootrt 0 000 S 0.0 0.0 0:01.61 migration/0 8 root20 0 000 S 0.0 0.0 0:00.00 rcu_bh 9 root20 0 000 S 0.0 0.0 0:53.60 rcu_sched10 root 0 -20 000 S 0.0 0.0 0:00.00 lru-add-drain11 rootrt 0 000 S 0.0 0.0 0:00.28 watchdog/0 12 rootrt 0 000 S 0.0 0.0 0:00.70 watchdog/1 13 rootrt 0 000 S 0.0 0.0 0:02.01 migration/1 14 root20 0 000 S 0.0 0.0 0:05.88 ksoftirqd/1 16 root 0 -20 000 S 0.0 0.0 0:00.00 kworker/1:0H 18 root20 0 000 S 0.0 0.0 0:00.02 kdevtmpfs19 root 0 -20 000 S 0.0 0.0 0:00.00 netns 20 root20 0 000 S 0.0 0.0 0:00.00 khungtaskd 21 root 0 -20 000 S 0.0 0.0 0:00.00 writeback22 root 0 -20 000 S 0.0 0.0 0:00.00 kintegrityd 23 root 0 -20 000 S 0.0 0.0 0:00.00 bioset 24 root 0 -20 000 S 0.0 0.0 0:00.00 bioset 25 root 0 -20 000 S 0.0 0.0 0:00.00 bioset 26 root 0 -20 000 S 0.0 0.0 0:00.00 kblockd 27 root 0 -20 000 S 0.0 0.0 0:00.00 md 28 root 0 -20 000 S 0.0 0.0 0:00.00 edac-poller 29 root 0 -20 000 S 0.0 0.0 0:00.00 watchdogd35 root20 0 000 S 0.0 0.0 0:02.61 kswapd0 36 root25 5 000 S 0.0 0.0 0:00.00 ksmd37 root39 19 000 S 0.0 0.0 0:00.43 khugepaged 38 root 0 -20 000 S 0.0 0.0 0:00.00 crypto 46 root 0 -20 000 S 0.0 0.0 0:00.00 kthrotld 49 root 0 -20 000 S 0.0 0.0 0:00.00 kmpath_rdacd 50 root 0 -20 000 S 0.0 0.0 0:00.00 kaluad 51 root 0 -20 000 S 0.0 0.0 0:00.00 kpsmoused[root@master ~]#

进行测试

[root@test-client tpcc-mysql]# ./tpcc_start -h 192.168.0.185 -P 7002 -d tpcc -u write -p Sanchuang123# -w 150 -c 12 -r 300 -l 360 -f test0.log -t test1.log - >test0.out1205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 1:11205, HY000, Lock wait timeout exceeded; try restarting transactionpayment 2:1[root@master ~]# top top - 20:36:02 up 9:34, 2 users, load average: 0.39, 0.16, 0.09Tasks: 109 total, 2 running, 107 sleeping, 0 stopped, 0 zombie%Cpu(s): 3.9 us, 7.1 sy, 0.0 ni, 88.1 id, 0.2 wa, 0.0 hi, 0.7 si, 0.0 stKiB Mem : 1863032 total, 81816 free, 950340 used, 830876 buff/cacheKiB Swap: 2097148 total, 2071796 free, 25352 used. 753432 avail Mem PID USERPR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2983 mysql20 0 2039012 754604 6240 S 24.9 40.5 9:38.89 mysqld 6 root20 0 000 S 1.0 0.0 0:14.60 ksoftirqd/0 1047 root20 0 574280 13352 984 S 0.3 0.7 0:05.14 tuned 1 root20 0 128188 4848 2892 S 0.0 0.3 0:04.61 systemd 2 root20 0 000 S 0.0 0.0 0:00.04 kthreadd 4 root 0 -20 000 S 0.0 0.0 0:00.00 kworker/0:0H 7 rootrt 0 000 S 0.0 0.0 0:01.61 migration/0 8 root20 0 000 S 0.0 0.0 0:00.00 rcu_bh 9 root20 0 000 R 0.0 0.0 1:09.73 rcu_sched10 root 0 -20 000 S 0.0 0.0 0:00.00 lru-add-drain11 rootrt 0 000 S 0.0 0.0 0:00.35 watchdog/0 12 rootrt 0 000 S 0.0 0.0 0:00.81 watchdog/1 13 rootrt 0 000 S 0.0 0.0 0:02.01 migration/1 14 root20 0 000 S 0.0 0.0 0:06.03 ksoftirqd/1 16 root 0 -20 000 S 0.0 0.0 0:00.00 kworker/1:0H 18 root20 0 000 S 0.0 0.0 0:00.02 kdevtmpfs19 root 0 -20 000 S 0.0 0.0 0:00.00 netns 20 root20 0 000 S 0.0 0.0 0:00.01 khungtaskd 21 root 0 -20 000 S 0.0 0.0 0:00.00 writeback22 root 0 -20 000 S 0.0 0.0 0:00.00 kintegrityd 23 root 0 -20 000 S 0.0 0.0 0:00.00 bioset 24 root 0 -20 000 S 0.0 0.0 0:00.00 bioset 25 root 0 -20 000 S 0.0 0.0 0:00.00 bioset 26 root 0 -20 000 S 0.0 0.0 0:00.00 kblockd 27 root 0 -20 000 S 0.0 0.0 0:00.00 md 28 root 0 -20 000 S 0.0 0.0 0:00.00 edac-poller 29 root 0 -20 000 S 0.0 0.0 0:00.00 watchdogd35 root20 0 000 S 0.0 0.0 0:03.70 kswapd0 36 root25 5 000 S 0.0 0.0 0:00.00 ksmd37 root39 19 000 S 0.0 0.0 0:00.48 khugepaged 38 root 0 -20 000 S 0.0 0.0 0:00.00 crypto 46 root 0 -20 000 S 0.0 0.0 0:00.00 kthrotld 49 root 0 -20 000 S 0.0 0.0 0:00.00 kmpath_rdacd 50 root 0 -20 000 S 0.0 0.0 0:00.00 kaluad 51 root 0 -20 000 S 0.0 0.0 0:00.00 kpsmoused[root@master ~]#

[root@test-client tpcc-mysql]# cat test0.out ****************************************** ###easy### TPC-C Load Generator ******************************************option h with value '192.168.0.185'option P with value '7002'option d with value 'tpcc'option u with value 'write'option p with value 'Sanchuang123#'option w with value '150'option c with value '12'option r with value '300'option l with value '360'option f with value 'test0.log'option t with value 'test1.log'non-option ARGV-elements: - <Parameters>[server]: 192.168.0.185[port]: 7002[DBname]: tpcc[user]: write[pass]: Sanchuang123#[warehouse]: 150[connection]: 12[rampup]: 300 (sec.)[measure]: 360 (sec.)RAMP-UP TIME.(300 sec.)MEASURING START.10, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):2.018|2.785, 0(0):0.000|0.00020, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00030, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00040, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00050, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00060, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00070, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00080, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.00090, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000100, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 1(0):0.000|1.117, 0(0):0.000|0.000110, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000120, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000130, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000140, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000150, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000160, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000170, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000180, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000190, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000200, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 1(0):0.000|1.911, 0(0):0.000|0.000210, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000220, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000230, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000240, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000250, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000260, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000270, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000280, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000290, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000300, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000310, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000320, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000330, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000340, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 1(0):0.000|0.862, 0(0):0.000|0.000350, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000360, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000STOPPING THREADS..........[root@test-client tpcc-mysql]#

如果觉得《基于keepalived+GTID半同步主从复制的高可用MySQL集群》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。