（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 statusG

（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

 注意，权限要是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';

#【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 statusG

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 statusG

【5.3】切换步骤（把 192.168.1.202 做新主库）

#【5.3.1】确保所有的relay log 全部读取完毕
stop slave io_thread;
show slave statusG
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 statusG
show processlist;
select count(1) from test.test4;

#【5.3.6】在新主库中开启事件
set global event_scheduler=1;
#如果要永久开，最好还是加入到 my.cnf 中去。

#【5.3.7】通知业务，调整数据库IP