生產環境故障處理演練-mysql資料庫主從恢復

Linux运维技术栈發表於2024-08-17
MySql資料庫

常用命令：

1、檢視myqsl當前所有使用者：SELECT user, host FROM mysql.user;
2、檢視錶 mom_tms.t_tms_db_car_base_info 的建立語句：show create table mom_tms.t_tms_db_car_base_info;
3、從庫執行檢視是否都是隻讀狀態：select @@read_only;
4、查詢 MySQL 中 performance_schema 表中 replication_applier_status_by_worker 的所有行資料，以監控和審查複製工作執行緒的狀態：SELECT * FROM performance_schema.replication_applier_status_by_worker\G;
5、停止從伺服器：STOP SLAVE;
6、設定下一個 GTID：SET gtid_next = '主庫GTID號';
7、開始一個新的事務，但不執行任何操作，然後立即提交：BEGIN;  和  COMMIT;
8、將 GTID 設定為自動模式：SET gtid_next = 'AUTOMATIC';
9、啟動從伺服器（啟動複製程序的命令）：START SLAVE;
10、檢視超級使用者是否是隻讀（1：只讀; 0:關閉只讀）：select @@super_read_only;
11、檢視普通使用者是否是隻讀（1：只讀; 0:關閉只讀）：select @@read_only;
12、關閉只讀：set global read_only=0;set global super_read_only=0; 
13、開啟只讀：set global read_only=1;set global super_read_only=1; 
14、mysql檢視所有使用者的命令：SELECT user, host FROM mysql.user;
15、MySQL建立一個新的使用者：create user '使用者名稱'@localhost IDENTIFIED WITH 'mysql_native_password' BY '密碼';
16、給指定的使用者授予對所有資料庫（*.*）的所有許可權：grant all on *.* to '使用者名稱'@localhost;
17、顯示從伺服器的複製狀態資訊：show slave status \G;
①Slave_IO_State：顯示從伺服器 I/O 執行緒的當前狀態。如果複製正常執行，通常應該看到 "Waiting for master to send event"。
②Master_Host 和 Master_Port：主伺服器的地址和埠。
③Connect_Retry：從伺服器嘗試重新連線到主伺服器的時間間隔。
④Master_Log_File 和 Read_Master_Log_Pos：從伺服器當前讀取的二進位制日誌檔名和位置。
⑤Relay_Log_File 和 Relay_Log_Pos：中繼日誌檔案和位置，表明從伺服器已經將主伺服器的事件讀取到哪裡。
⑥Slave_IO_Running 和 Slave_SQL_Running：這兩個欄位顯示 I/O 執行緒和 SQL 執行緒是否正在執行，正常情況下應該都是 "Yes"。
⑦Seconds_Behind_Master：表示從伺服器在複製上落後主伺服器的秒數。如果為 0，則表示從伺服器與主伺服器同步。
⑧Last_Error：最後發生的錯誤資訊。
⑨Last_SQL_Error：最後發生的 SQL 錯誤的詳細資訊。
18、顯示主伺服器（Master）的二進位制日誌（Binary Log）的狀態資訊：show master status\G
①File: 當前主伺服器正在寫入的二進位制日誌檔案的名稱。
②Position: 在當前二進位制日誌檔案中的寫入位置。這個位置表示下一個事件將被寫入的位置。
③Binlog_Do_DB: 一個可選引數，列出了需要複製的資料庫名稱。如果為空，則表示複製所有資料庫。
19、列出 MySQL 伺服器上所有資料庫的 SQL 命令：show databases;
④Binlog_Ignore_DB: 一個可選引數，列出了不需要複製的資料庫名稱。
⑤Executed_Gtid_Set: 列出了所有已執行的全域性事務識別符號（Global Transaction Identifiers，GTIDs），如果啟用了 GTID 模式。
⑥Auto_Position: 如果啟用了 GTID 模式，這個值表示從伺服器是否使用 GTID 來自動確定複製的位置。
20、配置從伺服器以連線到主伺服器並開始複製資料的命令：change master to master_host='主庫ip',master_port=3306,master_user='repl',master_password='Repl@123',master_auto_position=1;

第一步：排查主從不同步原因
1.檢視從庫
show slave status \G;
涉及公司資料庫引數資訊，圖片我就不展示了，直接說結論：看引數，Slave_SQL_Running為No；Seconds_Behind_Master為NULL，而不是0和具體延遲數值，說明主從同步斷開了。

2.試圖搶救下
從庫上連續執行 常用命令 裡5-9的命令

3.檢視從庫
show slave status\G;
過程：Slave_SQL_Running:為Yes了，過了幾分鐘後：Slave_SQL_Running為NO；Seconds_Behind_Master為NULL（說明有問題）

4.從庫執行sql
SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

最終結論：根據上述引數資訊，和開發核對，確定了是某開發操作不當，用超級使用者往從庫裡匯入了資料，所以導致的主從同步斷開。

第二步：主庫全量備份
1、主庫先登入MySQL執行如下，建立一個備份使用者
create user '使用者名稱'@localhost IDENTIFIED WITH 'mysql_native_password' BY '密碼';
2、賦權
grant all on *.* to 'bakuser'@localhost;
3、主庫先安裝xtrabackup
yum localinstall -y percona-xtrabackup-80-8.0.14-1.el8.x86_64.rpm
4、備份指令碼

#!/bin/bash
ulimit -n 102400
######################################################## 
streammod=xb
keep_days=$1
hostip=當前主庫ip
bakdir="/db/backup/mysql"
user=bakuser
password=39xUJQObLvM3KQux!
sock=當前主庫mysql.sock路徑
backupDir="${bakdir}"
start_time=`date +%y%m%d%H%M%S`
log=/log/${start_time}.log
backup_file=/files/${start_time}.xbstream
## parameters end

#如果沒有填keep_days,那就預設設定3天
if [ "${keep_days}" == '' ]; then
  keep_days=3
fi

#備份
function backup() {
case ${streammod} in
###xb
"xb")
  backup_file=${bakdir}/${backup_file}
  xtrabackup --socket=${sock} --user=${user} --password="${password}" --kill-long-queries-timeout=20 --kill-long-query-type=all  --compress --compress-threads=8 --stream=xbstream --backup --target-dir=${bakdir} 1>${backup_file} 2>${bakdir}/${log}
  end_time=`date +%y%m%d%H%M%S`
  echo ${end_time}
  return $?
;;
esac
}

function check_result() {
    end_time=$1
    last_row=`tail -n 1 ${bakdir}/${log}`
    # 1表示備份成功，其餘數表示可能備份異常
    backup_file_size=`du -sh ${bakdir}/${backup_file} | awk -F' ' '{print $1}'`
    is_success=`tail -n 1 ${bakdir}/${log} | grep 'completed OK' | wc -l`
    if [ ${is_success} -eq 1 ];then
        backup_result='success'
        delete_backup
    else
        backup_result='fail'
    fi
    mysql_bacup_info="{\"ip\":\"${hostip}\", \"port\":\"3306\", \"backup_tool\":\"${streammod}\", \"start_time\":\"${start_time}\", \"end_time\":\"${end_time}\", \"backup_file_size\":\"${backup_file_size}\", \"backup_file\":\"${backup_file}\", \"backup_dir\":\"${backupDir}\", \"backup_result\":\"${backup_result}\", \"log_last_row\":\"${last_row}\"}"  
    echo $mysql_bacup_info >> $bakdir/log/backup_info.log
}

function delete_backup() {
        find  ${bakdir}/files/  -name '*.xbstream' -mtime +${keep_days} -exec rm {} \;  > /dev/null 2>&1  
        find  ${bakdir}  -name '*.gz' -mtime +${keep_days} -exec rm {} \;  > /dev/null 2>&1  
        find  ${bakdir}/log/  -name '*.log' -mtime +${keep_days} -exec rm {} \;  > /dev/null 2>&1
}

if [ -d ${backupDir} ]; then
        end_time=$(backup)
        wait
        check_result ${end_time}
        if [ $? -gt 80 ]; then
            delete_backup
        fi
else
        mkdir -p ${backupDir}
        chmod 750 ${backupDir}
        end_time=$(backup)
        wait
        check_result ${end_time}
        if [ $? -gt 80 ]; then
            delete_backup
        fi
fi

7、賦權
chmod +x /db/backup/mysql/mysqlbackup.sh
8、執行全備指令碼
sh /db/backup/mysql/mysqlbackup.sh
檢視指令碼生成的日誌顯示備份成功，此時/db/backup/mysql/files路徑下為生成123456.xbstream檔案傳到從庫（檔案具體名用12345代稱）

第三步：恢復主從
1、在兩個從庫的123456.xbstream備份檔案的當前目錄執行如下命令

systemctl stop mysqld
rm -rf /db/mysql/3306/data/*
rm -rf /db/mysql/3306/log/{binlog,relaylog,slowlog,errorlog}
xbstream -x < 240813171655.xbstream  -C /db/mysql/3306/data
xtrabackup --decompress --parallel=4 --target-dir=/db/mysql/3306/data
xtrabackup --prepare --parallel=4 --target-dir=/db/mysql/3306/data
cd /db/mysql/3306/data
mkdir -p /db/mysql/3306/log/{binlog,relaylog,slowlog,errorlog}
chown -R mysql:mysql /db/mysql/3306/
systemctl start mysqld
start slave; 
change master to master_host='主庫ip',master_port=3306,master_user='repl',master_password='從連線主的密碼',master_auto_position=1;
start slave;
登入從庫，執行show slave status\G看看結果

此時可以檢視到Slave_SQL_Running引數為yes；Seconds_Behind_Master的引數在下降，到0就說明主從恢復成功

2、開啟兩個從庫所有使用者的只讀
set global read_only=1;set global super_read_only=1;
這一手是為了防止後續同事的誤操作，至於為啥從庫不能寫資料，因為 一主兩從mysql架構 又叫 一寫兩讀

相關文章