上节回顾：
- 存储引擎种类
- InnoDB 核心特性
- 事务ACID
- 锁 ,隔离级别
- innodb_flush_log_at_trx_commit
innodb_flush_method
innodb_buffer_pool_size
===================================
日志管理(工具)
1. 错误日志
1.1 作用
排错.MySQL 启动和工作过程状态,警告,错误.
1.2 配置
默认开启.存储位置, datadir/hostname.err
log_error=/data/mysql/data_3306/mysql.log
1.3 查看
[ERROR]
2. 二进制日志
2.1 作用
数据恢复,主从复制.记录的是所有变化类的操作(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模式, 记录数据行的变化. 记录够准确.
SBR : statement模式 ,以语句方式记录DML语句.会出现记录偏差
MBR : Mixed 模式,混合模式.
mysql> select @@binlog_format;
(3) 内容格式
event:
以event事件的方式记录所有的变化.binlog的最小记录单元为事件.
DDL DCL ,每条语句就是一个事件.
DML ====> begin; A; ...... commit;一个事务由多个事件构成.
event的结构:
起始点:position
事件内容.
结束点: end_position
上一个event的结束点就是下个event的起始点.是连续记录,不能出现断点.
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"
内容查看:
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 使用二进制日志进行数据恢复
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;
重启数据库
select @@max_binlog_size;
2.4.4 binlog的删除
(1) 自动删除
select @@expire_logs_days;
根据全备周期,至少全备周期+1,企业要求至少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具备幂等性,执行过的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的数据恢复
模拟数据和故障:
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中较慢语句的日志.排查数据库语句问题的日志.
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--> 看执行计划 ---> 索引 ----> 语句
数据库慢:
1. 应急性的 ------> show full processlist;
2. 间歇性 ------> slow_log
扩展:
- pt-query-digest 分析慢日志. 集成Anemometer进行图形化展示.
- ELFK ---> slowlog
==========================================
备份和恢复
1. 运维人员在备份恢复方面的职责
1.1 设计备份策略
(1) 备份内容: 数据,二进制日志
(2) 数据容量: 大小
(3) 备份周期:
1. 每天全备
2. 周期全备+增量备份
3. 周期全备+日志备份
(4) 备份时间: 晚上备份
(5) 备份目标位置
1.2 日常备份检查
日志 , 备份的内容,备份大小
1.3 定期恢复演练
我们建议,每半年做一次.
1.4 故障时的恢复
快速准确恢复数据.
1.5 平台数据迁移
同构平台
异构平台
2. 备份工具介绍
2.1 介绍
mysqldump (MDP)
Percona Xtrabackup(xbk,pbk,pxb)
2.2 区别
MDP : 逻辑备份,SQL文件,文本形式,可读性高,便于处理,压缩比高,备份相对较慢 ,比较适合于100G以内的备份 .
100G 30-40 Min
xbk : 物理备份,数据文件,可读性较低,压缩比低,备份相对较快,比较适合于100G以内的备份 .
100G 10min
600G 50min
100 PB ---拆分数据库---> MDP ---压缩->
3. mysqldump
3.1 备份逻辑
将磁盘数据,加载到临时表,转换为SQL(建库,建表,插入语句)
3.2 核心参数
(1) 连接参数
本地: -u -p -S
远程: -u -p -H -P
(2) 备份参数
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# chown -R mysql.mysql /data
-- 基础参数
-- 1. 全备参数
[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
-- 高级参数
-- 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
备份的结果:
第30行查看日志pos -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1162;
-- 6. --single-transaction ★★★★★★
开启独立事务,备份InnoDB表的一致性快照.实现InnoDB表的热备功能.
mysqldump -uroot -p123 -A --master-data=2 --single-transaction >/data/backup/full.sql
补充: ★★★★★★
对于非InnoDB表(FTWRL,Flush tables with read lock),对于mysql库下的系统表备份的时候,会加global read lock
-- 7. 特殊的数据库对象
-R 存储过程和函数
--triggers 触发器
-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
出现场景: 在数据库有超大比表时,需要加此的参数.
-- 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) 模拟原始数据
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) 模拟新的数据
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到测试库 , 业务测试.
-- 5. 导出故障库数据,恢复到生产 .
-- 6. 撤维护页.
恢复过程:
准备备份
vim /data/backup/full_2019-12-26.sql
第30行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;截取二进制日志
-- 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恢复备份
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;
- 检查数据
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;
- 扩展: 从全备中提取 单库 单表的数据进行恢复
1、获得表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
grep -i 'INSERT INTO `city`' full.sqll >data.sql &
3.获取单库的备份
sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql
