226_mysql_复制技术_主备切换_故障转移

主备实例切换

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

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" " echo "$curl_date: Usage: $0 " >> $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;

MySQL 云容灾

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。