日志管理(工具)
1. 錯誤日志
1.1 作用
排錯.MySQL 啟動和工作過程狀態(tài),警告,錯誤.
1.2 配置
默認開啟.存儲位置, datadir/hostname.err
log_error=/data/mysql/data_3306/mysql.log
1.3 查看
[ERROR]
2. 二進制日志
2.1 作用
數(shù)據(jù)恢復,主從復制.記錄的是所有變化類的操作(DDL,DML,DCL).
2.2 配置
server_id=6
log_bin=/data/mysql/binlog_3306/mysql-bin
2.3 binlog 對于不同語句的記錄格式
(1)非事務型語句: DDL DCL
以statement語句模式記錄
(2)事務型語句(insert ,update ,delete )
begin; A ; commit;
只記錄提交的事務.
記錄格式,三種:
RBR : ROW模式, 記錄數(shù)據(jù)行的變化. 記錄夠準確.
SBR : statement模式 ,以語句方式記錄DML語句.會出現(xiàn)記錄偏差
MBR : Mixed 模式,混合模式.
mysql> select @@binlog_format;
(3) 內(nèi)容格式
event:
以event事件的方式記錄所有的變化.binlog的最小記錄單元為事件.
DDL DCL ,每條語句就是一個事件.
DML ====> begin; A; ...... commit;一個事務由多個事件構(gòu)成.
event的結(jié)構(gòu):
起始點:position
事件內(nèi)容.
結(jié)束點: end_position
上一個event的結(jié)束點就是下個event的起始點.是連續(xù)記錄,不能出現(xiàn)斷點.
2.3 查看二進制日志
簡單查看:
mysql> show binary logs;
mysql> flush logs;
mysql> show master status ;
事件查看:
show master status ;
show binlog events in 'mysql-bin.000004';
mysql -e "show binlog events in 'mysql-bin.000004'" |grep -C 5 "create"
內(nèi)容查看:
mysqlbinlog /data/mysql/binlog_3306/mysql-bin.000004 |grep -v "SET"
mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/binlog_3306/mysql-bin.000004 |grep -v "SET"
2.4 使用二進制日志進行數(shù)據(jù)恢復
2.4.1 步驟:
1. 截取日志
起點:
終點:
2. 恢復日志
source /tmp/bin.sql
2.4.2 模擬故障并binlog恢復
mysql> create database oldboy;
mysql> use oldboy
Database changed
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;
mysql> drop database oldboy;
截取日志
起點:
| mysql-bin.000004 | 980 | Query | 6 | 1080 | create database oldboy
終點:
| mysql-bin.000004 | 2083 | Query | 6 | 2181 | drop database oldboy
mysqlbinlog --start-position=980 --stop-position=2083 /data/mysql/binlog_3306/mysql-bin.000004>/tmp/bin.sql
恢復日志
set sql_log_bin=0;
source /tmp/bin.sql
set sql_log_bin=1;
2.4.3 日志滾動
flush logs;
重啟數(shù)據(jù)庫
select @@max_binlog_size;
2.4.4 binlog的刪除
(1) 自動刪除
select @@expire_logs_days;
根據(jù)全備周期,至少全備周期+1,企業(yè)要求至少2*全備周期+1
(2) 手工刪除
PURGE BINARY LOGS TO 'mysql-bin.000004';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
(3) 全部清空
reset master;
2.4.5 擴展-基于GTID的日志記錄
(1) GTID 介紹
5.6 版本的新特性,5.7進行了增強,建議開啟.
全局事務ID號.
特點: 對于每一個事務,都會有一個唯一性的編號.
GTID具備冪等性,執(zhí)行過的GTID的事務會自動跳過.
(2) 格式
server_uuid:TID
mysql> create database xtz;
Query OK, 1 row affected (0.01 sec)
mysql> show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 | 310 | | | 411148c1-26bf-11ea-a420-000c298780da:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
(3) 開啟GTID功能
gtid-mode=on
enforce-gtid-consistency=true
(4) 基于GTID的數(shù)據(jù)恢復
模擬數(shù)據(jù)和故障:
create database bgx;
use bgx
create table t1 (id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
drop database bgx;
恢復步驟:
1. 截取日志
起點:
| mysql-bin.000002 | 310 | Gtid | 6 | 375 | SET @@SESSION.GTID_NEXT= '411148c1-26bf-11ea-a420-000c298780da:2' |
| mysql-bin.000002 | 375 | Query | 6 | 466 | create database bgx
終點:
| mysql-bin.000002 | 1380 | Gtid | 6 | 1445 | SET @@SESSION.GTID_NEXT= '411148c1-26bf-11ea-a420-000c298780da:7' |
| mysql-bin.000002 | 1445 | Query | 6 | 1534 | drop database bgx
mysqlbinlog --skip-gtids --include-gtids='411148c1-26bf-11ea-a420-000c298780da:2-6' /data/mysql/binlog_3306/mysql-bin.000002 >/tmp/gtid.sql
2. 恢復日志
set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;
注意: 開啟GTID,可以有兩種方式截取日志.但是都要在截取是加入--skip-gtids.
面試可以用的故障案例一則: gtid模式下的binlog截取及恢復.
3. 慢日志
3.1 記錄MySQL中較慢語句的日志.排查數(shù)據(jù)庫語句問題的日志.
3.2 配置
mysql> show variables like '%slow%';
mysql> select @@long_query_time;
mysql> select @@log_queries_not_using_indexes;
slow_query_log=ON
slow_query_log_file=/data/mysql/data_3306/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1
3.3 模擬慢語句
mysql> select * from t100w limit 10;
mysql> select * from t100w order by k1 limit 10;
mysql> select * from t100w order by k1 limit 10;
mysql> select * from t100w order by k2 limit 10;
mysql> select * from t100w order by k1,k2 limit 10;
mysql> select * from t100w order by k1,k2,dt limit 10;
mysql> select * from t100w order by k1,k2 desc limit 10;
mysql> select k1 ,count(k2) from t100w group by k1 order by count(k2) desc limit 10;
mysql> select k1 ,count(k2) from t100w group by k1 order by count(k2) desc limit 10;
mysql> select k1 ,dt ,count(k2) from t100w group by k1,dt order by count(k2) desc limit 10;
mysql> select k1 ,dt ,count(k2) from t100w group by k1,dt order by count(k2) desc limit 10;
select a.k1 ,b.k2 ,b.dt from (select k1,k2 from t100w) a join (select k2,dt from t100w) b on a.k2=b.k2 order by a.k1 desc limit 10;
select a.k1 ,b.k2 ,b.dt
from (select k1,k2 from t100w where id<10) a
join (select k2,dt from t100w where id<10) b
on a.k2=b.k2
order by a.k1 desc
limit 10;
3.4 分析慢日志
mysqldumpslow -s c -t 3 db01-slow.log
--select--> 看執(zhí)行計劃 ---> 索引 ----> 語句
數(shù)據(jù)庫慢:
1. 應急性的 ------> show full processlist;
2. 間歇性 ------> slow_log
擴展:
1. pt-query-digest 分析慢日志. 集成Anemometer進行圖形化展示.
2. ELFK ---> slowlog
=============================
備份和恢復
1. 運維人員在備份恢復方面的職責
1.1 設計備份策略
(1) 備份內(nèi)容: 數(shù)據(jù),二進制日志
(2) 數(shù)據(jù)容量: 大小
(3) 備份周期:
1. 每天全備
2. 周期全備+增量備份
3. 周期全備+日志備份
(4) 備份時間: 晚上備份
(5) 備份目標位置
1.2 日常備份檢查
日志 , 備份的內(nèi)容,備份大小
1.3 定期恢復演練
我們建議,每半年做一次.
1.4 故障時的恢復
快速準確恢復數(shù)據(jù).
1.5 平臺數(shù)據(jù)遷移
同構(gòu)平臺
異構(gòu)平臺
2. 備份工具介紹
2.1 介紹
mysqldump (MDP)
Percona Xtrabackup(xbk,pbk,pxb)
2.2 區(qū)別
MDP : 邏輯備份,SQL文件,文本形式,可讀性高,便于處理,壓縮比高,備份相對較慢 ,比較適合于100G以內(nèi)的備份 .
100G 30-40 Min
xbk : 物理備份,數(shù)據(jù)文件,可讀性較低,壓縮比低,備份相對較快,比較適合于100G以內(nèi)的備份 .
100G 10min
600G 50min
100 PB ---拆分數(shù)據(jù)庫---> MDP ---壓縮->
3. mysqldump
3.1 備份邏輯
將磁盤數(shù)據(jù),加載到臨時表,轉(zhuǎn)換為SQL(建庫,建表,插入語句)
3.2 核心參數(shù)
(1) 連接參數(shù)
本地: -u -p -S
遠程: -u -p -H -P
(2) 備份參數(shù)
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# chown -R mysql.mysql /data
-- 基礎參數(shù)
-- 1. 全備參數(shù)
[root@db01 ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql
-- 2. 單庫或多庫備份
[root@db01 ~]# mysqldump -uroot -p123 -B bgx oldboy test world mysql > /data/backup/db.sql
-- 3. 單表或多表備份
[root@db01 ~]# mysqldump -uroot -p123 world city country >/data/backup/tab.sql
-- 4. 只導出建表語句
[root@db01 ~]# mysqldump -uroot -p123 world city country --no-data >/data/backup/tab1.sql
-- 高級參數(shù)
-- 5. --master-data=2 ******
(1) 自動記錄備份時的binlog filename 及pos
(2) 自動加鎖和解鎖
(3) 加了--single-transaction , 會有不一樣的效果( global read lock is only taken a short time )
mysqldump -uroot -p123 -A --master-data=2 >/data/backup/full.sql
備份的結(jié)果:
30 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1162;
-- 6. --single-transaction ******
開啟獨立事務,備份InnoDB表的一致性快照.實現(xiàn)InnoDB表的熱備功能.
mysqldump -uroot -p123 -A --master-data=2 --single-transaction >/data/backup/full.sql
補充: ******
對于非InnoDB表(FTWRL,Flush tables with read lock),對于mysql庫下的系統(tǒng)表備份的時候,會加global read lock
-- 7. 特殊的數(shù)據(jù)庫對象
-R 存儲過程和函數(shù)
--triggers 觸發(fā)器
-E 事件
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E >/data/backup/full.sql
-- 8. --max_allowed_packet=128M
1153 - Got a packet bigger than 'max_allowed_packet' bytes
出現(xiàn)場景: 在數(shù)據(jù)庫有超大比表時,需要加此的參數(shù).
-- 9. 終極備份語句
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_`date +%F`.sql.gz
3.3 模擬故障和恢復(mysqldump 全備+ binlog)
(1) 模擬原始數(shù)據(jù)
create database mdp charset utf8mb4;
use mdp
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
insert into t1 values(1);
commit;
insert into t2 values(1);
commit;
insert into t3 values(1);
commit;
(2) 模擬全備
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_`date +%F`.sql.gz
(3) 模擬新的數(shù)據(jù)
use mdp
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;
(4) 搞破壞
drop database mdp;
(5) 恢復思路
-- 1. 掛維護頁
-- 2. 找測試庫
-- 3. 準備備份
full
截取binlog部分
-- 4. 恢復全備+binlog到測試庫 , 業(yè)務測試.
-- 5. 導出故障庫數(shù)據(jù),恢復到生產(chǎn) .
-- 6. 撤維護頁.
恢復過程:
1. 準備備份
vim /data/backup/full_2019-12-26.sql
第30行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
2. 截取二進制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
起點 : 2570
mysql> show binlog events in 'mysql-bin.000003';
| mysql-bin.000003 | 3388 | Query | 6 | 3477 | drop database mdp
終點 : 3388
mysqlbinlog --skip-gtids --start-position=2570 --stop-position=3388 /data/mysql/binlog_3306/mysql-bin.000003 >/data/backup/bin.sql
3. 恢復備份
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;
4. 檢查數(shù)據(jù)
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;
5. 擴展: 從全備中提取 單庫 單表的數(shù)據(jù)進行恢復
1椿疗、獲得表結(jié)構(gòu)
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql
2、獲得INSERT INTO 語句歹河，用于數(shù)據(jù)的恢復
# grep -i 'INSERT INTO `city`' full.sqll >data.sql &
3.獲取單庫的備份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql
