个人网站租用服务器,广告主平台,wordpress js版,西安百度推广开户多少钱目录
项目名称#xff1a; 基于Mysqlrouter MHA keepalived实现半同步主从复制MySQL Cluster
MySQL Cluster#xff1a;
项目架构图#xff1a;
项目环境#xff1a;
项目环境安装包#xff1a;
项目描述#xff1a;
项目IP地址规划#xff1a;
项目步骤:
一…目录
项目名称 基于Mysqlrouter MHA keepalived实现半同步主从复制MySQL Cluster
MySQL Cluster
项目架构图
项目环境
项目环境安装包
项目描述
项目IP地址规划
项目步骤:
一、准备10台全新虚拟机按照IP规划配置好静态IP修改主机名。安装部署Ansible并建立Ansible的免密通道调用一键二进制安装MySQL脚本自动化批量部署MySQL
1、根据ip规划配置好静态ip
2、修改主机名
3、建立Ansible免密通道
4、安装部署ansible
5、准备好MySQL软件包mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz上传到linux的root目录下并上传一键二进制安装mysql脚本
6、编写主机清单加入主机组
7、编写playbook上传源码包到远程服务器调用本地脚本二进制安装MySQL
8、检查yaml文件语法并执行yaml文件
9、查看Mysql是否安装成功
二、配置Mysql主从复制半同步模式semi-sync
1、在master服务器上开启二进制日志并配置server_id1并且在slave服务器上配置server_id2
2、刷新master和slave上的Mysql数据库服务
3、在master上创建可以给slave服务器过来复制二进制日志文件的用户
4、在master上做一个全备导出数据导入到slave上保持master和slave上的数据是一致的
5、再将导出的数据传出到slave服务器上去并将该sql文件导入Mysql数据库中
6、将该sql文件导入Mysql数据库中并检查我们的slave的Mysql数据是否与master的Mysql数据一致
7、在slave上配置master上拉取的二进制日志用户名和密码和日志文件名称和位置号和端口等信息
8、启动slave服务器查看IO线程和SQL线程是否正常启动
9、启用半同步复制
9.1、在主服务器master上执行安装
9.2、在从服务器slave上执行安装
9.3、接下来我们将启用我们下载好的插件在master服务器上
9.4、在slave服务器上启用我们下载好的插件
9.5、接下来我们将在slave从服务器上刷新服务
9.6、查看是否在slave上打开了半同步复制
9.7、验证是否可以实现同步操作
9.8、验证是否可以实现异步操作
三、rsyncsersync计划任务 实现实时同步全备数据数据备份
1、对master进行建库建表并插入数据用于模拟企业已经存在的真实数据也方便后续效果测试
2、计划任务定时全备数据
2.1、编写数据备份脚本
2.2、编写计划任务每天凌晨2点全备数据
3、rsyncsersync实现备份数据实时同步到异地备份服务器
rsync - backup备份服务器操作
3.1、新建/backup文件夹用于存放备份数据
3.2、安装rsync服务端软件
3.3、设置开机启动
3.4、启动xinetd查看进程看到进程说明xinetd已经启动成功
3.5、修改/etc/rsyncd.conf配置文件 注意[back_data]最好不要加注释防止出错
3.6、创建用户认证文件
3.7、设置文件权限
3.8、启动rsync查看对应的进程、端口 看到进程就说明rsync启动成功、xinetd监听873端口
rsync - master数据源服务器操作
3.9、在master上安装rsync、xinetd软件
3.10、启动xinetd查看进程
3.11、修改/etc/rsyncd.conf配置文件注意[sync]段配置文件最好不要加注释防止报错
3.12、创建用户认证文件
3.13、设置文件权限非root用户不可读认证文件
3.14、测试数据同步
3.15、数据源服务器增加文件或者删除文件看备份服务器/backup/是否有变化
4、安装sersync工具inotify实时触发rsync进行同步–数据源服务器操作
4.1、修改inotify默认参数inotify默认内核参数过小修改参数inotify在内核里已经安装了只需要传参
4.2、设置永久生效
4.3、装sersync软件包
4.4、解压sersync软件
4.5、创建sersync
4.6、修改配置 data_configxml.xml
4.7、加入PATH环境变量并设置开机永久生效
4.8、启动sersync启动成功如下
4.9、查看sersync进程
4.10、设置sersync监控开机自动执行
4.11、效果测试
四、Ansible批量部署mha软件环境搭建MHA高可用架构实现自动failover完成主从切换
1、编写主机清单增添4个mha node节点ip地址以及一个mha manager节点ip地址
2、编写一键安装mha node脚本和一键安装mha mangaer脚本
3、编写playbook上传源码包到家目录下调用本地脚本远程安装部署mha相关软件环境
4、执行playbook
5、所有服务器互相建立免密通道
5.1、mha manager对所有mysql服务器建立免密通道
5.2、master对slave1、slave2建立免密通道
5.3、slave1对master、slave2建立免密通道 5.4、slave2对master、slave1建立免密通道
6、在搭建好的主从复制服务器里配置mha相关信息
6.1、所有mysql服务器加入log_bin和log-slave-updates并刷新配置文件
6.2、所有mysql服务器master、slave1、slave2将mysql命令和mysqlbinlog命令软链接到/usr/sbin方便manager管理节点
6.3、所有mysql服务器新建允许manager访问的授权用户mha密码123456
7、在mha manager节点上配置好相关脚本、管理节点服务器
7.1、mha manager节点上复制相关脚本到/usr/local/bin下
7.2、复制自动切换时vip管理的脚本到/usr/local/bin下
7.3、修改master_ip_failover文件内容配置vip只配置vip相关参数其他默认不修改
7.4、创建 MHA 软件目录并复制配置文件使用app1.cnf配置文件来管理 mysql 节点服务器配置文件一般放在/etc/目录下注意注释只是提示用编辑配置文件时最好不要加注释否则很可能会出错
8、master服务器上手工开启vip
9、manager节点上测试ssh免密通道如果正常最后会输出successfully注意是否每台mysql间都建立了ssh免密通道否则会报错
10、在 manager 节点上测试 mysql 主从连接情况最后出现 MySQL Replication Health is OK 字样说明正常如果报错思考是否软链接建立好了或者主从复制搭建正确了
11、manager节点后台开启MHA
11.1、查看 MHA 状态可以看到当前的 master 是 Mysql1 节点
11.2、查看MHA日志看到当前matser是192.168.2.150
11.3、查看mha进程
五、搭建mysqlrouter-keepalived集群实现高可用 - 读写分离功能减轻主节点的压力
1、下载安装Mysql-routerMySQL :: Download MySQL Router
2、安装Mysql-router并解压
3、修改mysqlrouter配置文件mysqlrouter必须绑定到keepalived设定的vip上或者使用任意地址0.0.0.0上切记不能添加注释我添加是为了方便解释否则会报错
4、刷新mysqlrouter服务并查看端口
5、安装配置keepalived服务
6、修改keepalived的配置文件从36行以下的全部都不需要配置单vip的高可用服务
6.1、master上的配置
6.2、backup上的配置
7、重新启动keepalived服务
7.1、查看效果
8、模拟测试如果master挂掉了那么vip是否会转移到backup上去呢
8.1、我们将master上的keepalived关闭查看backup上的ip地址
8.2、backup上的IP地址
8.3、当我们重启mastervip又会从backup上漂移到master上去的
9、外部连接测试keepalived实现的高可用效果
10、配置双vip实现keepalived配置跟MySQL的主主复制十分相似
10.1、master配置文件
10.2、backup配置文件
10.3、刷新keepalived服务并查看master和backup的IP地址
六、使用sysbench压力测试工具测试整个数据库集群的性能了解集群系统性能资源的瓶颈
1、安装sysbench工具
2、调大内核资源限制
3、在master上创建用户和库配置用户的权限可以使他可以访问库Mysql的主从复制
4、基于sysbench构造测试表和测试数据
5、创建我们需要的数据库里的数据
6、数据库读写性能测试获取测试数据
7、执行完成压测之后可以将run改成cleanup清除数据
8、IO性能压力测试
8.1、创建5个文件总共500MB每个文件大概100MB
8.2、测试效果
8.3、清除数据
9、cpu性能压力测试
七、搭建基于prometheus grafana的监控系统对数据库集群进行监控
1、安装部署mysqld_exporter
2、上传软件包到linux的/root/目录下
3、解压并移动到/usr/local/mysqld_exporter下
4、在/usr/local/mysqld_exporter下编辑连接本机数据库的配置文件
5、将mysqld_exporter命令加入环境变量并设置开机自启
6、后台启动
7、看进程看端口网页访问测试
8、安装prometheus配置成服务
8.1、上传安装包到linux家目录下
8.2、解压Prometheus压缩包并移动到/prometheus目录下
8.3、将Prometheus配置成service服务
8.4、查看Prometheus端口
8.4、访问Prometheus服务页面
9、Prometheus添加node节点
9.1、刷新Prometheus服务
10、测试访问Prometheus服务:
11、安装grafana
11.1、上传grafana的rpm安装包
11.2、安装grafana
11.3、启动grafana
11.4、查看端口并访问测试grafana
12、在grafana中增添Prometheus数据源
13、添加Dashboards模板推荐使用14057模板因为14057模板比较美观出图效果也比较好推荐使用
14、grafana效果展示
八、搭建DNS主域名服务器增添两条负载均衡记录实现对mycat2双vip地址的DNS负载均衡
1、安装DNS服务
2、修改dns配置文件任意ip可以访问本机的53端口并且允许dns解析。
3、搭建主域名服务器
3.1、修改named.rfc1912.zones配置文件告诉named为claylpf.xyz提供域名解析
3.2、创建claylpf.xyz主域名的数据文件
3.3、修改claylpf.xyz.zone文件
3.4、刷新dns服务
4、效果测试
4.1、修改linux客户机的dns服务器的地址为搭建的dns服务器192.168.2.155
4.2、查看效果
项目结束
项目遇到的问题
项目心得 项目名称 基于Mysqlrouter MHA keepalived实现半同步主从复制MySQL Cluster
MySQL Cluster MySQL Cluster 是 MySQL 数据库的一种高可用性和高可扩展性解决方案特别适用于需要在分布式环境中提供实时访问和处理大量数据的应用。MySQL Cluster 使用了分布式架构和内存数据库技术可以提供高度可用的数据库服务同时支持水平扩展和垂直扩展。 项目架构图 项目环境 软件环境 CentOSCentOS Linux release 7.9.2009 (Core) MySQLmysql Ver 14.14 Distrib 5.7.41, for linux-glibc2.12 (x86_64) using EditLine wrapper mha managermha4mysql-manager-0.58 mha nodemha4mysql-node-0.58 MysqlrouterMySQL Router Ver 8.0.34 for Linux on x86_64 (MySQL Community - GPL) Keepalived: Keepalived v1.3.5 (03/19,2017) sysbenchsysbench 1.0.17 Ansibleansible 2.9.27 Prometheus: prometheus, version 2.43.0 mysqld_exportermysqld_exporter, version 0.15.1 Grafana: grafana 9.4.7 DNSBIND 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.13 项目环境安装包 链接https://pan.baidu.com/s/184n8h0mxUmt1_ETq3wTwRg?pwdfrm3 提取码frm3 项目描述 本项目旨在通过整合 MySQL Router、MHAMaster High Availability以及 Keepalived 等关键技术构建一个高可用半同步 MySQL 集群解决方案。通过该解决方案实现 MySQL 数据库的高可用性、负载均衡以及半同步主从复制以提供稳定和高效的数据库服务能方便处理大并发的后端MySQL业务。 项目IP地址规划 主机名 IP地址Linux客户机 192.168.2.77mysqlrouter1 192.168.2.181 vip 192.168.2.221mysqlrouter2 192.168.2.182 vip 192.168.2.201mha_manager、mha_node 192.168.2.141master、mha_node 192.168.2.150 vip 192.168.2.227slave1、mha_node 192.168.2.151 slave2、mha_node 192.168.2.152ansible 192.168.2.230sysbench 192.168.2.162prometheusgrafana 192.168.2.149dns 192.168.2.155 backup 192.168.2.157 项目步骤:
一、准备11台全新虚拟机按照IP规划配置好静态IP修改主机名。安装部署Ansible并建立Ansible的免密通道调用一键二进制安装MySQL脚本自动化批量部署MySQL
1、根据ip规划配置好静态ip
参考计算机网络 day6 arp病毒 - ICMP协议 - ping命令 - Linux手工配置IP地址_Claylpf的博客-CSDN博客
[rootmaster ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
BOOTPROTOstatic #静态配置ip
NAMEens33 #网卡名称ens33
DEVICEens33 #本机网卡ens33
ONBOOTyes #开机启动
IPADDR192.168.2.150 #ip地址
PREFIX24 #子网掩码24
GATEWAY192.168.2.1 #我选择的是桥接网络所以网关是路由器ip 192.168.2.1
DNS1114.114.114.114 #dns服务器114.114.114.114[rootmaster ~]# service network restart #重启网络
Restarting network (via systemctl): [ 确定 ]
[rootmaster ~]# ip add #查看ip
[rootmaster ~]# ping www.baidu.com #测试能否上网2、修改主机名
[rootmaster ~]# hostnamectl set-hostname master #修改指定主机名方便辨认
[rootmaster ~]# su -
3、建立Ansible免密通道
参考Linux - SSH服务 - SCP - 免密通道建立_服务器只允许堡垒机登录_Claylpf的博客-CSDN博客
[rootansible ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
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:wyZdVrUlL22pZs6sLTAXx/FTcrW66v8CDCtqrQEXBnA rootansible
The keys randomart image is:
---[RSA 2048]----
| ..E ..o |
| . . . ..*|
| o o . |
| . .o . oo |
| . .. S o .|
| o * . |
| .o . . |
| o.. .. |
| ... .ooo. |
----[SHA256]-----
[rootansible ~]# [rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150 #master[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151 #slave1[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152 #slave2[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.141 #mha_manager[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.149 #Prometheus[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.181 #mysqlrouter1[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.182 #mysqlrouter2[rootansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.162 #sysbench
4、安装部署ansible
参考ansible ansible的组成 - ansible的配置免密通道 - ansible 常用模块 - playbook模块的使用_Claylpf的博客-CSDN博客
[rootansible ~]# yum install -y epel-release
[rootansible ~]# yum install ansible
5、准备好MySQL软件包mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz上传到linux的root目录下并上传一键二进制安装mysql脚本
参考Mysql的介绍和软件环境的部署_mysql有软件吗_Claylpf的博客-CSDN博客 [rootmaster ~]# cat onekey_install_mysql_binary.sh
#!/bin/bash#步骤
#解决软件依赖关系
yum install cmake ncurses-devel gcc gcc-c vim lsof bzip2 openssl-devel -y#解压二进制安装包
tar xf mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz#移动mysql解压文件到/usr/local下改名为mysql
mv mysql-5.7.41-linux-glibc2.12-x86_64 /usr/local/mysql#新建组和用户 mysql
groupadd mysql#mysql这个用户的shell 是/bin/false 无法被调用 表示系统用户 属于mysql这个组
useradd -r -g mysql -s /bin/false mysql#进入/usr/local/mysql
cd /usr/local/mysql#关闭firewalld防火墙服务并且设置开机不启动
service firewalld stop
systemctl disable firewalld #临时关闭selinux,永久关闭selinux
setenforce 0
sed -i /^SELINUX/ s/enforcing/disabled/ /etc/selinux/config#mysql初始化操作
#创建/data/mysql文档用于存放数据使用
mkdir /data/mysql -p
#修改文件权限和组别这样mysql用户可以对这个文件夹进行读写操作了
chown mysql:mysql /data/mysql/
chmod 750 /data/mysql/#进入/usr/local/mysql/bin目录下进行初始化操作
cd /usr/local/mysql/bin
#设置启动用户为mysql base目录为/usr/local/mysql/ data目录为/data/mysql passwd.txt目的是将生成的临时密码存入passwd.txt文件中
./mysqld --initialize --usermysql --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表示最后一个字段#修改环境变量添加我们编译安装的mysql的可执行命令的路径
#临时修改PATH变量的值
export PATH/usr/local/mysql/bin/:$PATH
#永久修改
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内的第70行的内容(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-serverutf8
[mysql]
auto-rehash
prompt\\u\\d \\R:\\m mysql
EOF#启动mysqld服务
service mysqld start#将mysqld添加到linux系统里的服务管理名单里
chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on#登录重新设置初始密码为123456
#初次修改密码需要使用 --connect-expired-password 选项
#-e 后面接的命令是表示我们需要在mysql里执行的命令
#set password123456; 表示修改root用户的密码为123456
mysql -uroot -p$tem_passwd --connect-expired-password -e set password123456;#建议修改密码是否修改成功
mysql -uroot -p123456 -e show databases; echo database is installed success
[rootmaster ~]# 6、编写主机清单加入主机组
[rootansible ~]# cd /etc/ansible/
[rootansible ansible]# ls
ansible.cfg hosts nginx node_exporter roles
[rootansible ansible]# vim hosts
[rootansible ansible]# cat hosts
[mysql]
192.168.2.150 #master
192.168.2.151 #slave1
192.168.2.152 #slave2
[rootansible ansible]# 7、编写playbook上传源码包到远程服务器调用本地脚本二进制安装MySQL
[rootansible ansible]# vim software_install.yaml
[rootansible ansible]# cat software_install.yaml
- hosts: mysqlremote_user: roottasks:- name: copy file #上传本地源码包到mysql主机组copy: src/root/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz dest/root/- name: one key binary install mysql #调用本地二进制安装脚本远程执行安装mysqlscript: /root/onekey_install_mysql_binary.sh- name: alter path #确保mysql命令加入环境变量shell: export PATH/usr/local/mysql/bin/:$PATH
[rootansible ansible]# 使用ansible中的copy模块上传源码包时速度可能比较慢—可以考虑直接使用xshell里的xftp工具直接上传不写playbook 8、检查yaml文件语法并执行yaml文件
[rootansible ansible]# ansible-playbook --syntax-check /etc/ansible/software_install.yaml # 检查yaml文件语法playbook: /etc/ansible/software_install.yaml
[rootansible ansible]# ansible-playbook software_install.yaml # 执行yaml文件9、查看Mysql是否安装成功
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
database is installed success[rootslave1 ~]# ps aux|grep mysql
root 23541 0.0 0.1 11824 1596 pts/1 S 05:36 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir/data/mysql --pid-file/data/mysql/slave1.pid
mysql 23695 1.0 20.6 1544672 205120 pts/1 Sl 05:36 0:00 /usr/local/mysql/bin/mysqld --basedir/usr/local/mysql --datadir/data/mysql --plugin-dir/usr/local/mysql/lib/plugin --usermysql --log-errorslave1.err --open-files-limit8192 --pid-file/data/mysql/slave1.pid --socket/data/mysql/mysql.sock --port3306
root 23769 0.0 0.0 112824 988 pts/1 R 05:37 0:00 grep --colorauto mysql
[rootslave1 ~]# 二、配置Mysql主从复制半同步模式semi-sync
参考Mysql - 主从复制介绍_Claylpf的博客-CSDN博客
1、在master服务器上开启二进制日志并配置server_id1并且在slave服务器上配置server_id2
master上
[rootmaster ~]# cat /etc/my.cnf
[mysqld_safe][client]
socket/data/mysql/mysql.sock[mysqld]
socket/data/mysql/mysql.sock
log-error/data/mysql/mysql_error.log #表示设置Mysql的error日志的路径
slow_query_log 1 #表示开启Mysql慢日志
long_query_time 0.001 #表示设置慢日志的阈值为0.001毫秒
general_log #我们不需要添加路径了因为Mysql会自动帮助我们添加通用日志的路径和日志文件表示打开了通用日志
log_bin #表示我们开启了二进制日志
server_id 1 #给我们的Mysql服务器进行编号
expire_logs_days 7 #表示二进制日志文件过 7天 自动清除port 3306
open_files_limit 8192
innodb_buffer_pool_size 512M
character-set-serverutf8[mysql]
auto-rehash
prompt\u\d \R:\m mysql
[rootmaster ~]# slave上
[rootslave1 ~]# cat /etc/my.cnf
[mysqld_safe][client]
socket/data/mysql/mysql.sock[mysqld]
socket/data/mysql/mysql.sock
log-error/data/mysql/mysql_error.log #表示设置Mysql的error日志的路径
slow_query_log 1 #表示开启Mysql慢日志
long_query_time 0.001 #表示设置慢日志的阈值为0.001毫秒
general_log #我们不需要添加路径了因为Mysql会自动帮助我们添加通用日志的路径和日志文件表示打开了通用日志
log_bin #表示我们开启了二进制日志
server_id 2 #给我们的Mysql服务器进行编号
expire_logs_days 7 #表示二进制日志文件过 7天 自动清除port 3306
open_files_limit 8192
innodb_buffer_pool_size 512M
character-set-serverutf8[mysql]
auto-rehash
prompt\u\d \R:\m mysql
[rootslave1 ~]# 2、刷新master和slave上的Mysql数据库服务
[rootmaster ~]# systemctl restart mysqld3、在master上创建可以给slave服务器过来复制二进制日志文件的用户
[rootmaster ~]# mysql -u root -p123456
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 4
Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.#创建sc_slave用户允许任意网段访问
root(none) 06:01 mysqlcreate user sc_slave% identified by 123456;
Query OK, 0 rows affected (0.00 sec)#赋予该用户允许复制二进制日志文件的权限
root(none) 06:02 mysqlgrant replication slave on *.* to sc_slave%;
Query OK, 0 rows affected (0.00 sec)#重新加载用户权限表
root(none) 06:02 mysqlFLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)root(none) 06:02 mysql4、在master上做一个全备导出数据导入到slave上保持master和slave上的数据是一致的
#为master上的数据做全备并导出
[rootmaster ~]# mkdir /backup
[rootmaster ~]# mysqldump -uroot -p123456 --all-databases /backup/all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[rootmaster ~]# 5、再将导出的数据传出到slave服务器上去并将该sql文件导入Mysql数据库中
[rootmaster ~]# scp /backup/all_db.sql root192.168.2.151:/root
all_db.sql 100% 866KB 28.4MB/s 00:00
[rootmaster ~]# scp /backup/all_db.sql root192.168.2.152:/root
all_db.sql 100% 866KB 19.8MB/s 00:00
[rootmaster ~]# 6、将该sql文件导入Mysql数据库中并检查我们的slave的Mysql数据是否与master的Mysql数据一致
[rootslave1 ~]# mysql -uroot -p123456 all_db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[rootslave1 ~]# 7、在slave上配置master上拉取的二进制日志用户名和密码和日志文件名称和位置号和端口等信息
在master上查看二进制日志文件和位置号
[rootmaster mysql]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.# 刷新二进制文件 每运行一次flush logs; 会从新创建一个二进制文件
root(none) 06:33 mysqlflush logs;
Query OK, 0 rows affected (0.01 sec)# 查看正在使用的二进制文件和它对应的位置号
root(none) 06:33 mysqlshow master status;
--------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
--------------------------------------------------------------------------------
| master-bin.000002 | 154 | | | |
--------------------------------------------------------------------------------
1 row in set (0.00 sec)root(none) 06:33 mysql上图所示我通过flush logs命令刷新了二进制日志产生了一个新的二进制日志文件位置号为154。 在slave上配置关于master的配置并上拉取二进制日志的用户信息和日志文件的名称和位置号
[rootslave2 ~]# mysql -uroot -p123456
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 5
Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.# 在slave上配置master的信息
root(none) 06:35 mysqlCHANGE MASTER TO MASTER_HOST192.168.2.150,- MASTER_USERsc_slave,- MASTER_PASSWORD123456,- MASTER_PORT3306,- MASTER_LOG_FILEmaster-bin.000002,- MASTER_LOG_POS154;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
root(none) 06:35 mysql8、启动slave服务器查看IO线程和SQL线程是否正常启动 直接使用start slave命令 如果启动了可以查看如下 Slave_IO_Running: Yes Slave_SQL_Running: Yes 如果出现了上述内容说明Mysql的主从复制功能成功的搭建了 # 在slave上运行 目的是启动slave服务
root(none) 06:41 mysqlstart slave;
Query OK, 0 rows affected (0.00 sec)root(none) 06:41 mysqlshow slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.150Master_User: sc_slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000002Read_Master_Log_Pos: 154Relay_Log_File: slave1-relay-bin.000002Relay_Log_Pos: 321Relay_Master_Log_File: master-bin.000002Slave_IO_Running: Yes #说明主从复制服务建立成功Slave_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: 529Until_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: 0
Master_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: 4cb77830-3958-11ee-83a7-000c29615077Master_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 specifiedroot(none) 06:42 mysql9、启用半同步复制
9.1、在主服务器master上执行安装
root(none) 06:44 mysqlINSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so;
Query OK, 0 rows affected (0.01 sec)root(none) 06:44 mysql9.2、在从服务器slave上执行安装
root(none) 06:42 mysqlINSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so;
Query OK, 0 rows affected (0.01 sec)root(none) 06:45 mysql我们可以通过命令show plugins;命令查看我们mysql安装的插件。 9.3、接下来我们将启用我们下载好的插件在master服务器上
root(none) 06:44 mysqlSET GLOBAL rpl_semi_sync_master_enabled 1;
Query OK, 0 rows affected (0.00 sec)root(none) 06:47 mysql9.4、在slave服务器上启用我们下载好的插件
root(none) 06:45 mysqlSET GLOBAL rpl_semi_sync_slave_enabled 1;
Query OK, 0 rows affected (0.00 sec)root(none) 06:48 mysql9.5、接下来我们将在slave从服务器上刷新服务
root(none) 06:48 mysqlstop slave;
Query OK, 0 rows affected (0.00 sec)root(none) 06:49 mysqlstart slave;
Query OK, 0 rows affected (0.00 sec)root(none) 06:49 mysql9.6、查看是否在slave上打开了半同步复制
root(none) 06:51 mysqlshow global variables like %rpl_semi%;
----------------------------------------
| Variable_name | Value |
----------------------------------------
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
----------------------------------------
2 rows in set (0.00 sec)root(none) 06:51 mysql在master上查看
root(none) 06:47 mysqlshow global variables like %rpl_semi%;
-------------------------------------------------------
| Variable_name | Value |
-------------------------------------------------------
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
-------------------------------------------------------
6 rows in set (0.00 sec)root(none) 06:51 mysqlrpl_semi_sync_master_timeout, the value N is given in milliseconds. The default value is 10000 (10 seconds).意思是如果ack确认包在10秒钟内没有送达master会启用异步模式 9.7、验证是否可以实现同步操作
在master上创建一个新库
root(none) 06:51 mysqlcreate database liubo;
Query OK, 1 row affected (0.01 sec)在slave上查看是否存在liubo库
root(none) 06:53 mysqlshow databases;
--------------------
| Database |
--------------------
| information_schema |
| liubo |
| mysql |
| performance_schema |
| sys |
--------------------
5 rows in set (0.00 sec)root(none) 06:53 mysql9.8、验证是否可以实现异步操作
我们将slave服务中断然后在master上创建一个库10s过后查看slave服务器上是否产生了相同的库
slave服务中断
root(none) 06:53 mysqlstop slave;
Query OK, 0 rows affected (0.00 sec)在master上创建一个库
# 10.01 sec 表示该过程持续了10s
root(none) 06:53 mysqlcreate database liubo2;
Query OK, 1 row affected (10.01 sec) root(none) 06:56 mysql如上图所示一共维持了10s钟才完成了库的创建然后我们打开slave服务查看slave服务器上是否存在了liubo2库 root(none) 06:56 mysqlstart slave;
Query OK, 0 rows affected (0.00 sec)root(none) 06:57 mysqlshow databases;
--------------------
| Database |
--------------------
| information_schema |
| liubo |
| liubo2 |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)root(none) 06:58 mysql如上图所示当我们一打开slave服务databases里面就出现了了lao10这个库说明他变成了异步复制。而这两者的结合就是半同步复制。 三、rsyncsersync计划任务 实现实时同步全备数据数据备份
1、对master进行建库建表并插入数据用于模拟企业已经存在的真实数据也方便后续效果测试
root(none) 06:56 mysqlcreate database test1;
Query OK, 1 row affected (0.00 sec)root(none) 07:10 mysqluse test1;
Database changed
roottest1 07:11 mysqlcreate table t1(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)roottest1 07:11 mysqlinsert into t1 values(1,li);
Query OK, 1 row affected (0.04 sec)roottest1 07:11 mysqlinsert into t1 values(2,peng);
Query OK, 1 row affected (0.00 sec)roottest1 07:11 mysqlinsert into t1 values(3,fei);
Query OK, 1 row affected (0.00 sec)roottest1 07:11 mysqlselect * from t1;
----------
| id | name |
----------
| 1 | li |
| 2 | peng |
| 3 | fei |
----------
3 rows in set (0.00 sec)roottest1 07:11 mysql2、计划任务定时全备数据
2.1、编写数据备份脚本
[rootmaster ~]# cd /backup/
[rootmaster backup]# vim backup_db.sh
[rootmaster backup]# cat backup_db.sh
#!/bin/bash#导出数据库数据到/backup目录下以日期时间的格式命名
mysqldump -uroot -p123456# --all-databases /backup/$(date %F)_all_db.sql
[rootmaster backup]# 2.2、编写计划任务每天凌晨2点全备数据
[rootmaster backup]# crontab -e
no crontab for root - using an empty one
crontab: installing new crontab
[rootmaster backup]# crontab -l
30 2 * * * bash /backup/backup_db.sh
[rootmaster backup]# 3、rsyncsersync实现备份数据实时同步到异地备份服务器 数据源服务器master 192.168.2.150 备份服务器backup 192.168.31.157 rsync - backup备份服务器操作
3.1、新建/backup文件夹用于存放备份数据
[rootbackup ~]# mkdir /backup
[rootbackup ~]# 3.2、安装rsync服务端软件
[rootbackup ~]# yum install -y epel-release[rootbackup ~]# yum install -y rsync xinetd -y3.3、设置开机启动
[rootbackup ~]# echo /usr/bin/rsync --daemon --config/etc/rsyncd.conf #设置开机启动rsync /etc/rc.d/rc.local
/usr/bin/rsync --daemon --config/etc/rsyncd.conf
[rootbackup ~]# chmod x /etc/rc.d/rc.local #赋予可执行权限
[rootbackup ~]# 3.4、启动xinetd查看进程看到进程说明xinetd已经启动成功
[rootbackup ~]# systemctl start xinetd
[rootbackup ~]# ps aux|grep xinetd
root 13695 0.0 0.0 25044 588 ? Ss 06:20 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid
root 13697 0.0 0.0 112824 976 pts/1 S 06:20 0:00 grep --colorauto xinetd
[rootbackup ~]# xinetd就好像是一个保姆进程rsync是它照顾的进程 3.5、修改/etc/rsyncd.conf配置文件 注意[back_data]最好不要加注释防止出错
[rootbackup ~]# /etc/rsyncd.conf
[rootbackup ~]# vim /etc/rsyncd.conf
[rootbackup ~]# cat /etc/rsyncd.conf
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 storedignore errors yesread only nohosts allow 192.168.2.150 #允许的ip地址(数据源服务器地址)
[rootbackup ~]# 3.6、创建用户认证文件
[rootbackup ~]# vim /etc/rsyncd.pass
[rootbackup ~]# cat /etc/rsyncd.pass
backup:123456 #设置 认证用户和密码后面会用到
[rootbackup ~]# 注意不要添加注释
3.7、设置文件权限
[rootbackup ~]# chmod 600 /etc/rsyncd.conf
[rootbackup ~]# chmod 600 /etc/rsyncd.pass增强安全性非root用户不可读配置文件和认证文件 3.8、启动rsync查看对应的进程、端口 看到进程就说明rsync启动成功、xinetd监听873端口
[rootbackup ~]# /usr/bin/rsync --daemon --config/etc/rsyncd.conf
[rootbackup ~]# ps aux|grep rsyncd
root 13708 0.0 0.0 114852 572 ? Ss 06:28 0:00 /usr/bin/rsync --daemon --config/etc/rsyncd.conf
root 13710 0.0 0.0 112824 980 pts/1 R 06:29 0:00 grep --colorauto rsyncd
[rootbackup ~]# netstat -anplut|grep rsync
tcp 0 0 0.0.0.0:873 0.0.0.0:* LISTEN 13708/rsync
tcp6 0 0 :::873 :::* LISTEN 13708/rsync
[rootbackup ~]# rsync - master数据源服务器操作
3.9、在master上安装rsync、xinetd软件
[rootmaster backup]# yum install -y rsync xinetd3.10、启动xinetd查看进程
[rootmaster backup]# systemctl start xinetd
[rootmaster backup]# ps aux|grep xinetd
root 24033 0.0 0.0 25044 588 ? Ss 07:42 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid
root 24035 0.0 0.0 112824 984 pts/1 R 07:42 0:00 grep --colorauto xinetd
[rootmaster backup]# 3.11、修改/etc/rsyncd.conf配置文件注意[sync]段配置文件最好不要加注释防止报错
[rootmaster backup]# /etc/rsyncd.conf
[rootmaster backup]# cd ~
[rootmaster ~]# vim /etc/rsyncd.conf
[rootmaster ~]# cat /etc/rsyncd.conf
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 Syncuid rootgid rootport 873
[rootmaster ~]# 3.12、创建用户认证文件
[rootmaster ~]# vim /etc/passwd.txt
[rootmaster ~]# cat /etc/passwd.txt
123456 #该密码应与备份服务器中的/etc/rsync.pass中的密码一致
[rootmaster ~]# 3.13、设置文件权限非root用户不可读认证文件
[rootmaster ~]# chmod 600 /etc/passwd.txt3.14、测试数据同步
数据源服务器开启rysnc同步
[rootmaster ~]# rsync -avH --port873 --progress --delete /backup/ root192.168.2.157::back_data --password-file/etc/passwd.txtsending incremental file list
./
all_db.sql887,244 100% 67.91MB/s 0:00:00 (xfr#1, to-chk1/3)
backup_db.sh165 100% 13.43kB/s 0:00:00 (xfr#2, to-chk0/3)sent 887,818 bytes received 61 bytes 1,775,758.00 bytes/sec
total size is 887,409 speedup is 1.00
[rootmaster ~]# –port 备份服务器端rsync端口 /backup/ 数据源服务器上要备份的数据 root:192.168.2.157 备份服务器的用户和ip地址 如果有报错建议查看日志文件/var/log/rysncd.conf
3.15、数据源服务器增加文件或者删除文件看备份服务器/backup/是否有变化
数据源服务器新建文件夹并上传同步文件
[rootmaster ~]# cd /backup/
[rootmaster backup]# ls
all_db.sql backup_db.sh
[rootmaster backup]# touch test1.txt
[rootmaster backup]# ls
all_db.sql backup_db.sh test1.txt
# 使用rsync同步上传文件
[rootmaster backup]# rsync -avH --port873 --progress --delete /backup/ root192.168.2.157::back_data --password-file/etc/passwd.txtsending incremental file list
./
test1.txt0 100% 0.00kB/s 0:00:00 (xfr#1, to-chk0/4)sent 174 bytes received 42 bytes 432.00 bytes/sec
total size is 887,409 speedup is 4,108.38
[rootmaster backup]# 查看备份服务器/backup/的效果
[rootbackup ~]# cd /backup/
[rootbackup backup]# ls #没有同步之前
all_db.sql backup_db.sh
[rootbackup backup]# ls #同步之后
all_db.sql backup_db.sh test1.txt
[rootbackup backup]# 4、安装sersync工具inotify实时触发rsync进行同步–数据源服务器操作
4.1、修改inotify默认参数inotify默认内核参数过小修改参数inotify在内核里已经安装了只需要传参
[rootmaster backup]# cd ~
[rootmaster ~]# sysctl -w fs.inotify.max_queued_events99999999
fs.inotify.max_queued_events 99999999
[rootmaster ~]# sysctl -w fs.inotify.max_user_watches99999999
fs.inotify.max_user_watches 99999999
[rootmaster ~]# sysctl -w fs.inotify.max_user_instances65535
fs.inotify.max_user_instances 65535
[rootmaster ~]# 4.2、设置永久生效
[rootmaster ~]# vim /etc/sysctl.conf
fs.inotify.max_queued_events99999999
fs.inotify.max_user_watches99999999
fs.inotify.max_user_instances65535
4.3、装sersync软件包
[rootmaster ~]# wget http://down.whsir.com/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz4.4、解压sersync软件
[rootmaster ~]# tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz
4.5、创建sersync
[rootmaster ~]# mv GNU-Linux-x86/ /usr/local/sersync
[rootmaster ~]# cd /usr/local/sersync/
[rootmaster sersync]# ls
confxml.xml sersync2
[rootmaster sersync]# cp confxml.xml confxml.xml.bak #备份防止后续出错
[rootmaster sersync]# cp confxml.xml data_configxml.xm #增加数据配置
[rootmaster sersync]# ls
confxml.xml confxml.xml.bak data_configxml.xm sersync2
[rootmaster sersync]# 4.6、修改配置 data_configxml.xml
[rootmaster sersync]# vim data_configxml.xm #第24行位置sersynclocalpath watch/backup/ #本地要备份的数据文件remote ip192.168.31.210 nameback_data/ #备份服务器的ip地址以配置文件里的配置项!--remote ip192.168.8.39 nametongbu/--!--remote ip192.168.8.40 nametongbu/--/localpathrsynccommonParams params-artuz/auth startfalse usersroot passwordfile/etc/passwd.txt/ #认证用户和密码所在的路径userDefinedPort startfalse port874/!-- port874 --timeout startfalse time100/!-- timeout100 --ssh startfalse/4.7、加入PATH环境变量并设置开机永久生效
[rootmaster sersync]# PATH/usr/local/sersync/:$PATH
[rootmaster sersync]# which sersync2
/usr/local/sersync/sersync2
[rootmaster sersync]# echo PATH/usr/local/sersync/:$PATH /root/.bashrc
[rootmaster sersync]# 4.8、启动sersync启动成功如下
[rootmaster sersync]# sersync2 -d -r -o /usr/local/sersync/data_configxml.xm
set the system param
executeecho 50000000 /proc/sys/fs/inotify/max_user_watches
executeecho 327679 /proc/sys/fs/inotify/max_queued_events
parse the command param
option: -d run as a daemon
option: -r rsync all the local files to the remote servers before the sersync work
option: -o config xml name /usr/local/sersync/data_configxml.xm
daemon thread num: 10
parse xml config file
host ip : localhost host port: 8008
daemon startsersync run behind the console
config xml parse success
please set /etc/rsyncd.conf max connections0 Manually
sersync working thread 12 1(primary thread) 1(fail retry thread) 10(daemon sub threads)
Max threads numbers is: 22 12(Thread pool nums) 10(Sub threads)
please according your cpu use -n param to adjust the cpu rate
------------------------------------------
rsync the directory recursivly to the remote servers once
working please wait...
execute command: cd /backup rsync -artuz -R --delete ./ 192.168.2.157::back_data /dev/null 21
run the sersync:
watch path is: /backup
[rootmaster sersync]# 4.9、查看sersync进程
[rootmaster sersync]# ps aux|grep sersync
root 24100 0.0 0.0 92324 716 ? Ssl 08:06 0:00 sersync2 -d -r -o /usr/local/sersync/data_configxml.xm
root 24116 0.0 0.0 112824 984 pts/1 R 08:07 0:00 grep --colorauto sersync
[rootmaster sersync]# 4.10、设置sersync监控开机自动执行
[rootmaster sersync]# vim /etc/rc.d/rc.local
[rootmaster sersync]# cat /etc/rc.d/rc.local /usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/data_configxml.xm[rootmaster sersync]# 4.11、效果测试
数据源服务器新建文件夹
[rootmaster sersync]# cd /backup/
[rootmaster backup]# touch test2.txt
[rootmaster backup]# ls
all_db.sql backup_db.sh test1.txt test2.txt
[rootmaster backup]# backup备份服务器发现了实时同步的新建文件夹
[rootbackup backup]# ls
all_db.sql backup_db.sh test1.txt
[rootbackup backup]# ls
all_db.sql backup_db.sh test1.txt test2.txt
[rootbackup backup]# 测试成功rsyncsersync实时数据同步搭建成功
四、Ansible批量部署mha软件环境搭建MHA高可用架构实现自动failover完成主从切换
参考Mysql 搭建MHA高可用架构实现自动failover完成主从切换_Claylpf的博客-CSDN博客
1、编写主机清单增添4个mha node节点ip地址以及一个mha manager节点ip地址
[rootansible ansible]# vim /etc/ansible/hosts
[rootansible ansible]# cat /etc/ansible/hosts
[mha_manager]
192.168.2.141 #mha manager
[mha_node]
192.168.2.141 #mha manager
192.168.2.150 #master
192.168.2.151 #slave1
192.168.2.152 #slave2
[rootansible ansible]# 2、编写一键安装mha node脚本和一键安装mha mangaer脚本
mha node脚本
[rootansible ~]# cat onekey_install_mha_node.sh
#查看可以安装或者已安装的rpm包并且作缓存
yum list
#下载epel源
yum install epel-release --nogpgcheck -y
#下载依赖包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN
#软件包mha4mysql-node-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
#编译安装
perl Makefile.PL
make make install
mha manager脚本因为在安装mha node时已经安装好了所有的依赖包就可以直接进行解压编译安装了
[rootansible ~]# cat onekey_install_mha_manager.sh
#软件包mha4mysql-manager-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
#编译安装
perl Makefile.PL
make make install
3、编写playbook上传源码包到家目录下调用本地脚本远程安装部署mha相关软件环境
[rootansible ansible]# vim mha_install.yaml
[rootansible ansible]# cat mha_install.yaml
- hosts: mha_noderemote_user: roottasks: - name: copy filecopy: src/root/mha4mysql-node-0.58.tar.gz dest/root/- name: install mha_nodescript: /root/onekey_install_mha_node.sh
- hosts: mha_managerremote_user: roottasks:- name: copy filecopy: src/root/mha4mysql-manager-0.58.tar.gz dest/root/- name: install mha_managerscript: /root/onekey_install_mha_manager.sh
[rootansible ansible]# 4、执行playbook
[rootansible ansible]# ansible-playbook mha_install.yaml
5、所有服务器互相建立免密通道
参考Linux - SSH服务 - SCP - 免密通道建立_linux ssh服务状态_Claylpf的博客-CSDN博客
5.1、mha manager对所有mysql服务器建立免密通道 [rootmha_manager .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
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:36631NGvhLwX3HXPFgkfo8t/C0gk59hqkGi1cn0/cA rootmha_manager
The keys randomart image is:
---[RSA 2048]----
| |
| . o |
| . |
| o o. o|
| oS o.Eoo*|
| o o.ooo.*|
| . ..*.|
| .oBoo.|
| .o..o|
----[SHA256]-----
[rootmha_manager .ssh]# ssh-copy-id root192.168.2.150
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
/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 keys
root192.168.2.150s password: Number of key(s) added: 1Now try logging into the machine, with: ssh root192.168.2.150
and check to make sure that only the key(s) you wanted were added.[rootmha_manager .ssh]# [rootmha_manager .ssh]# ssh-copy-id root192.168.2.151[rootmha_manager .ssh]# ssh-copy-id root192.168.2.1525.2、master对slave1、slave2建立免密通道 [rootmysql-1 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:rB6Rg0nbJCHYxWxuBafl4HDB81RuuOpHC9/5LYRTAI rootmysql-1
The keys randomart image is:
---[RSA 2048]----
| ooBoE |
|. .*B.. . |
| oo..o. |
| .oB.o |
| . .Soo |
| .. |
| . . |
| o.o . |
| oo.. |
----[SHA256]-----
[rootmysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root192.168.2.151s password: Number of key(s) added: 1Now try logging into the machine, with: ssh 192.168.2.151
and check to make sure that only the key(s) you wanted were added.[rootmysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root192.168.2.152s password: Number of key(s) added: 1Now try logging into the machine, with: ssh 192.168.2.152
and check to make sure that only the key(s) you wanted were added.[rootmysql-1 ~]# 5.3、slave1对master、slave2建立免密通道 [rootmysql-2 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:MMCE8STghhwmha65CVG/w3/9k8/T96sFfcr75CFMTGs rootmysql-2
The keys randomart image is:
---[RSA 2048]----
|o* |
|B .. |
|o .. o . |
|o. . o o o |
|.o . . S E ..|
| ....|
|.o o . o.|
|o . . . o.|
| . ..B|
----[SHA256]-----
[rootmysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
The authenticity of host 192.168.2.150 (192.168.2.150) cant be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root192.168.2.150s password: Number of key(s) added: 1Now try logging into the machine, with: ssh 192.168.2.150
and check to make sure that only the key(s) you wanted were added.[rootmysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
The authenticity of host 192.168.2.152 (192.168.2.152) cant be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root192.168.2.152s password:
Permission denied, please try again.
root192.168.2.152s password: Number of key(s) added: 1Now try logging into the machine, with: ssh 192.168.2.152
and check to make sure that only the key(s) you wanted were added.[rootmysql-2 ~]# 5.4、slave2对master、slave1建立免密通道 [rootmysql-3 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:m6F9WyLFkNnweKy2ERj3LflPDHqU5ZULS8FpCbXhtw rootmysql-3
The keys randomart image is:
---[RSA 2048]----
| . o . o|
| X .o |
| . * X E..|
| * O o.|
| S o o..|
| B . o. .|
| . * o . .. |
| o |
| . |
----[SHA256]-----
[rootmysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
The authenticity of host 192.168.2.150 (192.168.2.150) cant be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root192.168.2.150s password: Number of key(s) added: 1Now try logging into the machine, with: ssh 192.168.2.150
and check to make sure that only the key(s) you wanted were added.[rootmysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: /root/.ssh/id_rsa.pub
The authenticity of host 192.168.2.151 (192.168.2.151) cant be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root192.168.2.151s password: Number of key(s) added: 1Now try logging into the machine, with: ssh 192.168.2.151
and check to make sure that only the key(s) you wanted were added.[rootmysql-3 ~]# 6、在搭建好的主从复制服务器里配置mha相关信息
6.1、所有mysql服务器加入log_bin和log-slave-updates并刷新配置文件
master rootmaster ~]# cat /etc/my.cnf
[mysqld_safe][client]
socket/data/mysql/mysql.sock[mysqld]
socket/data/mysql/mysql.sock
log-error/data/mysql/mysql_error.log #表示设置Mysql的error日志的路径
slow_query_log 1 #表示开启Mysql慢日志
long_query_time 0.001 #表示设置慢日志的阈值为0.001毫秒
general_log #我们不需要添加路径了因为Mysql会自动帮助我们添加通用日志的路径和日志文件表示打开了通用日志
log_bin #表示我们开启了二进制日志
server_id 1 #给我们的Mysql服务器进行编号
expire_logs_days 7 #表示二进制日志文件过 7天 自动清除
log-slave-updates #主从切换时主从服务器身份都可能对换从服务器重演relay_log日志操作时也写入自己的log_bin中port 3306
open_files_limit 8192
innodb_buffer_pool_size 512M
character-set-serverutf8[mysql]
auto-rehash
prompt\u\d \R:\m mysql
[rootmaster ~]# service mysqld restart
Shutting down MySQL............. SUCCESS!
Starting MySQL.. SUCCESS!
[rootmaster ~]# slave [rootslave1 ~]# vim /etc/my.cnf
[rootslave1 ~]# cat /etc/my.cnf
[mysqld_safe][client]
socket/data/mysql/mysql.sock[mysqld]
socket/data/mysql/mysql.sock
log-error/data/mysql/mysql_error.log #表示设置Mysql的error日志的路径
slow_query_log 1 #表示开启Mysql慢日志
long_query_time 0.001 #表示设置慢日志的阈值为0.001毫秒
general_log #我们不需要添加路径了因为Mysql会自动帮助我们添加通用日志的路径和日志文件表示打开了通用日志
log_bin #表示我们开启了二进制日志
server_id 2 #给我们的Mysql服务器进行编号
expire_logs_days 7 #表示二进制日志文件过 7天 自动清除
log-slave-updates #主从切换时主从服务器身份都可能对换从服务器重演relay_log日志操作时也写入自己的log_bin中port 3306
open_files_limit 8192
innodb_buffer_pool_size 512M
character-set-serverutf8[mysql]
auto-rehash
prompt\u\d \R:\m mysql
[rootslave1 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[rootslave1 ~]# 6.2、所有mysql服务器master、slave1、slave2将mysql命令和mysqlbinlog命令软链接到/usr/sbin方便manager管理节点 [rootmaster ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[rootmaster ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[rootsalve1 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[rootsalve1 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[rootsalve2 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[rootsalve2 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ 6.3、所有mysql服务器新建允许manager访问的授权用户mha密码123456 root(none) 08:38 mysqlgrant all on *.* to mha192.168.2.% identified by 123456;
Query OK, 0 rows affected, 1 warning (0.01 sec)root(none) 08:39 mysqlgrant all on *.* to mha192.168.2.150 identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)root(none) 08:39 mysqlgrant all on *.* to mha192.168.2.151 identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)root(none) 08:39 mysqlgrant all on *.* to mha192.168.2.152 identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)root(none) 08:40 mysqlselect user,host from mysql.user;
------------------------------
| user | host |
------------------------------
| sc_slave | % |
| mha | 192.168.2.% |
| mha | 192.168.2.150 |
| mha | 192.168.2.151 |
| mha | 192.168.2.152 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
------------------------------
8 rows in set (0.00 sec)root(none) 08:40 mysql7、在mha manager节点上配置好相关脚本、管理节点服务器
7.1、mha manager节点上复制相关脚本到/usr/local/bin下
[rootmha_manager ~]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
#-r 复制目录
#-p 同时复制文件访问权限
[rootmha_manager ~]# cd /usr/local/bin/
[rootmha_manager bin]# ls
scripts
[rootmha_manager bin]#
[rootmha_manager bin]# cd scripts/
[rootmha_manager scripts]# ls
master_ip_failover master_ip_online_change power_manager send_report
[rootmha_manager scripts]# 7.2、复制自动切换时vip管理的脚本到/usr/local/bin下
[rootmha_manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[rootmha_manager scripts]# cd ..
[rootmha_manager bin]# ls
master_ip_failover scripts
[rootmha_manager bin]# 7.3、修改master_ip_failover文件内容配置vip只配置vip相关参数其他默认不修改
[rootmha_manager bin]# /usr/local/bin/master_ip_failover # 清空文件
[rootmha_manager bin]# vim /usr/local/bin/master_ip_failover
[rootmha_manager bin]# cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use 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.2.227; #指定vip的地址自己指定
my $brdc 192.168.2.255; #指定vip的广播地址
my $ifdev ens33; #指定vip绑定的网卡
my $key 1; #指定vip绑定的虚拟网卡序列号
my $ssh_start_vip /sbin/ifconfig ens33:$key $vip; #代表此变量值为ifconfig ens33:1 192.168.2.227
my $ssh_stop_vip /sbin/ifconfig ens33:$key down; #代表此变量值为ifconfig ens33:1 192.168.2.227 down
my $exit_code 0; #指定退出状态码为0
#my $ssh_start_vip /usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;;
#my $ssh_stop_vip /usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key;
##################################################################################
GetOptions(
commands \$command,
ssh_users \$ssh_user,
orig_master_hosts \$orig_master_host,
orig_master_ips \$orig_master_ip,
orig_master_porti \$orig_master_port,
new_master_hosts \$new_master_host,
new_master_ips \$new_master_ip,
new_master_porti \$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 Disabling the VIP on old master: $orig_master_host \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 Enabling the VIP - $vip on the new master - $new_master_host \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;
exit 0;
}
else {
usage();
exit 1;
}
}
sub start_vip() {
ssh $ssh_user\$new_master_host \ $ssh_start_vip \;
}
## A simple system call that disable the VIP on the old_master
sub stop_vip() {
ssh $ssh_user\$orig_master_host \ $ssh_stop_vip \;
}sub usage {
print
Usage: master_ip_failover --commandstart|stop|stopssh|status --orig_master_hosthost --orig_master_ipip --orig_master_portport --new_master_hosthost --new_master_ipip --new_master_portport\n;
}
[rootmha_manager bin]# 7.4、创建 MHA 软件目录并复制配置文件使用app1.cnf配置文件来管理 mysql 节点服务器配置文件一般放在/etc/目录下注意注释只是提示用编辑配置文件时最好不要加注释否则很可能会出错
[rootmha_manager masterha]# app1.cnf
[rootmha_manager masterha]# vim app1.cnf
[rootmha_manager masterha]# cat app1.cnf
[server default]
manager_log/var/log/masterha/app1/manager.log #manager日志
manager_workdir/var/log/masterha/app1.log #manager工作目录
master_binlog_dir/data/mysql/ #master保存binlog的位置这里的路径要与master里配置的binlog的路径一致以便MHA能找到
master_ip_failover_script/usr/local/bin/master_ip_failover #设置自动failover时候的切换脚本也就是上面的那个脚本
master_ip_online_change_script/usr/local/bin/master_ip_online_change #设置手动切换时候的切换脚本
usermha #设置监控用户mha
password123456 #设置mysql中mha用户的密码这个密码是前文中创建监控用户的那个密码
ping_interval1 #设置监控主库发送ping包的时间间隔1秒默认是3秒尝试三次没有回应的时候自动进行failover
remote_workdir/tmp #设置远端mysql在发生切换时binlog的保存位置
repl_userslave #设置复制用户的用户slave
repl_password123456 #设置复制用户slave的密码
report_script/usr/local/send_report #设置发生切换后发送的报警的脚本
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152 #指定检查的从服务器IP地址
shutdown_script #设置故障发生后关闭故障主机脚本该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用
ssh_userroot #设置ssh的登录用户名[server1]
#master
hostname192.168.2.150
port3306[server2]
#slave1
hostname192.168.2.151
port3306
candidate_master1
#设置为候选master设置该参数以后发生主从切换以后将会将此从库提升为主库即使这个主库不是集群中最新的slavecheck_repl_delay0
#默认情况下如果一个slave落后master 超过100M的relay logs的话MHA将不会选择该slave作为一个新的master 因为对于这个slave的恢复需要花费很长时间通过设置check_repl_delay0MHA触发切换在选择一个新的master的时候将会忽略复制延时这个参数对于设置了candidate_master1的主机非常有用因为这个候选主在切换的过程中一定是新的master[server3]
#slave2
hostname192.168.2.152
port3306
[rootmha_manager masterha]# 8、master服务器上手工开启vip
[rootmaster ~]# ifconfig ens33:1 192.168.2.227/24
[rootmaster ~]# ip add
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 forever
2: ens33: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:61:50:77 brd ff:ff:ff:ff:ff:ffinet 192.168.2.150/24 brd 192.168.2.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.2.227/24 brd 192.168.2.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe61:5077/64 scope link valid_lft forever preferred_lft forever
[rootmysql-1 ~]#
9、manager节点上测试ssh免密通道如果正常最后会输出successfully注意是否每台mysql间都建立了ssh免密通道否则会报错
[rootmha_manager masterha]# masterha_check_ssh -conf/etc/masterha/app1.cnf
Mon Aug 14 07:00:46 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Aug 14 07:00:46 2023 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Aug 14 07:00:46 2023 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Aug 14 07:00:46 2023 - [info] Starting SSH connection tests..
Mon Aug 14 07:00:48 2023 - [debug]
Mon Aug 14 07:00:46 2023 - [debug] Connecting via SSH from root192.168.2.150(192.168.2.150:22) to root192.168.2.151(192.168.2.151:22)..
Mon Aug 14 07:00:47 2023 - [debug] ok.
Mon Aug 14 07:00:47 2023 - [debug] Connecting via SSH from root192.168.2.150(192.168.2.150:22) to root192.168.2.152(192.168.2.152:22)..
Mon Aug 14 07:00:48 2023 - [debug] ok.
Mon Aug 14 07:00:49 2023 - [debug]
Mon Aug 14 07:00:46 2023 - [debug] Connecting via SSH from root192.168.2.151(192.168.2.151:22) to root192.168.2.150(192.168.2.150:22)..
Mon Aug 14 07:00:48 2023 - [debug] ok.
Mon Aug 14 07:00:48 2023 - [debug] Connecting via SSH from root192.168.2.151(192.168.2.151:22) to root192.168.2.152(192.168.2.152:22)..
Mon Aug 14 07:00:49 2023 - [debug] ok.
Mon Aug 14 07:00:49 2023 - [debug]
Mon Aug 14 07:00:47 2023 - [debug] Connecting via SSH from root192.168.2.152(192.168.2.152:22) to root192.168.2.150(192.168.2.150:22)..
Mon Aug 14 07:00:48 2023 - [debug] ok.
Mon Aug 14 07:00:48 2023 - [debug] Connecting via SSH from root192.168.2.152(192.168.2.152:22) to root192.168.2.151(192.168.2.151:22)..
Mon Aug 14 07:00:49 2023 - [debug] ok.
Mon Aug 14 07:00:49 2023 - [info] All SSH connection tests passed successfully.
[rootmha_manager masterha]# 10、在 manager 节点上测试 mysql 主从连接情况最后出现 MySQL Replication Health is OK 字样说明正常如果报错思考是否软链接建立好了或者主从复制搭建正确了
[rootmha_manager masterha]# masterha_check_repl -conf/etc/masterha/app1.cnf
MySQL Replication Health is OK.
11、manager节点后台开启MHA
[rootmha_manager masterha]# nohup masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /var/log/masterha/app1/manager.log 21
[1] 1762111.1、查看 MHA 状态可以看到当前的 master 是 Mysql1 节点
[rootmha_manager masterha]# masterha_check_status --conf/etc/masterha/app1.cnf
app1 (pid:17621) is running(0:PING_OK), master:192.168.2.150
11.2、查看MHA日志看到当前matser是192.168.2.150
[rootmha_manager masterha]# cat /var/log/masterha/app1/manager.log | grep current master
Mon May 8 11:57:07 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.2.150(192.168.2.150:3306) (current master)11.3、查看mha进程
[rootmha_manager bin]# ps aux|grep manager
root 17624 0.1 4.5 299656 21992 pts/0 S 11:57 0:12 perl /usr/local/bin/masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover
root 17826 0.0 0.0 112824 984 pts/2 R 07:22 0:00 grep --colorauto manager
五、搭建mysqlrouter-keepalived集群实现高可用 - 读写分离功能减轻主节点的压力
参考Mysql - 配置Mysql主从复制-keepalived高可用-读写分离集群_Claylpf的博客-CSDN博客
Mysql - 读写分离_mysql读写分离的工具_Claylpf的博客-CSDN博客
有两台mysqlrouter服务器由于篇幅有限我就只展示了一台另外一台操作基本一致的
1、下载安装Mysql-routerMySQL :: Download MySQL Router
[rootmysqlrouter-1 ~]# ls
anaconda-ks.cfg mysql-router-community-8.0.34-1.el7.x86_64.rpm
[rootmysqlrouter-1 ~]# 注下载安装的mysqlrouter的时候必须注意版本否则解压安装的时候会报错 2、安装Mysql-router并解压 3、修改mysqlrouter配置文件mysqlrouter必须绑定到keepalived设定的vip上或者使用任意地址0.0.0.0上切记不能添加注释我添加是为了方便解释否则会报错
[rootmysqlrouter-1 mysqlrouter]# cat mysqlrouter.conf
# Copyright (c) 2015, 2023, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA#
# MySQL Router configuration file
#
# Documentation is available at
# http://dev.mysql.com/doc/mysql-router/en/[DEFAULT]
logging_folder /var/log/mysqlrouter
runtime_folder /run/mysqlrouter
config_folder /etc/mysqlrouter[logger]
level INFO# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval 60[routing:read_write]
bind_address 192.168.2.221 #vip地址 虚拟IP地址可以在keepalived配置之前确定 当然我们可以使用0.0.0.0任意IP地址,那么不管是vip还是本机的ip地址所对应的7001端口都能访问到
bind_port 7001 #自己的端口号
destinations 192.168.2.150:3306 #设置目的机器IP地址,可以填master的IP端口为mysqld运行的端口
mode read-write #设置目的机器IP地址,可以填master的IP端口为mysqld运行的端口
max_connections 65535
max_connect_errors 100
client_connect_timeout 9[routing:read_only_1]
bind_address 192.168.2.221
bind_port 7002
destinations 192.168.2.151:3306 #设置目的机器IP地址,可以填master的IP端口为mysqld运行的端口
mode read-only
max_connections 65535
max_connect_errors 100
client_connect_timeout 9[routing:read_only_2]
bind_address 192.168.2.221
bind_port 7003
destinations 192.168.2.152:3306 #设置目的机器IP地址,可以填master的IP端口为mysqld运行的端口
mode read-only
max_connections 65535
max_connect_errors 100
client_connect_timeout 9
[rootmysqlrouter-1 mysqlrouter]#
4、刷新mysqlrouter服务并查看端口
[rootmysqlrouter-1 mysqlrouter]# service mysqlrouter restart
Redirecting to /bin/systemctl restart mysqlrouter.service
[rootmysqlrouter-1 mysqlrouter]# [rootmysqlrouter-1 mysqlrouter]# netstat -anpult |grep mysqlrouter
tcp 0 0 192.168.2.221:7001 0.0.0.0:* LISTEN 42847/mysqlrouter
tcp 0 0 192.168.2.221:7002 0.0.0.0:* LISTEN 42847/mysqlrouter
tcp 0 0 192.168.2.221:7003 0.0.0.0:* LISTEN 42847/mysqlrouter
[rootmysqlrouter-1 mysqlrouter]#
5、安装配置keepalived服务
下载keepalived服务
[rootmysqlrouter-1 mysqlrouter]# yum install keepalived -y
已安装:keepalived.x86_64 0:1.3.5-19.el7 作为依赖被安装:lm_sensors-libs.x86_64 0:3.4.0-8.20160601gitf9185e5.el7 net-snmp-agent-libs.x86_64 1:5.7.2-49.el7_9.2 net-snmp-libs.x86_64 1:5.7.2-49.el7_9.2 完毕
6、修改keepalived的配置文件从36行以下的全部都不需要配置单vip的高可用服务
6.1、master上的配置
global_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr
# vrrp_strict # 这一行需要注释原因是因为会在iptables里添加一条规则会阻止网络通信导致出现脑裂现象vrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 { # 定义一个vrrp协议的实例 名字是VI_1 表示第一个vrrp实例state MASTER # 做master角色interface ens33 # 指定监听网络的接口其实就是vip绑定到那个网络接口上virtual_router_id 81 # 虚拟路由器ID 81表示的是编号另外一台机器上的配置也必须是81priority 160 # 优先级 数字越大就越容易成为masteradvert_int 1 # 宣告消息的时间间隔 为1sauthentication {auth_type PASS # 密码认证 passwordauth_pass 1111 # 具体密码 可以不用修改}virtual_ipaddress { # vip 虚拟ip地址 可以配置多个192.168.2.221 # 配置的vip#192.168.200.17#192.168.200.18}
}6.2、backup上的配置
[rootmysqlrouter-2 keepalived]# cat keepalived.conf
global_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr# vrrp_strict # 这一行需要注释原因是因为会在iptables里添加一条规则会阻止网络通信导致出现脑裂现象vrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 { # 定义一个vrrp协议的实例 名字是VI_1 表示第一个vrrp实例state BACKUP # 做backup角色interface ens33 # 指定监听网络的接口其实就是vip绑定到那个网络接口上virtual_router_id 81 # 虚拟路由器ID 81表示的是编号另外一台机器上的配置也必须是81priority 100 # 优先级 数字越大就越容易成为masteradvert_int 1 # 宣告消息的时间间隔 为1sauthentication {auth_type PASS # 密码认证 passwordauth_pass 1111 # 具体密码 可以不用修改}virtual_ipaddress { # vip 虚拟ip地址 可以配置多个192.168.2.221 # 配置的vip 必须与master配置的vip是一样的#192.168.200.17#192.168.200.18}
}
[rootmysqlrouter-2 keepalived]# 7、重新启动keepalived服务
[rootmysqlrouter-1 keepalived]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[rootmysqlrouter-1 keepalived]# 7.1、查看效果
[rootmysqlrouter-1 keepalived]# ip add
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 forever
2: ens33: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:90:87:dc brd ff:ff:ff:ff:ff:ffinet 192.168.2.181/24 brd 192.168.2.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.2.221/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe90:87dc/64 scope link valid_lft forever preferred_lft forever
[rootmysqlrouter-1 keepalived]# 8、模拟测试如果master挂掉了那么vip是否会转移到backup上去呢
8.1、我们将master上的keepalived关闭查看backup上的ip地址
[rootmysqlrouter-1 keepalived]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
[rootmysqlrouter-1 keepalived]# 8.2、backup上的IP地址
[rootmysqlrouter-2 keepalived]# ip add
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 forever
2: ens33: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:a9:7f:87 brd ff:ff:ff:ff:ff:ffinet 192.168.2.182/24 brd 192.168.2.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.2.221/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fea9:7f87/64 scope link valid_lft forever preferred_lft forever
[rootmysqlrouter-2 keepalived]# 成功观察到我们想要的效果vip真的漂移到了backup上了
8.3、当我们重启mastervip又会从backup上漂移到master上去的
[rootmysqlrouter-1 keepalived]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service
[rootmysqlrouter-1 keepalived]# ip add
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 forever
2: ens33: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:90:87:dc brd ff:ff:ff:ff:ff:ffinet 192.168.2.181/24 brd 192.168.2.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.2.221/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe90:87dc/64 scope link valid_lft forever preferred_lft forever
[rootmysqlrouter-1 keepalived]# 9、外部连接测试keepalived实现的高可用效果
当我通过Linux上访问192.168.2.221:7001端口的时候他会访问到我们的master机器的3306端口上去Mysql数据库的默认端口是3306
[rootmysql-2 ~]# mysql -h 192.168.2.221 -P 7001 -u claylpf -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.41-log MySQL Community Server (GPL)Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.claylpf(none) 18:54 mysqlexit
Bye
[roottest ~]# [rootmysql-2 ~]# mysql -h 192.168.2.221 -P 7002 -u claylpf -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.41 MySQL Community Server (GPL)Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.claylpf(none) 19:15 mysql10、配置双vip实现keepalived配置跟MySQL的主主复制十分相似 双 VIP 可以用于实现高可用性架构。在一个服务器出现故障时流量可以被自动切换到另一个服务器从而保持服务的连续性。这在负载均衡、高可用的网络应用中特别有用。 10.1、master配置文件
[rootmysqlrouter-1 keepalived]# cat keepalived.conf
global_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr# vrrp_strict vrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 81 priority 160 advert_int 1 authentication {auth_type PASS auth_pass 1111 }virtual_ipaddress { 192.168.2.221 }
}vrrp_instance VI_2 { state BACKUP interface ens33 virtual_router_id 101 priority 100 advert_int 1 authentication {auth_type PASS auth_pass 1111 }virtual_ipaddress { 192.168.2.201 }
}
[rootmysqlrouter-1 keepalived]# 10.2、backup配置文件
[rootmysqlrouter-2 keepalived]# cat keepalived.conf
global_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr# vrrp_strict # 这一行需要注释原因是因为会在iptables里添加一条规则会阻止网络通信导致出现脑裂现象vrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 { # 定义一个vrrp协议的实例 名字是VI_1 表示第一个vrrp实例state BACKUP # 做backup角色interface ens33 # 指定监听网络的接口其实就是vip绑定到那个网络接口上virtual_router_id 81 # 虚拟路由器ID 81表示的是编号另外一台机器上的配置也必须是51priority 100 # 优先级 数字越大就越容易成为masteradvert_int 1 # 宣告消息的时间间隔 为1sauthentication {auth_type PASS # 密码认证 passwordauth_pass 1111 # 具体密码 可以不用修改}virtual_ipaddress { # vip 虚拟ip地址 可以配置多个192.168.2.221 # 配置的vip 必须与master配置的vip是一样的}
}vrrp_instance VI_2 { # 定义一个vrrp协议的实例 名字是VI_1 表示第一个vrrp实例state MASTER # 做backup角色interface ens33 # 指定监听网络的接口其实就是vip绑定到那个网络接口上virtual_router_id 101 # 虚拟路由器ID 101表示的是编号另外一台机器上的配置也必须是101priority 120 # 优先级 数字越大就越容易成为masteradvert_int 1 # 宣告消息的时间间隔 为1sauthentication {auth_type PASS # 密码认证 passwordauth_pass 1111 # 具体密码 可以不用修改}virtual_ipaddress { # vip 虚拟ip地址 可以配置多个192.168.2.201 # 配置的vip 必须与master配置的vip是一样的}
}
[rootmysqlrouter-2 keepalived]# 10.3、刷新keepalived服务并查看master和backup的IP地址 六、使用sysbench压力测试工具测试整个数据库集群的性能了解集群系统性能资源的瓶颈
参考Mysql压力测试sysbench_Claylpf的博客-CSDN博客
1、安装sysbench工具
[rootab ~]# yum install epel-release -y[rootab ~]# yum install sysbench -y已安装:sysbench.x86_64 0:1.0.17-2.el7 作为依赖被安装:ck.x86_64 0:0.5.2-2.el7 luajit.x86_64 0:2.0.5-1.20220913.46e62cd.el7 postgresql-libs.x86_64 0:9.2.24-8.el7_9 完毕2、调大内核资源限制
[rootsysbench ~]# ulimit -n 100000
[rootsysbench ~]# ulimit -u 100000
[rootsysbench ~]# ulimit -s 1000003、在master上创建用户和库配置用户的权限可以使他可以访问库Mysql的主从复制
CREATE USER claylpf% IDENTIFIED BY 123456; #创建用户create database test_db; #创建测试库GRANT ALL PRIVILEGES ON test_db.* TO claylpf%; #配置用户权限FLUSH PRIVILEGES; #重新加载用户权限表
4、基于sysbench构造测试表和测试数据
[rootab ~]# sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host192.168.2.221 --mysql-port7001 --mysql-userclaylpf --mysql-password123456 --mysql-dbtest_db --tables10 --table_size1000 oltp_read_write --db-ps-modedisable prepare命令行中的参数说明 --db-drivermysql代表数据库驱动 --time300这个就是说连续访问300秒 --threads10这个就是说用10个线程模拟并发访问 --report-interval1这个就是说每隔1秒输出一下压测情况 --mysql-host192.168.2.221 --mysql-port7001 --mysql-userclaylpf --mysql-password123456数据库的用户和密码等信息 --mysql-dbtest_db --tables20 --table_size1000000这一串的意思就是说在test_db这个库里构造20个测试表每个测试表里构造100万条测试数据测试表的名字会是类似于sbtest1sbtest2这个样子的 oltp_read_write这个就是说执行oltp数据库的读写测试 --db-ps-modedisable这个就是禁止ps模式 prepare意思是参照这个命令的设置去构造出来我们需要的数据库里的数据他会自动创建20个测试表每个表里创建100万条测试数据所以这个工具是非常的方便的。 5、创建我们需要的数据库里的数据
[rootab ~]# sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host192.168.2.221 --mysql-port7001 --mysql-userclaylpf --mysql-password123456 --mysql-dbtest_db --tables10 --table_size1000 oltp_read_write --db-ps-modedisable prepare# 注意--tables10 --table_size1000所对应的数据不能调试太大如--tables20 --table_size1000000000否则会导致你的Mysql集群的磁盘耗尽导致集群崩溃.
sysbench 1.0.17 (using system LuaJIT 2.0.4)Initializing worker threads...Creating table sbtest10...
Creating table sbtest3...
Creating table sbtest9...
Creating table sbtest6...
Creating table sbtest1...
Creating table sbtest8...
Creating table sbtest4...
Creating table sbtest5...
Creating table sbtest7...
Creating table sbtest2...
Inserting 1000 records into sbtest10
Inserting 1000 records into sbtest1
Inserting 1000 records into sbtest2
Inserting 1000 records into sbtest4
Inserting 1000 records into sbtest5
Inserting 1000 records into sbtest6
Inserting 1000 records into sbtest7
Inserting 1000 records into sbtest8
Inserting 1000 records into sbtest9
Inserting 1000 records into sbtest3
Creating a secondary index on sbtest1...
Creating a secondary index on sbtest10...
Creating a secondary index on sbtest6...
Creating a secondary index on sbtest4...
Creating a secondary index on sbtest5...
Creating a secondary index on sbtest2...
Creating a secondary index on sbtest7...
Creating a secondary index on sbtest9...
Creating a secondary index on sbtest8...
Creating a secondary index on sbtest3...6、数据库读写性能测试获取测试数据
数据库读写性能测试将执行指令最后的prepare修改成run
[rootab ~]# sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host192.168.2.221 --mysql-port7001 --mysql-userclaylpf --mysql-password123456 --mysql-dbtest_db --tables10 --table_size1000 oltp_read_write --db-ps-modedisable 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 timeInitializing worker threads...Threads started!
# 下面是截取的执行1秒2秒3秒4秒等的数据。
[ 1s ] thds: 10 tps: 172.58 qps: 3597.26 (r/w/o: 2541.82/701.30/354.14) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 187.08 qps: 3691.57 (r/w/o: 2574.10/745.32/372.16) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 196.01 qps: 3961.13 (r/w/o: 2774.09/792.03/395.01) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 206.93 qps: 4139.62 (r/w/o: 2900.04/824.73/414.86) lat (ms,95%): 64.47 err/s: 1.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 212.06 qps: 4236.12 (r/w/o: 2970.79/841.22/424.11) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 184.06 qps: 3728.31 (r/w/o: 2608.92/751.26/368.13) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 10 tps: 208.86 qps: 4131.17 (r/w/o: 2894.02/819.44/417.71) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 10 tps: 214.14 qps: 4283.87 (r/w/o: 2997.01/858.58/428.29) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 10 tps: 187.96 qps: 3725.23 (r/w/o: 2600.47/750.85/373.92) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 10 tps: 195.96 qps: 3981.11 (r/w/o: 2795.38/791.82/393.91) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
^C
[rootab ~]# 对表中的数据进行说明以第一条数据做解释描述 thds: 10这个意思就是有10个线程在压测 tps: 151.70这个意思就是每秒执行了151.70个事务 qps: 2996.03这个意思就是每秒可以执行2996.03个请求 (r/w/o: 2091.83/600.80/303.40)这个意思就是说在每秒2996.03个请求中有2091.83个请求是读请求600.80个请求是写请求303.40个请求是其他的请求就是对QPS进行了拆解 lat (ms, 95%): 97.55这个意思就是说95%的请求的延迟都在 97.55毫秒以下 err/s: 0.00 reconn/s: 0.00这两个的意思就是说每秒有0个请求是失败的发生了0次网络重连 下面是执行完成后控制台输出的数据
SQL statistics:queries performed:read: 836822write: 239020other: 119517total: 1195359transactions: 59744 (199.12 per sec.)queries: 1195359 (3984.01 per sec.)ignored errors: 29 (0.10 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 300.0377stotal number of events: 59744Latency (ms):min: 19.82avg: 50.21max: 267.2895th percentile: 68.05sum: 2999907.14Threads fairness:events (avg/stddev): 5974.4000/21.35execution time (avg/stddev): 299.9907/0.017、执行完成压测之后可以将run改成cleanup清除数据
[rootab ~]# sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host192.168.2.221 --mysql-port7001 --mysql-userclaylpf --mysql-password123456 --mysql-dbtest_db --tables10 --table_size1000 oltp_read_write --db-ps-modedisable 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...
[rootab ~]# 8、IO性能压力测试
8.1、创建5个文件总共500MB每个文件大概100MB
[rootsysbench ~]# sysbench fileio --file-num5 --file-total-size500MB prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)5 files, 102400Kb each, 500Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
524288000 bytes written in 6.21 seconds (80.54 MiB/sec).
[rootsysbench ~]# 8.2、测试效果
[rootsysbench ~]# sysbench --events5000 --threads16 fileio --file-num5 --file-total-size500MB --file-test-moderndrw --file-fsync-freq0 --file-block-size16384 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)Running the test with following options:
Number of threads: 16
Initializing random number generator from current timeExtra file open flags: (none)
5 files, 100MiB each
500MiB total file size
Block size 16KiB
Number of IO requests: 5000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...Threads started!File operations:reads/s: 22083.80writes/s: 14833.29fsyncs/s: 590.67Throughput: #吞吐量read, MiB/s: 345.06 #读带宽written, MiB/s: 231.77 #写带宽General statistics:total time: 0.1292stotal number of events: 5000Latency (ms):min: 0.00avg: 0.13max: 29.0995th percentile: 0.02sum: 664.57Threads fairness:events (avg/stddev): 312.5000/671.89execution time (avg/stddev): 0.0415/0.01[rootsysbench ~]# 8.3、清除数据
[rootsysbench ~]# sysbench fileio --file-num5 --file-total-size500MB cleanup
sysbench 1.0.17 (using system LuaJIT 2.0.4)Removing test files...
[rootsysbench ~]# 9、cpu性能压力测试
[rootsysbench ~]# sysbench cpu --threads40 --events10000 --cpu-max-prime5000 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)Running the test with following options:
Number of threads: 40
Initializing random number generator from current timePrime numbers limit: 5000Initializing worker threads...Threads started!CPU speed: # CPU运行速度events per second: 4804.95 # 每秒运行的事件数General statistics:total time: 2.0789stotal number of events: 10000Latency (ms):min: 0.11avg: 7.31max: 858.8695th percentile: 0.31sum: 73145.78Threads fairness:events (avg/stddev): 250.0000/35.90execution time (avg/stddev): 1.8286/0.17[rootsysbench ~]# 七、搭建基于prometheus grafana的监控系统对数据库集群进行监控
参考Prometheus监控软件的学习_nfs exporter_Claylpf的博客-CSDN博客Grafana展示工具的学习_grafana学习_Claylpf的博客-CSDN博客
1、安装部署mysqld_exporter
下载地址Download | Prometheus 2、上传软件包到linux的/root/目录下 3、解压并移动到/usr/local/mysqld_exporter下
[rootmaster ~]# tar -zxvf mysqld_exporter-0.15.0.linux-amd64.tar.gz
mysqld_exporter-0.15.0.linux-amd64/
mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter
mysqld_exporter-0.15.0.linux-amd64/NOTICE
mysqld_exporter-0.15.0.linux-amd64/LICENSE
[rootmaster ~]# ls
anaconda-ks.cfg mha4mysql-node-0.58 mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz mysqld_exporter-0.15.0.linux-amd64.tar.gz onekey_install_mysql_binary.sh
mha4mysql-node-0.56.tar.gz mha4mysql-node-0.58.tar.gz mysqld_exporter-0.15.0.linux-amd64 onekey_install_mha_node.sh sersync2.5.4_64bit_binary_stable_final.tar.gz
[rootmaster ~]#
[rootmaster ~]# mv mysqld_exporter-0.15.0.linux-amd64 /usr/local/mysqld_exporter
[rootmaster ~]# cd /usr/local/mysqld_exporter/
[rootmaster mysqld_exporter]# ls
LICENSE mysqld_exporter NOTICE
[rootmaster mysqld_exporter]# 4、在/usr/local/mysqld_exporter下编辑连接本机数据库的配置文件
[rootmaster mysqld_exporter]# vim my.cnf
[rootmaster mysqld_exporter]# cat my.cnf
[client]
usermysqld_exporter
password123456
[rootmaster mysqld_exporter]# usermysqld_exporter #监控本机数据库的授权用户 password123456 #密码 5、将mysqld_exporter命令加入环境变量并设置开机自启
[rootmaster mysqld_exporter]# PATH/usr/local/mysqld_exporter/:$PATH
[rootmaster mysqld_exporter]# echo PATH/usr/local/mysqld_exporter/:$PATH /root/.bashrc
[rootmaster mysqld_exporter]# 6、后台启动
[rootmaster mysqld_exporter]# nohup mysqld_exporter --config.my-cnf/usr/local/mysqld_exporter/my.cnf
[1] 254157、看进程看端口网页访问测试
[rootmaster mysqld_exporter]# ps aux|grep exporter
root 25415 0.0 0.4 719560 4752 pts/0 Sl 11:18 0:00 mysqld_exporter --config.my-cnf/usr/local/mysqld_exporter/my.cnf
root 25419 0.0 0.0 112824 988 pts/0 R 11:19 0:00 grep --colorauto exporter
[rootmaster mysqld_exporter]# netstat -anplut|grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 25027/mysqld
tcp6 0 0 :::9104 :::* LISTEN 25415/mysqld_export
tcp6 0 0 192.168.2.150:3306 192.168.2.152:51232 ESTABLISHED 25027/mysqld
tcp6 0 0 192.168.2.150:3306 192.168.2.151:47084 ESTABLISHED 25027/mysqld
[rootmaster mysqld_exporter]# 网页访问测试 8、安装prometheus配置成服务
8.1、上传安装包到linux家目录下 8.2、解压Prometheus压缩包并移动到/prometheus目录下
[rootprometheus ~]# tar zvxf prometheus-2.43.0.linux-amd64.tar.gz
[rootprometheus ~]# mv prometheus-2.43.0.linux-amd64 /prometheus
8.3、将Prometheus配置成service服务
[rootmysql prometheus]# vim /usr/lib/systemd/system/prometheus.service
#创建一个prometheus.service文件
[rootmysql prometheus]# cat /usr/lib/systemd/system/prometheus.service
#如果需要自行复制
[Unit]
Descriptionprometheus[Service]
ExecStart/prometheus/prometheus/prometheus --config.file/prometheus/prometheus/prometheus.yml
ExecReload/bin/kill -HUP $MAINPID
KillModeprocess
Restarton-failure[Install]
WantedBymulti-user.target
[rootmysql prometheus]#
[rootmysql prometheus]# systemctl daemon-reload
#说明我已经向systemd注册prometheus.service文件了意思是重新加载systemd相关服务[rootmysql prometheus]# service prometheus restart #对prometheus服务进行运行
Redirecting to /bin/systemctl restart prometheus.service
[rootmysql prometheus]# [rootmysql prometheus]# ps aux|grep prometheus #检查prometheus的进程是否存在
root 5338 0.1 5.3 930420 52784 pts/0 Sl 19:39 0:02 prometheus --config.file/prometheus/prometheus/prometheus.yml
root 5506 0.0 0.0 112824 992 pts/0 R 20:22 0:00 grep --colorauto prometheus
[rootmysql prometheus]# service prometheus stop
Redirecting to /bin/systemctl stop prometheus.service
[rootmysql prometheus]# ps aux|grep prometheus
root 5338 0.1 5.3 930420 52784 pts/0 Sl 19:39 0:02 prometheus --config.file/prometheus/prometheus/prometheus.yml
root 5524 0.0 0.0 112824 988 pts/0 R 20:22 0:00 grep --colorauto prometheus
因为第一次是使用nohup方式启动的prometheus所以还是需要kill的方式杀死进程
后面就可以使用service的方式启动prometheus了
[rootmysql prometheus]# kill -9 5338 #需要kill原来没有使用service运行的prometheus进程
[rootmysql prometheus]# service prometheus restart
Redirecting to /bin/systemctl restart prometheus.service
[1] 已杀死 nohup prometheus --config.file/prometheus/prometheus/prometheus.yml
[rootmysql prometheus]# ps aux|grep prometheus
root 5541 1.0 3.9 798700 39084 ? Ssl 20:23 0:00 /prometheus/prometheus/prometheus --config.file/prometheus/prometheus/prometheus.yml
root 5548 0.0 0.0 112824 992 pts/0 R 20:23 0:00 grep --colorauto prometheus
[rootmysql prometheus]# service prometheus stop #关闭prometheus成功关闭
Redirecting to /bin/systemctl stop prometheus.service
[rootmysql prometheus]# ps aux|grep prometheus
root 5567 0.0 0.0 112824 992 pts/0 R 20:23 0:00 grep --colorauto prometheus
[rootmysql prometheus]#
验证成功
8.4、查看Prometheus端口
[rootprometheus ~]# netstat -anpult|grep prometheus
tcp6 0 0 :::9090 :::* LISTEN 2433/prometheus
tcp6 0 0 ::1:9090 ::1:42032 ESTABLISHED 2433/prometheus
tcp6 0 0 ::1:42032 ::1:9090 ESTABLISHED 2433/prometheus
[rootprometheus ~]# 8.4、访问Prometheus服务页面 9、Prometheus添加node节点
[rootprometheus ~]# cd /prometheus/prometheus
[rootprometheus prometheus]# ls
console_libraries consoles LICENSE NOTICE prometheus prometheus.yml promtool
[rootprometheus prometheus]# vim prometheus.yml
[rootprometheus prometheus]# cat prometheus.yml
#l config
global:scrape_interval: 15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.# scrape_timeout is set to the global default (10s).# Alertmanager configuration
alerting:alertmanagers:- static_configs:- targets:# - alertmanager:9093# Load rules once and periodically evaluate them according to the global evaluation_interval.
rule_files:# - first_rules.yml# - second_rules.yml# A scrape configuration containing exactly one endpoint to scrape:
# Here its Prometheus itself.
scrape_configs:# The job name is added as a label jobjob_name to any timeseries scraped from this config.- job_name: prometheus# metrics_path defaults to /metrics# scheme defaults to http.static_configs:- targets: [localhost:9090]- job_name: mysqlrouter1static_configs:- targets: [192.168.2.181:9104]- job_name: mysqlrouter2static_configs:- targets: [192.168.2.182:9104]- job_name: slave1static_configs:- targets: [192.168.2.151:9104]- job_name: slave2static_configs:- targets: [192.168.2.152:9104]- job_name: masterstatic_configs:- targets: [192.168.2.150:9104]- job_name: mha_managerstatic_configs:- targets: [192.168.2.141:9104]- job_name: backupstatic_configs:- targets: [192.168.2.157:9104]
[rootprometheus prometheus]# 9.1、刷新Prometheus服务
[rootprometheus prometheus]# service prometheus restart
Redirecting to /bin/systemctl restart prometheus.service
[rootprometheus prometheus]# 10、测试访问Prometheus服务: 11、安装grafana
11.1、上传grafana的rpm安装包 11.2、安装grafana
[rootprometheus ~]# yum install -y grafana-enterprise-9.4.7-1.x86_64.rpm
11.3、启动grafana
[rootprometheus ~]# service grafana-server start
Starting grafana-server (via systemctl): [ 确定 ]
[rootprometheus ~]# 11.4、查看端口并访问测试grafana
查看端口
[rootprometheus ~]# netstat -anpult | grep grafana
tcp 0 0 192.168.2.149:47398 34.120.177.193:443 ESTABLISHED 2627/grafana
tcp 0 0 192.168.2.149:58982 185.199.108.133:443 ESTABLISHED 2627/grafana
tcp6 0 0 :::3000 :::* LISTEN 2627/grafana
[rootprometheus ~]# 访问测试grafana 页面访问第一次登录账号密码默认都为admin 登陆后需要修改密码(lpf1375509........) 账号默认admin 如果忘记密码了我们可以使用 sqlite3 /var/lib/grafana/grafana.db update user set password 59acf18b94d7eb0694c61e60ce44c110c7a683ac6a8f09580d626f90f4a242000746579358d77dd9e570e83fa24faa88a8a6, salt F3FAxVm33R where login admin; .exit PS: 用户:admin 密码admin 重启服务 /etc/init.d/grafana-server restart 12、在grafana中增添Prometheus数据源 13、添加Dashboards模板推荐使用14057模板因为14057模板比较美观出图效果也比较好推荐使用 14、grafana效果展示 八、搭建DNS主域名服务器增添两条负载均衡记录实现对Mysqlrouter双vip地址的DNS负载均衡
1、安装DNS服务
[rootdns ~]# systemctl disable firewalld #关闭防火墙防止windows客户机无法访问dns服务器
[rootdns ~]# systemctl disable NetworkManager #关闭NetworkManager
[rootdns ~]# yum install bind* #安装dns服务的软件包
[rootdns ~]# service named start #启动dns服务
[rootdns ~]# systemctl enable named #开机启动dns服务2、修改dns配置文件任意ip可以访问本机的53端口并且允许dns解析。
[rootdns ~]# vim /etc/named.conf
listen-on port 53 { any; };#允许任意ip访问53端口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; }; #允许任意dns解析3、搭建主域名服务器
3.1、修改named.rfc1912.zones配置文件告诉named为claylpf.xyz提供域名解析
[rootdns ~]# vim /etc/named.rfc1912.zones
[rootdns ~]# cat /etc/named.rfc1912.zones
// named.rfc1912.zones:
//
// Provided by Red Hat caching-nameserver package
//
// ISC BIND named zone configuration for zones recommended by
// RFC 1912 section 4.1 : localhost TLDs and address zones
// and http://www.ietf.org/internet-drafts/draft-ietf-dnsop-default-local-zones-02.txt
// (c)2007 R W Franks
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//zone localhost.localdomain IN {type master;file named.localhost;allow-update { none; };
};zone localhost IN {type master;file named.localhost;allow-update { none; };
};zone claylpf.xyz IN {type master;file claylpf.xyz.zone;allow-update { none; };
};
#添加上面的配置建议在localhost的后面zone 1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa IN {type master;file named.loopback;allow-update { none; };
};zone 1.0.0.127.in-addr.arpa IN {type master;file named.loopback;allow-update { none; };
};zone 0.in-addr.arpa IN {type master;file named.empty;allow-update { none; };
};[rootdns ~]#
3.2、创建claylpf.xyz主域名的数据文件
[rootdns ~]# cd /var/named/
[rootdns named]# ls
chroot chroot_sdb data dynamic dyndb-ldap named.ca named.empty named.localhost named.loopback slaves
[rootdns named]# cp -a named.localhost claylpf.xyz.zone
[rootdns named]# ls
chroot chroot_sdb claylpf.xyz.zone data dynamic dyndb-ldap named.ca named.empty named.localhost named.loopback slaves
[rootdns named]#
3.3、修改claylpf.xyz.zone文件
[rootdns named]# vim claylpf.xyz.zone
[rootdns named]# cat claylpf.xyz.zone
$TTL 1DIN SOA rname.invalid. (0 ; serial1D ; refresh1H ; retry1W ; expire3H ) ; minimumNS A 192.168.2.155
www IN A 192.168.2.221
www IN A 192.168.2.201
[rootdns named]# 3.4、刷新dns服务
[rootdns named]# service named restart
Redirecting to /bin/systemctl restart named.service
[rootdns named]# 4、效果测试
4.1、修改linux客户机的dns服务器的地址为搭建的dns服务器192.168.2.155
[rootclaylpf network-scripts]# vim /etc/resolv.conf
[rootclaylpf network-scripts]# cat /etc/resolv.conf
# Generated by NetworkManager
#nameserver 114.114.114.114
nameserver 192.168.2.155
[rootclaylpf network-scripts]#
4.2、查看效果
[rootclaylpf ~]# nslookup www.claylpf.xyz
Server: 192.168.2.155
Address: 192.168.2.155#53Name: www.claylpf.xyz
Address: 192.168.2.221
Name: www.claylpf.xyz
Address: 192.168.2.201[rootclaylpf ~]# 同一域名解析出了中间件mysqlrouter的双vip地址实现了基于dns的负载均衡 项目结束
项目遇到的问题 1、各种软件的有些配置项加了注释项导致无法正常启动 答多查看软件的报错信息以及查看服务日志对排错的帮助会很大 2、主从复制时只在slave上进行了操作导致事务数比主服务器还要多主从复制一直起不来 答查看报错信息是事务数比主服务器还要多—尽可能删除比主服务器还要多出来的数据在reset master并重新设置master_info信息 3、本以为mysqlrouter访问读写数据源的vip地址mha架构里写服务器的特有ip要修改mysql的绑定ip地址才能登录后来发现只要新建了允许mysqlrouter访问的授权用户允许所有ip登录就行 4、使用rsync时数据源备份文件/backup/打成了/backup导致把整个文件夹都同步过去了 发现dns负载均衡的效果不是很明显再linux上访问能解析出199而在windows机上能解析出188 5、压力测试的时候由于内核参数的限制导致无法起太多的线程 答修改内核限制参数以及调大mysql中与内核相关的参数 6、使用ansible调用一键二进制安装脚本中mysql加入环境变量不成功 答再使用shell模块远程加入PATH变量 7、ulimit -n修改不生效 答写入到/etc/security/limits.conf中使其永久生效 8、修改主机名后mysql重启不成功 答需要杀死所有mysqld进程在重新启动mysql 项目心得 1、提前规划好整个集群的架构可以提高项目开展时效率 2、运行报错多看出错信息提示以及日志对排错的帮助很大 3、对半同步的主从复制有了更深入的理解 4、对keepalived的脑裂和vip漂移现象也有了更加深刻的体会和分析 5、加强了mha架构中自动failover的实现原理的理解 6、对基于mysqlrouter的读写分离过程更加的熟悉 7、认识到了数据备份的重要性 8、深刻的体会到了rsyncsersync数据同步工具的便利与好处 9、熟练了sysbench下的压力测试认识到了系统性能资源的重要性对压力测试下整个集群的瓶颈有了一个整体概念 10、对监控也有了的更进一步的认识监控可以提前看到问题做好预警 11、对很多软件之间的配合有了一定的理解如mysqlrouter、mha、mysql等 12、troubleshooting的能力得到了提升
