准备七台机器，4台是MySQL服务器，搭建主从复制的集群，一个是master服务器，2个是slave服务器，一个延迟备份服务器，同时延迟备份服务器也可以充当异地备份服务器，数据从master或者slave上导出，然后rsync到备份服务器。
2台MySQLrouter服务器，安装好keepalived软件，实现高可用的读写分离服务。
1台ansible中控服务器（中台服务器），实现对MySQL整个及群里的服务器进行批量管理。
| 机器名称 | IP地址 |
| mysql-master | 192.168.0.198 |
| mysql-slave1 | 192.168.0.199 |
| mysql-slave2 | 192.168.0.200 |
| mysql-backup | 192.168.0.202 |
| mysql-ansible | 192.168.0.203 |
| mysql-router1 | 192.168.0.106 |
| mysql-router2 | 192.168.0.100 |
| test | 192.168.38.127 |
第1步：搭建好4台MySQL数据库服务器，安装好MySQL
- master上的配置
1.1安装半同步插件
root@(none) 09:59 scmysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
安装从的插件是为了方便把基础数据传给从服务器后任然有从的插件
root@(none) 18:14 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
1.2master上的配置文件
[root@sc-master ~]# cat /etc/my.cnf
[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
expire_logs_days = 7
#开启半同步，需要提前安装半同步的插件
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
#gtid功能
#gtid-mode=ON
#enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
[root@sc-master ~]#
[root@sc-master mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
1.3在master上新建一个授权用户，给slave来复制二进制日志
root@(none) 16:05 mysql>grant replication slave on *.* to 'niecailing'@'192.168.0.%' identified by 'Sanchuang123#';
Query OK, 0 rows affected, 1 warning (1.01 sec)
1.4 导出master上的基础数据
[root@sc-master ~]# mysqldump -uroot -p'Sanchuang123#' --all-databases --triggers --routines --events --single-transaction >all_db.SQL
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2.slave的配置(两台slave服务器都差不多，只需要修改server_id就行，slave2不需要开启写二进制文件功能)
2.1安装slave插件
root@(none) 18:14 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.07 sec)
2.2slave上的配置文件
[root@sc-slave mysql]# cat /etc/my.cnf
[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=utf8mb4
#error log
log_error = tanxue.err
#general log
general_log
#slow query log
slow_query_log = 1
long_query_time = 0.001
#log bin 二进制日志
log_bin
server_id = 2
expire_logs_days = 7
#拿到主的二进制文件进行SQL语句时，会产生二进制文件
log_slave_updates=ON
#开启半同步，需要提前安装半同步的插件
rpl_semi_sync_slave_enabled=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m scmysql>
[root@sc-slave mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@sc-slave mysql]#
3.backup上的配置
3.1安装slave插件
root@(none) 18:14 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.07 sec)
3.2slave上的配置文件
[root@sc-slave mysql]# cat /etc/my.cnf
[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=utf8mb4
#error log
log_error = tanxue.err
#general log
general_log
#slow query log
slow_query_log = 1
long_query_time = 0.001
#log bin 二进制日志
log_bin
server_id = 2
expire_logs_days = 7
#开启半同步，需要提前安装半同步的插件
rpl_semi_sync_slave_enabled=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m scmysql>
[root@sc-slave mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@sc-slave mysql]#
注意：backup机器、slave2是不需要开启log_slave_updates=ON，只有slave1开启就行，因为backup要从slave1里面拿二进制文件执行
第2步：搭建好ansible服务器
1.安装好ansible服务
[root@ansible ~]# yum install epel-release -y
[root@ansible ~]# yum install ansible -y
2.将dbservers写入ansible配置文件中
[root@ansible ~]# vi /etc/ansible/hosts
[dbservers]
192.168.0.198
192.168.0.199
192.168.0.200
192.168.0.202
[dbslaves]
192.168.0.199
192.168.0.200
192.168.0.202
3.将ansible服务器与上面4台MySQL服务器建立免密通道
创建密钥
[root@ansible ~]# ssh-keygen -t rsa
传送密钥
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.198
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.199
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.200
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.202
验证是否建立免密通道成功
[root@ansible ~]# ssh 'root@192.168.0.198'
[root@ansible ~]# ssh 'root@192.168.0.199'
[root@ansible ~]# ssh 'root@192.168.0.200'
[root@ansible ~]# ssh 'root@192.168.0.202'
- 将原先备份的master基础数据传送到slave上
先将基础数据传入到本地
[root@ansible ~]# scp root@192.168.0.198:/root/all_db.SQL .
all_db.SQL 100% 912KB 32.8MB/s 00:00
把基础数据下发给所有slave
[root@ansible ~]# ansible -m copy -a "src=/root/all_db.SQL dest=/root" dbslaves
在三台slave上导入数据
[root@sc-slave ~]# mysql -uroot -p'Sanchuang123#' <all_db.SQL
mysql: [Warning] Using a password on the command line interface can be insecure.
注意：如果出现以下错误,是因为要先备份完再开启gtid功能
[root@slave2 ~]# mysql -uroot -p'Sanchuang123#' <all_db.SQL
ERROR 1840 (HY000) at line 1993: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
操作如下：
先关闭四台机器的gtid功能
Master上的配置文件
#gtid-mode=ON
#enforce-gtid-consistency=ON
Slave上的配置文件
#gtid-mode=ON
#enforce-gtid-consistency=ON
log_slave_updates=ON
rpl_semi_sync_slave_enabled=1
在master上将数据导出
[root@sc-master ~]# mysqldump -uroot -p'Sanchuang123#' --all-databases >all_db.SQL
mysqldump: [Warning] Using a password on the command line interface can be insecure.
在ansible服务器上下发给所有slave
先将基础数据传入到本地
[root@ansible ~]# scp root@192.168.0.198:/root/all_db.SQL .
all_db.SQL 100% 912KB 32.8MB/s 00:00
把基础数据下发给所有slave
[root@ansible ~]# ansible -m copy -a "src=/root/all_db.SQL dest=/root" dbslaves
在三台slave上导入数据
[root@sc-slave ~]# mysql -uroot -p'Sanchuang123#' <all_db.SQL
mysql: [Warning] Using a password on the command line interface can be insecure.
第3步 开启GTID功能，开启主从复制功能
1.Master上的配置文件
加入下面配置：
#gtid功能
gtid-mode=ON
enforce-gtid-consistency=ON
2.slave和backup上的配置文件
加入下面配置：
#开启gtid功能
gtid-mode=ON
enforce-gtid-consistency=ON
注意：如果启动失败
要重新安装半同步的从插件
root@(none) 11:22 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.71 sec)
并且注释这里
#rpl_semi_sync_slave_enabled=1
3.在从slave上开启相关配置
root@(none) 20:29 mysql>stop slave;
Query OK, 0 rows affected (0.01 sec)
root@(none) 20:29 mysql>reset master;
Query OK, 0 rows affected (0.01 sec)
root@(none) 20:29 mysql>reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@(none) 20:29 mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.198' , MASTER_USER='niecailing',MASTER_PASSWORD='Sanchuang123#',MASTER_PORT=3306, master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
root@(none) 20:29 mysql>start slave;
Query OK, 0 rows affected (0.01 sec)
root@(none) 20:30 mysql>show slave status;
4.验证相关配置
4.1验证半同步功能是否开启：
Master上
root@(none) 11:27 scmysql>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
root@(none) 11:27 scmysql>
Slave上
root@(none) 11:28 mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME
-> LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.08 sec)
或者使用下面命令
Master上
root@(none) 16:02 mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| 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.01 sec)
Slave上
root@(none) 20:44 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.02 sec)
4.2进行删库、建库操作验证是否同步
第4步在backup上开启延迟备份功能（backup从slave1拿二进制文件）
1.在backup服务器上配置好相关配置，并且设置延迟10min
root@(none) 21:10 mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 21:10 mysql>reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@(none) 21:10 mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.199' , MASTER_USER='niecailing',MASTER_PASSWORD='Sanchuang123#',MASTER_PORT=3306, master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
root@(none) 21:10 mysql>CHANGE MASTER TO MASTER_DELAY = 600; #延迟备份10min
Query OK, 0 rows affected (0.00 sec)
root@(none) 21:11 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 21:11 mysql>show slave status\G; #查看sql线程和io线程是否开启
2.进行建库或者删库操作，验证是否延迟备份
第5步在master上创建一个计划任务每天2:30进行数据库的备份，编写备份脚本每天备份数据，备份文件包含当前的日期，使用rsync+sersync远程同步到ansible服务器（相当于一台异地备份服务器）
1.在master与ansible服务器之间建立双向免密通道
因为上面已经建立好ansible到master的免密通道，旧秩序呀建立master到ansible的免密通道
在master上建立免密通道
[root@sc-master ~]# ssh-keygen -t rsa
[root@sc-master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.203
验证免密通道是否建立成功
[root@sc-master ~]# ssh root@192.168.0.203
Last login: Wed May 1 18:37:24 2024 from 192.168.0.202
2.在ansible服务器上开启rsync
2.1、关闭 selinux
[root@ansible ~]# mkdir /backup
[root@ansible ~]# setenforce 0
[root@ansible ~]# getenforce
Permissive
2.2、关闭防火墙
[root@mysql2 backup]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
2.3、安装rsync服务端软件，并设置开机启动
[root@mysql2 backup]# yum install rsync xinetd -y
#设置开机启动
[root@mysql2 backup]# vim /etc/rc.d/rc.local
/usr/bin/rsync --daemon --config=/etc/rsyncd.conf # 添加开机启动
#添加可执行权限
[root@mysql2 backup]# chmod +x /etc/rc.d/rc.local
#启动服务
[root@mysql2 backup]# systemctl start xinetd
（xinetd是一个提供保姆服务的进程，rsync是它照顾的进程）
独立的服务：ssh，dhcp，mysql
非独立的服务，非独立的服务需要依赖其他的服务来管理，rsync就是一个非独立的服务，依赖xinetd来管理
2.4、创建rsyncd.conf配置文件
[root@mysql2 backup]# 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.0.198 #允许的ip地址(数据源服务器地址)
2.5、创建用户认证文件
#这其实可以不写，因为rsync底层用的就是ssh，只要双方建立好免密通道就不需要密码了
[root@mysql2 backup]# vim /etc/rsync.pass 配置文件，添加以下内容，添加允许传输用户和密码
sc:sc123456#在文件中添加这一行 ， 格式是用户名:密码，可以设置多个，每行一个用户名:密码
2.6、设置文件权限
$ chmod 600 /etc/rsyncd.conf #设置文件所有者读取、写入权限
$ chmod 600 /etc/rsync.pass #设置文件所有者读取、写入权限
2.7、启动rsync和xinetd
[root@mysql2 backup]# /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
[root@mysql2 backup]# ps -aux|grep rsync
root 8838 0.0 0.0 114852 576 ? Ss 13:13 0:00 /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
root 8840 0.0 0.0 112824 976 pts/1 S+ 13:14 0:00 grep --color=auto rsync
[root@mysql2 backup]# systemctl start xinetd
[root@mysql2 backup]# ps -aux|grep xinetd
root 8829 0.0 0.0 25044 584 ? Ss 13:02 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid
root 8848 0.0 0.0 112824 980 pts/1 R+ 13:14 0:00 grep --color=auto xinetd
2.8.查看rsync监听的端口号
[root@mysql2 backup]# netstat -anplut
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:873 0.0.0.0:* LISTEN 8838/rsync
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 6879/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 7193/master
tcp 0 0 192.168.0.197:57066 39.155.141.16:80 TIME_WAIT -
tcp 0 36 192.168.0.197:22 192.168.0.104:65140 ESTABLISHED 8480/sshd: root@pts
tcp 0 0 192.168.38.143:22 192.168.38.1:64656 ESTABLISHED 7071/sshd: root@pts
tcp 0 0 192.168.0.197:57064 39.155.141.16:80 TIME_WAIT -
tcp6 0 0 :::873 :::* LISTEN 8838/rsync
tcp6 0 0 :::3306 :::* LISTEN 7110/mysqld
tcp6 0 0 :::22 :::* LISTEN 6879/sshd
tcp6 0 0 ::1:25 :::* LISTEN 7193/master
udp 0 0 127.0.0.1:323 0.0.0.0:* 5934/chronyd
udp6 0 0 ::1:323 :::* 5934/chronyd
3.master服务器上开启rsync
3.1、关闭 selinux #永久关闭linux防火墙
3.2、关闭防火墙
3.3、安装rsync服务端软件
[root@mysql1 backup]# yum install rsync xinetd -y
[root@mysql1 backup]# vim /etc/rc.d/rc.local # #设置开机启动
/usr/bin/rsync --daemon --config=/etc/rsyncd.conf # 添加开机启动
[root@mysql1 backup]# chmod +x /etc/rc.d/rc.local
[root@mysql1 backup]# systemctl start xinetd
#启动xinetd
[root@mysql1 backup]# 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
#启动服务
[root@mysql1 backup]# systemctl start xinetd
#启动（CentOS中是以xinetd来管理rsync服务的）
3.4、创建认证密码文件
#编辑文件，添加以下内容，该密码应与目标服务器中的/etc/rsync.pass中的密码一致
[root@mysql1 backup]# vim /etc/passwd.txt
[root@mysql1 backup]# cat /etc/passwd.txt
sc123456
#设置文件权限，只设置文件所有者具有读取、写入权限即可
[root@mysql1 backup]# chmod 600 /etc/passwd.txt
3.5、测试数据同步
Master服务器192.168.0.198 到ansible服务器192.168.0.203之间的数据同步
在master上输入，表示将/backup文件作为监听文件
[root@mysql1 backup]# rsync -avH --port=873 --progress --delete /backup root@192.168.0.203::back_data --password-file=/etc/passwd.txt
sending incremental file list
backup/
backup/2024-04-22_wangshuai.sql
2,066 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=2/4)
backup/backup_db.sh
218 100% 212.89kB/s 0:00:00 (xfr#2, to-chk=1/4)
backup/ws.sql
2,018 100% 1.92MB/s 0:00:00 (xfr#3, to-chk=0/4)
sent 4,588 bytes received 77 bytes 405.65 bytes/sec
total size is 4,302 speedup is 0.92
4.实现自动实时同步
在master服务器上开启sersync功能
4.1、修改inotify默认参数（inotify默认内核参数值太小） 修改参数：
[root@mysql1 backup]# sysctl -w fs.inotify.max_queued_events="99999999"
fs.inotify.max_queued_events = 99999999
[root@mysql1 backup]# sysctl -w fs.inotify.max_user_watches="99999999"
fs.inotify.max_user_watches = 99999999
[root@mysql1 backup]# sysctl -w fs.inotify.max_user_instances="65535"
fs.inotify.max_user_instances = 65535
永久修改参数
[root@mysql1 backup]# vim /etc/sysctl.conf
fs.inotify.max_queued_events=99999999
fs.inotify.max_user_watches=99999999
fs.inotify.max_user_instances=65535
4.2、安装sersync
[root@mysql1 backup]# yum install wget -y
[root@mysql1 backup]# wget http://down.whsir.com/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz
[root@mysql1 backup]# tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz
[root@mysql1 backup]# mv GNU-Linux-x86/ /usr/local/sersync
4.3、创建rsync
[root@mysql1 backup]# cd /usr/local/sersync
[root@mysql1 sersync]# ls
confxml.xml sersync2
备份配置文件，防止修改错了，不知道哪里出错，好还原
[root@mysql1 sersync]# cp confxml.xml confxml.xml.bak
[root@mysql1 sersync]# cp confxml.xml data_configxml.xml
[root@mysql1 sersync]# ls
confxml.xml confxml.xml.bak data_configxml.xml sersync2
data_configxml.xml 是后面需要使用的配置文件
4.4、修改配置 data_configxml.xml 文件
第24行后的配置
[root@mysql1 sersync]# vim data_configxml.xml
<localpath watch="/backup">
<remote ip="192.168.0.203" name="back_data"/>
<!--<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"/>
4.5、启动服务
[root@mysql1 sersync]# PATH=/usr/local/sersync/:$PATH
[root@mysql1 sersync]# which sersync2
/usr/local/sersync/sersync2
[root@mysql1 sersync]# echo 'PATH=/usr/local/sersync/:$PATH' >>/root/.bashrc
[root@mysql1 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.2.197::back_data >/dev/null 2>&1
run the sersync:
watch path is: /backup
*************************************************结束*****************************************************************
[root@mysql1 sersync]# ps -aux|grep sersync
root 6948 0.0 0.0 114852 556 ? Ss 17:50 0:00 /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
root 7555 0.0 0.0 92324 716 ? Ssl 18:20 0:00 sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
root 7573 0.0 0.0 112824 976 pts/0 R+ 18:22 0:00 grep --color=auto syn
验证：去/backup目录下新建一些文件或者文件夹，测试是否在备份服务器上可以看到
4.6、设置sersync监控开机自动执行
[root@sc-mysql backup]# vim /etc/rc.local
/usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/data_configxml.xml
4.7、验证是否实现同步
在master上建立一个文件，看ansible上也有文件
5.实现每晚2：30自动备份所有数据
在master上执行
[root@sc-master /]# vim backup_all_db.sh
#!/bin/bash
mysqldump -uroot -p"Sanchuang123#" >/backup/$(date +%Y%m%d)_all_db.SQL
[root@sc-master /]# crontab -e
30 2 * * * bash /backup_all_db.sh
第6步搭建2台mysqlrouter服务器实现读写分离和高可用功能
1.在2台mysqlrouter服务器上安装部署mysqlrouter中间件软件，实现读写分离
1.1安装mysqlrouter
先去官方网站下载，将安装包传送到Linux中
官方地址：https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.0.21-1.el7.x86_64.rpm
解压
[root@router1 ~]# rpm -ivg mysql-router-community-8.0.21-1.el7.x86_64\ \(1\).rpm
[root@router1 ~]# cd /etc/mysqlrouter 进入存放配置文件的目录
[root@router1 mysqlrouter]# ls
mysqlrouter.conf
1.2修改配置文件
[root@router1 mysqlrouter]# vim mysqlrouter.conf
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter
[logger]
level = INFO
#read-only
[routing:slaves]
bind_address = 192.168.0.106:7001
#slave
destinations = 192.168.0.199:3306,192.168.0.200:3306
mode = read-only
connect_timeout = 1
#write-only
[routing:masters]
bind_address = 192.168.0.106:7002
#master
destinations = 192.168.0.198:3306
mode = read-write
connect_timeout = 2
1.3启动MySQL router服务
[root@router1 mysqlrouter]# service mysqlrouter start
Redirecting to /bin/systemctl start mysqlrouter.service
1.4查看mysqlrouter监听端口
[root@router1 mysqlrouter]# netstat -anplut|grep mysqlrouter
tcp 0 0 192.168.0.106:7001 0.0.0.0:* LISTEN 7557/mysqlrouter
tcp 0 0 192.168.0.106:7002 0.0.0.0:* LISTEN 7557/mysqlrouter
1.4在master上创建两个测试账号，一个是读的，一个是写的
root@(none) 14:46 mysql>create user 'write'@'%';
Query OK, 0 rows affected (0.01 sec)
root@(none) 14:47 mysql>grant all on *.* to 'write'@'%';
Query OK, 0 rows affected (0.00 sec)
root@(none) 14:47 mysql>create user 'read'@'%';
Query OK, 0 rows affected (0.01 sec)
root@(none) 14:47 mysql>grant select on *.* to 'read'@'%';
Query OK, 0 rows affected (0.00 sec)
1.5在mysql客户端上测试读写分离的效果，使用2个测试账号（需要安装MySQL客户端mysqladmin）
#登录读功能用户
[root@node1 ~]# mysql -h 192.168.2.106 -P 7001 -uscread -p'Sanchuang1234#'
进行删库等操作，发现无法进行
#登录写功能用户
[root@node1 ~]# mysql -h 192.168.2.106 -P 7002 -uscwrite -p'Sanchuang1234#'
2.在2台服务器上都安装keepalived实现高可用，配置2个vrrp实例实现双vip的高可用功能
2.1安装keepalived
yum install keepalived -y
2.2修改配置文件
[root@router1 mysqlrouter]# cd /etc/keepalived/
[root@router1 keepalived]# ls
keepalived.conf
[root@router1 keepalived]# vim keepalived.conf
router1配置
! 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_script send_mail {
script "/mail/sendmail.sh"
interval 3
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 80
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
virtual_ipaddress {
192.168.0.187
}
}
vrrp_instance VI_2 {
state backup
interface ens33
virtual_router_id 81
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
virtual_ipaddress {
192.168.0.186
}
}
router2的配置
! 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
}
#keepalived进程去封装vrrp报文
#当keepalived进程发送vrrp报文，执行的脚本，每隔3秒钟执行一次，配置名字叫send_mail
vrrp_script send_mail {
script "/mail/sendmail.sh"
interval 3
}
vrrp_instance VI_1 {
state backup
interface ens33
virtual_router_id 80
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
notify_backup /mail/backup.sh
notify_master /mail/master.sh
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.0.187
}
}
vrrp_instance VI_2 {
state master
interface ens33
virtual_router_id 81
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
notify_backup /mail/backup.sh
notify_master /mail/master.sh
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.0.186
}
}
注意：
mysqlrouter的配置文件中bind_address最好写成bind_address = 0.0.0.0:7002，方便vip漂移
第7步进行压力测试，使用sysbench进行测试
1.在测试机上安装sysbench
[root@nfs-server ~]# yum install epel-release -y
[root@nfs-server ~]# yum install sysbench -y
2.在master上建立sbtest库
root@(none) 01:46 mysql>create database sbtest;
Query OK, 1 row affected (0.00 sec)
3.在测试机上开始测试
sysbench插入数据
[root@nfs-server src]# sysbench --mysql-host=192.168.2.187 --mysql-port=7002 --mysql-user=scwrite --mysql-password='Sanchuang1234#' /root/sysbench/sysbench-1.0.15/src/lua/oltp_common.lua --tables=10 --table_size=10000 prepare
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 10000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 10000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 10000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 10000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 10000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...
sysbench的检测结果
[root@nfs-server src]# sysbench --threads=4 --time=20 --report-interval=5 --mysql-host=192.168.2.187 --mysql-port=7001 --mysql-user=scwrite --mysql-password='Sanchuang1234#' /root/sysbench/sysbench-1.0.15/src/lua/oltp_read_write.lua --tables=10 --table_size=100000 run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 4 tps: 109.14 qps: 2203.91 (r/w/o: 1545.10/113.33/545.48) lat (ms,95%): 80.03 err/s: 0.80 reconn/s: 0.00
[ 10s ] thds: 4 tps: 118.69 qps: 2378.68 (r/w/o: 1665.31/129.08/584.28) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 97.94 qps: 1955.49 (r/w/o: 1368.22/114.73/472.53) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 102.79 qps: 2055.78 (r/w/o: 1438.64/121.59/495.55) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 30128
write: 2399
other: 10505
total: 43032
transactions: 2148 (107.02 per sec.)
queries: 43032 (2144.06 per sec.)
ignored errors: 4 (0.20 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0665s
total number of events: 2148
Latency (ms):
min: 7.18
avg: 37.29
max: 232.38
95th percentile: 74.46
sum: 80104.43
Threads fairness:
events (avg/stddev): 537.0000/158.50
execution time (avg/stddev): 20.0261/0.02
遇到的问题：
1.导入数据的时候GTID问题，先不开启GTID功能，数据导入同步之后，再开启GTID功能
2.slave的IO线程一直起不来
原因是：slave上的GTID编号比master上的还大（意味着slave上的数据比master还新），导致IO线程启动失败
解决方法：在所有的slave上ing出master信息和slave信息
reset master
reset slave
3.在做测试的时候，端口号搞混了，我需要写但是指定端口是指定成读端口，导致写数据到从服务器上，因为有2个从服务器，会轮询调度到不同的机器，会出现找不到数据库。并且需要使用写的账号连接，不然会没有权限去写入数据
3928
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
