上节回顾
1.上节遗留问题
RC 模式： 可以屏蔽脏读，但是会出现 不可重复读和幻读
2. 重点参数
innodb_flush_log_at_trx_commit
作用：控制redo buffer刷写磁盘的策略
0 : redobuffer---每秒-->os buffer--每秒->磁盘
如果出现宕机，有可能丢失一秒的事务。
如果说你的业务，对数据丢失有一定容忍度，可以使用。
1 : redobuffer---commit-->os buffer--commit->磁盘
2 :redobuffer---commit-->os buffer--每秒-->磁盘
innodb_flush_method=O_DIRECT
作用： 控制 redo buffer 和 data buffer 刷磁盘时是否使用os buffer
数据刷盘，不使用os buffer；日志刷盘，使用os buffer
innodb_buffer_pool_size 50-80%
1. 日志管理
## 1.1 排错
### 错误日志
### 怎么配置？
默认位置:
mysql> select @@log_error;
DATADIR/hostname.err
配置方式
vim /etc/my.cnf
log_error=/data/mysql/data/mysql.log
使用方法：
查看[ERROR] 上下文
1.2 数据恢复
binlog(二进制日志)
1.2.1 作用
数据恢复
主从复制
1.2.2 如何配置
log_bin ：
1. 开关
2. 设定存放位置
server_id
1. 5.6 中不需要
2. 5.7 使用以上参数时必须加server_id
生产要求：日志要和数据分开存放
[root@db01 ~]# mkdir /data/binlog -p
[root@db01 ~]# vim /etc/my.cnf
log_bin=/data/binlog/mysql-bin
server_id=6
1.2.3 如何查看配置
mysql> show variables like '%log_bin%';
1.2.4 binlog记录了什么?
大面上说明
记录了数据库中所有变更类的操作
DDL
DCL
DML
详细的说明
（1）
对于DDL和DCL语句，记录发生过的语句
（2）DML(IUD)
前提： 已经提交的事务IUD
关于记录格式：
ROW ：RBR 行记录模式，记录的是行的变化
STATEMENT ：SBR 语句记录模式，记录操作语句
MIXED ：MBR 混合记录模式
电话面试的题目：
delete from city where id>1000
RBR,逐行记录日志,日志量很大，可读性差。但是够严谨，不会出现记录错误
SBR,只记录语句本身，日志量很少，可读性较强。对于函数类的操作，将来恢复时会造错误。
5.7 版本 默认是RBR，是企业建议模式
查看:
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
mysql>
1.2.5 二进制日志事件（event）
简介
二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
position号码
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
#### event的组成
三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用是什么？
为了方便我们截取事件
1.2.6 二进制日志位置的查看
查看二进制日志所在位置
mysql> show variables like '%log_bin%';
[root@db01 /data/binlog]# ls -l /data/binlog/
总用量 12
-rw-r----- 1 mysql mysql 177 6月 25 09:35 mysql-bin.000001
-rw-r----- 1 mysql mysql 154 6月 25 09:35 mysql-bin.000002
-rw-r----- 1 mysql mysql 60 6月 25 09:35 mysql-bin.index
查看正在使用的二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql>
log_name : 目前MySQL存在的二进制日志名字
file_size: 目前mysql用到哪个position号
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 322 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
1.2.7 查看二进制文件内容 ******
查看二进制日志事件
mysql> show master status ; ## 确认当前再用的binlog
mysql> show binlog events in 'mysql-bin.000002'; ## 查看2号binlog的事件
注释：每一行都是一个事件
Log_name ：日志名
Pos ：事件开始的position *****
Event_type ：事件类型
Server_id ：发生在哪台机器的事件
End_log_pos：事件结束的位置号 *****
Info ：事件内容 *****
查看二进制日志内容
[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000002 |grep -v "SET" >/tmp/aa.txt
[root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
1.2.8 基于二进制日志数据恢复案例
如何按需截取日志
1. 基于position号的截取 *****
--start-position=###
--stop-position=###
截取二进制日志核心在于，找起点和终点
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql
恢复：
mysql> drop database oldboy1;
mysql> show databases;
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
2. 基于时间点的截取(了解)
--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56
#### 案例恢复详解
案例: 使用binlog日志进行数据恢复
模拟:
#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;
恢复:
#1. 找到起点和终点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2196 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#2. 查看事件
mysql> show binlog events in 'mysql-bin.000002';
| mysql-bin.000002 | 980 | Query | 6 | 1096 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 2098 | Query | 6 | 2196 | drop database binlog
980-2098
# 3. 截取日志
[root@db01 ~]# mysqlbinlog --start-position=980 --stop-position=2098 /data/binlog/mysql-bin.000002 >/tmp/bak.sql
# 4. 进行恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/bak.sql
# 5. 验证数据
mysql> use binlog
Database changed
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
### 1.2.9 开启GTID功能的二进制日志管理
#### 思考问题？下面怎么恢复？
#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;
truncate table t1;
insert into t1 values(3);
commit;
#4.
drop database binlog;
基于position号恢复需要多次截取，找起点和终点过程很复杂。
#### 什么是GTID （全局事务编号）
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
说明：
DDL DCL，一条语句（事件）就是一个事务，占一个GTID号
DML：一个完整的事务(begin--》commit)，是一个事务，占一个GTID号
#### 开启GTID
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld
#### 查看本机GTID信息
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截取二进制日志
[root@db01 ~]# mysqlbinlog --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql
注意： 以上日志截取出来的日志不能直接恢复？为啥呢？
gtid的幂等性？
#### 正确的截取方法是
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql
#### 跳过某些gtid不截取
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 二进制日志其他操作
#### 临时关闭
set sql_log_bin=0;
说明：
临时关闭二进制日志记录，退出mysql窗口可以恢复
做数据恢复之前，使用以上参数
#### 自动清理
参数：
mysql> select @@expire_logs_days;
设置依据？
至少是一个全备周期+1，企业建议至少2个全备周期+1
怎么设置？
临时设置，重启失效
mysql> set global expire_logs_days=8;
永久设置，重启生效
vim /etc/my.cnf
expire_logs_days=8
#### 手工清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000003';
注意:不要手工 rm binlog文件
1. my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
删除所有binlog，从000001开始（危险！！！！）
mysql> reset master;
#### 日志滚动
重启数据库
flush logs
mysqladmin -uroot -p flush-logs
show variables like '%max_binlog_size%';
备份加一些参数，会触发滚动日志
## 1.3 优化相关日志-slowlog
### 1.3.1 作用
记录慢SQL语句的日志,定位低效SQL语句的工具日志
### 1.3.2 开启慢日志(默认没开启)
开关:
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/mysql/slow.log
设定慢查询时间:
long_query_time=0.1
没走索引的语句也记录:
log_queries_not_using_indexes
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
systemctl restart mysqld
### 1.3.3 mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/data/slow.log
# 1.3.4 第三方工具(自己扩展)
https://www.percona.com/downloads/percona-toolkit/LATEST/
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
toolkit工具包中的命令:
./pt-query-diagest /data/mysql/slow.log
Anemometer基于pt-query-digest将MySQL慢查询可视化
# 备份恢复与迁移 ******
# 1. DBA 在数据库备份恢复方面的职责
## 1.1 设计备份策略
全备
增量
时间
自动
## 1.2 日常备份检查
备份存在性
备份空间够用否
## 1.3 定期恢复演练(测试库)
一季度 或者 半年
## 1.4 故障恢复
通过现有备份,能够将数据库恢复到故障之前的时间点
## 1.5 迁移（非技术）
1. 停机时间
2. 回退方案
2. 备份类型
## 2.1 热备
在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小
## 2.2 温备
锁表备份,只能查询不能修改（myisam）
影响到写入操作
## 2.3 冷备
关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止
3. 备份方式及工具介绍
## 3.1 逻辑备份工具
基于SQL语句进行备份
mysqldump（MDP） *****
mysqlbinlog *****
## 3.2 物理备份工具
基于磁盘数据文件备份
xtrabackup(XBK) ：Percona 第三方 *****
MySQL Enterprise Backup（MEB）
4. 逻辑备份和物理备份的比较
## 4.1 mysqldump (MDP)
优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间
缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB =1024 PB = 1000000 TB
## 4.2 xtrabackup(XBK)
优点：
1.类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2.可读性差
3.压缩比低，需要更多磁盘空间
建议：
>100G<TB
5.备份策略
## 5.1 备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog 或者 xtrabackup_full+binlog
## 5.2 备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量
其他：通过主从复制备份
6. 逻辑备份工具-mysqldump
# 6.1 客户端通用命令,和链接有关
-u
-p
-S
-h
-P
本地备份连接方式：
mysqldump -uroot -pxxx -S /tmp/mysql.sock
远程备份的连接方式：
mysqldump -uroot -pxxx -h xxx -P xxx
# 6.2 基本备份参数
-A 全库备份
例子：实现全库备份
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# mysqldump -uroot -p123 -A -S /tmp/mysql.sock >/data/backup/full.sql
-B 备份 单个库或多个库数据
例子: 备份oldboy和world数据库
[root@db01 ~]# mysqldump -uroot -p123 -B world oldboy -S /tmp/mysql.sock >/data/backup/db.sql
库名 表名 ：备份某个库下的1张或多张表
例子：备份world数据库下的city和country表
[root@db01 ~]# mysqldump -uroot -p123 world city country -S /tmp/mysql.sock >/data/backup/tab.sql
注意： 此种方法，只会备份建表+插入语句。所以，恢复前需要把库建好，而且要use到库中。
# 6.3 必加参数（1）
-R 在备份时，同时备份存储过程和函数，如果没有会自动忽略
-E 在备份时，同时备份EVENT，如果没有会自动忽略
--triggers 在备份时，同时备份触发器，如果没有会自动忽略
# 6.4 必加参数(2)
--master-data=2
记录备份开始时 position号 ，可以作为将来做日志截取的起点。
功能：
1. 记录备份时的position
2. 自动锁表
3. 配合--single-transaction，减少锁的(innodb引擎)
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
对于innodb的表，实现快照备份，不锁表
# 6.5 其他(不是必加的，了解下功能即可)
-F
--set-gtid-purged=auto
auto , on
off
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --max-allowed-packet=128M --single-transaction --set-gtid-purged=ON >/data/backup/full.sql
--max-allowed-packet=#
