226_mysql_復制技術_主備切換_故障轉移

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小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。