1. 日志管理
日志作用：排错、数据恢复、优化
1.1 排错
错误日志（默认开启）
默认错误日志查看
3306 [(none)]>select @@datadir; 数据目录
+-------------------+
| @@datadir |
+-------------------+
| /data/mysql/data/ | 错误日志文件默认位置
+-------------------+
1 row in set (0.00 sec)
3306 [(none)]>select @@log_error; 日志文件路径
+-------------+
| @@log_error |
+-------------+
| ./db01.err | 错误日志默认用主机名做文件名
+-------------+
1 row in set (0.00 sec)
配置方式：
vim /etc/my.cnf
log_error=/data/mysql/data/mysql_error.log 文件名可自定义
systemctl restart mysqld
3306 [(none)]>select @@log_error;
+----------------------------------+
| @@log_error |
+----------------------------------+
| /data/mysql/data/mysql_error.log |
+----------------------------------+
1 row in set (0.00 sec)
错误日志查看方法：
查看[ ERROR]关键字，上下文
[root@db01 ~]# vim /data/mysql/data/mysql_error.log
2019-06-25T01:59:04.257173Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-06-25T01:59:04.257198Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-06-25T01:59:04.280635Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 312161443
…………省略部分内容
1.2 数据恢复
为什么要数据恢复？
当数据库出现了数据损坏或丢失的时候，就需要用到日志进行数据恢复，把数据恢复到误操作之前。
binlog(二进制日志)
作用：
（1）记录数据库中所有变更类的操作
（2）数据恢复。（也是以后备份工作中，必须要备份的日志）
（3）主从复制，也是依赖于binlog从底层来实现的。
binlog配置方法（默认没有开启二进制日志功能）
binlog必配参数
log_bin 作用：打开二进制功能，并且设设置日志存放位置
server_id 作用：5.6中，单机情况下不用设置server_id。5.7中使用以上参数时，必须加server_id。
5.7版本中两者必须结合
注意：日志、数据、备份要分盘存放，这是生产要求！！！
配置过程：
[root@db01 ~]# mkdir -p /data/binlog
[root@db01 ~]# vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin（mysql-bin可自定义）
chown -R mysql. /data/*
/etc/init.d/mysql restart
[root@db01 /data/binlog]# ls
mysql-bin.000001 #这就是生成的二进制日志文件，每重启一次数据库就会生成一个新的二进制日志文件，默认最大大小为1G，可更改。
mysql-bin.index #二进制日志文件的索引，里面存放着所有系统生成的二进制日志文件的名字信息和位置信息，mysql获取二进制日志时，先读的Index，看一下一共有多少个二进制日志文件。
1.2.3 查看binlog配置参数
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON （永久开启或关闭） |
| log_bin_basename | /data/binlog/mysql-binlog |
| log_bin_index | /data/binlog/mysql-binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON （临时开启或关闭） |
+---------------------------------+------------------------------+
1.2.4 binlog记录了什么？
1. 大面上的说明
记录了数据库中所有变更类的操作（除了select、show等语句）
DDL：DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
常用的语句关键字包括：
（1）CREATE
（2）ALTER
（3）DROP
DCL：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
DML：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性
常用的语句关键字包括：
insert、delete、udpate 和select 等。(增添改查）
2. 详细的说明
（1）对于DDL和DCL语句，记录发生过的语句。例如创建了一个数据库，binlog就会把建库语句保存在二进制日志文件中。
（2）DML（insert、update、delete(IUD)）
要记录DML语句，前提是必须保证事务已经提交（begin到commit一个完成的生命周期），才可以被记录到二进制日志中。
关于binlog记录格式：（面试题）
mysql默认提供了3中binlog记录格式（二进制格式）（面试题）
（1）ROW：RBR 行记录模式，记录的是行的变化（5.7及以上默认的级别）
（2）STATEMENT：SBR 语句记录模式，记录操作语句本身
（3）MIXED：MBR 混合记录模式，
电话面试题目：
请说明一下RBR和SBR的记录模式。
如以下语句：
delete from city where id>1000;
RBR，逐行记录每一行的变化，日志量较大，可读性差，但是够严谨，不会出现记录错误
SBR，只记录语句本身，日志量很少并且可读性较强。对于函数类的操作，将来恢复时可能会出错。特别是时间函数。
5.7 版本及8.0版本，默认就是RBR，是企业建议模式
查看当前binlog模式：
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW | 默认模式
+-----------------+
1 row in set (0.00 sec)
1.2.5 二进制日志事件（单元）（event）
简介
二进制日志的最小记录单元（事件，event），每一个操作，都会视为一个事件。
对于DDL、DCL来讲，一个语句就是一个event。
对于DML事务语句来讲，只记录了已经提交的事务。
例如以下例子，就被分为了4个event。
position号码（相对位置号。截取日志专用，最准确）
begin; 120（at） - 340（end_log_pos）
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
截取日志方法：从事务的开始到结束。120-760。
evnet的组成
三部分构成
（1）事件的开始标识：at xxx
（2）事件内容
（3）事件的结束标识：end_log_pos xxx
Position：（binlog中相对位置号）
开始标识：at 194
结束标识：end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用？
为了方便我们截取事件
1.2.6 二进制日志的查看
查看二进制日志所在位置
mysql> show variables like '%log_bin%'; 查看二进制日志手动指定的安装位置
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| log_bin_index | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)
[root@db01 /data/binlog]# ll /data/binlog/
total 16
-rw-r----- 1 mysql mysql 177 Jun 25 10:02 mysql-bin.000001
-rw-r----- 1 mysql mysql 177 Jun 25 10:02 mysql-bin.000002
-rw-r----- 1 mysql mysql 154 Jun 25 10:03 mysql-bin.000003
-rw-r----- 1 mysql mysql 90 Jun 25 10:03 mysql-bin.index
#mysql-bin.00000x文件，数据库每重启一次，就会按数字顺序生成一个
查看正在使用的二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看所有使用的二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 154 | >>>最后一个就是正在使用的二进制日志文件
+------------------+-----------+
3 rows in set (0.00 se
Log_name：目前数据库存在的二进制日志名字
File_size(字节)：目前数据库用到哪个Position号了
1.2.7 查看二进制文件内容
查看二进制日志事件
查看方式：
mysql>show master status; (确认当前正在使用的binlog二进制日志文件。)
mysql> show binlog events in 'mysql-bin.000003'; (mysql-bin.000003'为正在使用的事件，查看就是要查看正在使用的)
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
注意：以下每行都是一个事件
Log_name：日志名
Pos：事件“开始”的position（重点）
Event_type：事件类型
Server_id：事件发生在哪台机器上的
End_log_pos：事件“结束”的位置号（重点）
Info：事件内容（重点）
image.png
binlog日志查看方式：
MySQL5.7版本及以后的版本，154之前都是binlog日志的头格式信息，也就是说，4-154是每个binlog日志文件都有的（5.6是前120）固定格式。
那么我们查看binlog日志时，直接从154开始查看就行了。
Anonumous_Gtid行也不用看。
从一个at到下一个at。
查看事务时，要从begin看到commit。
使用工具查看日志文件内容（二进制文件只能借助工具查看，不然看不了）
[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000003
普通查看内容方式（DML语句部分无法查看）：
[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000003 | grep -v "SET" > /tmp/1.txt 过滤SET行，因为SET行对于查看二进制日志并没有什么太大的帮助
详细查看二进制日志内容：（DML语句可查看）
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003
1.2.8 基于二进制日志数据恢复案例
如何按需截取日志
（1）基于Position号的截取（重点）
参数：
--start-position=
--stop-position=
截取二进制日志的核心在于：“找起点和终点”（找event列表或at到end）
（2）基于时间点的截取（了解）
--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56
可以使用mysqlbinlog --help查看帮助
例子：截取建库语句
mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 6 | 307 | create database t1 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
mysqlbinlog --start-position=219 --stop-position=307 /data/binlog/mysql-bin.000003 > /tmp/bin.sql
模拟误删除及恢复
删除数据库
mysql> drop database t1;
Query OK, 0 rows affected (0.09 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| ceshi |
| mysql |
| oldboy |
| oldguo |
| onon |
| performance_schema |
| school |
| sys |
| world |
| zhihu |
+--------------------+
12 rows in set (0.16 sec)
恢复数据库：
建议使用souce命令进行恢复，不记录恢复过程中的二进制日志
mysql> set sql_log_bin=0; #临时关闭日志 只影响当前会话
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/bin.sql
Query OK, 0 rows affected (0.00 sec)
…………省略部分内容
查看恢复是否成功：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
……省略
| t1 | #恢复成功
…省略
案例：恢复详解
模拟案例：误删除后，使用binlog日志进行数据恢复
1.
[(none)]>create database binlog charset utf8mb4;
2.
[(none)]>use binlog;
[binlog]>create table t1(id int);
3.
[binlog]>insert into t1 values(1);
[binlog]>commit;
[binlog]>insert into t1 values(2);
[binlog]>commit;
[binlog]>insert into t1 values(3);
[binlog]>commit;
4.
[binlog]>drop database binlog;
恢复步骤：
（1）看日志，找到起点和终点
mysql> show master status; #查看当前使用的日志
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1900 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> show binlog events in 'mysql-bin.000003'; #查看事件
+------------------+------+----------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------+
…………省略部分内容
| mysql-bin.000003 | 684 | Query | 6 | 800 | create database binlog charset utf8mb4 |
| mysql-bin.000003 | 800 | Anonymous_Gtid | 6 | 865 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 865 | Query | 6 | 966 | use `binlog`; create table t1(id int) |
| mysql-bin.000003 | 966 | Anonymous_Gtid | 6 | 1031 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1031 | Query | 6 | 1105 | BEGIN |
| mysql-bin.000003 | 1105 | Table_map | 6 | 1152 | table_id: 110 (binlog.t1) |
| mysql-bin.000003 | 1152 | Write_rows | 6 | 1192 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000003 | 1192 | Xid | 6 | 1223 | COMMIT /* xid=75 */ |
| mysql-bin.000003 | 1223 | Anonymous_Gtid | 6 | 1288 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1288 | Query | 6 | 1362 | BEGIN |
| mysql-bin.000003 | 1362 | Table_map | 6 | 1409 | table_id: 110 (binlog.t1) |
| mysql-bin.000003 | 1409 | Write_rows | 6 | 1449 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000003 | 1449 | Xid | 6 | 1480 | COMMIT /* xid=77 */ |
| mysql-bin.000003 | 1480 | Anonymous_Gtid | 6 | 1545 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1545 | Query | 6 | 1619 | BEGIN |
| mysql-bin.000003 | 1619 | Table_map | 6 | 1666 | table_id: 110 (binlog.t1) |
| mysql-bin.000003 | 1666 | Write_rows | 6 | 1706 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000003 | 1706 | Xid | 6 | 1737 | COMMIT /* xid=79 */ |
| mysql-bin.000003 | 1737 | Anonymous_Gtid | 6 | 1802 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1802 | Query | 6 | 1900 | drop database binlog |
+------------------+------+----------------+-----------+-------------+-------------------------------------------+
29 rows in set (0.00 sec)
（2）找到create起点到drop的起点
684-1802
mysqlbinlog --start-position=684 --stop-position=1802 /data/binlog/mysql-bin.000003 > /tmp/bak.sql
（3）进行恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/bak.sql
Query OK, 0 rows affected (0.00 sec)
（4）验证恢复
mysql> use binlog;
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
………………省略
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;
恢复方法：跳过删除数据部分进行恢复！！！GTID
基于position号恢复需要多次截取，找起点和终点的过程很复杂，如果是一个一两年的日志，那么截取数据会更加复杂麻烦！！！这个时候就推荐开启GTID。
什么是GTID（Global transaction identifiers，全局事务唯一编号）
5.6版本新加的特性，5.7中做了加强
5.6中默认不开启，没有这个功能
5.7中的GTID默认也没有开启，但是有匿名的GTID自动生成
SET @@SESSION.GTID_NEXT='ANONYMOUS'; 匿名GTID，匿名GTID只对系统维护有作用。
GTID是对于一个已提交的事务编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
7E11FA47-31CA-19E1-9E56-C43AA21293967：uuid或sourceid
29：GTID，会自动增长
说明：
DDL、DCL，一条语句（事件）就是一个事务，占一个GTID号
DML：一个完整的事务（begin-->commit），是一个事物，占一个GTID号
开启GTID
vim /etc/my.cnf
gtid-mode=on 开启GTID模式，也可以=1。
enforce-gtid-consistency=true
systemctl restart mysqld
查看GTID开启后的状态
3306 [(none)]> system cat /data/mysql/data/auto.cnf;
[auto]
server-uuid=ffe55c30-8da3-11e9-8813-000c29577287
3306 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>3306 [(none)]>show master status;
+---------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+----------------------------------------+
| mysql-binlog.000003 | 313 | | | 3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1 |
+---------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.01 sec)
模拟状态，查看开启GTID后的状态
3306 [(none)]> create database gg;
3306 [(none)]> show master status;
+---------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+------------------------------------------+
| mysql-binlog.000003 | 466 | | | 3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-2 |
+---------------------+----------+--------------+------------------+------------------------------------------+
3306 [(none)]> use gg;
3306 [gg]> create table t1 (id int);
3306 [gg]> insert into t1 values(1);
3306 [gg]> commit;
3306 [gg]> insert into t1 values(2);
3306 [gg]> commit;
3306 [gg]> insert into t1 values(3);
3306 [gg]> commit;
3306 [(none)]>drop database gg;
查看上述一系列操作后的GTID
3306 [(none)]>show master status;
+---------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+------------------------------------------+
| mysql-binlog.000003 | 1523 | | | 3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-7 |
+---------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
3306 [(none)]>show binlog events in 'mysql-binlog.000003';
+---------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-binlog.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-binlog.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-binlog.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1' |
| mysql-binlog.000003 | 219 | Query | 6 | 313 | create database test |
| mysql-binlog.000003 | 313 | Gtid | 6 | 378 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:2' |
| mysql-binlog.000003 | 378 | Query | 6 | 466 | create database gg |
| mysql-binlog.000003 | 466 | Gtid | 6 | 531 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:3' |
| mysql-binlog.000003 | 531 | Query | 6 | 625 | use `gg`; create table t1 (id int) |
| mysql-binlog.000003 | 625 | Gtid | 6 | 690 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:4' |
| mysql-binlog.000003 | 690 | Query | 6 | 760 | BEGIN |
| mysql-binlog.000003 | 760 | Table_map | 6 | 803 | table_id: 108 (gg.t1) |
| mysql-binlog.000003 | 803 | Write_rows | 6 | 843 | table_id: 108 flags: STMT_END_F |
| mysql-binlog.000003 | 843 | Xid | 6 | 874 | COMMIT /* xid=21 */ |
| mysql-binlog.000003 | 874 | Gtid | 6 | 939 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:5' |
| mysql-binlog.000003 | 939 | Query | 6 | 1009 | BEGIN |
| mysql-binlog.000003 | 1009 | Table_map | 6 | 1052 | table_id: 108 (gg.t1) |
| mysql-binlog.000003 | 1052 | Write_rows | 6 | 1092 | table_id: 108 flags: STMT_END_F |
| mysql-binlog.000003 | 1092 | Xid | 6 | 1123 | COMMIT /* xid=23 */ |
| mysql-binlog.000003 | 1123 | Gtid | 6 | 1188 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:6' |
| mysql-binlog.000003 | 1188 | Query | 6 | 1258 | BEGIN |
| mysql-binlog.000003 | 1258 | Table_map | 6 | 1301 | table_id: 108 (gg.t1) |
| mysql-binlog.000003 | 1301 | Write_rows | 6 | 1341 | table_id: 108 flags: STMT_END_F |
| mysql-binlog.000003 | 1341 | Xid | 6 | 1372 | COMMIT /* xid=25 */ |
| mysql-binlog.000003 | 1372 | Gtid | 6 | 1437 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:7' |
| mysql-binlog.000003 | 1437 | Query | 6 | 1523 | drop database gg |
+---------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
image.png
模拟删除恢复（恢复删除的gg库）
基于GTID截取二进制日志（注意：这种方法不能直接恢复数据）
[root@db01 /data/mysql/data]# mysqlbinlog --include-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:2-6' /data/binlog/mysql-binlog.000003 > /tmp/gtid.sql
--include-gtids:包含，比如从2-6行的数据
这样截取出来的不能恢复数据！！！在source的过程中会报错！！！
如下：
3306 [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>source /tmp/gtid.sql
省略部分内容………………
Query OK, 0 rows affected (0.00 sec)
ERROR 1049 (42000): Unknown database 'gg'
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
省略部分内容……………………
上述恢复报错原因：
涉及到GTID的幂等性？恢复过程中跳过重复性的操作！！！
正确的恢复方法：
加一个参数 --skip-gtids：排除所有GTID号
[root@db01 /data/mysql/data]# mysqlbinlog --skip-gtids --include-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:2-6' /data/binlog/mysql-binlog.000003 > /tmp/gtid.sql
再次恢复测试：
3306 [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>source /tmp/gtid.sql
省略输出…………
3306 [gg]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| gg | gg库恢复成功
| mysql |
| oldboy |
| performance_schema |
| sys |
| test |
+--------------------+
8 rows in set (0.00 sec)
3306 [gg]>use gg
Database changed
3306 [gg]>show tables;
+--------------+
| Tables_in_gg |
+--------------+
| t1 |
+--------------+
1 row in set (0.00 sec)
3306 [gg]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
跳过某些GTID不截取（--exclude-gtids）
（1）跳过连续的GTID
mysqlbinlog --skip-gtids --include-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:2-6' --exclude-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:3-4' /data/binlog/mysql-binlog.000003 > /tmp/gtid.sql
（2）跳过多个不连续GTID
mysqlbinlog --skip-gtids --include-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:2-6' --exclude-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:3,3276d3f1-bd6d-11e9-9553-000c29f1dbe8:5' /data/binlog/mysql-binlog.000003 > /tmp/gtid.sql
1.2.10 二进制日志其他操作
临时关闭binlog
set sql_log_bin=0; 临时关闭当前会话二进制日志记录。数据恢复时，不想出现其他日志。退出当前会话窗口开启。
应用场景：
数据恢复之前，使用以上参数。
自动清理日志
参数：expire_logs_days
查看日志过期天数:
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 | 默认为0天，如设置为7，就会自动删除7天之前的日志
+--------------------+
1 row in set (0.00 sec)
设置自动清理日志依据
至少是一个全量备份的周期+1天，如果每天一个全备的话，也就是8天。企业建议至少2个全备周期+1，也就是15天。
设置方式：
在线（临时）设置，重启失效
3306 [(none)]>set global expire_logs_days=8;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.01 sec)
手动设置（永久），重启也生效
vim /etc/my.cnf
expire_logs_days=8
手工清理binlog
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; 删除3天前的日志。
PURGE BINARY LOGS TO 'mysql-bin.000010'; 删除到某一个为止。这里是把10前面的GTID记录全部删除，只剩下10。
上述两种方法删除binlog，当数据库重启后，日志号码不会从000001开始计数，除非把二进制日志关闭，然后重启数据库并重新开启二进制日志。
注意：不要手工rm binlog文件
如果不小心在数据库启动时删除了binlog文件，可能会导致数据库无法启动，可以按照以下方法启动数据库。
1. 在my.cnf文件中把binlog关闭掉，启动数据库
2. 把数据库关闭，开启binlog，启动数据库
删除所有binlog，并从000001开始重新记录日志
如果因为日志太多，有好几十万个日志号码或者更多，就想让日志号码从000001开始计数，
首先必须得做一个全备，并且不要在主从复制的主库上面执行，一旦在主库上面执行，主从必崩。
如果要对主从环境删除，首先停止业务，让数据没有变化，删除后重新构建主从环境！
删除所有binlog
危险删除法：物理上逻辑上文件全部删除，从000001重新开始
3306 [(none)]>reset master;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>exit
Bye
[root@db01 /data/binlog]# ll
total 8
-rw-r----- 1 mysql mysql 154 Aug 26 15:59 mysql-binlog.000001
-rw-r----- 1 mysql mysql 33 Aug 26 15:59 mysql-binlog.index
binlog日志滚动
作用:
从000001切换到000002（新的GTID），防止一个日志文件太大，分析麻烦。
方法：
（1）重启数据库
（2）flush logs 刷新新的binlog日志出来
（3）mysqladmin -uroot -p flush-logs
（4）show variables like 'max_binlog_size%'; 可以通过设置这个的默认大小为128M来控制日志大小，推荐设置128，方便日志分析
3306 [(none)]>show variables like 'max_binlog_size%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 | 默认大小1G，达到这个值后就会刷新另一个binlog日志
+-----------------+------------+
1 row in set (0.00 sec)
1.3 优化相关日志-slowlog
1.3.1 作用：
记录慢SQL语句的日志，定位低效SQL语句的工具日志
1.3.2 开启慢日志（默认没有开启）
查看慢日志状态
3306 [(none)]> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
slowlog默认存放位置
3306 [(none)]>select @@slow_query_log_file;
+--------------------------------+
| @@slow_query_log_file |
+--------------------------------+
| /data/mysql/data/db01-slow.log |
+--------------------------------+
1 row in set (0.00 sec)
开启方式：
vim /etc/my.cnf
slow_query_log=1
#slow_query_log_file=/data/mysql/data/db01-slow.log
long_query_time=0.1 设置语句执行超过0.1秒就记录到日志中，默认单位是10秒
log_queries_not_using_indexes 记录不使用索引的查询，默认为0
systemctl restart mysqld
1.3.3 mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/data/db01-slow.log
默认情况下，mysqldumpslow按平均查询时间(相当于-s at)排序。
-s：对输出进行排序，c：按计数排序
-t：只显示输出中的前N个查询，这里为前10个
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 -y 安装依赖
toolkit工具包中的命令:
./pt-query-diagest /data/mysql/db01-slow.log
Anemometer基于pt-query-digest将MySQL慢查询可视化，网页展示
备份恢复与迁移
1. DBA在数据库备份恢复方面的职责
1.1 设计备份策略
全量备分
增量备份：MySQL只能做基于上一天的增量
备份时间：业务不繁忙期间，如半夜23点或0点
自动备份：定时任务
数据量小，比如说几十G，可以天天全备。
数据量大，上TB级别了，定期全备（周或月），天天增备。
1.2 日常备份检查
备份是否存在（天天检查，看看路径是否有）
备份空间是否够用（定期检查，如df -h）
1.3 定期恢复演练（测试库）
一季度或半年
1.4 故障恢复
通过现有备份，能够将数据恢复到故障之前的时间点
1.5 同构迁移与异构迁移
除了考虑技术方面的问题外，还要考虑一下两点：
（1）考虑停机时间
（2）考虑迁移失败后的回退方案
2. 备份类型
2.1 热备（MySQL中只有Innodb支持热备）
在数据库业务正常运行时，备份数据，并且能够一致性恢复（只能是innodb），对业务影响非常小
2.2 温备
锁表备份，只能查询不能修改（myisam支持温备）
影响到写入操作
2.3 冷备
关闭数据库业务，数据库没有任何变更的情况下，进行备份数据
业务停止
3. 备份方式及工具介绍
3.1 逻辑备份工具
基于SQL语句进行备份
mysqldump
mysqlbinlog
3.2 物理备份工具
基于磁盘数据文件备份
xtrabackup(XBK)：Percona 的第三方工具
MySQL Enterprise Backup（MEB）：只能在MySQL企业版中使用，收费的
4. 逻辑备份和物理备份比较
4.1 mysqldump(MDP)
优点：
1. 不需要下载安装，因为安装数据库的时候就已经装好了
2. 备份出来的是SQL语句，文本格式，可读性高，便于备份处理
3. 压缩比更高，节省备份的磁盘空间
缺点：
1. 依赖于数据库引擎(mysqld)，需要从磁盘把数据读出，然后转换成SQL进行转存储，比较耗费CPU及内存的资源，数据量大的话效率较低，备份时间长。
建议：
（1）100G以内的数据量级，可以使用mysqldump，也可以用XBK，如果超100G以上，做分库分表后，也可以使用mysqldump。
（2）超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统，配合mysqldump应用。
1EB=1024PB=1000000TB
4.2 xtrabackup(XBK)
优点：
1. 类似于直接CP文件，不需要管逻辑结构，相对来说性能较高
缺点：
1. 可读性差
2. 压缩比低，需要更多的磁盘空间
建议：
>100G<1TB
超过TB，建议另做考虑！
5. 备份策略
（1）备份方式：
全量备份：全库备份，备份所有数据
增量备份：只备份发生变化的数据
逻辑备份=mysqldump+mysqlbinlog(mysqldump做全备，mysqlbinlog做增量)
物理备份=xtrabackup_full+xtrabackup_incr+binlog 或者xtrabackup_full+binlog（xtrabackup_full全备+binlog增备）
（2）备份周期：
根据数据量级设计备份周期
比如：周日全备，周1-周6增量
（3）其他：通过主从复制备份
6. 逻辑备份工具使用-mysqldump
6.1 客户端通用命令,和链接有关
-u：用户
-p：密码
-S：socket文件
-h：IP
-P：端口
本地备份的连接方式：
mysqldump -uroot -pxxx -S /tmp/mysql.sock
远程备份的连接方式：
mysqldump -uroot -pxxx -h xxx -P xxx
6.2 基本备份参数（测试环境使用，不能上生产）
-A：实现全库备份
-B：单库或多库备份
库名 表名
-A
例子：全库备份
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# mysqldump -uroot -p123 -A -S /tmp/mysql.sock > /data/backup/full.sql
[root@db01 ~]# ll -h /data/backup/*
-rw-r--r-- 1 root root 49M Jun 25 18:04 /data/backup/full.sql
注意备份文件的头格式：MySQL恢复时，会读取这个文件的注释
[root@db01 /data/backup]# vim bak.sql
-- MySQL dump 10.13 Distrib 5.7.26, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.26-log
省略部分内容…………
-B：单库或多库备份
例子：备份oldboy和world数据库
[root@db01 ~]# mysqldump -uroot -p123 -B world oldboy -S /tmp/mysql.sock > /data/backup/db.sql
[root@db01 ~]# ll -h /data/backup/*
-rw-r--r-- 1 root root 48M Jun 25 18:10 /data/backup/db.sql
-rw-r--r-- 1 root root 49M Jun 25 18:04 /data/backup/full.sql
库名 表名：备份某个库下的1张或多张表
例子：备份world下的city表和country表
[root@db01 ~]# mysqldump -uroot -p123 world city country -S /tmp/mysql.sock > /data/backup/tab.sql
[root@db01 ~]# ll -h /data/backup/*
-rw-r--r-- 1 root root 48M Jun 25 18:10 /data/backup/db.sql
-rw-r--r-- 1 root root 49M Jun 25 18:04 /data/backup/full.sql
-rw-r--r-- 1 root root 213K Jun 25 18:16 /data/backup/tab.sql
注意：针对全库备份用-A，备份单库或多库用-B。只备份某表，不备份库，就啥也不加，此种方法只会备份建表+插入语句，没有建库语句，所以恢复前需要把库先建好，而且要use到库中。
注意：如果是单库单表恢复，恢复时要先到测试库进行恢复，然后把恢复好的库、表导出到生产库恢复。
6.3 企业级备份参数（生产环境必加参数）
（1）
-R：备份时，同时备份存储过程和函数，如果没有存储过程和函数，会自动忽略，没有警告。
-E：在备份时，同时备份事件（event），如果没有自动忽略。
--triggers：在备份时，同时备份触发器，如果没有自动忽略。
（2）面试题
--master-data=2：记录备份时的 position号，可以作为将来日志截取的起点
功能：
1. 记录备份时的position号
2. 自动锁表
3. 配合--single-transaction，减少锁（innodb）表的时间
--single-transaction：对于Innodb的表，实现快照备份， 不锁表
（3）其他类，非可选（了解功能即可）
-F：有多个备份库，就生成多少个binlog日志、GTID号
--set-gtid-purged=auto : 不加该参数默认就为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/bakcup/full.sql
2. auto或on，在构建主从复制环境时需要的参数配置（重点）
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON > /data/backup/full.sql
3. --max-allowed-packet=xxxM，防止备份大表时报错超出最大包大小
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --max-allowed-packet=128M --single-transaction --set-gtid-purged=ON > /data/backup/full.sql
网友评论