admin管理员组文章数量:1651802
项目名称
基于mycat2+mha+keepalived的半同步主从复制mysql集群
项目架构图
项目环境
项目所需的软件,有需要的自行下载
链接:https://pan.baidu/s/1oH-Q4460c_riYv3cB4eSbw
提取码:0ahf
软件环境
- CentOS:CentOS Linux release 7.9.2009 (Core)
- MySQL:mysql Ver 14.14 Distrib 5.7.38, for linux-glibc2.12 (x86_64)
- mha manager:mha4mysql-manager-0.58
- mha node:mha4mysql-node-0.58
- mycat2:mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar、mycat2-install-template-1.21.zip
- Keepalived: Keepalived v1.3.5 (03/19,2017)
- sysbench:sysbench 1.0.17
- Ansible:ansible 2.9.27
- Prometheus: prometheus, version 2.43.0
- mysqld_exporter:mysqld_exporter, version 0.12.1
- Grafana: grafana 9.4.7
- DNS:BIND 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.13
硬件环境
- CentOS 7.9(10台1核512MB)
项目描述
简介
模拟企业构建一个高可用并且高性能的MySQL集群项目,能处理大并发的后端MySQL业务。
- 使用Ansible实现软件环境的部署
- 使用中间件mycat2实现MySQL集群的读写分离
- Keepalived搭建双vip架构实现中间件mycat2的高可用
- GITD+半同步的主从复制实现后端MySQL的热备
- 使用MHA实现MySQL集群的自动failover达到高可用
- Prometheus+mysqld_exporter+Grafana实现对MySQL集群以及读写分离器的系统资源监控
- rsync+sersync+计划任务实现MySQL数据的异地实时备份
- 使用sysbench对MySQL集群进行压力测试,了解其系统资源瓶颈
IP地址规划
主机名 | IP地址 |
---|---|
windows客户机 | 192.168.31.68 |
mycat1 | 192.168.31.180 vip 192.168.31.188 |
mycat2 | 192.168.31.190 vip 192.168.31.199 |
mha_manager、mha_node | 192.168.31.220 |
master、mha_node | 192.168.31.221 vip 192.168.31.200 |
slave1、candidate master、mha_node | 192.168.31.222 vip 192.168.31.200 |
slave2、mha_node | 192.168.31.223 |
ansible、异地备份服务器 | 192.168.31.210 |
sysbench | 192.168.31.66 |
prometheus+grafana | 192.168.31.125 |
dns | 192.168.31.153 |
项目步骤
一、准备10台全新虚拟机,按照IP规划配置好静态IP。安装部署Ansible,建立免密通道,调用一键二进制安装MySQL脚本,自动化批量部署MySQL
1.根据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.31.221 #ip地址
PREFIX=24 #子网掩码24
DNS1=114.114.114.114 #dns服务器114.114.114.114
GATEWAY=192.168.31.1 #我选择的是桥接网络,所以网关是路由器ip 192.168.31.1
[root@master ~]# service network restart #重启网络
Restarting network (via systemctl): [ 确定 ]
[root@master ~]# ip add #查看ip
[root@master ~]# ping www.baidu #测试能否上网
[root@master ~]# hostnamectl set-hostname master #修改指定主机名,方便辨认
[root@master ~]# su
其他服务器的配置过程类似,我就不一一写明了
2.建立免密通道
[root@ansible ~]# ssh-keygen #生成密钥对--一路回车
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.221 #上传公钥到远程服务器--yes,然后输入远程服务器密码--master
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.222 #slave1
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.223 #slave2
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.220 #mha manager
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.125 #Prometheus
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.180 #mycat1
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.190 #mycat2
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.66 #sysbench
3.安装部署ansible
[root@ansible ~]# yum install -y epel-release
[root@ansible ~]# yum install ansible
4.准备好MySQL软件包,上传到linux家目录下
4.编写主机清单,加入主机组
加入远程主机ip
[root@ansible ~]# cd /etc/ansible/
[root@ansible ansible]# ls
ansible.cfg hosts roles
[root@ansible ansible]# vim hosts
[mysql]
192.168.31.221 #master
192.168.31.222 #slave1
192.168.31.223 #slave2
5.编写playbook,上传源码包到远程服务器,调用本地脚本二进制安装MySQL
[root@ansible ansible]# vim software_install.yaml
- hosts: mysql
remote_user: root
tasks:
- name: copy file #上传本地源码包到mysql主机组
copy: src=/root/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz dest=/root/
- name: one key binary install mysql #调用本地二进制安装脚本,远程执行安装mysql
script: /root/onekey_install_mysql_binary_v5.7.38.sh
- name: alter path #确保mysql命令加入环境变量
shell: export PATH=/usr/local/mysql/bin/:$PATH
使用ansible中的copy模块上传源码包时,速度可能比较慢—可以考虑直接使用xshell里的xftp工具直接上传,不写playbook
6.检查yaml文件语法
[root@ansible ~]# ansible-playbook --syntax-check /etc/ansible/software_install.yaml
playbook: /etc/ansible/software_install.yaml
7.执行yaml文件
[root@ansible ansible]# ansible-playbook software_install.yaml
8.查看是否安装成功
查看进程
[root@master ~]# ps aux|grep mysqld
root 10590 0.0 0.3 11824 1600 ? S 18:09 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql 10744 0.1 42.8 1610072 205368 ? Sl 18:09 0:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master.err --open-files-limit=8192 --pid-file=/data/mysql/master.pid --socket=/data/mysql/mysql.sock --port=3306
root 10818 0.0 0.2 112824 984 pts/3 S+ 18:55 0:00 grep --color=auto mysqld
查看端口
[root@master ~]# netstat -anplut|grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 10744/mysqld
一键二进制安装脚本
[root@ansible ~]# cat onekey_install_mysql_binary_v5.7.38.sh
#解决软件依赖关系并且安装需要工具
yum install -y cmake ncurses-devel gcc gcc-c++ vim lsof bzip2 openssl-devel ncurses-compat-libs net-tools
#解压二进制安装包
tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
#移动mysql解压后的文件到/usr/local下改名为mysql,/usr/local/mysql是mysql的安装目录
mv mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql
#新建组和用户 mysql
groupadd 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用户启动的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}')
tem_passwd=$(cat passwd.txt |grep temporary|awk '{print $NF}')
#修改PATH变量,加入mysql bin目录的路径
#临时修改PATH变量的值
export PATH=/usr/local/mysql/bin/:$PATH
#设置开机修改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/mysql脚本文件里的datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld
#生成/etc/myf配置文件
cat >/etc/myf <<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 65535
#设置开机启动的时候也配置生效
echo "* soft nofile 65535" >>/etc/security/limits.conf
echo "* hard nofile 65535" >>/etc/security/limits.conf
#启动mysqld进程
service mysqld start
#将mysqld添加到linux系统里的服务管理名单里
systemctl enable mysqld
#登录mysql,密码可以自行设置指定
mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='Sanchuang123#';"
#校验上一步密码是否成功,如果有输出能看到mysql里的数据库,说明成功。
mysql -uroot -p'Sanchuang123#' -e "show databases;"
二、mysqldump+mysql命令模拟对企业mysql已有数据的冷备,rsync+sersync+计划任务 实现实时同步全备数据
1.master建库建表插入数据,模拟企业已经存在的真实数据,也方便后续效果测试
[root@master ~]# mysql -uroot -p"Sanchuang123#"
root@(none) 19:49 mysql>create database test1;
Query OK, 1 row affected (0.00 sec)
root@(none) 19:50 mysql>use test1;
Database changed
root@test1 19:50 mysql>create table t1(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
root@test1 19:50 mysql>insert into t1 values(1,'chen');
Query OK, 1 row affected (0.02 sec)
root@test1 19:51 mysql>insert into t1 values(2,'li');
Query OK, 1 row affected (0.01 sec)
root@test1 19:52 mysql>insert into t1 values(3,'biao');
Query OK, 1 row affected (0.00 sec)
root@test1 19:52 mysql>select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | chen |
| 2 | li |
| 3 | biao |
+----+------+
3 rows in set (0.00 sec)
2.数据同步,使用mysqldump+mysql命令数据同步到slave1,slave2
1.master使用mysqldump命令数据备份到/backup
[root@master ~]# mkdir /backup
[root@master ~]# cd /backup/
[root@master backup]# mysqldump -uroot -p"Sanchuang123#" --all-databases >all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master backup]# ls
all_db.sql
2.master建立免密通道到slave1、slave2,方便传输备份文件
[root@master backup]# ssh-keygen
[root@master backup]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.222
[root@master backup]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.223
3.将备份文件scp到slave1、slave2的/backup文件夹下
[root@master backup]# ssh root@192.168.31.222 mkdir -p /backup
[root@master backup]# ssh root@192.168.31.223 mkdir -p /backup
[root@master backup]# scp /backup/all_db.sql root@192.168.31.223:/backup/
all_db.sql
[root@master backup]# scp /backup/all_db.sql root@192.168.31.222:/backup/
all_db.sql
4.slave1、slave2使用mysql命令将备份文件导入到自己的数据库
[root@slave1 ~]# cd /backup/
[root@slave1 backup]# ls
all_db.sql
[root@slave1 backup]# mysql -uroot -p"Sanchuang123#" <all_db.sql
slave2操作一致
5.查看效果
root@(none) 20:23 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.02 sec)
root@(none) 20:23 mysql>use test1;
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@test1 20:23 mysql>select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | chen |
| 2 | li |
| 3 | biao |
+----+------+
3 rows in set (0.00 sec)
数据同步成功
3.计划任务定时全备数据
1.编写数据备份脚本
[root@master backup]# vim backup_db.sh
#!/bin/bash
#导出数据库数据到/backup目录下,以日期时间的格式命名
mysqldump -uroot -p"Sanchuang123#" --all-databases >/backup/$(date +%F)_all_db.sql
2.编写计划任务,每天凌晨2点半全备数据
[root@master backup]# crontab -e
[root@master backup]# crontab -l
30 2 * * * bash /backup/backup_db.sh
4.rsync+sersync实现备份数据实时同步到异地备份服务器
数据源服务器:master 192.168.31.221
备份服务器:ansible 192.168.31.210
rsync备份服务器操作
1.新建/backup文件夹,用于存放备份数据
[root@ansible ~]# mkdir /backup
2.安装rsync服务端软件
[root@ansible ~]# yum install -y rsync xinetd -y
如果没有,可以先尝试yum install -y epel-release 下载epel源
3.设置开机启动
[root@ansible ~]# echo "/usr/bin/rsync --daemon --config=/etc/rsyncd.conf #设置开机启动rsync" >>/etc/rc.d/rc.local
[root@ansible ~]# chmod +x /etc/rc.d/rc.local #赋予可执行权限
4.启动xinetd,查看进程
[root@ansible ~]# systemctl start xinetd
[root@ansible ~]# ps aux|grep xinetd
root 1825 0.0 0.1 25044 588 ? Ss 19:11 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid
root 1827 0.0 0.2 112824 988 pts/1 S+ 19:11 0:00 grep --color=auto xinetd
xinetd就好像是一个保姆进程,rsync是它照顾的进程
看到进程说明xinetd已经启动成功
5.修改/etc/rsyncd.conf配置文件
[root@ansible ~]# >/etc/rsyncd.conf #清空文件内容
[root@ansible ~]# vim /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 stored
ignore errors = yes
read only = no
hosts allow = 192.168.31.221 #允许的ip地址(数据源服务器地址)
注意:[back_data]最好不要加注释,防止出错
6.创建用户认证文件
[root@ansible backup]# vim /etc/rsyncd.pass
backup:123456 #认证用户,密码,后面会用到
7.设置文件权限
[root@ansible backup]# chmod 600 /etc/rsyncd.conf
[root@ansible backup]# chmod 600 /etc/rsyncd.pass
增强安全性,非root用户不可读配置文件和认证文件
8.启动rsync,查看进程、端口
[root@ansible backup]# /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
[root@ansible backup]# ps aux|grep rsyncd
root 1846 0.0 0.1 114852 576 ? Ss 19:25 0:00 /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
root 1848 0.0 0.2 112824 988 pts/1 S+ 19:25 0:00 grep --color=auto rsyncd
[root@ansible backup]# netstat -anplut|grep rsync
tcp 0 0 0.0.0.0:873 0.0.0.0:* LISTEN 1846/rsync
tcp6 0 0 :::873 :::* LISTEN 1846/rsync
看到进程,就说明rsync启动成功
xinetd监听873端口
rsync数据源服务器操作
1.安装rsync、xinetd软件
[root@master backup]# yum install -y rsync xinetd
2.设置开机自启
[root@master backup]# echo "/usr/bin/rsync --daemon --config=/etc/rsyncd.conf #设置开机启动rsync" >>/etc/rc.d/rc.local
[root@master backup]# chmod +x /etc/rc.d/rc.local
3.启动xinetd
[root@master backup]# systemctl start xinetd
4.修改/etc/rsyncd.conf配置文件
[root@master ~]# >/etc/rsyncd.conf
[root@master ~]# vim /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 = Sync
uid = root
gid = root
port= 873
注意:[sync]段配置文件最好不要加注释,防止报错
5.创建用户认证文件
[root@master ~]# vim /etc/passwd.txt
123456 #该密码应与备份服务器中的/etc/rsync.pass中的密码一致
6.设置文件权限,非root用户不可读认证文件
[root@master ~]# chmod 600 /etc/passwd.txt
7.测试数据同步
数据源服务器开启rysnc同步
[root@master backup]# rsync -avH --port=873 --progress --delete /backup/ root@192.168.31.210::back_data --password-file=/etc/passwd.txt
–port 备份服务器端rsync端口
/backup/ 数据源服务器上要备份的数据
root:192.168.31.210 备份服务器的用户和ip地址
如果有报错,建议查看日志文件/var/log/rysncd.conf
数据源服务器增加文件或者删除文件,看备份服务器/backup/是否有变化
数据源服务器新建文件夹
查看备份服务器/backup/
数据同步成功
安装sersync工具,inotify实时触发rsync进行同步–数据源服务器操作
1.修改inotify默认参数(inotify默认内核参数过小),修改参数
inotify在内核里已经安装了,只需要传参
[root@master backup]# sysctl -w fs.inotify.max_queued_events="99999999"
fs.inotify.max_queued_events = 99999999
[root@master backup]# sysctl -w fs.inotify.max_user_watches="99999999"
fs.inotify.max_user_watches = 99999999
[root@master backup]# sysctl -w fs.inotify.max_user_instances="65535"
fs.inotify.max_user_instances = 65535
2.设置永久生效
[root@master backup]# vim /etc/sysctl.conf
fs.inotify.max_queued_events=99999999
fs.inotify.max_user_watches=99999999
fs.inotify.max_user_instances=65535
3.安装sersync软件包
[root@master ~]# wget http://down.whsir/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz
4.解压sersync软件
[root@master ~]# tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz
5.创建sersync
[root@master ~]# mv GNU-Linux-x86/ /usr/local/sersync
[root@master ~]# cd /usr/local/sersync/
[root@master sersync]# ls
confxml.xml sersync2
[root@master sersync]# cp confxml.xml confxml.xml.bak #备份,防止后续出错
[root@master sersync]# cp confxml.xml data_configxml.xml #增加数据配置
[root@master sersync]# ls
confxml.xml confxml.xml.bak data_configxml.xml sersync2
6.修改配置 data_configxml.xml
#第24行位置
<sersync>
<localpath watch="/backup/"> #本地要备份的数据文件
<remote ip="192.168.31.210" name="back_data"/> #备份服务器的ip地址以配置文件里的配置项
<!--<remote ip="192.168.8.39" name="tongbu"/>-->
<!--<remote ip="192.168.8.40" name="tongbu"/>-->
</localpath>
<rsync>
<commonParams params="-artuz"/>
<auth start="false" users="root" passwordfile="/etc/passwd.txt"/> #认证用户和密码所在的路径
<userDefinedPort start="false" port="874"/><!-- port=874 -->
<timeout start="false" time="100"/><!-- timeout=100 -->
<ssh start="false"/>
7.加入PATH环境变量,并设置开机永久生效
[root@master sersync]# PATH=/usr/local/sersync/:$PATH
[root@master sersync]# which sersync2
/usr/local/sersync/sersync2
[root@master sersync]# echo "PATH=/usr/local/sersync/:$PATH" >>/root/.bashrc
8.启动sersync
[root@master sersync]# sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
set the system param
execute:echo 50000000 > /proc/sys/fs/inotify/max_user_watches
execute:echo 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.xml
daemon thread num: 10
parse xml config file
host ip : localhost host port: 8008
daemon start,sersync run behind the console
config xml parse success
please set /etc/rsyncd.conf max connections=0 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.31.210::back_data >/dev/null 2>&1
run the sersync:
watch path is: /backup #启动成功
9.查看进程
[root@master sersync]# ps aux|grep sersync
root 7416 0.0 0.1 92324 704 ? Ssl 20:45 0:00 sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
root 7433 0.0 0.2 112824 988 pts/2 S+ 20:49 0:00 grep --color=auto sersync
看到进程就算启动成功了
10.设置sersync监控开机自动执行
[root@master sersync]# vim /etc/rc.d/rc.local
/usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
11.效果测试
数据源服务器新建文件夹
备份服务器发现了实时同步的新建文件夹
测试成功!rsync+sersync实时数据同步搭建成功!
三、搭建GTID+半同步的主从复制(一主两从),实现MySQL热备,并为读写分离、故障转移做准备
1.所有MySQL服务器修改配置文件,开启二进制日志、GIID功能
其实实现主从复制只需要在master上开启二进制日志,但为了方便后续故障转移的实现,两台slave机都有可能成为master,就全部mysql服务器都开启二进制日志、GTID、半同步功能**
[root@master ~]# vim /etc/myf
[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
#开启二进制日志
log_bin
server_id = 1
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
切记:slave1、slave2配置一致,但是server_id绝对不能一样,slave1我设置的是2,slave2设置的是3
2.查看是否开启二进制日志
root@(none) 21:01 mysql>show variables like "%log_bin%";
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |#已开启
| log_bin_basename | /data/mysql/master-bin |
| log_bin_index | /data/mysql/master-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)
3.查看是否开启GTID功能
root@(none) 21:07 mysql>show variables like "%gtid%";
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |#已开启
| gtid_next | AUTOMATIC |#已开启
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
4.master新建授权用户slave,用于slave取二进制日志
root@(none) 21:34 mysql>grant replication slave on *.* to 'slave'@'192.168.31.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@(none) 21:38 mysql>select user,host from mysql.user;
+---------------+--------------+
| user | host |
+---------------+--------------+
| slave | 192.168.31.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+--------------+
4 rows in set (0.00 sec)
grant replication slave on . to ‘slave’@‘192.168.31.%’ identified by ‘123456’;
含义:允许31网段的IO线程通过用户slave密码123456来拿取二进制日志
5.slave1,slave2修改master信息
root@(none) 21:42 mysql>stop slave;
root@(none) 21:42 mysql>change master to master_host='192.168.31.221',master_user='slave',master_password='123456',master_port=3306,master_auto_position=1;
- master_host=‘192.168.31.221’ #master ip地址
- master_user=‘slave’ #master授权的用户
- master_password=‘123456’ #授权用户密码
- master_port=3306 #master端口号
- master_auto_position=1 #gtid中事务数自增量为1
6.开启slave,查看master_info信息
root@(none) 21:44 mysql>start slave;
root@(none) 21:48 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.221
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 449
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 664
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes #IO线程启动成功
Slave_SQL_Running: Yes #SQL线程启动成功,两个yes代表主从复制搭建成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 449
Relay_Log_Space: 872
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 0d9754a1-ebf6-11ed-b0c2-000c29ae59ea #gtid格式会用的uuid
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 0d9754a1-ebf6-11ed-b0c2-000c29ae59ea:1 #GTID号,出现则代表gtid功能已经实现了
Executed_Gtid_Set: 0d9754a1-ebf6-11ed-b0c2-000c29ae59ea:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
7.master下载主服务器半同步插件,开启半同步功能
1.下载插件
root@(none) 21:57 mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
2.临时设置超时时间为1秒,开启半同步
root@(none) 21:57 mysql>set global rpl_semi_sync_master_timeout = 1;
Query OK, 0 rows affected (0.01 sec)
root@(none) 21:59 mysql>set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.01 sec)
3.永久开启半同步,超时时间1秒
[root@master ~]# vim /etc/myf
[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
#开启二进制日志
log_bin
server_id = 1
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
#设置主服务器半同步超时时间
rpl_semi_master_timeout=1000
#开启主服务器半同步主从复制
rpl_semi_master_enabled=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
4.重启服务
[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
5.查看是否开启半同步
root@(none) 22:09 mysql>show variables like "%semi_sync%"
-> ;
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |#已开启主服务器半同步
| rpl_semi_sync_master_timeout | 1000 |
| 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 |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
8.slave1、slave2下载从服务器半同步插件,开启半同步功能
1.下载插件
root@(none) 22:04 mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
2.临时开启半同步
root@(none) 22:05 mysql>set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
3.永久开启半同步
[root@slave1 ~]# vim /etc/myf
[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
#开启二进制日志
log_bin
server_id = 2
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
#开启从服务器半同步主从复制
rpl_semi_sync_slave_enabled=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
slave2配置一致
4.重启服务
[root@slave1 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
5.查看是否开启
root@(none) 22:10 mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| 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 |
| rpl_semi_sync_slave_enabled | ON |#已经开启
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.01 sec)
9.主从复制效果测试
1.master建库建表插入数据
root@(none) 22:11 mysql>create database repl;
Query OK, 1 row affected (0.01 sec)
root@(none) 22:13 mysql>use repl;
Database changed
root@repl 22:13 mysql>create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
root@repl 22:13 mysql>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@repl 22:14 mysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
2.slave1、slave2查看是否有库有表有数据
root@(none) 22:11 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| repl |
| sys |
| test1 |
+--------------------+
6 rows in set (0.00 sec)
root@(none) 22:15 mysql>use repl;
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@repl 22:15 mysql>show tables;
+----------------+
| Tables_in_repl |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
root@repl 22:15 mysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
3.主从复制效果成功!
4.master使用mysqlbinlog查看二进制日志,测试GTID功能是否真正实现
[root@master ~]# cd /data/mysql/
[root@master mysql]# mysqlbinlog master-bin.000006|g
5.发现了GTID,GTID测试成功!
四、Ansible批量部署mha软件环境,搭建MHA高可用架构,实现自动failover,完成主从切换
1.编写主机清单,增添4个mha node节点ip地址,以及一个mha manager节点ip地址
[root@ansible ansible]# vim /etc/ansible/hosts
[mha_manager]
192.168.31.220 #mha manager
[mha_node]
192.168.31.220 #mha manager
192.168.31.221 #master
192.168.31.222 #slave1
192.168.31.223 #slave2
2.编写一键安装mha node脚本和一键安装mha mangaer脚本
mha node脚本
[root@ansible ~]# 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时已经安装好了所有的依赖包,就可以直接进行解压编译安装了
[root@ansible ~]# cat onekey_install_mha_manager.sh
#软件包mha4mysql-manager-0.58.tar.gz放入/root目录下
tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
#编译安装
perl Makefile.PL
make && make install
3.编写playbook,上传源码包到家目录下,调用本地脚本,远程安装部署mha相关软件环境
[root@ansible ansible]# cat mha_install.yaml
- hosts: mha_node
remote_user: root
tasks:
- name: copy file
copy: src=/root/mha4mysql-node-0.58.tar.gz dest=/root/
- name: install mha_node
script: /root/onekey_install_mha_node.sh
- hosts: mha_manager
remote_user: root
tasks:
- name: copy file
copy: src=/root/mha4mysql-manager-0.58.tar.gz dest=/root/
- name: install mha_manager
script: /root/onekey_install_mha_manager.sh
4.执行playbook
[root@ansible ansible]# ansible-playbook mha_install.yaml
5.所有服务器互相建立免密通道
mha manager对所有mysql服务器建立免密通道
[root@mha_manager bin]# ssh-keygen
[root@mha_manager bin]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.221
[root@mha_manager bin]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.222
[root@mha_manager bin]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.223
master对slave1、slave2建立免密通道(mysqldump做数据同步的时候我已经建立过了)
[root@master bin]# ssh-keygen
[root@master bin]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.222
[root@master bin]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.31.223
slave1对master、slave2建立免密通道
[root@slave1 bin]# ssh-keygen
[root@slave1 bin]# ssh-coy-id -i /root/.ssh/id_rsa.pub 192.168.31.221
[root@slave1 bin]# ssh-coy-id -i /root/.ssh/id_rsa.pub 192.168.31.223
slave2对master、slave1建立免密通道
[root@slave2 bin]# ssh-keygen
[root@slave2 bin]# ssh-copy-id /root/.ssh/id_rsa.pub 192.168.31.221
[root@slave2 bin]# ssh-copy-id /root/.ssh/id_rsa.pub 192.168.31.222
6.在搭建好的主从复制服务器里,配置mha相关信息
1.所有mysql服务器加入log_bin和log-slave-updates,并刷新配置文件
#开启二进制日志
log_bin
server_id = 1 #不同mysql服务器,绝对不能一样
#主从切换时,主从服务器身份都可能对换,从服务器重演relay_log日志操作时,也写入自己的log_bin中
log-slave-updates
service mysqld restart #刷新配置文件
2.所有mysql服务器(master、slave1、slave2)将mysql命令和mysqlbinlog命令软链接到/usr/sbin,方便manager管理节点
[root@master ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@master ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
[root@slave1 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@slave1 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
[root@slvae2 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@slave2 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
3.所有mysql服务器新建允许manager访问的授权用户mha,密码123456
root@(none) 11:16 mysql>grant all on *.* to 'mha'@'192.168.31.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)
root@(none) 11:18 mysql>grant all on *.* to 'mha'@'192.168.31.221' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@(none) 11:19 mysql>grant all on *.* to 'mha'@'192.168.31.222' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 11:19 mysql>grant all on *.* to 'mha'@'192.168.31.223' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@(none) 11:19 mysql>select user,host from mysql.user;
+---------------+----------------+
| user | host |
+---------------+----------------+
| mha | 192.168.31.% |
| slave | 192.168.31.% |
| mha | 192.168.31.221 |
| mha | 192.168.31.222 |
| mha | 192.168.31.223 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+----------------+
8 rows in set (0.00 sec)
7.在mha manager节点上配置好相关脚本、管理节点服务器
1.mha manager节点上复制相关脚本到/usr/local/bin下
[root@mha_manager bin]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
[root@mha_manager bin]# cd /usr/local/bin/
[root@mha_manager bin]# ls
apply_diff_relay_logs masterha_check_repl masterha_check_status masterha_manager masterha_master_switch masterha_stop save_binary_logs
filter_mysqlbinlog masterha_check_ssh masterha_conf_host masterha_master_monitor masterha_secondary_check purge_relay_logs scripts
[root@mha_manager bin]# cd scripts/
[root@mha_manager scripts]# ls
master_ip_failover master_ip_online_change power_manager send_report
-r 复制目录
-p 同时复制文件访问权限
2.复制自动切换时vip管理的脚本到/usr/local/bin下
[root@mha_manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
3.修改master_ip_failover文件内容,配置vip(只配置vip相关参数,其他默认不修改)
[root@mha_manager scripts]# >/usr/local/bin/master_ip_failover #清空文件内容,复制以下内容
[root@mha_manager scripts]# vim /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.31.200'; #指定vip的地址,自己指定
my $brdc = '192.168.31.255'; #指定vip的广播地址
my $ifdev = 'ens33'; #指定vip绑定的网卡
my $key = '1'; #指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #代表此变量值为ifconfig ens33:1 192.168.31.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #代表此变量值为ifconfig ens33:1 192.168.31.200 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(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "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 --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
4.创建 MHA 软件目录并复制配置文件,使用app1f配置文件来管理 mysql 节点服务器,配置文件一般放在/etc/目录下
注意:注释只是提示用,编辑配置文件时最好不要加注释,否则很可能会出错
[root@mha_manager scripts]# mkdir /etc/masterha
[root@mha_manager scripts]# cp /root/mha4mysql-manager-0.58/samples/conf/app1f /etc/masterha/
[root@mha_manager scripts]# cd /etc/masterha/
[root@mha_manager masterha]# ls
app1f
[root@mha_manager masterha]# >app1f #清空原有内容
[root@mha_manager masterha]# vim app1f
[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 #设置手动切换时候的切换脚本
user=mha #设置监控用户mha
password=123456 #设置mysql中mha用户的密码,这个密码是前文中创建监控用户的那个密码
ping_interval=1 #设置监控主库,发送ping包的时间间隔1秒,默认是3秒,尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp #设置远端mysql在发生切换时binlog的保存位置
repl_user=slave #设置复制用户的用户slave
repl_password=123456 #设置复制用户slave的密码
report_script=/usr/local/send_report #设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.31.222 -s 192.168.31.223 #指定检查的从服务器IP地址
shutdown_script="" #设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用)
ssh_user=root #设置ssh的登录用户名
[server1]
#master
hostname=192.168.31.221
port=3306
[server2]
#slave1
hostname=192.168.31.222
port=3306
candidate_master=1
#设置为候选master,设置该参数以后,发生主从切换以后将会将此从库提升为主库,即使这个主库不是集群中最新的slave
check_repl_delay=0
#默认情况下如果一个slave落后master 超过100M的relay logs的话,MHA将不会选择该slave作为一个新的master, 因为对于这个slave的恢复需要花费很长时间;通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master
[server3]
#slave2
hostname=192.168.31.223
port=3306
8.master服务器上手工开启vip
[root@master mysql]# ifconfig ens33:1 192.168.31.200/24
9.manager节点上测试ssh免密通道,如果正常最后会输出successfully
[root@mha_manager masterha]# masterha_check_ssh -conf=/etc/masterha/app1f
Mon May 8 11:50:00 2023 - [info] All SSH connection tests passed successfully.
如果报错,多看日志/var/log/masterha/app1/manager.log
注意是否每台mysql间都建立了ssh免密通道,否则会报错
根据提示信息,修复错误
10.在 manager 节点上测试 mysql 主从连接情况,最后出现 MySQL Replication Health is OK 字样说明正常。
[root@mha_manager masterha]# masterha_check_repl -conf=/etc/masterha/app1f
MySQL Replication Health is OK.
如果报错,多看日志/var/log/masterha/app1/manager.log
如果报错,思考是否软链接建立好了?或者主从复制搭建正确了
11.manager节点后台开启MHA
[root@mha_manager masterha]# nohup masterha_manager --conf=/etc/masterha/app1f --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 5085
1.查看 MHA 状态,可以看到当前的 master 是 Mysql1 节点。
[root@mha_manager masterha]# masterha_check_status --conf=/etc/masterha/app1f
app1 (pid:5085) is running(0:PING_OK), master:192.168.31.221
2.查看MHA日志,看到当前matser是192.168.31.221
[root@mha_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.31.221(192.168.31.221:3306) (current master)
3.查看mha进程
[root@mha_manager bin]# ps aux|grep manager
root 5085 0.1 4.5 299656 21992 pts/0 S 11:57 0:12 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1f --remove_dead_master_conf --ignore_last_failover
root 14939 0.0 0.2 112824 984 pts/0 S+ 14:39 0:00 grep --color=auto manager
12.故障转移效果测试,模拟matser宕机,指定slave1成为新的master
1.manager节点监控日志记录
[root@mha_manager bin]# tail -f /var/log/masterha/app1/manager.log
2.模拟master宕机,停掉master
[root@master mysql]# service mysqld stop
3.查看vip是否漂移到了slave1
4.查看日志信息
日志显示,master已经切换到了192.168.31.222(slave1)
slave2也已经选择slave1作为master
5.查看/etc/masterha/app1f
发现原来的server1配置被删除了
6.再来看看slave2的master_info信息
master已经切换到了192.168.31.222(slave1)
13.原master故障修复(原master转为slave,指向slave1)
1.原master开启mysqld
[root@master ~]# service mysqld start
2.修复主从,原master修改master_info指向新的master
root@(none) 17:18 mysql>change master to master_host='192.168.31.222',master_user='slave',master_password='123456',master_port=3306,master_auto_position=1;
root@(none) 17:19 mysql>start slave;
root@(none) 17:19 mysql>show slave status\G;
3.在 manager 节点上修改配置文件/etc/masterha/app1f(再把这个记录添加进去,因为master宕机后原来的server1会被自动删除)
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/data/mysql/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=slave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.31.221 -s 192.168.31.223
shutdown_script=""
ssh_user=root
user=mha
[server1]
hostname=192.168.31.222
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.31.221
port=3306
[server3]
hostname=192.168.31.223
port=3306
4.重启mha manager,并检查此时的master
[root@mha_manager ~]# masterha_stop --conf=/etc/masterha/app1f
Stopped app1 successfully.
[1]+ 退出 1 nohup masterha_manager --conf=/etc/masterha/app1f --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1
[root@mha_manager ~]# nohup masterha_manager --conf=/etc/masterha/app1f --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 20022
[root@mha_manager ~]# masterha_check_status --conf=/etc/masterha/app1f
app1 (pid:20022) is running(0:PING_OK), master:192.168.31.222
master已经从192.168.31.221切换到了192.168.31.222
并且原来的192.168.31.221(原master)变成了slave,并从192.168.31.222(原slave1)拿二进制日志了
至此,mha就算搭建成功了!
五、搭建mycat2实现对后端MySQL集群的读写分离,减轻主节点的读请求压力
有两台mycat服务器,由于篇幅有限,我就只做一台了,另外一台操作基本一致的
1.安装部署mycat2
1.安装好mycat2环境所需要的mysql数据库(和步骤一里安装mysql数据库一样,我就不重复了)
因为mycat2是部署成一个mysql服务,然后向操作mysql一样去操作mycat2,所以要安装好mysql环境
2.安装好mycat2的java环境
[root@mycat1 ~]# yum install -y java
3.官网下载源码包(核心源码包jar包、外壳zip包)
[root@mycat1 ~]# wget http://dl.mycat/2.0/1.22-release/mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar
[root@mycat1 ~]# wget http://dl.mycat/2.0/install-template/mycat2-install-template-1.21.zip
[root@mycat2 ~]# wget http://dl.mycat/2.0/1.22-release/mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar
[root@mycat2 ~]# wget http://dl.mycat/2.0/install-template/mycat2-install-template-1.21.zip
4.解压zip包
[root@mycat1 ~]# yum install -y unzip
[root@mycat1 ~]# unzip -q mycat2-install-template-1.21.zip
[root@mycat2 ~]# yum install -y unzip
[root@mycat2 ~]# unzip -q mycat2-install-template-1.21.zip
5.将jar包放入解压后的mycat/lib下
[root@mycat1 ~]# mv mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar mycat/lib/
[root@mycat2 ~]# mv mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar mycat/lib/
6.将整合好的mycat放入/usr/local文件夹下
[root@mycat1 bin]# mv /root/mycat /usr/local/
[root@mycat2 bin]# mv /root/mycat /usr/local/
7.授予相关文件最高权限,防止启动报错
[root@mycat1 bin]# cd /usr/local/mycat/bin/
[root@mycat1 bin]# chmod 777 mycat
[root@mycat1 bin]# chmod 777 wrapper-linux-ppc-64
[root@mycat1 bin]# chmod 777 wrapper-linux-x86-32
[root@mycat1 bin]# chmod 777 wrapper-linux-x86-64
8.将mycat2加入PATH环境变量,并设置开机启动
[root@mycat1 ~]# echo "PATH=/usr/local/mycat/bin/:$PATH" >>/root/.bashrc
[root@mycat1 ~]# PATH=/usr/local/mycat/bin/:$PATH
9.编辑prototypeDs.datasource.json默认数据源文件,并重启mycat(连接本机mysql数据库环境)
[root@mycat1 datasources]# vim prototypeDs.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"prototypeDs",
"password":"Sanchuang123#",
"type":"JDBC",
"url":"jdbc:mysql://localhost:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}
[root@mycat1 datasources]# mycat restart
Stopping mycat2...
mycat2 was not running.
Starting mycat2...
“password”:“Sanchuang123#” —本机mysql数据库的root用户密码
“user”:“root” —指定本机mysql数据库用户root
10.启动mycat2
[root@mycat1 ~]# mycat start
2.mysql集群(master、slave1、slave2)新建授权用户,允许mycat2访问
root@(none) 16:42 mysql>grant all on *.* to 'mycat2'@'192.168.31.%' identified by '123456';
3.验证数据库访问情况
mycat2作为数据库中间件要和数据库部署在不同机器上,所以要验证远程访问情况
注意:因为故障转移时,写服务器的访问是通过vip地址的,所以进行读写分离时,绑定后端数据源写服务器的时候的ip应该是vip地址。
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.31.200 #测试写服务器vip地址连接
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.31.221
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.31.222
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.31.223
4.登录mycat2客户端,并新建逻辑库test1
[root@mycat1 datasources]# mysql -uroot -p123456 -P8066 -h 192.168.31.180
mycat客户端默认的用户:root
默认密码123456
-h指定本机ip地址
-P8066 mycat客户端的端口
可以看到已经成功登录mycat客户端了
新建逻辑库test1
root@(none) 21:43 mysql>create database test1;
Query OK, 0 rows affected (0.34 sec)
新建完成后,会在schema目录下生成一个逻辑库文件
5.修改配置test1.schema.json配置文件,配置逻辑库对应的集群
[root@mycat1 schemas]# vim test1.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"test1",
"targetName":"cluster",
"shardingTables":{},
"views":{}
}
“targetName”:“cluster”,—新增一条配置,指向后端数据源集群的名字时cluster
重启mycat
[root@mycat1 schemas]# mycat restart
6.注解方式添加集群、数据源,对应后端真实数据库
注解方式相当于修改配置文件,但是一个一个去修改配置文件比较繁琐,我这里就使用了mycat2的特色:使用注解方式修改配置文件
注解方式可以直接在mycat2客户端命令行里直接运行,也可以通过连接SQLyog客户端,在SQLyog上运行,推荐在SQLyog等客户端上运行,效果展示的更好
登录mycat
[root@mycat1 ~]# mysql -uroot -p123456 -P8066 -h 192.168.31.180
添加只读数据源master
注意:因为之前进行mha故障迁移的时候,192.168.31.221(master)已经变为slave状态了,新的master是192.168.31.222(slave1)
/*+ mycat:createDataSource{ "name":"r1",
"url":"jdbc:mysql://192.168.31.221:3306/test1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"mycat2",
"password":"123456" } */;
添加读写数据源slave1
slave1现在是写服务器,可以通过vip地址接收写请求,所以数据源ip写vip地址192.168.31.200
/*+ mycat:createDataSource{ "name":"rw",
"url":"jdbc:mysql://192.168.31.200:3306/test1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"mycat2",
"password":"123456" } */;
添加只读数据源slave2
/*+ mycat:createDataSource{ "name":"r2",
"url":"jdbc:mysql://192.168.31.223:3306/test1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"mycat2",
"password":"123456" } */;
查询配置数据源结果
root@(none) 22:12 mysql>/*+ mycat:showDataSources{} */\G;
创建集群信息
/*!mycat:createCluster{"name":"cluster","masters":["rw"],"replicas":["r1","r2"]} */;
查看集群信息
/*+ mycat:showClusters{} */;
7.重启mycat
[root@mycat1 schemas]# mycat restart
8.验证读写分离
1.查看逻辑库真实数据库同步效果
[root@mycat1 schemas]# mysql -uroot -p123456 -P8066 -h192.168.31.180
可以看到mycat逻辑库test1已经成功关联到了后端真实mysql数据库
2.为了让读写分离的效果更明显,逐步关闭从节点的主从复制
slave2停止主从复制
root@test1 18:11 mysql>stop slave;
slave1(新master)插入数据
root@test1 18:16 mysql>insert into t1 values(5,'ko');
master停止主从复制
root@test1 18:11 mysql>stop slave;
slave1(新master)再插入数据
root@test1 18:16 mysql>insert into t1 values(6,'kk');
登录mycat客户端,查询t1表数据
root@test1 18:16 mysql>select * from t1;
发现数据再不断的变化,这就是读请求的负载均衡效果!
对mycat逻辑库test1的读操作,负载均衡的将读请求转发到了后端3个真实数据库
而对mycat逻辑库test1的写操作,只会转发到后端的主服务器上
六、使用keepalived搭建双vip架构,实现对mycat2中间件的高可用
1.安装部署keepalived
[root@mycat2 ~]# yum install -y epel-release #安装epel源
[root@mycat2 ~]# yum install -y keepalived #安装keepalived
2.修改配置文件/etc/keepalived/keepalived.conf
mycat1
[root@mycat1 ~]# 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广播
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER #选为master
interface ens33 #绑定本机网卡ens33
virtual_router_id 88 #虚拟路由器id,两个实例一定不能一样,否则会出现脑裂现象
priority 150 #优先级
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.31.188 #vip地址
}
}
vrrp_instance VI_2 {
state BACKUP
interface ens33
virtual_router_id 99
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.31.199
}
}
mycat2
[root@mycat2 ~]# 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 BACKUP
interface ens33
virtual_router_id 88
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.31.188
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 99
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.31.199
}
}
3.重启keepalived,查看vip地址
[root@mycat1 ~]# service keepalived restart
mycat1
mycat2
4.模拟主机宕机,观察vip漂移
mycat1停止keepalived
[root@mycat1 ~]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
查看mycat2的vip地址
发现mycat1的vip地址188漂移到了mycat2上
重启keepalived,发现vip地址188又漂回了mycat1上
5.测试通过vip地址登录mycat客户端
[root@mycat1 users]# mysql -uroot -p123456 -P8066 -h 192.168.31.188
[root@mycat2 users]# mysql -uroot -p123456 -P8066 -h 192.168.31.199
当一台mycat宕机后,vip地址自动漂移到另外一台mycat服务器,即使一台mycat宕机了,也可以通过另外一台mycat正确访问,正常进行读写分离,实现了对mycat中间件的高可用
七、使用sysbench压力测试工具测试整个数据库集群的性能,了解集群系统性能资源的瓶颈
1.安装部署sysbench
[root@sysbench ~]# yum install -y epel-release
[root@sysbench ~]# yum install -y sysbench
2.调大内核资源限制
[root@sysbench ~]# ulimit -n 100000
[root@sysbench ~]# ulimit -u 100000
[root@sysbench ~]# ulimit -s 100000
2.写请求压力测试
1.模拟1000个客户10秒内持续写入
数据准备
[root@sysbench ~]# sysbench --threads=1000 --time=10 --report-interval=5 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-port=8066 --mysql-host=192.168.31.188 --mysql-db=test1 oltp_insert prepare
–threads:并发数,这里模拟的是1000个客户端;
–time:测试时间,单位为秒;
–report-interval:每隔几秒输出一次详细结果;
–db-driver:测试的数据库,可以是mysql,postgresql等,
–mysql-user:数据库用户(因为这里测试入口是mycat,所以也创建也mycat用户);
–mysql-password:数据库密码;
–mysql-port:数据库端口号;
–mysql-host:数据库ip地址;
–mysql-db:针对哪个库进行测试(这里我使用的之前建立的逻辑库test1);
oltp_insert:测试的sql语句类型,因为场景为高并发写入,肯定是insert语句,所以选择oltp_insert;
测试
[root@sysbench ~]# sysbench --threads=1000 --time=60 --report-interval=5 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-port=8066 --mysql-host=192.168.31.188 --mysql-db=test1 oltp_insert run
测试结果
关键指标:
- tps:接收、处理的tps数量(tps是一个完成的请求,即:客户端发送一个请求,后端处理并正确返回给客户端 是一个完整的tps)*
- qps:接收、处理的qps数量(qps代表的是服务端接收的能力,客户端发送一个请求,就是一个qps,或者说是一个查询请求;)
最大tps:3485.09 最大qps:3485.09
清空测试数据
[root@sysbench ~]# sysbench --threads=1000 --time=60 --report-interval=5 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-port=8066 --mysql-host=192.168.31.188 --mysql-db=test1 oltp_insert cleanup
2.模拟3000个客户10秒内持续写入
测试结果
最大tps:4486.61 最大qps:4488.39
3.IO性能压力测试
mycat1主机安装sysbench工具
创建5个文件,总共2G,每个文件大概400M
准备数据
[root@mycat1 ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare
测试
[root@mycat1 ~]# sysbench --events=5000 --threads=16 fileio --file-num=5 --file-total-size=2G --file-test-mode=rndrw --file-fsync-freq=0 --file-block-size=16384 run
测试结果
4.cpu性能压力测试
[root@mycat1 ~]# sysbench cpu --threads=40 --events=10000 --cpu-max-prime=20000 run
八、搭建基于prometheus+grafana的监控系统,对数据库集群进行监控
1.安装部署mysqld_exporter
由于篇幅有限,我只写了slave1的操作,master、slave2、mycat1、mycat2服务器的操作我就不写了,配置基本是一致的;
注意:在配置mycat授权用户的时候,记得要登录mycat的客户端而不是mysql的,因为要监控的是mycat
1.所有被监控数据库服务器建立允许mysqld_exporter访问的授权用户
root@(none) 20:28 mysql>grant all on *.* to 'mysqld_exporter'@'%' identified by '123456';
2.上传软件包到linux家目录下
3.解压,并移动到/usr/local/mysqld_exporter下
[root@slave1 ~]# tar -zxvf mysqld_exporter-0.12.1.linux-amd64\ \(1\).tar.gz
mysqld_exporter-0.12.1.linux-amd64/
mysqld_exporter-0.12.1.linux-amd64/NOTICE
mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter
mysqld_exporter-0.12.1.linux-amd64/LICENSE
[root@slave1 ~]# ls
11.text mha4mysql-node-0.58.tar.gz mysqld_exporter-0.12.1.linux-amd64 nohup.out
anaconda-ks.cfg myf mysqld_exporter-0.12.1.linux-amd64 (1).tar.gz
mha4mysql-node-0.58 mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz node_exporter-1.5.0.linux-amd64.tar.gz
[root@slave1 ~]# mv mysqld_exporter-0.12.1.linux-amd64 /usr/local/mysqld_exporter
[root@slave1 ~]# cd /usr/local/mysqld_exporter/
[root@slave1 mysqld_exporter]# ls
LICENSE mysqld_exporter NOTICE
4.在/usr/local/mysqld_exporter下编辑连接本机数据库的配置文件
[root@slave1 mysqld_exporter]# vim myf
[client]
user=mysqld_exporter
password=123456
user=mysqld_exporter #监控本机数据库的授权用户
password=123456 #密码
4.将mysqld_exporter命令加入环境变量,并设置开机自启
[root@slave1 mysqld_exporter]# PATH=/usr/local/mysqld_exporter/:$PATH
[root@slave1 mysqld_exporter]# echo "PATH=/usr/local/mysqld_exporter/:$PATH" >>/root/.bashrc
5.后台启动
[root@slave1 mysqld_exporter]# nohup mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/myf &
6.看进程,看端口,网页访问测试
看进程
[root@slave1 ~]# ps aux|grep exporter
看端口
[root@slave1 ~]# netstat -anplut|grep mysqld
访问测试
2.安装prometheus,配置成服务
1.上传安装包到linux家目录下
2.解压
[root@prometheus ~]# tar zvxf prometheus-2.43.0.linux-amd64.tar.gz
[root@prometheus ~]# mv prometheus-2.43.0.linux-amd64 /prometheus
3.配置成服务
[root@prometheus system]# vi /usr/lib/systemd/system/prometheus.service
[root@prometheus system]# systemctl daemon-reload
[root@prometheus system]# service prometheus start
Redirecting to /bin/systemctl start prometheus.service
4.访问测试
查看端口
访问页面
3.Prometheus添加node节点
[root@prometheus prometheus]# vi prometheus.yml
scrape_configs:
# The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
- job_name: "prometheus"
static_configs:
- targets: ["localhost:9090"]
- job_name: "mycat1"
static_configs:
- targets: ["192.168.31.180:9104"]
- job_name: "mycat2"
static_configs:
- targets: ["192.168.31.190:9104"]
- job_name: "slave1"
static_configs:
- targets: ["192.168.31.222:9104"]
- job_name: "slave2"
static_configs:
- targets: ["192.168.31.223:9104"]
- job_name: "master"
static_configs:
- targets: ["192.168.31.221:9104"]
访问测试
成功!
4.安装grafana
1.上传rpm包
2.安装
[root@prometheus ~]# yum install -y grafana-enterprise-9.1.2-1.x86_64.rpm
3.启动
[root@prometheus ~]# service grafana-server start
4.访问测试
查看端口,为3000
页面访问,第一次登录账号密码默认都为admin
5.增添Prometheus数据源
6.导入14057模板
14057模板比较美观,出图效果也比较好,推荐使用
7.效果展示
九、搭建DNS主域名服务器,增添两条负载均衡记录,实现对mycat2双vip地址的DNS负载均衡
1.安装
[root@dns ~]# systemctl disable firewalld #关闭防火墙,防止windows客户机无法访问dns服务器
[root@dns ~]# systemctl disable NetworkManager #关闭NetworkManager
[root@dns ~]# yum install bind* #安装dns服务的软件包
[root@dns ~]# service named start #启动dns服务
[root@dns ~]# systemctl enable named #开机启动dns服务
2.修改dns配置文件,任意ip可以访问本机的53端口,并且允许dns解析。
[root@dns ~]# 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.搭建主域名服务器
1.编辑dns次要配置文件/etc/named.rfc1912.zones,增加一条主域名记录
zone "chenlb666.top" IN {
type master; #类型为主域名
file "chenlb666.top.zone"; #chenlb666.top域名的数据文件,需要去/var/named/下创建
allow-update { none; };
};
2.创建chenlb666.top主域名的数据文件
[root@dns ~]# cd /var/named/
[root@dns named]# cp -a named.localhost chenlb666.top.zone
3.编辑数据文件,增加两条负载均衡记录。
[root@dns named]# vim chenlb666.top.zone
$TTL 1D
@ IN SOA @ rname.invalid. (
0 ; serial
1D ; refresh
1H ; retry
1W ; expire
3H ) ; minimum
NS @
A 192.168.31.153 #默认指向本机ip地址
www IN A 192.168.31.188 #负载均衡记录指向mycat1的vip地址
www IN A 192.168.31.199 #负载均衡记录指向mycat2的vip地址
4.刷新dns服务
[root@dns named]# service named restart
4.修改windows客户机的dns服务器的地址为搭建的dns服务器192.168.31.153。
5.效果测试
同一域名解析除了中间件mycat的双vip地址,实现了基于dns的负载均衡
项目遇到的问题
-
各种软件的有些配置项加了注释项,导致无法正常启动
答:多查看软件的报错信息以及查看服务日志,帮助很大 -
主从复制时,只在slave上进行了操作,导致事务数比主服务器还要多,主从复制一直起不来
答:查看报错信息,是事务数比主服务器还要多—>尽可能删除比主服务器还要多出来的数据,在reset master,并重新设置master_info信息 -
本以为mycat访问读写数据源的vip地址(mha架构里写服务器的特有ip)要修改mysql的绑定ip地址才能登录,后来发现只要新建了允许mycat访问的授权用户允许所有ip登录就行
-
本以为通过keepalived架构中的vip地址登录进mycat客户端,需要新建用户文件,后来发现不需要,直接-h指定vip地址就可以直接登录mycat客户端
-
搭建mycat2时,mycat2一直无法正常启动,后来去看了日志信息,发现是因为没有安装java环境。。
-
一直登录不上mycat2客户端,原因是因为没有部署好mycat2的mysql环境,修改默认数据源对应的mysql环境,因为mycat2是部署成一个mysql服务,然后向操作mysql一样去操作mycat2
-
使用rsync时,数据源备份文件/backup/打成了/backup导致把整个文件夹都同步过去了
-
发现dns负载均衡的效果不是很明显,再linux上访问能解析出199,而在windows机上能解析出188
-
压力测试的时候,由于内核参数的限制,导致无法起太多的线程
答:修改内核限制参数,以及调大mysql中与内核相关的参数 -
使用ansible调用一键二进制安装脚本中mysql加入环境变量不成功
答:再使用shell模块,远程加入PATH变量 -
ulimit -n修改不生效
答:写入到/etc/security/limits.conf中使其永久生效 -
修改主机名后,mysql重启不成功
答:杀死所有mysqld进程,在重新启动mysql
项目心得
- 提前规划好整个集群的架构,可以提高项目开展时效率
- 运行报错,多看出错信息提示以及日志,帮助很大
- 对基于GTID+半同步的主从复制有了更深入的理解
- 对keepalived的脑裂和vip漂移现象也有了更加深刻的体会和分析
- 加强了mha架构中自动failover的实现原理的理解
- 对基于mycat2的读写分离过程更加的熟悉
- 认识到了数据备份的重要性
- 深刻的体会到了rsync+sersync数据同步工具的便利与好处
- 熟练了sysbench下的压力测试,认识到了系统性能资源的重要性,对压力测试下整个集群的瓶颈有了一个整体概念
- 对监控也有了的更进一步的认识,监控可以提前看到问题,做好预警
- 对很多软件之间的配合有了一定的理解,如mycat2、mha、mysql等
- troubleshooting的能力得到了提升
本文标签: 主从集群mhamysqlKeepalived
版权声明:本文标题:基于mycat2+mha+keepalived的半同步主从复制mysql集群 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dongtai/1729566537a1206578.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论