(5.13)mysql高可用系列——1主3从复制(SSL)
目录:
【0】需求
目前使用Mysql数据库,100GB+数据量,需要实现1主3从环境。
需要实现SSL安全复制,同时需要测试异常宕机切换演练
【1】实验环境
数据库架构:主从复制,基于主库搭建3个从库,采用Xtrabackup + GTID + 无损同步复制
【2】配置主从
【2.0】配置文件 my.cnf(/etc/my.cnf)
【2.1】主库创建复制用户并授权
【2.2】主库准备测试数据
【3】搭建主从
【3.1】查看备份截止的GTID
【3.2】主从配置
【3.3】核验
【4】配置 SSL 安全协议
#【4.1】在主服务器上面创建 ssl/rsa 文件
#【4.2】在主服务器上修改my.cnf,并重启mysql
#【4.3】在主服务器上创建一个 SSL 认证的复制用户
#【4.4】在从服务上开启SSL功能
#把主服务生成的证书传给从服务器(在主服务器上执行)
#【4.5】修改从服务的配置文件
#【4.6】测试 SSL 连通性
#【4.7】在从库配置基于 SSL 的主从
#【4.8】测试 SSL 复制及数据验证
【5】故障切换
情况思路:
当主库(201)宕机了,暂时不能修复。
先找一台从库(比如202)来当主库,所有从库重新执行新主库(202)。
如果原主库(201)修好了,那么把它当做一个新从库使用,执行新主库(202)。
【5.1】模拟主库宕机
【5.2】检查从库状态
【5.3】切换步骤(把 192.168.1.202 做新主库)
#【5.3.1】确保所有的relay log 全部读取完毕
#【5.3.2】对新主库 192.168.1.202 进行配置
#【5.3.3】在新主库(202)上查看是否有复制用户,没有则新建
#【5.3.4】配置另外2台从库(203~204)到新主库(202)
#在203,204机器上操作
#【5.3.5】校验
#【5.3.6】在新主库中开启事件
#【5.3.7】通知业务,调整数据库IP
正文:
【0】需求
目前使用Mysql数据库,100GB+数据量,需要实现1主3从环境。
需要实现SSL安全复制,同时需要测试异常宕机切换演练
【1】实验环境
操作系统:CentOS 7.5
数据库版本:MySQL 5.7.24
数据库架构:主从复制,基于主库搭建3个从库,采用Xtrabackup + GTID + 无损同步复制
主库IP:192.168.1.201 port:3306
主库IP:192.168.1.202 port:3306
主库IP:192.168.1.203 port:3306
主库IP:192.168.1.204 port:3306
大致步骤思路:
(1)配置好主从
(2)配置好SSL
(3)故障切换演练
【2】配置主从
【2.0】配置文件 my.cnf(/etc/my.cnf)
#replication_new log_bin=/mysql/log/3306/mysql-bin #开启binlog log_bin_index=/mysql/log/3306/mysql-bin.index binlog_format=row binlog_rows_query_log_events=on max_binlog_size=2048 bind-address=0.0.0.0 server_id=2013306 #务必记得修改 expire_logs_days=7 #超过7天的binlog清理 innodb_support_xa=1 binlog_cache_size=1M log_bin_trust_function_creators=1 #同步存储过程、函数、触发器 innodb_flush_log_at_trx_commit=1 sync_binlog=1 transaction-isolation=read-committed
#slave parameter 如果是从库,务必放开 #relay_log=/mysql/log/3306/relaylog/mysql-relay.log #read_only=1 #slave-parallel-type=LOGICAL_CLOCK #slave-parallel-workers=4 #master_info_repository=table #master_info 会记录到 mysql.slave_master_info #relay_log_info_repository=table #relay_log 会记录到,mysql.slave_relay_log_info #relay_log_recovery=1 #slave_skip_errors=ddl_exist_errors #slave_preserve_commit_order=1
#5.7的增强半同步 #如果是5.7,参数前面加上loose_,如下列,如果是5.6 则直接使用 rpl_semi_sync_master_enabled=1 之类的就好了。 #我这里是5.7就直接做增强半同步了(loseless Semisynchronous )
plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
loose_rpl_semi_sync_master_enabled=1 #MySQL开启主的半同步复制(rpl_semi_sync_master_enabled)
loose_rpl_semi_sync_slave_enabled=1 #MySQL5.6开启从的半同步复制
loose_rpl_semi_sync_master_timeout=5000 #超时5秒,切回异步
rpl_semi_sync_master_wait_for_slave_count=1 #至少收到1个slave发会的ack
rpl_semi_sync_master_wait_point=AFTER_SYNC #MySQL 5.7的方法,AFTER_SYNC(default value,增强半同步) & AFTER_COMMIT(传统半同步)
#GTID mode gtid_mode=on enforce_gtid_consistency=1
log-slave-updates=1
【2.1】主库创建复制用户并授权
create user 'rpl'@'192.168.1.%' identified by '123456'; grant replication slave on *.* to 'rpl'@'192.168.1.%'; flush privileges; select user,host from mysql.user;
【2.2】主库准备测试数据
or 下载与使用测试库:https://www.cnblogs.com/gered/p/10360034.html
or 下面使用测试数据:(这里我们用这个吧)
注意事件参数,set global event_scheduler=1; 要永久生效请配置到 My.cnf中去
-- 【2.2.1】构造test库和test库下的test1,test2,test3表。test4表用于模拟业务一直在运行 create database test; use test; create table test1(id int); insert into test1 values(1); create table test2(id int); insert into test2 values(2); create table test3(id int); insert into test3 values(3); commit; create table test4(id int); insert into test4 values(4); commit; -- 【2.2.2】构造存储过程sp_test4来循环插入test4表,模拟业务运行 use test; drop procedure if exists sp_test4; delimiter $$ create procedure sp_test4() begin declare n int; set n=11; while(n<=20) do insert into test.test4 values(n); commit; set n=n+1; end while; end $$ delimiter ; -- 【2.2.3】构造事件,来调度sp_test4过程 use test; set global event_scheduler=1; delimiter $$ create event if not exists event_test4 on schedule every 5 second on completion preserve enable do begin call sp_test4(); end $$ delimiter ; -- 为了防止测试数据量累计导致卡顿,我这里5小时做一次truncate delimiter $$ create event if not exists event_truncate_test4 on schedule every 5 hour on completion preserve enable do begin truncate table test.test4; end $$ delimiter ;
下载与使用xtrabackup:
(4.15)mysql备份还原——物理备份之XtraBackup的下载与安装
(4.16)mysql备份还原——物理备份之XtraBackup实践
在主服务器上:
# 主服务器备份 innobackupex --defaults-file=/etc/my.cnf -uroot -p123456 --no-timestamp /mysql/backup/full.bak
#主服务器的备份 传输到从机器
scp -r /mysql/backup/full.bak root@192.168.1.202:/mysql/backup/
scp -r /mysql/backup/full.bak root@192.168.1.203:/mysql/backup/
scp -r /mysql/backup/full.bak root@192.168.1.204:/mysql/backup/
#在从服务器还原
innobackupex --apply-log /mysql/backup/full.bak
service mysql stop
cd /mysql/data/3306
mkdir data
innobackupex --defaults-file=/etc/my.cnf --copy-back /mysql/backup/full.bak
chown -R mysql:mysql /mysql
chmod -R 755 /mysql
--因为默认从库的event选项是关闭的,所以从库不会执行,且加了read only。
【3】搭建主从
【3.1】查看备份截止的GTID
【3.2】主从配置
#在从服务器上执行
stop slave; reset slave; reset master; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; set global gtid_purged='2c8b1813-e26f-11e9-adce-000c29658c19:1-417'; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
show master status;
change master to master_host='192.168.1.201', master_user='rpl', master_password='123456', master_port=3306, master_auto_position=1; start slave;
【3.3】核验
(1)show slave status\G
(2)show processlist;
(3)select count(1) from test.test4;
【4】配置 SSL 安全协议
#【4.1】在主服务器上面创建 ssl/rsa 文件
cd /mysql/data/3306/data
mysql_ssl_rsa_setup --datadir=/mysql/data/3306/data --user=mysql --uid=mysql
![](https://img2018.cnblogs.com/blog/1302413/201909/1302413-20190930095312983-1017968979.png)
注意,权限要是mysql的,不然可能用不了。
#【4.2】在主服务器上修改my.cnf,并重启mysql
[mysqld]
echo "ssl-ca=/mysql/data/3306/data/ca.pem">>/etc/my.cnf
echo "ssl-cert=/mysql/data/3306/data/server-cert.pem">>/etc/my.cnf
echo "ssl-key=/mysql/data/3306/data/server-key.pem">>/etc/my.cnf
#systemctl restart mysql
service mysql restart
#check log and status
tail -100f /mysql/log/3306/mysql.err
show variables like '%ssl%';
show variables like 'tls_version';
#【4.3】在主服务器上创建一个 SSL 认证的复制用户
create user 'rplssl'@'192.168.1.%' identified by '123456'; grant replication slave on *.* to 'rplssl'@'192.168.1.%'; flush privileges; select user,host from mysql.user;
#【4.4】在从服务上开启SSL功能
#把主服务生成的证书传给从服务器(在主服务器上执行)
cd /mysql/data/3306/data
scp ca.pem client-cert.pem client-key.pem root@192.168.1.202:/mysql/data/3306/data/
scp ca.pem client-cert.pem client-key.pem root@192.168.1.203:/mysql/data/3306/data/
scp ca.pem client-cert.pem client-key.pem root@192.168.1.204:/mysql/data/3306/data/
#在从服务器
chown mysql:mysql /mysql/data/3306/data/*
chmod 755 /mysql/data/3306/data/*
#【4.5】修改从服务的配置文件
echo "ssl-ca=/mysql/data/3306/data/ca.pem" >> /etc/my.cnf
echo "ssl-cert=/mysql/data/3306/data/server-cert.pem" >>/etc/my.cnf
echo "ssl-key=/mysql/data/3306/data/server-key.pem" >>/etc/my.cnf
#systemctl restart mysql
service mysql restart
#check log and status
tail -100f /mysql/log/3306/mysql.err
show variables like '%ssl%';
show variables like 'tls_version';
![](https://img2018.cnblogs.com/blog/1302413/201909/1302413-20190929151418700-1797850660.png)
#【4.6】测试 SSL 连通性
mysql -u'rplssl' -p123456 -h192.168.1.201 --ssl-ca=/mysql/data/3306/data/ca.pem --ssl-cert=/mysql/data/3306/data/client-cert.pem --ssl-key=/mysql/data/3306/data/client-key.pem
#【4.7】在从库配置基于 SSL 的主从
stop slave;
change master to
master_host='192.168.1.201',
master_port=3306,
master_user='rplssl',
master_password='123456',
master_auto_position=1,
master_ssl=1,
master_ssl_ca='/mysql/data/3306/data/ca.pem',
master_ssl_cert='/mysql/data/3306/data/client-cert.pem',
master_ssl_key='/mysql/data/3306/data/client-key.pem';
start slave;
show slave status\G
show processlist;
#【4.8】测试 SSL 复制及数据验证
#(1)主库上抓包,查看是否能加密传输:
tcpdump -i ens34 -nn -XX ip dst host 192.168.1.201 and tcp dst port 3306
#(2)测试数据
select count(1) from test.test4;
#(3)构造新数据测试
create databases test1;
create table test1(id int);
insert into test1.test1 values(1);
commit;
【5】故障切换
有2种情况
(1)有SSL
建议方案1:先按无SSL的切换,然后在等原主修复好,切换回原主
建议方案2:先按无SSL的切换,然后再按上面的重新配置操作 SSL。
(2)无SSL
生成中巨多。
情况思路:
当主库(201)宕机了,暂时不能修复。
先找一台从库(比如202)来当主库,所有从库重新执行新主库(202)。
如果原主库(201)修好了,那么把它当做一个新从库使用,执行新主库(202)。
#【5.1】模拟主库宕机 select count(1) from test.test4; shutdown -h now
#【5.2】检查从库状态
select count(1) from test.test4;
show processlist;
show slave status\G
【5.3】切换步骤(把 192.168.1.202 做新主库)
#【5.3.1】确保所有的relay log 全部读取完毕
stop slave io_thread;
show slave status\G
show processlist;
#对比
Master_log_file | Read_Master_Log_Pos
Relay_master_log_file | Exec_Master_Log_Pos
#核心还是查看Relay_master_log_file 和 Exec_Master_Log_Pos,哪个从库的值最大就是主库。
#【5.3.2】对新主库 192.168.1.202 进行配置
stop slave;
reset master;
reset slave all;
#参数文件 my.cnf
read-only=1
slave相关的等等....全部注释掉
#systemctl restart mysqld;
service mysql restart
#【5.3.3】在新主库(202)上查看是否有复制用户,没有则新建
select user,host from mysql.user;
-- create replication user: rpl
create user 'rpl'@'192.168.1.%' identified by '123456'; grant replication slave on *.* to 'rpl'@'192.168.1.%'; flush privileges; select user,host from mysql.user;
#【5.3.4】配置另外2台从库(203~204)到新主库(202)
#在203,204机器上操作
stop slave;
reset slave all;
reset master;
change master to
master_host='192.168.1.202',
master_port=3306,
master_user='rpl',
master_password='123456',
master_auto_position=1;
start slave;
#【5.3.5】校验
show slave status\G
show processlist;
select count(1) from test.test4;
#【5.3.6】在新主库中开启事件
set global event_scheduler=1;
#如果要永久开,最好还是加入到 my.cnf 中去。
#【5.3.7】通知业务,调整数据库IP