基于双vip的GTID的半同步主从复制MySQL高可用集群
基于双vip的GTID的半同步主从复制MySQL高可用集群
一.项目介绍
1.拓扑图
2.详细介绍
项目名称:基于双vip的GTID的半同步主从复制MySQL高可用集群
项目环境:centos7.9,mysql5.7.43,mysqlrouter8.0.21,keepalived 1.3.5,ansible 2.9.27,prometheus-2.47.0,grafana-enterprise-10.1.1-1,node_exporter-1.6.1等。
项目描述:
本项目的目的是构建一个高可用的能实现读写分离的高效的MySQL集群,确保业务的稳定,能沟通方便的监控整个集群,同时能批量的去部署和管理整个集群。
项目步骤:
1.配置好ansible服务器并建立免密通道,一键安装MySQL、mysqlroute、node_exporters、dns等软件,在master上导出基础数据到ansible上,发布到所有slave服务器上并导入
2.安装好半同步相关的插件,开启gtid功能,启动主从复制服务,配置延迟备份服务器,从slave1上拿二进制日志
3.在master上创建一个计划任务每天2:30进行数据库的备份脚本,使用rsync+sersync远程同步到slave4异地备份服务器上
4.安装部署mysqlrouter中间件软件,实现读写分离;安装keepalived实现高可用,配置2个vrrp实例实现双vip的高可用功能
5.搭建DNS域名服务器,配置一个域名对应2个vip,实现基于DNS的负载均衡,访问同一URL解析出双vip地址
6.使用sysbench整个MySQL集群的性能(cpu、IO、内存等)进行压力测试,安装部署prometheus实现监控,grafana出图了解系统性能的瓶颈并调优
二.前期准备
1.项目环境
cecentos7.9,mysql5.7.43,mysqlrouter8.0.21,keepalived 1.3.5,ansible 2.9.27,prometheus-2.47.0,grafana-enterprise-10.1.1-1,node_exporter-1.6.1等
2.IP划分
准备11台centos7.9的虚拟机,并且分配IP地址:
主机名 | IP |
---|---|
DNS服务器 | 192.168.153.145 |
mysqlrouter1 | 192.168.153.155 |
mysqlrouter2 | 192.168.153.147 |
master | 192.168.153.150 |
slave1 | 192.168.153.151 |
slave2 | 192.168.153.152 |
slave3 | 192.168.153.153 |
slave4 | 192.168.153.154 |
ansible | 192.168.153.148 |
监控服务器 | 192.168.153.149 |
sysbench压力测试机 | 192.168.153.144 |
3.根据ip规划配置好静态ip
[root@master ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
BOOTPROTO=static #静态配置ip
NAME=ens33 #网卡名称ens33
DEVICE=ens33 #本机网卡ens33
ONBOOT=yes #开机启动
IPADDR=192.168.153.150 #ip地址
PREFIX=24 #子网掩码24
GATEWAY=192.168.153.2 #我选择的是NAT网络,所以网关是路由器ip 192.168.153.2
DNS1=114.114.114.114 #dns服务器114.114.114.114
[root@master ~]# service network restart #重启网络
Restarting network (via systemctl): [ 确定 ]
[root@master ~]# ip add #查看ip
[root@master ~]# ping www.baidu.com #测试能否上网
4.修改主机名方便管理
[root@mysql-master ~]# hostnamectl set-hostname mysql-master #修改指定主机名,方便辨认
[root@mysql-master ~]# su -
5.配置ansible免密通道
[root@ansible ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:uSX20PtsydUfYFov6VR0a7Q/BM5uWH/dCMpHYJOxOIA root@ansible
The key's randomart image is:
+---[RSA 2048]----+
| .. .o |
| E . .=. . o.|
| o..oo + +|
| + oB * |
| S.ooB.Oo+|
| . *o+.*o=*|
| . oo+o. =|
| o+. .|
| .o |
+----[SHA256]-----+
[root@ansible ~]#
[root@ansible ~]# cd .ssh/
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.150 #master
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.151 #salve1
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.152 #salve2
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.153 #salve3
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.154 #salve4
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.145 #DNS
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.145 #mysqlrouter1
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.147 #mysqlrouter2
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub root@192.168.153.149 #prometheus
三. 项目步骤
1.安装部署ansible
[root@ansible ~]# yum install -y epel-release
[root@ansible ~]# yum install ansible -y
#修改配置文件,配置以下八台,监控和压力测试机单独部署
[root@localhost ~]# vim /etc/ansible/hosts
[mysqlrouter]
192.168.153.155
192.168.153.147
[mysql]
192.168.153.150
192.168.153.151
192.168.153.152
192.168.153.153
192.168.153.154
[dns]
192.168.153.145
1.利用xftp上传MySQL软件包(mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz)和MySQL Router的安装包(mysql-router-community-8.0.21-1.el7.x86_64.rpm)以及Node Exporter的软件包(node_exporter-1.6.1.linux-amd64.tar.gz)
[root@ansible ~]# ls
anaconda-ks.cfg node_exporter.sh
mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz onekey_install_mysql.sh
mysql-router-community-8.0.21-1.el7.x86_64.rpm software_install.yaml
node_exporter-1.6.1.linux-amd64.tar.gz
[root@ansible ~]#
2.编写安装mysql和node_porters的脚本
[root@ansible ~]# cat node_exporter.sh
#!/bin/bash
cd ~
tar xf node_exporter-1.6.1.linux-amd64.tar.gz
mv node_exporter-1.6.1.linux-amd64 /node_exporter
cd /node_exporter
PATH=/node_exporter:$PATH
echo "PATH=/node_exporter:$PATH" >>/root/.bashrc
nohup node_exporter --web.listen-address 0.0.0.0:8090 &
[root@ansible ~]#
[root@ansible ~]# cat onekey_install_mysql.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.43-linux-glibc2.12-x86_64.tar.gz
#移动mysql解压后的文件到/usr/local下改名叫mysql
mv mysql-5.7.43-linux-glibc2.12-x86_64 /usr/local/mysql
#新建组和用户 mysql
groupadd mysql
#mysql这个用户的shell 是/bin/false 属于mysql组
useradd -r -g mysql -s /bin/false mysql
#关闭firewalld防火墙服务,并且设置开机不要启动
service firewalld stop
systemctl disable firewalld
#临时关闭selinux
setenforce 0
#永久关闭selinux
sed -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/
#获得临时密码
tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')
#$NF表示最后一个字段
# abc=$(命令) 优先执行命令,然后将结果赋值给abc
# 修改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/目录下叫mysqld
cp ../support-files/mysql.server /etc/init.d/mysqld
#修改/etc/init.d/mysqld脚本文件里的datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld
#生成/etc/my.cnf配置文件
cat >/etc/my.cnf <<EOF
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m mysql>
EOF
#修改内核的open file的数量
ulimit -n 1000000
#设置开机启动的时候也配置生效
echo "ulimit -n 1000000" >>/etc/rc.local
chmod +x /etc/rc.d/rc.local
#将mysqld添加到linux系统里服务管理名单里
/sbin/chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on
#启动mysqld进程
service mysqld start
#初次修改密码需要使用--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;"
[root@ansible ~]# cat node_exporter.sh
#!/bin/bash
#进入root家目录
cd ~
#解压node_exporters源码包
tar xf node_exporter-1.6.1.linux-amd64.tar.gz
#改名
mv node_exporter-1.6.1.linux-amd64 /node_exporter
cd /node_exporter
#修改PATH环境变量
PATH=/node_exporter:$PATH
echo "PATH=/node_exporter:$PATH" >>/root/.bashrc
#后台运行,监听8090端口
nohup node_exporter --web.listen-address 0.0.0.0:8090 &
3.编写playbook批量部署mysql、mysqlroute、node_exporters、dns等软件
[root@ansible ~]# vim software_install.yaml
- hosts: mysql #mysql集群
remote_user: root
tasks:
- name: copy mysql.tar.gz #上传MySQL安装包到mysql主机组
copy: src=/root/mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz dest=/root/
- name: copy mysql.sh #上传脚本到mysql主机组
copy: src=/root/onekey_install_mysql.sh dest=/root/
- name: install mysql #安装MySQL
script: /root/onekey_install_mysql.sh
- hosts: mysqlrouter #mysqlrouter服务器
remote_user: root
tasks:
- name: copy file #上传mysqlrouter安装包到服务器
copy: src=/root/mysql-router-community-8.0.21-1.el7.x86_64.rpm dest=/root/
- name: install mysqlrouter #安装mysqlrouter
shell: rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
- name: install keepalived #安装keepalived实现高可用
yum: name=keepalived state=installed
- hosts: dns #dns服务器
remote_user: root
tasks:
- name: install dns
yum: name=bind.* state=installed
- hosts: mysqlrouter mysql #调用本地node_exporter脚本,批量安装部署node_exporter,为prometheus采集数据
remote_user: root
tasks:
- name: copy file #上传node_exporter安装包到服务器
copy: src=/root/node_exporter-1.6.1.linux-amd64.tar.gz dest=/root/
- name: copy file #上传脚本到服务器
copy: src=/root/node_exporter.sh dest=/root/
- name: install node_exporters #执行脚本
script: /root/node_exporter.sh
tags: install_exporter
- name: start node_exporters #后台运行node_exporters
shell: nohup node_exporter --web.listen-address 0.0.0.0:8090 &
tags: start_exporters #打标签,方便后面直接跳转到此处批量启动node_exporters
'检查一下语法'
[root@ansible ~]# ansible-playbook --syntax-check software_install.yaml
playbook: software_install.yaml
[root@ansible ~]#
开始执行
[root@ansible ~]# ansible-playbook software_install.yaml
PLAY [mysql] ***************************************************************************************
TASK [Gathering Facts] *****************************************************************************
ok: [192.168.153.150]
ok: [192.168.153.152]
ok: [192.168.153.153]
ok: [192.168.153.154]
ok: [192.168.153.151]
TASK [copy mysql.tar.gz] ***************************************************************************
changed: [192.168.153.150]
changed: [192.168.153.153]
changed: [192.168.153.152]
changed: [192.168.153.151]
changed: [192.168.153.154]
TASK [copy mysql.sh] *******************************************************************************
changed: [192.168.153.150]
changed: [192.168.153.153]
changed: [192.168.153.151]
changed: [192.168.153.154]
changed: [192.168.153.152]
TASK [install mysql] *******************************************************************************
changed: [192.168.153.154]
changed: [192.168.153.151]
changed: [192.168.153.153]
changed: [192.168.153.150]
changed: [192.168.153.152]
PLAY [mysqlrouter] *********************************************************************************
TASK [Gathering Facts] *****************************************************************************
ok: [192.168.153.155]
ok: [192.168.153.147]
TASK [copy file] ***********************************************************************************
changed: [192.168.153.155]
changed: [192.168.153.147]
TASK [install mysqlrouter] *************************************************************************
[WARNING]: Consider using the yum, dnf or zypper module rather than running 'rpm'. If you need to
use command because yum, dnf or zypper is insufficient you can add 'warn: false' to this command
task or set 'command_warnings=False' in ansible.cfg to get rid of this message.
changed: [192.168.153.155]
changed: [192.168.153.147]
TASK [install keepalived] **************************************************************************
changed: [192.168.153.155]
changed: [192.168.153.147]
PLAY [dns] *****************************************************************************************
TASK [Gathering Facts] *****************************************************************************
ok: [192.168.153.145]
TASK [install dns] *********************************************************************************
changed: [192.168.153.145]
PLAY [mysqlrouter:mysql] ***************************************************************************
TASK [Gathering Facts] *****************************************************************************
ok: [192.168.153.147]
ok: [192.168.153.155]
ok: [192.168.153.150]
ok: [192.168.153.151]
ok: [192.168.153.152]
ok: [192.168.153.153]
ok: [192.168.153.154]
TASK [copy file] ***********************************************************************************
changed: [192.168.153.155]
changed: [192.168.153.150]
changed: [192.168.153.147]
changed: [192.168.153.152]
changed: [192.168.153.151]
changed: [192.168.153.153]
changed: [192.168.153.154]
TASK [copy file] ***********************************************************************************
changed: [192.168.153.155]
changed: [192.168.153.147]
changed: [192.168.153.150]
changed: [192.168.153.152]
changed: [192.168.153.151]
changed: [192.168.153.153]
changed: [192.168.153.154]
TASK [install node_exporters] **********************************************************************
changed: [192.168.153.155]
changed: [192.168.153.151]
changed: [192.168.153.147]
changed: [192.168.153.150]
changed: [192.168.153.152]
changed: [192.168.153.153]
changed: [192.168.153.154]
TASK [start node_exporters] ************************************************************************
changed: [192.168.153.152]
changed: [192.168.153.155]
changed: [192.168.153.147]
changed: [192.168.153.151]
changed: [192.168.153.150]
changed: [192.168.153.154]
changed: [192.168.153.153]
PLAY RECAP *****************************************************************************************
192.168.153.145 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.155 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.147 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.150 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.151 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.152 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.153 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.153.154 : ok=9 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
[root@ansible ~]#
2.同步mysql数据
1.导出master上的mysql上的数据
[root@mysql-master ~]# mysqldump -uroot -p'Sanchaung123#' --all-databases >all_db.SQL
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
[root@mysql-master ~]# ls
all_db.SQL mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz node_exporter.sh
anaconda-ks.cfg node_exporter-1.6.1.linux-amd64.tar.gz onekey_install_mysql.sh
[root@mysql-master ~]#
2.使用scp把all_db.SQL传递给ansible
[root@ansible ~]# scp root@192.168.153.150:/root/all_db.SQL /root
all_db.SQL 100% 0 0.0KB/s 00:00
[root@ansible ~]# ls
all_db.SQL mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz node_exporter-1.6.1.linux-amd64.tar.gz onekey_install_mysql.sh
anaconda-ks.cfg mysql-router-community-8.0.21-1.el7.x86_64.rpm node_exporter.sh software_install.yaml
[root@ansible ~]#
3.使用ansible下发到slave集群机器上
ansible mysql -m copy -a "src=/root/all_db.SQL dest=/root/"
ansible mysql -m shell -a "mysql -uroot -p'Sanchuang123#' <all_db.SQL"
效果
[root@ansible ~]# ansible mysql -m copy -a "src=/root/all_db.SQL dest=/root/"
192.168.153.153 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": true,
"checksum": "da39a3ee5e6b4b0d3255bfef95601890afd80709",
"dest": "/root/all_db.SQL",
"gid": 0,
"group": "root",
"md5sum": "d41d8cd98f00b204e9800998ecf8427e",
"mode": "0644",
"owner": "root",
"secontext": "system_u:object_r:admin_home_t:s0",
"size": 0,
"src": "/root/.ansible/tmp/ansible-tmp-1703827454.67-25212-144285109251097/source",
"state": "file",
"uid": 0
}
192.168.153.151 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": true,
"checksum": "da39a3ee5e6b4b0d3255bfef95601890afd80709",
"dest": "/root/all_db.SQL",
"gid": 0,
"group": "root",
"md5sum": "d41d8cd98f00b204e9800998ecf8427e",
"mode": "0644",
"owner": "root",
"secontext": "system_u:object_r:admin_home_t:s0",
"size": 0,
"src": "/root/.ansible/tmp/ansible-tmp-1703827454.74-25208-115323625408171/source",
"state": "file",
"uid": 0
}
192.168.153.154 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": true,
"checksum": "da39a3ee5e6b4b0d3255bfef95601890afd80709",
"dest": "/root/all_db.SQL",
"gid": 0,
"group": "root",
"md5sum": "d41d8cd98f00b204e9800998ecf8427e",
"mode": "0644",
"owner": "root",
"secontext": "system_u:object_r:admin_home_t:s0",
"size": 0,
"src": "/root/.ansible/tmp/ansible-tmp-1703827454.72-25215-134472582139467/source",
"state": "file",
"uid": 0
}
192.168.153.150 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"checksum": "da39a3ee5e6b4b0d3255bfef95601890afd80709",
"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": 0,
"state": "file",
"uid": 0
}
192.168.153.152 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": true,
"checksum": "da39a3ee5e6b4b0d3255bfef95601890afd80709",
"dest": "/root/all_db.SQL",
"gid": 0,
"group": "root",
"md5sum": "d41d8cd98f00b204e9800998ecf8427e",
"mode": "0644",
"owner": "root",
"secontext": "system_u:object_r:admin_home_t:s0",
"size": 0,
"src": "/root/.ansible/tmp/ansible-tmp-1703827454.86-25209-185887644266718/source",
"state": "file",
"uid": 0
}
[root@ansible ~]#
[root@ansible ~]# ansible mysql -m shell -a "mysql -uroot -p'Sanchuang123#' <all_db.SQL"
192.168.153.150 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
192.168.153.153 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
192.168.153.154 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
192.168.153.152 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
192.168.153.151 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ansible ~]#
3.配置基于GTID的半同步主从复制
1.在master上安装配置半同步的插件
root@(none) 14:07 mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.03 sec)
root@(none) 14:18 mysql>exit
2.修改master配置文件 /etc/my.conf 并开启gtid功能
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
#开启二进制日志
log_bin
server_id = 1
#开启半同步功能,需要提前安装半同步的插件
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
#开启gtid功能
gtid-mode=ON
enforce-gtid-consistency=ON
3.在每台从服务器上配置安装半同步的插件,配置slave配置文件
root@(none) 14:23 mysql>install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
root@(none) 14:28 mysql>set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.01 sec)
root@(none) 14:28 mysql>
[root@mysql-salve2 ~]# vim /etc/my.cnf
[mysqld]
#log bin 二进制日志
log_bin
server_id = 2 #注意:每台slave的id都不一样
expire_logs_days = 15 #二进制日志保存15天
#开启半同步,需要提前安装半同步的插件
rpl_semi_sync_slave_enabled=1
#开启gtid功能
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
最后一定要记得刷新服务
[root@mysql-salve2 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
4.在master上新建一个授权用户,给slave1和salve2来复制二进制日志
grant replication slave on *.* to 'gaohui'@'192.168.153.%' identified by '123456';
root@(none) 17:33 mysql>grant replication slave on *.* to 'gaohui'@'192.168.153.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (1.02 sec)
root@(none) 17:41 mysql>
5.在slave2上创建授权用户,给salve3复制二进制日志
grant replication slave on *.* to 'gaofei'@'192.168.153.%' identified by '123456';
root@(none) 17:42 mysql>grant replication slave on *.* to 'gaofei'@'192.168.153.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
root@(none) 17:42 mysql>
6.在slave上配置master info的信息
在slave1和slave2上:
root@(none) 17:45 mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 17:45 mysql>reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@(none) 17:45 mysql>
-> change master to master_host='192.168.153.150' ,
-> master_user='gaohui',
-> master_password='123456',
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
root@(none) 17:46 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 17:46 mysql>
在slave3上:
root@(none) 17:48 mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 17:48 mysql>reset slave all;
Query OK, 0 rows affected (0.01 sec)
root@(none) 17:48 mysql>change master to master_host='192.168.153.152' ,
-> master_user='gaofei',
-> master_password='123456',
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
root@(none) 17:49 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 17:50 mysql>
7.查看
在slave上查看
root@(none) 16:34 scmysql>show slave status\G;
在master上查看
root@(none) 16:35 mysql>show variables like "%semi_sync%";
在slave上查看
root@(none) 16:35 scmysql>show variables like "%semi_sync%";
8.验证GTID的半同步主从复制
在master上新建库或者表,在slave上看有没有
主:
root@(none) 17:54 mysql>create database gaohui;
Query OK, 1 row affected (0.01 sec)
root@(none) 17:54 mysql>use gaohui;
Database changed
root@gaohui 17:54 mysql>CREATE TABLE t1 (id INT);
Query OK, 0 rows affected (0.02 sec)
root@gaohui 17:55 mysql>INSERT INTO t1 (id) VALUES (1), (2);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@gaohui 17:56 mysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
从:
root@(none) 17:51 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gaohui |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
root@(none) 17:56 mysql>use gaohui;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@gaohui 17:57 mysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
root@gaohui 17:57 mysql>
9.配置slave3延迟备份服务器
#停止同步服务
root@(none) 13:42 mysql>stop slave;
#延迟10分钟再备份
root@(none) 13:42 mysql>change master to master_delay = 600;
#开始同步
root@(none) 13:43 mysql>start slave;
#查看设置情况
root@(none) 13:43 mysql>show slave status\G;
SQL_Delay: 60
4.master创建一个计划任务,远程同步到slave4异地备份服务器
1.在slave4上操作
[root@mysql-salve4 ~]# vim onekey_install_rsync_slave.sh
#!/bin/bash
#创建备份目录
mkdir /backup
#关闭firewalld防火墙服务,并且设置开机不要启动
service firewalld stop
systemctl disable firewalld
#临时关闭selinux
setenforce 0
#永久关闭selinux
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
#安装rsync服务端软件
yum install rsync xinetd -y
#设置开机启动
echo '/usr/bin/rsync --daemon --config=/etc/rsyncd.conf' >>/etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local
#生成/etc/rsyncd.conf配置文件
cat >/etc/rsyncd.conf <<EOF
uid = root
gid = root
use chroot = yes
max connections = 0
log file = /var/log/rsyncd.log
pid 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 stored
ignore errors = yes
read only = no
hosts allow = 192.168.153.150
EOF
#创建用户认证文件
cat >/etc/rsync.pass <<EOF
slave:123456
EOF
#设置文件所有者读取、写入权限
chmod 600 /etc/rsyncd.conf
chmod 600 /etc/rsync.pass
#启动rsync
/usr/bin/rsync --daemon --config=/etc/rsyncd.conf
#启动xinetd(xinetd是一个提供保姆服务的进程,rsync是它照顾的进程)
systemctl start xinetd
2.查看rsync和xinetd监听的进程
[root@mysql-salve4 ~]# ps aux|grep rsync
root 18653 0.0 0.0 114852 572 ? Ss 18:24 0:00 /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
root 18663 0.0 0.0 112824 972 pts/0 S+ 18:24 0:00 grep --color=auto rsync
[root@mysql-salve4 ~]# ps aux|grep xinetd
root 18661 0.0 0.0 25044 588 ? Ss 18:24 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid
root 18665 0.0 0.0 112824 976 pts/0 S+ 18:24 0:00 grep --color=auto xinetd
[root@mysql-salve4 ~]#
3.在数据源master服务器操作
[root@mysql-master ~]# vim onekey_install_rsync_master.sh
#!/bin/bash
#关闭firewalld防火墙服务,并且设置开机不要启动
service firewalld stop
systemctl disable firewalld
#临时关闭selinux
setenforce 0
#永久关闭selinux
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
#安装rsync服务端软件
yum install rsync xinetd -y
#设置开机启动
echo '/usr/bin/rsync --daemon --config=/etc/rsyncd.conf' >>/etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local
#生成/etc/rsyncd.conf配置文件
cat >/etc/rsyncd.conf <<EOF
log file = /var/log/rsyncd.log
pid file = /var/run/rsyncd.pid
lock file = /var/run/rsync.lock
motd file = /etc/rsyncd.Motd
[Sync]
comment = Sync
uid = root
gid = root
port= 873
EOF
#启动xinetd(xinetd是一个提供保姆服务的进程,rsync是它照顾的进程)
systemctl start xinetd
#创建认证密码文件,该密码应与slave服务器中的/etc/rsync.pass中的密码一致
cat >/etc/passwd.txt <<EOF
123456
EOF
#设置文件所有者读取、写入权限
chmod 600 /etc/passwd.txt
4.测试数据源master服务器192.168.153 到slave异地备份服务器192.168.153.154之间的数据同步
[root@mysql-master backup]# rsync -avH --port=873 --progress --delete /backup root@192.168.153.154::back_data --password-file=/etc/passwd.txt
sending incremental file list
backup/
backup/halou/
sent 89 bytes received 20 bytes 10.38 bytes/sec
total size is 0 speedup is 0.00
[root@mysql-master backup]#
5.数据源服务器上安装sersync工具,实现自动的实时的同步
一键安装安装sersync工具
[root@mysql-master ~]# vim onekey_install_sersync.sh
#!/bin/bash
cd ~
#修改inotify默认参数(inotify默认内核参数值太小)
sysctl -w fs.inotify.max_queued_events="99999999"
sysctl -w fs.inotify.max_user_watches="99999999"
sysctl -w fs.inotify.max_user_instances="65535"
#下载并安装sersync
yum install wget -y
wget http://down.whsir.com/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz
#解压并改名
tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz
mv /root/GNU-Linux-x86 /usr/local/sersync
#创建rsync
cd /usr/local/sersync/
cp confxml.xml confxml.xml.bak
cp confxml.xml data_configxml.xml #data_configxml.xml 是后面需要使用的配置文件
#修改data_configxml.xml配置文件
#修改需要备份的路径为/backup
sed -i 's/watch="\/opt\/tongbu"/watch="\/backup"/' /usr/local/sersync/data_configxml.xml
#修改服务器信息为slave4远程备份服务器
sed -i 's/ip="127.0.0.1" name="tongbu1"/ip="192.168.153.154" name="back_data"/' /usr/local/sersync/data_configxml.xml
#开启身份认证,修改密码文件为/etc/passwd.txt
sed -i 's/start="false" users="root" passwordfile="\/etc\/rsync.pas"/start="true" users="root" passwordfile="\/etc\/passwd.txt"/' /usr/local/sersync/data_configxml.xml
#添加到PATH变量
PATH=/usr/local/sersync/:$PATH
echo 'PATH=/usr/local/sersync/:$PATH' >>/root/.bashrc
#启动
sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
#设计开机启动
echo '/usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/data_configxml.xml' >>/etc/rc.local
永久修改参数方法
[root@mysql-master ~]# vim /etc/sysctl.conf
fs.inotify.max_queued_events=99999999
fs.inotify.max_user_watches=99999999
fs.inotify.max_user_instances=65535
查看是否成功
[root@mysql-master backup]# ps aux|grep sersync
root 30021 0.0 0.0 157860 704 ? Ssl 10:05 0:00 sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
root 30040 0.0 0.0 112824 972 pts/0 S+ 10:09 0:00 grep --color=auto sersync
[root@mysql-master backup]# mkdir gaofei
[root@mysql-master backup]# ls
gaofei halou
[root@mysql-salve4 ~]# cd /backup/
[root@mysql-salve4 backup]# ls
gaofei halou
[root@mysql-salve4 backup]#
6.创建计划任务
在master上操作
每天2:30进行数据库的备份脚本
[root@mysql-master ~]# crontab -e
30 2 * * * bash /root/backup_log.sh
[root@mysql-master ~]# crontab -l
30 2 * * * bash /root/backup_log.sh
[root@mysql-master ~]#
[root@mysql-master ~]# vim /root/backup_log.sh
mysqldump -uroot -p'Sanchuang123#' --all-databases >/backup/all_db.SQL
5.部署mysqlrouter中间件实现读写分离,安装keepalived部署双vip实现高可用
安装部署mysql-router软件
之前在playbook里边已经安装部署过了
[root@mysql-router1 ~]# ls
anaconda-ks.cfg node_exporter-1.6.1.linux-amd64.tar.gz
mysql-router-community-8.0.21-1.el7.x86_64.rpm node_exporter.sh
[root@mysql-router1 ~]#
1.修改配置文件
[root@mysql-router1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
#在最下边加上
#read
[routing:slave]
bind_address = 0.0.0.0:7001
destinations = 192.168.153.150:3306,192.168.153.151:3306
mode = read-only
connect_timeout = 1
#write and read
[routing:master]
bind_address = 0.0.0.0:7002
destinations = 192.168.153.150:3306
mode = read-write
connect_timeout = 1
2.启动MySQL router服务,监听了7001和7002端口
[root@mysql-router2 ~]# service mysqlrouter restart
Redirecting to /bin/systemctl restart mysqlrouter.service
[root@mysql-router1 ~]# netstat -anplut|grep mysql
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 27732/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 27732/mysqlrouter
[root@mysql-router1 ~]#
3.在master上创建两个用户实现读写分离
一个是读的,一个是写的
root@(none) 10:47 mysql>grant all on *.* to 'scwrite'@'%' identified by 'Sanchuang123#';
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@(none) 10:49 mysql>grant select on *.* to 'scread'@'%' identified by 'Sanchuang123#';
Query OK, 0 rows affected, 1 warning (0.00 sec)
由于实现了半同步复制,故需要将slave机器上面的scwrite用户删除
root@gaohui 10:50 mysql>drop user 'scwrite'@'%';
Query OK, 0 rows affected (0.01 sec)
root@gaohui 10:50 mysql>
4.测试
在只读账户中操作
在读写账户上操作
测试成功
5.安装keepalived部署双vip实现高可用
在mysql-route1上
[root@mysql-router1 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 88
priority 120
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.153.88
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 99
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.153.99
}
}
router1和route2的不同是优先级两个调换一下,这样就会形成双vip
在mysql-router2上
[root@mysql-router2 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 88
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.153.88
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 99
priority 120
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.153.99
}
}
记得刷新服务
[root@mysql-router1 ~]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[root@mysql-router1 ~]#
查看是否配置成功
#'router1:'
[root@mysql-router1 ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:fc:08:6a brd ff:ff:ff:ff:ff:ff
inet 192.168.153.146/24 brd 192.168.153.255 scope global noprefixroute dynamic ens33
valid_lft 1580sec preferred_lft 1580sec
inet 192.168.153.88/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::c143:aa4e:4eda:6960/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@mysql-router1 ~]#
#'router2:'
[root@mysql-router2 ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:10:87:f8 brd ff:ff:ff:ff:ff:ff
inet 192.168.153.147/24 brd 192.168.153.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.153.99/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe10:87f8/64 scope link
valid_lft forever preferred_lft forever
[root@mysql-router2 ~]#
6.搭建DNS服务器
参考文档:https://blog.csdn.net/qq_51235445/article/details/124898262?fromshare=blogdetail
#'关闭防火墙和selinux'
[root@dns ~]# systemctl stop firewalld
[root@dns ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@dns ~]# vim /etc/selinux/config
SELINUX=disabled
[root@dns ~]# su
[root@dns ~]# systemctl enable named #开机启动dns服务
Created symlink from /etc/systemd/system/multi-user.target.wants/named.service to /usr/lib/systemd/system/named.service.
[root@dns ~]# systemctl start named #启动dns服务
[root@dns ~]#
1.修改dns配置文件,任意ip可以访问本机的53端口,并且允许dns解析
[root@dns ~]# vim /etc/named.conf
options {
listen-on port 53 { any; }; #修改
listen-on-v6 port 53 { any; }; #修改
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
recursing-file "/var/named/data/named.recursing";
secroots-file "/var/named/data/named.secroots";
allow-query { any; }; #修改
#重启named服务
[root@dns ~]# service named restart
2.编辑dns次要配置文件/etc/named.rfc1912.zones,增加一条主域名记录
[root@dns named]# vim /etc/named.rfc1912.zones
zone "gaohui.com" IN {
type master; #类型为主域名
file "gaohui.com.zone"; #gaohui.com域名的数据文件,需要去/var/named/下创建
allow-update { none; };
};
[root@dns named]# ls
data dynamic named.ca named.empty named.localhost named.loopback slaves
[root@dns named]# cp -a named.localhost gaohui.com.zone
[root@dns named]# ls
data dynamic gaohui.com.zone named.ca named.empty named.localhost named.loopback slaves
[root@dns named]# vim gaohui.com.zone
$TTL 1D
@ IN SOA @ rname.invalid. (
0 ; serial
1D ; refresh
1H ; retry
1W ; expire
3H ) ; minimum
NS @
A 192.168.153.145
www IN A 192.168.153.88
www IN A 192.168.153.99
3.修改linux客户机的dns服务器的地址为搭建的dns服务器192.169.153.145
[root@dns named]# vim /etc/resolv.conf
#Generated by NetworkManager
#nameserver 114.114.114.114
nameserver 192.168.153.145
[root@dns named]# service named restart
Redirecting to /bin/systemctl restart named.service
4.查看效果
[root@dns named]# nslookup www.gaohui.com
Server: 192.168.153.145
Address: 192.168.153.145#53
Name: www.gaohui.com
Address: 192.168.153.99
Name: www.gaohui.com
Address: 192.168.153.88
[root@dns named]#
同一域名解析出了中间件mysqlrouter的双vip地址,实现了基于dns的负载均衡
7.监控部署
1.利用xftp把安装包拉取进来
[root@prometheus ~]# ls
anaconda-ks.cfg prometheus-2.47.0.linux-amd64.tar.gz
2.一键源码安装prometheus
[root@prometheus ~]# vim onekey_install_prometheus.sh
#!/bin/bash
#创建存放prometheus的目录
mkdir /prom
#解压并改名
tar xf ./prometheus-2.47.0.linux-amd64.tar.gz -C /prom
mv /prom/prometheus-2.47.0.linux-amd64 /prom/prometheus
#添加到PATH变量
PATH=/prom/prometheus:$PATH
echo "PATH=/prom/prometheus:$PATH " >>/root/.bashrc
#nohub后台执行启动
nohup prometheus --config.file=/prom/prometheus/prometheus.yml &
#关闭防火墙
service firewalld stop
systemctl disable firewalld
#关闭 selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
3.把prometheus做成一个服务来进行管理
[root@prometheus prometheus]# vim /usr/lib/systemd/system/prometheus.service
[Unit]
Description=prometheus
[Service]
ExecStart=/prom/prometheus/prometheus --config.file=/prom/prometheus/prometheus.yml
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
#重新加载systemd相关的服务
[root@prometheus prometheus]# systemctl daemon-reload
第一次因为是使用nohup 方式启动的prometheus,还是需要使用后kill 的方式杀死第一次启动的进程;后面可以使用service方式管理prometheus了
[root@prometheus prometheus]# ps aux|grep prometheus
root 24794 0.1 2.4 1316264 45584 pts/0 Sl 15:14 0:00 prometheus --config.file=/prom/prometheus/prometheus.yml
root 24900 0.0 0.0 112824 976 pts/0 S+ 15:19 0:00 grep --color=auto prometheus
[root@prometheus prometheus]# kill -9 24794
[root@prometheus prometheus]# ps aux|grep prometheus
root 24902 0.0 0.0 112824 972 pts/0 S+ 15:19 0:00 grep --color=auto prometheus
[root@prometheus prometheus]# service prometheus start
[root@prometheus prometheus]# service prometheus stop
4.在node节点服务器上安装exporter程序,已经在ansible的playbook中安装完成
5.在prometheus server里添加安装了exporter程序的机器
[root@prometheus prometheus]# vim /prom/prometheus/prometheus.yml
static_configs:
- targets: ["localhost:9090"]
#从这开始添加!!!!!!!!!!
- job_name: "master"
static_configs:
- targets: ["192.168.153.150:8090"]
- job_name: "slave1"
static_configs:
- targets: ["192.168.153.151:8090"]
- job_name: "slave2"
static_configs:
- targets: ["192.168.153.152:8090"]
- job_name: "slave3"
static_configs:
- targets: ["192.168.153.153:8090"]
- job_name: "slave4"
static_configs:
- targets: ["192.168.153.154:8090"]
- job_name: "mysqlrouter1"
static_configs:
- targets: ["192.168.153.155:8090"]
- job_name: "mysqlrouter2"
static_configs:
- targets: ["192.168.153.147:8090"]
刷新服务
#重启prometheus服务
[root@prometheus prometheus]# service prometheus restart
访问9090端口
6.配置部署grafana
先用迅雷下载https://dl.grafana.com/enterprise/release/grafana-enterprise-10.1.1-1.x86_64.rpm
再用xftp拖进来安装
[root@prometheus grafana]# ls
grafana-enterprise-10.1.1-1.x86_64.rpm
[root@prometheus grafana]# yum install grafana-enterprise-10.1.1-1.x86_64.rpm -y
启动grafana
[root@prometheus grafana]# service grafana-server start
Starting grafana-server (via systemctl): [ 确定 ]
[root@prometheus grafana]# systemctl enable grafana-server #设置开机自启动
grafana监听的端口号是3000
http://192.168.153.149:3000/
默认的用户名和密码是
用户名admin
密码admin
配置数据源
模板id号:8919
8.sysbench压力测试
1.安装sysbench工具
[root@localhost ~]# yum install epel-release -y
[root@localhost ~]# yum install sysbench -y
2.调大内核资源限制
[root@localhost ~]# ulimit -n 100000
[root@localhost ~]# ulimit -u 100000
[root@localhost ~]# ulimit -s 100000
3.在master上创建测试库
root@(none) 17:11 mysql>create database test_db; #创建测试库
Query OK, 1 row affected (0.00 sec)
4.基于sysbench构造测试表和测试数据
[root@localhost ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=www.gaohui.com --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=50 oltp_read_write --db-ps-mode=disable prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Initializing worker threads...
Creating table 'sbtest8'...
Creating table 'sbtest4'...
Creating table 'sbtest2'...
Creating table 'sbtest10'...
Creating table 'sbtest3'...
Inserting 50 records into 'sbtest4'
Inserting 50 records into 'sbtest8'
Inserting 50 records into 'sbtest10'
Inserting 50 records into 'sbtest3'
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest8'...
Inserting 50 records into 'sbtest2'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest6'...
Creating table 'sbtest9'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest1'...
Inserting 50 records into 'sbtest9'
Inserting 50 records into 'sbtest5'
Inserting 50 records into 'sbtest6'
Inserting 50 records into 'sbtest7'
Inserting 50 records into 'sbtest1'
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest1'...
[root@localhost ~]#
命令行中的参数说明:
**–db-driver=mysql:**代表数据库驱动
**–time=300:**这个就是说连续访问300秒
**–threads=10:**这个就是说用10个线程模拟并发访问
**–report-interval=1:**这个就是说每隔1秒输出一下压测情况
**–mysql-host=www.gaohui.com --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123#:**数据库的用户和密码等信息
**–mysql-db=test_db --tables=10 --table_size=50:**这一串的意思,就是说在test_db这个库里,构造10个测试表,每个测试表里构造50条测试数据,测试表的名字会是类似于sbtest1,sbtest2这个样子的
**oltp_read_write:**这个就是说,执行oltp数据库的读写测试
**–db-ps-mode=disable:**这个就是禁止ps模式
**prepare:**意思是参照这个命令的设置去构造出来我们需要的数据库里的数据,他会自动创建10个测试表,每个表里创建50条测试数据,所以这个工具是非常的方便的。
5.数据库读写性能测试(获取测试数据)把prepare改成run
这里有个问题 如果我用www.gaohui.com去登录,会超时
[root@localhost ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=192.168.153.88 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=50 oltp_read_write --db-ps-mode=disable run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 323.63 qps: 6666.45 (r/w/o: 4696.68/1308.52/661.25) lat (ms,95%): 34.95 err/s: 4.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 493.32 qps: 9932.49 (r/w/o: 6961.55/1980.29/990.65) lat (ms,95%): 27.66 err/s: 6.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 525.00 qps: 10661.98 (r/w/o: 7488.98/2112.00/1061.00) lat (ms,95%): 24.83 err/s: 9.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 532.99 qps: 10745.89 (r/w/o: 7530.92/2143.98/1070.99) lat (ms,95%): 24.38 err/s: 5.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 534.99 qps: 10804.84 (r/w/o: 7583.89/2144.97/1075.98) lat (ms,95%): 24.83 err/s: 6.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 542.03 qps: 10992.55 (r/w/o: 7714.38/2185.11/1093.05) lat (ms,95%): 24.38 err/s: 9.00 reconn/s: 0.00
[ 7s ] thds: 10 tps: 536.96 qps: 10831.28 (r/w/o: 7602.50/2149.86/1078.93) lat (ms,95%): 24.83 err/s: 6.00 reconn/s: 0.00
[ 8s ] thds: 10 tps: 546.04 qps: 10955.84 (r/w/o: 7668.59/2194.17/1093.08) lat (ms,95%): 23.10 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 10 tps: 546.93 qps: 10983.58 (r/w/o: 7694.00/2191.72/1097.86) lat (ms,95%): 23.52 err/s: 4.00 reconn/s: 0.00
[ 10s ] thds: 10 tps: 539.02 qps: 10761.49 (r/w/o: 7532.35/2149.10/1080.05) lat (ms,95%): 23.95 err/s: 2.00 reconn/s: 0.00
[ 11s ] thds: 10 tps: 534.02 qps: 10838.47 (r/w/o: 7609.33/2155.09/1074.05) lat (ms,95%): 23.95 err/s: 6.00 reconn/s: 0.00
执行完300s之后
SQL statistics:
queries performed:
read: 2215234 // 在300.0257秒的时间内执行了2215234次读请求
write: 628913 // 在300.0257秒的时间内执行了628913次写请求
other: 314876 // 在300.0257秒的时间内执行了314876次其他请求
total: 3159023 // 在300.0257秒的时间内执行了3159023次总的请求
transactions: 156645 (522.10 per sec.) // 在300.0257秒的时间内执行了156645次事务, 平均每秒执行522.10次
queries: 3159023 (10529.16 per sec.) // 在300.0257秒的时间内执行了3159023次查询, 平均每秒执行10529.16次
ignored errors: 1586 (5.29 per sec.) // 在300.0257秒的时间内发生了1586个忽略的错误, 平均每秒发生5.29个
reconnects: 0 (0.00 per sec.) // 在300.0257秒的时间内没有发生重新连接的次数
General statistics:
total time: 300.0257s // 总时间为300.0257秒
total number of events: 156645 // 总事件数量为156645个
Latency (ms):
min: 11.43 // 最小延迟为11.43毫秒
avg: 19.15 // 平均延迟为19.15毫秒
max: 65.59 // 最大延迟为65.59毫秒
95th percentile: 25.28 // 95% 的请求延迟在25.28毫秒以内
sum: 2999415.26 // 总延迟为2999415.26毫秒
Threads fairness:
events (avg/stddev): 15664.5000/50.32 // 事件的平均值为15664.5000,标准偏差为50.32
execution time (avg/stddev): 299.9415/0.01 // 执行时间的平均值为299.9415秒,标准偏差为0.01秒
6.执行完成压测之后可以将run改成cleanup,清除数据
[root@localhost ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=192.168.153.88 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=50 oltp_read_write --db-ps-mode=disable cleanup
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
[root@localhost ~]#
四.出现的问题
1.在使用ansble一键配置之后输入mysql无法找到命令
解决的办法是: 修改 ~/.bashrc
更改环境变量: export PATH=/usr/local/mysql/bin:/node_exporter:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:$PATH
最后再:source ~/.bashrc
[root@mysql-slave3 ~]# vim .bashrc
# .bashrc
# User specific aliases and functions
alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
export PATH=/usr/local/mysql/bin:/node_exporter:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:$PATH
[root@mysql-slave3 ~]# source ~/.bashrc
2.在配置grafana的时候没数据
很奇怪,Prometheus有数据,但是grafana没出图
解决办法
:
先看一下自带的Prometheus2.0有没有数据
如果有数据,那可能是模板不适配的问题
我换成了id为8919的版本,就出来效果了
3.压力测试的时候,由于内核参数的限制,导致无法起太多的线程
修改内核限制参数,以及调大mysql中与内核相关的参数
4.主从复制时,只在slave上进行了操作,导致事务数比主服务器还要多,主从复制一直起不来
查看报错信息,是事务数比主服务器还要多—>尽可能删除比主服务器还要多出来的数据,在reset master,并重新设置master_info信息
5.虚拟机内存不够,新建不了虚拟机
可以适当的给slave少一点内存,电脑内存实在不够,就换内存条吧
五.项目心得
1.一定要规划好整个集群的架构,配置要细心,脚本和软件要提前准备好,边做边修改
2.防火墙和selinux的必须都关闭
3.静态配置ip固定好,不要一关电脑,再打开ip变了
4.对MySQL的集群和高可用有了深入的理解
5.认识到了数据备份的重要性
6.深刻的体会到了rsync+sersync数据同步工具的便利与好处
7.对自动化批量部署和监控有了更加多的应用和理解
8.keepalived的配置需要更加细心和IP地址的规划有了新的认识
9.对双vip的使用,添加2条负载均衡记录实现dns轮询,达到向2个vip负载均衡器上分流