MySQL日志管理与备份（误删除恢复）

第六章：MySQL-日志

日志管理(工具)

1.错误日志
1.1：作用
排错. MySQL 启动和工作过程状态,警告,错误。
1.2：配置
默认开启.存储位置，datadir/hostname.err .
1og_ error=/data/mysql/data_ 3306/mysql.1og
1.3：查看
[ ERROR]

[root@db01 ~]# vim /etc/my.cnf
......
log_error=/data/mysql/data_3306/mysql.log
......
[root@db01 binlog_3306]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 

2.二进制日志（记录的都是语句）-----比较适合做数据恢复。
2.1 作用.
数据恢复，主从复制.记录的是所有变化类的操作(DDL,DML,DCL).

2.2配置（5.6和5.7有区别）

[root@db01 data_3306]# cd /data/mysql/binlog_3306/
[root@db01 binlog_3306]# vim /etc/my.cnf
.......
server_id=6
log_bin=/data/mysql/binlog_3306/mysql-bin  
.......
##所有的日志都在这个目录下，然后前缀是以mysql-bin开头的。###这个目录必须要提前创建好。

[root@db01 binlog_3306]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 

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;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

(3) 内容格式

是以事件event的方式记录所有的变化。
DDL DCL ，每条语句就是一个事件。
DML  ------>begin;  A;   commit;一个事物由多个事件构成。
event的结构：开始的位置

（起始点）：Position
          事件的内容
（结束点）：end_position

上一个事件的结束点就是下一个事件的起始点，是连续记录，不能出现断点。

2.3查看二进制

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;   ##滚动日志
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

Mysql只会使用最后一个mysql-bin.000002  ###此处是mysql-bin.000002 

###查看当前正在使用的二进制日志情况
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
###查二进制日志内容（可以基于事件的方式查看）
①模拟创建
mysql> create database oldwangyongqiang;
Query OK, 1 row affected (0.01 sec)

mysql> use oldwangyongqiang
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.34 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  commit;
Query OK, 0 rows affected (0.00 sec)

mysql> 

②
mysql> show master status;
mysql> mysql> show binlog events in 'mysql-bin.000002';  ##为mysql-bin.000002最后一个

[root@db01 binlog_3306]# mysqlbinlog  /data/mysql/binlog_3306/mysql-bin.000002 |grep -v "SET"
DELIMITER /*!*/;
# at 4
#191226  9:48:54 server id 6  end_log_pos 123 CRC32 0xbe471d11 	Start: binlog v 4, server v 5.7.26-log created 191226  9:48:54
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
hhEEXg8GAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AREdR74=
'/*!*/;
# at 123
#191226  9:48:54 server id 6  end_log_pos 154 CRC32 0xbabe942c 	Previous-GTIDs
# [empty]
# at 154
#191226  9:52:21 server id 6  end_log_pos 219 CRC32 0x9f59b967 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
# at 219
#191226  9:52:21 server id 6  end_log_pos 349 CRC32 0x3ce11286 	Query	thread_id=3   exec_time=0	error_code=0
/*!\C utf8 *//*!*/;
create database oldwangyongqiang
/*!*/;
# at 349
#191226  9:53:18 server id 6  end_log_pos 414 CRC32 0x3ffd652e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
# at 414
#191226  9:53:18 server id 6  end_log_pos 535 CRC32 0x5777c387 	Query	thread_id=3   exec_time=0	error_code=0
use `oldwangyongqiang`/*!*/;
create table t1(id int)
/*!*/;
# at 535
#191226  9:53:27 server id 6  end_log_pos 600 CRC32 0x6c5ac5a1 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes

# at 600
#191226  9:53:27 server id 6  end_log_pos 684 CRC32 0x601326a3 	Query	thread_id=3   exec_time=0	error_code=0
BEGIN
/*!*/;
# at 684
#191226  9:53:27 server id 6  end_log_pos 741 CRC32 0xdf6af298 	Table_map: `oldwangyongqiang`.`t1` mapped to number 108
# at 741
#191226  9:53:27 server id 6  end_log_pos 791 CRC32 0xa39e5e84 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
lxIEXhMGAAAAOQAAAOUCAAAAAGwAAAAAAAEAEG9sZHdhbmd5b25ncWlhbmcAAnQxAAEDAAGY8mrf
lxIEXh4GAAAAMgAAABcDAAAAAGwAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAAIRenqM=
'/*!*/;
# at 791
#191226  9:53:27 server id 6  end_log_pos 822 CRC32 0x27c40bc3 	Xid = 14
COMMIT/*!*/;
# at 822
#191226 10:02:20 server id 6  end_log_pos 887 CRC32 0x58407014 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
# at 887
#191226 10:02:20 server id 6  end_log_pos 1015 CRC32 0xc69ae790 	Query	thread_id=3	exec_time=0	error_code=0
drop database oldwangyongqiang
/*!*/;
DELIMITER ;
# End of log file

[root@db01 binlog_3306]# mysql binlog --help
[root@db01 binlog_3306]# mysqlbinlog  --base64-output=decode-rows -vvv /data/mysql/binlog_3306/mysql-bin.000002 |grep -v "SET"
###查看详细内容，将看不懂的row格式做个翻译。

2.4使用二进制日志进行数据恢复（前提是需要准确的定位）

步骤：

​ 1、截取日志 (明确找出起点和终点)

​ 起点：

​ 终点：

​ 2、恢复日志

​ source /tmp/bin.sql

###模拟故障恢复
mysql> create database oldboy;
Query OK, 1 row affected (0.38 sec)

mysql> use oldboy;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(3);
Query OK, 1 row affected (0.36 sec)

mysql>  commit;
Query OK, 0 rows affected (0.00 sec)

###一个傻子把库删了。。。。。。
mysql> drop database oldboy;
Query OK, 1 row affected (0.12 sec)

mysql> show master status;
mysql> show binlog events in 'mysql-bin.000002';
| mysql-bin.000002 | 1080 | Query   |    6 |    1180 | create database oldboy             | mysql-bin.000002 | 1926 | Query   |    6 |    2024 | drop database oldboy               

[root@db01 binlog_3306]# mysqlbinlog --start-position=1080  --stop-position=1926  /data/mysql/binlog_3306/mysql-bin.000002 >/tmp/bin.sql

恢复日志：---->在数据库里面分条执行如下语句。
mysql >
set sql_log_bin=0;
source /tmp/bin.sql
set sql_log_bin=1;

###需要配合备份一起用
如果两年的数据都删了，怎么办？前提：公司每周全备，周三上午十点删的。
思路：恢复上周的和本周的，数据就恢复了。

2.4.3日志滚动

#备份日志时一般会进行flush操作
①
mysql> flush logs;
mysql> show binary logs;
②
mysql> select @@max_binlog_size;   ###达到这个大小自动滚动
③
重启数据库
[root@db01 binlog_3306]# /etc/init.d/mysqld restart

2.4.4 日志binlog的自动删除---(考虑你的全备份周期+1天--（至少）)

(1)自动删除
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> 

###怎么设置？
vim /etc/my.cnf
......
expire_logs_days=15
......
[root@db01 binlog_3306]# /etc/init.d/mysqld restart

（2）手动删除
mysql> PURGE BINARY LOGS TO 'mysql-bin.000002';
Query OK, 0 rows affected (1.99 sec)
mysql> PURGE BINARY LOGS BEFORE ' 2008-04-02 22:46:26' ;
###一般我们直接设置自动删除。
(3)全部清空
mysql> reset master;   ##谨慎使用

2.4.5 扩展--基于GTID的日志记录 ----->5.7的gitid一般是开着的

（1）GTID 介绍
		5.6版本的新特性，5.7进行了增强，建议开启。
		全局事物ID号，对于单机来说，也是可以开GTID的--->影响：对于每一个事物都会有一个编号。可以很方便的基于GTID来快速的管理（主要在主从配置可以提高性能），GTID具有幂等性（执行过的GTID的事物会自动跳过）。
		
（2）格式：
     server_uuid:TID
     
（3）开启GTID功能
vim /etc/my.cnf
......
gtid-mode=on
enforce-gtid-consistency=true
......
[root@db01 binlog_3306]# /etc/init.d/mysqld restart
mysql> create database xtz;   ###必须新创建一个新库。
Query OK, 1 row affected (1.77 sec)
mysql> show master status;
| a7c862b0-20dc-11ea-b483-000c29502c0d:1 |
1 row in set (0.00 sec)
### GTID 已经开启

(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;
一个傻子删库了--->bgx

[root@db01 binlog_3306]# mysqlbinlog --skip-gtids --include-gtids='a7c862b0-20dc-11ea-b483-000c29502c0d:1' /data/mysql/binlog_3306/mysql-bin.000005 >/tmp/gtid.sql  ##只要开了GTID，必须加skip-gtids

### 注意：##只要开了GTID，必须加skip-gtids

恢复日志：---->在数据库里面分条执行如下语句。
mysql >
set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;

故障处理----实例（GTID）----可以当做面试故障处理。

刚上5.7日志截取怎么都恢复不了，头疼了一个周，研究了一下官方文档，添加skip-gtids后就好了，从此之后我就特别喜欢研究官方文档了。

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   ###时间
 
 mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)
###生产一般在一个阶段一个阶段优化。

[root@db01 data_3306]# vim /etc/my.cnf
......
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
......
[root@db01 data_3306]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@db01 ~]# mysql -uroot -p
Enter password: 
##慢语句模拟
mysql> source /root/t100w.sql
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;

[root@db01 ~]# cd /data/mysql/data_3306/
[root@db01 data_3306]# ll
-rw-r----- 1 mysql mysql 49789557 Dec 26 12:08 db01-slow.log


###分析慢日志
[root@db01 data_3306]# mysqldumpslow -s c -t 3 db01-slow.log

###出错，先导入数据，再添加配置文件、再添加模拟。
---->看执行计划 ---->索引  ----> 

数据库慢

1、应急性的 ------>show full processlist;

2、间歇性 -------->slow_log

扩展：(图形化)

1：-----> pt-query-digest分析慢日志.集成Anemometer进行图形化展示.
2: ----->ELEK-->slowlog

备份恢复

---

备份和恢复
1.运维人员在备份恢复方面的职责（刚去公司怎么做？）

看有没有备份-----（contabl -l）

1.1 设计备份策略

（1）备份内容：数据、日志

（2）数据容量：大小

（3）备份周期：1、每天全备（数据量小）2、周期全备+增量备份 3、周期全备+日志备份

（4）备份时间：晚上备份（占用大量IO,要缩表）

（5）目标位置：要有足够的存储空间。

1.2日常备份检查

日志，备份的内容，备份大小。

1.3 定期恢复演练

建议一般是半年做一次或者每季度做一次

1.4 故障时的恢复

快速准确恢复数据。

1.5平台数据迁移

同构平台 异构平台

2.备份工具介绍

2.1介绍

mysqldump （MDP）

Percona Xtrabackup(xbk,pbk,pxb)

MySQL enterprise backup(MEB)---->收费版

2.区别

MDP：逻辑备份，SQL文件，文本形式，可读性高，便于处理，压缩比高，备份相对较慢。

模拟26G数据 4核（4个CPU），8G 硬盘企业级（没有做reid）----->大约备份了26分28秒。

------>企业级硬盘大概17分钟。------->比较适合于100G以内的数据（一般用的少）。

100G 大约30-40min

xbk:物理备份，数据文件，可读性较低，压缩比较低，备份相对较快，比较适合于100G以内的备份。

100G数据 10min

[root@db01 ~]# dd if=/dev/zero of=/data/bigfile bs=4096 count=1024000

600G 不到50分钟。

面试DBA：超过 TB 一张表都两三百个G 淘宝：超过TB PB EB

100PB ------->拆分数据库------>MDP------>自己研发压缩工具

3、mysqldump
3.1备份逻辑
将磁盘数据,加载到临时表,转换为SQL(建库,建表,插入语句)
3.2核心参数.
(1) 连接参数
本地: -u -P -S
远程: -u -P -H -P

-- 一、（基础参数）
-- 1、全备参数
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# chown -R mysql.mysql /data
[root@db01 ~]# mysqldump -uroot -p123456 -A >/data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 


-- 2、单库或者多库备份（bgx test world mysql）
[root@db01 ~]# mysqldump -uroot -p123456 -B bgx test world mysql >/data/backup/db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

-- 3、单表或者多表备份
[root@db01 ~]# mysqldump -uroot -p123456 world city country >/data/backup/tab.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

-- 4、只导出建表语句
[root@db01 ~]# mysqldump -uroot -p123456 world city country --no-data >/data/backup/tab1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

二、高级参数
-- 5、--master-data=2    
（1）自动记录备份时的binlog filename 及pos
（2）自动加锁和解锁
（3）加了--single-transaction会有不一样的效果（只会使用短时间的读写）。
[root@db01 backup]# mysqldump --help
This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.

###命令执行
[root@db01 backup]#  mysqldump -uroot -p123456 -A --master-data=2 >/data/backup/full.sql

###备份的结果
[root@db01 backup]#  vim /data/backup/full.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1162;
------->   这是备份的起点   <-------


-- 6、--single-transaction (开启独立事物，备份InnoDB表的一致性快照，实现Innodb表的热备功能)。 ******
##   补充：****** 
对于非InnoDB表（FTWRL  ：  flush tables with read lock），对于MySQL库下的系统表备份的时候会加global read lock    ##这句话非常重要，面试很重要。

[root@db01 backup]#  mysqldump -uroot -p123456 -A --master-data=2  --single-transaction >/data/backup/full.sql


[root@db01 backup]# mysqldump --help
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.

-- 7、特殊的数据库对象
-R       				存储过程和函数
--triggers 			触发器
-E							事件

[root@db01 backup]#  mysqldump -uroot -p -A --master-data=2  --single-transaction -R --triggers -E >/data/backup/full.sql


-- 8、--max_allowed_packet=128M

出现场景：在数据库有超大表时，需要加此参数

-- 9、终极备份语句
[root@db01 backup]# 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) 模拟原始数据 
mysql> create database mdp charset utf8mb4;
mysql> use mdp
mysql> create table t1 (id int);
mysql> create table t2 (id int);
mysql> create table t3 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t2 values(1);
mysql> commit;
mysql> insert into t3 values(1);
mysql> 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;
mysql> select * from t1；
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

(4)搞破坏
drop database mdp;
(5)恢复思路
-- 1、挂维护页
-- 2、找测试库（版本环境都一样）
-- 3、准备备份
		full
		截取binlog部分
-- 4、恢复全备+binlog到测试库，source ,业务测试。
-- 5、导出数据恢复到生产。
-- 6、撤销维护页面。

恢复过程：
1、准备备份
[root@db01 backup]# gunzip full_2019-12-26.sql.gz 
[root@db01 backup]# vim full_2019-12-26.sql   ###第三十行
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1602;
2、截取二进制日志
起点：1602
mysql> show binlog events in 'mysql-bin.000007';
| mysql-bin.000007 | 2420 | Query   |   6 |        2509 | drop database mdp  
终点 : 2420

mysqlbinlog --skip-gtids --start-position=1602 --stop-position=2420 /data/mysql/binlog_3306/mysql-bin.000003 >/data/backup/bin.sql

3. 恢复备份
mysql> 
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;
4、检验恢复是否成功
mysql> use mdp;
mysql> select * from t1；
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

扩展: 从全备中提取 单库 单表的数据进行恢复

特别擅长恢复误删除的数据比较少的数据。

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

比如说我们把city表单独拎出来

注意：这儿我们举的是表的例子。
1、[root@db01 backup]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full_2019-12-26.sql >createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
[root@db01 backup]# grep -i 'INSERT INTO `city`'  full_2019-12-26.sql >data.sql
###假如说误删除了city
mysql -uroot -p123456
mysql> use world;
mysql> drop table city;
mysql> source createtable.sql    #数据恢复
mysql> source data.sql           #数据恢复
mysql> select * from city;       #通过查看看出数据已经恢复 

数据恢复
###注意：这是库的例子
3.获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql