1. 上節(jié)遺留問題
RC模式: 可以屏蔽臟讀,但是會(huì)出現(xiàn)不可重復(fù)讀和幻讀
2.重點(diǎn)參數(shù) innodb_flush_log_at_trx_commit
作用： 控制redo buffer 刷寫磁盤的策略
0： redo buffer -----> 每秒 -----> os buffer -----> 每秒 -----> 磁盤
如果 出現(xiàn)宕機(jī)，有可能丟失一秒的事務(wù)。
如果你的業(yè)務(wù)，對(duì)數(shù)據(jù)丟失有一定的容忍度，可以使用（類似zabbix）
1： redo buffer -----> commit -----> os buffer -----> commit -----> 磁盤
#工作中重點(diǎn)使用
2： redo buffer -----> commit -----> os buffer -----> 每秒 -----> 磁盤
innodb_flush_method=O_DIRECT
作用:
控制 redo buffer 和 data buffer 刷磁盤時(shí)是否使用 os buffer 數(shù)據(jù)刷盤,不使用 os buffer;；日志刷盤,使用 os buffer
innodb_buffer_pool_size 50-80%
===================================
1、日志管理
1.1 排錯(cuò)
1.1.1 錯(cuò)誤日志
現(xiàn)位置：
默認(rèn)位置：
DATADIR/hostname.err
配置方式：
vim /etc/my.cnf
log_error=/data/mysql/data/mysql.log
使用方法：
查看 [ ERROR ] 上下文
1.2 數(shù)據(jù)恢復(fù)
binlog(二進(jìn)制日志)
1.2.1 作用：
數(shù)據(jù)恢復(fù)
主從復(fù)制
1.2.2 如何配置？
log_bin
1. 開關(guān)
2. 設(shè)定存放位置
server_id
1. 5.6 中不需要
2. 5.7 使用以上參數(shù)時(shí)必須加上server_id
注意：
生產(chǎn)要求?。?！
日志要和數(shù)據(jù)分開存放！
使用不同的物理磁盤進(jìn)行存放！
配置二進(jìn)制日志：
mkdir /data/binlog -p #創(chuàng)建存放目錄
chown -R mysql. /data #授權(quán)
vim /etc/my.cnf #編輯配置文件
log_bin=/data/mysql/data/mysql-bin
/etc/init.d/mysqld restart # 重啟
1.2.3 如何查看配置
mysql> show variables like '%log_bin%';
1.2.4 binlog 記錄了什么？
###1. 大面上說明：
記錄了數(shù)據(jù)庫中所有變更類的操作
DDL
DCL
DML
###2. 詳細(xì)的說明
（1）
對(duì)于DDL和DCL語句，記錄發(fā)生過的語句
（2）DML（IUD）
前提：已經(jīng)提交的事務(wù)IUD
關(guān)于記錄格式：
ROW ：RBR 行記錄模式 ，記錄的是行的變化
STATEMENT ：SBR 語句記錄模式，記錄操作語句本身
MIXED ：MBR 混合記錄模式
電話面試的題目：
delete from city where id>1000；
記錄方式：
RBR,逐行記錄日志,日志量很大,可讀性差。但是夠嚴(yán)謹(jǐn),不會(huì)出現(xiàn)記錄錯(cuò)誤
SBR,只記錄語句本身, 日志量很少,可讀性較強(qiáng)。對(duì)于函數(shù)類的操作,將來恢復(fù)時(shí)會(huì)有錯(cuò)誤
5.7 版本，默認(rèn)是RBR格式，也是企業(yè)建議的模式。
二進(jìn)制日志記錄格式查看
mysql> select @@binlog_format;
1.2.5 二進(jìn)制日志事件（event）
簡(jiǎn)介
二進(jìn)制日志的最小記錄單元
對(duì)于DDL, DCL,一個(gè)語句就是一個(gè)event
對(duì)于DML語句來講:只記錄已提交的事務(wù)。
例如以下列子,就被分為了4個(gè)event
position號(hào)碼（截取日志使用）
beqin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
### event的組成
三部分構(gòu)成:
(1)事件的開始標(biāo)識(shí)
(2)事件內(nèi)容
(3)事件的結(jié)束標(biāo)識(shí)
Position:
開始標(biāo)識(shí): at 194
結(jié)束標(biāo)識(shí): end_log_pos 254 1
194? 254?
某個(gè)事件在binlog中的相對(duì)位置號(hào)
位置號(hào)的作用是什么?
為了方便我們截取事件
1.2.6 二進(jìn)制日志的查看
查看正在使用的二進(jìn)制日志
mysql> show binary logs;
log_name：目前Mysql存在的二進(jìn)制日志名字
file_size：目前Mysql用到哪個(gè)polation號(hào)
mysql> show master status ;
1. 2. 7 查看二進(jìn)制文件內(nèi)容*****
（1）查看二進(jìn)制日志事件
確認(rèn)當(dāng)前在用的binlog
mysql> show master status ;
查看binlog事件
mysql> show binlog events in 'mysql-bin.000001';
注釋：每一行都是一個(gè)事件
每列說明：
Log_name ：日志名
Pos ：事件開始的position *****
Event_type ：事件類型
Server_id ：發(fā)生在那臺(tái)機(jī)器上的事件
End_log_pos ：事件結(jié)束的位置號(hào) *****
Info ：事件內(nèi)容 *****
（2）查看二進(jìn)制日志內(nèi)容
平看日志信息：
mysqlbinlog /data/binlog/mysql-bin.000001 |grep -v "SET"
詳細(xì)查看日志信息
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000001
1.2.8 基于二進(jìn)制日志數(shù)據(jù)恢復(fù)案例
如何按需截取日志
1. 基于position號(hào)的截取
--start-position=###
--stop-position=###
截取二進(jìn)制日志核心在于找起點(diǎn)和終點(diǎn)
截取日志
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql
恢復(fù)：
mysql> drop database oldboy1;
mysql> show databases;
mysql> set sql_log_bin=0;
mysql>source /tmp/bin.sql
2. 基于時(shí)間點(diǎn)的截?。ㄐ枨蟛淮?，了解即可）
--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56
《按需恢復(fù)詳解》
案例: 使用binlog日志進(jìn)行數(shù)據(jù)恢復(fù)
模擬:
#1.
create database binlog charset utf8mb4;
#2.
use binlog;
create table t1(id int);
#3.
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
#4.
drop database binlog;
恢復(fù)：
1.找到起點(diǎn)和終點(diǎn)
mysql> show master status;
2.查看事件
mysql> show binlog events in 'mysql-bin.000001';
3. 截取日志
mysqlbinlog --start-position=259 --stop-position=1377 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
4. 進(jìn)行恢復(fù)
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
5. 驗(yàn)證數(shù)據(jù)
mysql> show databases;
1.2.9 開啟GTID功能的二進(jìn)制日志管理
什么是GTID
全局事務(wù)編號(hào)
開啟GTID
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
/etc/init.d/mysqld restart
查看本機(jī)GTID信息
環(huán)境模擬：
mysql> create database gg;
mysql> show master status;
mysql> use gg;
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;
drop database gg;
基于GTID截取二進(jìn)制日志
mysqlbinlog --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:7-11' /data/binlog/mysql-bin.000004 >/tmp/gtid.sql
注意：
以上日志截取出來的日志不能直接恢復(fù)！
gtid的冪等性
正確的截取方式：
mysqlbinlog --skip-gtids --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:7-11' /data/binlog/mysql-bin.000004 >/tmp/gtid.sql
恢復(fù)
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
跳過某些gtid不截?。ú贿B續(xù)取號(hào)或排除）
mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' --exclude-gtids='ee956c61-9653-11e9-8518-000c29099eb6:2,ee956c61-9653-11e9-8518-000c29099eb6:4' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql
1.2.10 二進(jìn)制日志其他操作
（1）臨時(shí)關(guān)閉
set sql_log_bin=0;
說明：
臨時(shí)關(guān)閉二進(jìn)制日志記錄，退出mysql窗口可以恢復(fù)
做數(shù)據(jù)恢復(fù)之前，使用以上參數(shù)。
（2）自動(dòng)清理
參數(shù)：
mysql> select @@expire_logs_days;
設(shè)置依據(jù)？
至少是一個(gè)全備周期 +1，企業(yè)建議至少2個(gè)全被周期 +1
臨時(shí)設(shè)置：重啟失效
mysql> set global expire_logs_days=8';
永久設(shè)置：重啟生效
vim /etc/my.cnf
expire_logs_days=8
（3）手工清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';
注意:不要手工rm binlog文件
1. my.cnf binlog關(guān)閉掉,啟動(dòng)數(shù)據(jù)庫
2. 數(shù)據(jù)庫關(guān)閉,開啟binlog,啟動(dòng)數(shù)據(jù)庫
刪除所有binlog，并從000001開始重新記錄日志
注意，禁用：
刪除所有binlog，從000001開始刪除（危險(xiǎn)?。。。。。?mysql>reset master;
（4）日志滾動(dòng)
mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000003 | 1498 |
| mysql-bin.000004 | 1451 |
| mysql-bin.000005 | 194 |
+------------------+-----------+
mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
備份加一些參數(shù)，會(huì)觸發(fā)滾動(dòng)日志
1. 3 優(yōu)化相關(guān)日志— slowlog
1.3.1 作用
記錄慢SQL語句的日志，定位低效SQL語句的工具日志。
1.3.2 開啟慢日志，默認(rèn)沒有開啟
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> select @@slow_query_log_file;
+--------------------------------+
| @@slow_query_log_file |
+--------------------------------+
| /data/mysql/data/db01-slow.log |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
最終設(shè)置：
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
1.3.3 mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
備份恢復(fù)與遷移 ******
1、DBA、運(yùn)維在數(shù)據(jù)庫備份恢復(fù)方面的職責(zé)
1.1 設(shè)計(jì)備份策略
全備
增量
時(shí)間
自動(dòng)
1.2 日常備份檢查!
備份存在性—檢查備份路徑下是否有真實(shí)數(shù)據(jù)
定期檢查備份空間是否充足
1.3 定期恢復(fù)演練（測(cè)試庫）
一季度 或者 半年
1.4 故障恢復(fù)
通過現(xiàn)有備份,能夠?qū)?shù)據(jù)庫恢復(fù)到故障之前的時(shí)間點(diǎn).
1.5 遷移
- 停機(jī)時(shí)間
- 回退方案
2、備份類型
2.1 熱備
在數(shù)據(jù)庫正常業(yè)務(wù)時(shí)，備份數(shù)據(jù),并且能夠一致性恢復(fù)(只能是innodb)
對(duì)業(yè)務(wù)影響非常小
2.2 溫備
鎖表備份，只能查詢不能修改(myisam)
影響到寫入操作
2.3 冷備
關(guān)閉數(shù)據(jù)庫業(yè)務(wù),數(shù)據(jù)庫沒有任何變更的情況下,進(jìn)行備份數(shù)據(jù).業(yè)務(wù)停止
3、備份方式及工具介紹
3.1 邏輯備份工具
基于SQL語句進(jìn)行備份
mysqldump(MDP) *****
mysqlbinlog *****
3.2 物理備份工具
基于磁盤數(shù)據(jù)文件備份
xtrabackup (XBK) : percona第三方 *****
MysQL Enterprise Backup (MEB,企業(yè)版收費(fèi)的)
4、邏輯備份和物理備份的比較
4.1 mysqldump (MDP)
優(yōu)點(diǎn):
- 不需要下載安裝.
- 備份出來的是SQL,文本格式,可讀性高,便于備份處理
- 壓縮比較高,節(jié)省備份的磁盤空間
缺點(diǎn)
- 依賴于數(shù)據(jù)庫引擎,需要從磁盤把數(shù)據(jù)讀出
然后轉(zhuǎn)換成SQL進(jìn)行轉(zhuǎn)儲(chǔ),比較耗費(fèi)資源,數(shù)據(jù)量大的話效率較低
建議:
100G以內(nèi)的數(shù)據(jù)量級(jí),可以使用mysqldump
超過TB以上,我們也可能選擇的是mysqldump,配合分布式的系統(tǒng)
1EB =1024 PB =1000000 TE
4.2 xtrabackup (XBK)
優(yōu)點(diǎn):
- 類似于直接cp數(shù)據(jù)文件,不需要管邏輯結(jié)構(gòu),相對(duì)來說性能較高
缺點(diǎn):
- 可讀性差
- 壓縮比低,需要更多磁盤空間
建議:
100GKTB
5、備份策略
備份方式:
全備：全庫備份,備份所有數(shù)據(jù)
增量：備份變化的數(shù)據(jù)
邏輯備份=mysqldump+mysqlbinlog
物理備份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
備份周期:
根據(jù)數(shù)據(jù)量設(shè)計(jì)備份周期
比如：周日全備,周1 - 周6增量
其他：通過主從復(fù)制備份
6、邏輯備份工具-mysqldump
6. 1 客戶端通用命令，和鏈接有關(guān)
-u
-p
-S
-h
-P
本地備份連接方式：
mysqldump -uroot -pxxx -S /tmp/myql.sock
遠(yuǎn)程備份連接方式：
mysqldump -uroot -pxxx -h xxx -P xxx
6.2 基本備份參數(shù)
-A 全庫備份
例子：實(shí)現(xiàn)全庫備份
mkdir -p /data/backup
mysqldump -uroot -p123 -A -S /tmp/mysql.sock >/data/backup/full.sql
-B 備份單個(gè)庫或多個(gè)庫數(shù)據(jù)
例子：備份oldboy和world數(shù)據(jù)庫
mysqldump -uroot -p123 -B world oldboy -S /tmp/mysql.sock >/data/backup/db.sql
庫名 表名 ： 備份某個(gè)庫下的1張或多張表
例子：備份world數(shù)據(jù)庫下的city和country表
mysqldump -uroot -p123 world city country -S /tmp/mysql.sock >/data/backup/tab.sql
注意：
此種方法，只會(huì)備份建表 + 插入語句
所以恢復(fù)前需要把庫建好，而且要use到庫中。
6. 3 必加參數(shù) （1）
-R
在備份時(shí)，同時(shí)備份存儲(chǔ)過程和函數(shù)，如果沒有會(huì)自動(dòng)忽略
-E
在備份時(shí)，同時(shí)備份EVENT，如果沒有會(huì)自動(dòng)忽略
--triggers
在備份時(shí)，同時(shí)備份觸發(fā)器，如果沒有會(huì)自動(dòng)忽略
6. 4 必加參數(shù)（2）重要參數(shù)
--master-data=2
記錄備份開始時(shí) position號(hào)，可以作為將來做日志截取的起點(diǎn)。
功能：
- 記錄備份時(shí)的position
- 自動(dòng)鎖表
- 配合--single-transaction，減少鎖的（innodb引擎）
--single-transaction
對(duì)于innodb的表，實(shí)現(xiàn)快照備份，不鎖表
6.5 其他參數(shù)（不是必加，了解即可）
-F
--set-gtid-purged=auto
--max-allowed-packet
