mysql 日誌方面與備份 恢復

2022-09-23 07:56:59 字數 2923 閱讀 1316

查詢二進位制日誌位置

show varsables like "%log_bin%"

重新整理重新生成一個binary_log

flush logs;

檢視已有的二進位制日誌檔案

show binary logs;

可以檢視目前正在使用的哪個二進位制日誌

show master status;

檢視二進位制日誌模式

select @@binlog_format

mixed 混合模式 mbr

row 行模式 rbr 5.7版預設推薦用

statment 原語句模式 sbr 5.6版預設

輸出事務資訊

show binlog events in 'mysql-bin.000009';

..可以檢視事務的開始at位置號,結束at位置號

檢視二進位制日誌內容

mysqlbinlog mysql-bin.000009;

mysqlbinlog --base64-output=decode-rows -vvv mysql-binlog.000009;

找到建表起始點和刪除之前為結束點的at號進行恢復(-d 庫名)

mysqlbinlog -d 庫名 --skip-gtids --start-position=219 --stop-position=335 mysql-binlog.000009 >/tmp/xx.sql

..起始點at219,結束點at335

恢復前臨時關閉當前會話二進位制日誌

set sql_log_bin=0;

source /tmp/xx.sql;

set sql_log_bin=1;

組成server_uuid:tid

儲存在./data/auto.cnf

gtid冪等性

如果拿有gtid的日誌去恢復時,檢查當前系統中是否有相同的gtid號,如果有相同的就是自動跳過

會影響到binlog恢復和主從複製

gtid開啟和配置

gtid-mode=on

enforce-gtid-consistency=true

gtid恢復

mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql

7-12表示從7到12的gtid子號

--skip-gtids 表示匯出時忽略原有的gtid資訊,恢復時生產最新的gtid資訊,不忽略就會跳過匯入,最終失敗。

set sql_log_bin=0

source /tmp/bin.sql

set sql_log_bin=1

mysqlbinlog 其他引數--exclude-gtids排除(用於排除刪除操作例如drop table),多段日誌編號用逗號分開

開關:slow_query_log=1

檔案位置及名字

slow_query_log_file=/data/mysql/slow.log

long_query_time=0.1

沒走索引的語句也記錄:

log_queries_not_using_indexes

慢日誌分析

mysqldumpslow -s c -t 10 /data/mysql/slow.log

過濾出 按照語句被執行次數排序,top10

備份恢復思路

先找個臨時庫,採用之前時間點的全備份恢復

然後使用binlog 恢復 全備份點之後新增的資料

注意難點:全備份中--master-data可以看到備份截止at點,或者是gtid點(show binlog event in 'mysql-bin.00000*')

最後用測試庫頂替,或者是匯出需要的部分表,再匯入到生產

mysqldump命令備份

mysqldump -uroot -p123 -a -r --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%f).sql.gz

mysqldump -uroot -p123 -a -r --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%f-%t).sql.gz

-r儲存過程 || --triggers 觸發器

--master-data=2

*(1)記錄備份時刻的binlog資訊

*(2)自動鎖表,不加--single-transaction,就是溫備份

*加了--single-transaction ,對與innodb表不鎖表,一致性快照備份,(快照備份)

擴充套件引數

--set-gtid-purged=auto/on 儲存gtid資訊

--set-gtid-purged=off 裡面不儲存gtid,單機資料庫使用沒有問題,但是主從環境下必須開啟on

--max_allowed_packet=128m 傳輸資料包較大時使用

1、獲得表結構

*sed -e '/./' -e 'x;/create tablezabbix/!d;q' full.sql>createtable.sql

2、獲得insert into 語句,用於資料的恢復

*grep -i 'insert intozabbix' full.sql>data.sql &

3、獲取單庫的備份

*sed -n '/^-- current database:world/,/^-- current database: `/p' all.sql>world.sql