主备实例切换

        根据业务变更或运维需要，将主的写访问请求转移到其它数据库实例上,（升级场景&主机故障等）同时分为在线切换,离线切换

1 在线切换 (删除账号&修改连接数)

• 删除账号, 执行切换前删除账号,使业务无法连接主库写入数据
• 修改连接数, 切换前修改连接数为1，kill掉应用连接保证无法写入新的数据

2 基于删除账号的切换 主从+keepalived

        2.1 脚本& keepalived 相关配置

       监控脚本sh

# 脚本 cat /usr/local/keepalived/chkmysql.sh

#! /bin/bash
log_file=/usr/local/keepalived/chkmysql.log
process_tag=mysqld_safe
cur_date=`date '+%x %x'`
port=3306

if [ "$port" == "" ]
then 
	echo "Usage: $0" <gateway_tab> <port>"
	echo "$curl_date: Usage: $0 <gateway_dir> <port>" >> $log_file
	exit 1
fi 

listen_port = `netstat -plnt | grep ":$port"`
if ["$listen_port" ==""]
then
	echo "$cur_date: port $port hasn’t been listened." >> $log_file
	exit 1;
	
else
	echo "$cur_date: port $port is ok." >> $log_file
	exit 0;

fi 

     keepalived配置

# 配置 cat /etc/keepalived/keepalived.conf

vrrp_scipt chk_mysql{
  interval 5
  script "/usr/local/keepalived/chkmysql.sh"
}

#主全局参数
 global_defs {
 notification_email {admin@163.com}      #设置报警收件人邮箱
 notification_email_from admin@hw.com    #设置发件人
 smtp_server 127.0.0.1                    #定义邮件服务器
 smtp_connect_timeout 30
 router_id alex_103                       #设置路由ID号（核心参数）
 }
 
 vrrp_instance VI_1 {
 state BACKUP                     # 主服务器为MASTER（备服务器需要修改为BACKUP）
 interface eth0                   #定义网络接口
 virtual_router_id 51             #主-备服务器VRID号必须一致
 priority 100                     #服务器优先级,优先级高优先获取VIP（实验需要修改）
 advert_int 1
 
 # 主备服务器密码
 authentication {
 auth_type pass                         # 防止攻击者 在旁边另外配置一台keeplive priority 255，进行攻击
 auth_pass 1111                         # 主备服务器密码必须一致
 }
 virtual_ipaddress {        			#谁是主服务器谁获得该VIP 
 192.168.10.10  dev eth0 label eth0:1   # 可以配置多个 
}

 track_script{
  chk_mysql 
  }
 }

2.2 查看主的VIP， 使用sysbench加压

ip addr   # 查看 vip 

sysbench #对VIP加压
sysbench --db-driver=mysql --time=99999 --thread=2 --report-interval=1 --mysql-host=vip --mysql-port=3306 --mysql-user=xxx --mysql-password=xxx 
--mysql-db=xxx --tables=2 --table-size=xxxx  oltp_read_write --db-ps-mode=disable run  

2.3 升级从库

  2.3.1 从库升级

stop slave io_thread;
show slave status \G;  等待SQL线程 追上IO 线程
	# master_log_file = relay_master_log_File 
	# read_master_log_pos = exec_master_log_pos
	

 2.3.2 升级数据库

总体步骤 INPLACE 升级过程原理
a. 安装新版本软件
b. 关闭原数据库（挂维护页）  #set global innodb_fast_shutdown=0 ; # 不留脏数据 前滚/回滚完成
   备份原数据库数据  #冷备 CP到其它地方备份
c. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables#跳过授权表 user表 ,--skip-networking #不允许远程登录) #升级系统表 权限表等
d. 升级 ： 只是升级系统表。升级时间和数据量无关的。(8.0 和 5.7 区别)
e. 正常重启数据库
f. 验证各项功能是否正常
g. 业务恢复

5.6.46 ----> 5.7.28 Inplace 升级演练
a. 安装 新版本软件
b. 停原库 & 冷备# 快速关库功能关闭(优先刷脏页)
mysql> set global innodb_fast_shutdown=0 ;
[root@db01 app]# /usr/local/mysql56/bin/mysqladmin -S/tmp/mysql3356.scok shutdown
[root@db01 app]# cp -r /data/3356/data/   /opt/3356.bak    #冷备

c. 使用高版本软件挂低版本数据启动 （改5.6配置文件）
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/data/mysql56/data
socket=/tmp/mysql3356.scok
port=3356
server_id=56

#启动
[root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3356/my.cnf --skip-grant-tables --skip-networking &

d. 升级 （升级到8.0可以省略）
[root@db01 data]# /usr/local/mysql57/bin/mysql_upgrade -S/tmp/mysql3356.scok --force    #升级后会有 mysql_upgrade_info
e. 重启数据库到正常状态
[root@db01 data]# mysqladmin -S /tmp/mysql3356.sock; shutdown  （5.7版本支持 内部 shutdown）
#备注：sql_mode (only_full_group_by ); GTID 支持；
f: 正常启动数据库
[root@db01 app]# /usr/local/mysql57/bin/mysqld_safe &
g # 连接查看
mysql> show variables like '%version%';
# 测试应用
1、各项功能验证
2、SQL_MODE: 日期、group by
临时：关闭相应 SQL_mode ;建议：让应用满足 SQL_mode

升级 5.7 ~ 8.0
mysql-shell工具，8.0以后，可以调用这个命令，升级之前的预检查。
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"
# 且不需要 mysql_upgrade 

1. 下载 8.0.20 版本的 mysql-shell，并安装 。
[root@db01 app]# yum install -y mysql-shell-8.0.18-1.el7.x86_64.rpm

2. 创建用户
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';
3. 预 检查
mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()" > /tmp/up.log


# 开始升级
a. 安装 8.0软件
b. 优雅需要升级的数据库 
[root@db01 app]# /data/app/mysql/bin/mysql -S/tmp/mysql56.scok
mysql> set global innodb_fast_shutdown=0 ;
mysql> shutdown;
c. 使用高版本软件挂低版本数据启动
[mysqld]
user=mysql
basedir=/data/app/mysql8
datadir=/data/mysql3357/data
socket=/tmp/mysql57.scok
port=3356
d.高版本软件挂低版本数据启动
[root@db01 data]# /data/app/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip——networking &
e. 正常启动数据库
[root@db01 data]# /data/app/mysql8/bin/mysqladmin -S/tmp/mysql56.sock shutdown
f: 正常启动数据库
[root@db01 app]# /data/app/mysql8/bin/mysqld_safe &

2.3.3账号删除的在线切换

主库
 # 1全局加读锁
 flush table with read lock; 
 # 2使用pt_show_grants工具获取用户账号信息  --ignore 排除一些管理系统依赖账户 
 pt-show-grants --ignore=" 'mysql.seesion'@'localhost', 'mysql.sys'@'localhost', 'root'@'localhost', 'repl'@'%', 'mysqlxsys'@'localhost', 'mysql.infoschema'@'localhost'" -uroot -ppassword --drop --flush > /data/user_info.sql
 
 # 3主库中删除相关账户
	# 先基于脚本过滤出 drop user 和 delete from
	cat /data/user_info.sql |grep -E 'DROP USER | DELETE FROM '  > /data/user_delete.sql 
    unlock tables; source /data/user_delete.sql ; flush table with read lock 
	scp /data/user_delete.sql  slave:/data/
 # 4 查询活跃 ID & kill
	select *  from information_schema.processlist where user not in ('mysql.session', 'mysql.sys', 'root','repl','mysqlxsys','mysql.infoschema','admin');
	kill 100 ; kill 200;
 
 
 从库 追平master 且 准备升主
  1 show slave status  #等待SQL线程追平IO线程
  2 配置多线程复制
	[mysqld]
	slave_parallel_workers = 16
	slave_parallel_type = logical_clock
	slave_preserve_commit_order = on
	log_timestamps = system 
	
  3 stop slave 
	reset slave all
	show slave status \G 
	
主库升级  流程同slave ，一旦关闭 VIP会漂移到 slave上

从库导入用户信息
	source /data/user_info_sql;
	set global read_only = 0;

主库变为从库 变更完成
	change master to master_host="slave_ip" , master_user="repl", master_password="pwd", master_auto_position =1;
	start slave 
	show slave status \G；

2.3.4 基于修改连接数在线切换

主库
   #检查相关用户账户 不允许有 SUPER权限账户 保证改最大连接数后, 连接不会被super账户抢占
    select *  from information_schema.processlist where super_priv='Y' and user not in ('mysql.session', 'mysql.sys', 'root','repl','mysqlxsys','mysql.infoschema','admin');
	revoke super on *.* from xxxx@"%";

	flush table with read lock;
	show variables like "max_connections"
	set global max_connections = 1;
	set global read_only =1;
	select *  from information_schema.processlist where user not in ('mysql.session', 'mysql.sys', 'root','repl','mysqlxsys','mysql.infoschema','admin');
	kill 100 ; kill 200;
		
从库
	show slave status \G;
	stop slave;
	reset slave all;
	set global read_only = 0 ;
	
重启keepalived #使VIP 飘到 从库
	service keepalived restart 
	ip addr

主机降备
	change master to master_host="master_ip" , master_user="repl", master_password="pwd", master_auto_position =1;
	set global max_connections= 3000;
	start slave 
	show slave status \G；

数据库故障转移

        主库宕机, 尽快将读写访问转移到正常状态的实例上

常见情况：

      主库宕机, 主机存活(进程在无法登陆/执行报错), 主库可能存在大事物, 网络不稳定, 磁盘被写满/网络IO达上线, 最大连接数到顶

背景： 主62-从63 + 只读64 + vip 100  + 半同步复制模式

配置文件中使用plugin_load加载半同步复制插件
[mysqld]
plugin_load="repl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#开关——主
rpl_semi_sync_master_enabled = 1

rpl_semi_sync_master_timeout=xxxx #半同步将为异步复制的超时时间 单位为毫秒

# master检测到变量 rpl_semi_sync_master_clients为0时,即 slave的个数, 立即降为异步复制 不会等超时时间
rpl_semi_sync_master_wait_no_slave = off 
rpl_semi_sync_master_wait_slave_count=1 # master等待多少个slave返回ACK 
rpl_semi_sync_master_wait_point=AFTER_SYNC   #master设置事务在提交时等待 ACK消息位置
rpl_semi_sync_slave_enabled = 1 #从库开关

查看状态

# master
mysql > SHOW STATUS LIKE "%semi%" ;
| Variable_name  | Value |
| Rpl_semi_sync_master_clients  | 1 | 
| Rpl_semi_sync_master_no_tx  | 0 |
| Rpl_semi_sync_master_status  | ON |
| Rpl_semi_sync_master_yes_tx  | 1 |

# slave 
mysql > SHOW STATUS LIKE "%semi%" ;
| Rpl_semi_sync_slave_status | ON |


Rpl_semi_sync_master_status 在半同步复制中主库的半同步复制是否开启的状态值，ON为开启
Rpl_semi_sync_slave_status 在半同步复制中从库的半同步复制是否开启的状态值，ON为开启
Rpl_semi_sync_master_clients 在半同步复制中连接到主库的客户端数量，即slave个数
Rpl_semi_sync_master_yes_tx 在半同步复制中主库收到从库ack回复提交的事务数，即在半同步复制的状态下提交的事务数

Rpl_semi_sync_master_no_tx  主库没有收到从库ack回复而超时提交切换成异步复制的事务,如果持续增长,可能网络波动
rpl_semi_sync_master_no_times :  主库半同步插件被关闭的次数，如果次数增加说明网络可能不稳定
rpl_semi_sync_master_tx_avg_wait_time: 半同步复制,主接收ACK的相应时间 毫秒

master1 
	ip addr #查看vip
	select * from performance_schema.global_status where variable_name in ('rpl_semi_sync_master_status','rpl_semi_sync_master_no_tx','rpl_semi_sync_master_no_times','rpl_semi_sync_master_tx_wait_time');
 #确保rpl_semi_sync_master_status 状态值为on
		
slave1&2
	  select * from performance_schema.global_status where variable_name in ('rpl_semi_sync_slave_status'); 
         #确保rpl_semi_sync_slave_status 状态值为on
	  show variables like "read_only"; # 备机必须只读

master1：
	kill -9 `pgrep mysqld`
	ip addr   # vip已经飘走

slave1 
	show  slave status \G    #file  mysql_bin.000010 , executed_gtid_set xxx:1 ~110
	# master_log_file = relay_master_log_file
	# read_master_log_pos = exec master_log_pos
		
	stop slave;
	reset slave all;
	show slave status \G; show master status \G
	set global read-only = 0;
		
slave2
	stop slave;
	reset slave all;
	change master to master_host="slave1_ip", master_port=3306, master_user='repl', master_password="xxx", master_auto_position=1;
	start slave;
	show slave status \G;

故障master1
	show master status \G   # file mysql_bin.000011, executed_gtid_set xxxx:1 ~ 111 
	# 比新主多个个事务/文件，需要闪回
	/root/flashback --binlogFileNames = 'mysql_bin.000010, mysql_bin.000011' --exclude-gtids='xxxx:1 ~ 110' --outBinlog File  NameBase='binlog_flashback'
		#binlog_flashback.flashback
		#binlog_flashback.000001
		
	# 使用mysqlbinlog 解析所有的 binlog_flashback.flashback*  从不带编号开始，带编号从小到大解析
	mysqlbinlog --skip-gtids --disable-log-bin binlog_flashback.flashback > a.sql
	mysqlbinlog --skip-gtids --disable-log-bin binlog_flashback.flashback.00001 -vv >> a.sql 
		
	mysql -uroot -pxxx < a.sql
	reset master ;
	show master status \G;
	set global gtid_purged = 'xxxx: 1~110'   #slave1升为新主的时间点内获取 GTID SET 
	Start slave;
	show slave status \G;