目录
MySQL日志
错误日志（log_error）
log_error作用
- 记录启动/关闭/日常运行过程中的状态信息（警告，错误）。
log_error配置
默认开启，存放于数据库文件的数据目录中：hostname.err
[root@localhost ~]# ll /data/mysql/*err
-rw-r-----. 1 mysql mysql 15374 Sep 23 15:55 /data/mysql/localhost.localdomain.err
通过修改配置文件手工指定错误日志位置：
-
查看log_error变量（log_error是定义是否启用错误日志的功能和错误日志的存储位置）
mysql [(none)]>select @@log_error; +-------------+ | @@log_error | +-------------+ | stderr | +-------------+ 1 row in set (0.00 sec) -
修改mysql配置文件，指定log_error位置（若文件不存在需创建文件并修改文件全权限）
[root@localhost log]# vim /etc/my.cnf log_error=/var/log/mysql.err[root@localhost ~]# touch /var/log/mysql.err [root@localhost ~]# chown mysql.mysql /var/log/mysql.err重启mysqld服务生效
[root@localhost ~]# systemctl restart mysqld -
再次查看log_error信息：show variables like ‘log_error’;
mysql [(none)]>show variables like 'log_error'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | log_error | /var/log/mysql.err | +---------------+--------------------+ 1 row in set (0.00 sec)
log_error查看
- 重点关注ERROR，查看上下文信息。
二进制日志（binlog）
binlog作用
- 备份恢复必须依赖二进制日志；
- 主从环境必须依赖二进制日志。
binlog配置
binlog基本参数
-
查看binlog是否开启（MySQL默认是没有开启二进制日志的）
mysql [(none)]>select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 0 | +-----------+ 1 row in set (0.06 sec) -
查看binlog日志路径及名称
mysql [(none)]>select @@log_bin_basename; +--------------------+ | @@log_bin_basename | +--------------------+ | NULL | +--------------------+ 1 row in set (0.00 sec) -
查看服务server ID号
mysql [(none)]>select @@server_id; +-------------+ | @@server_id | +-------------+ | 6 | +-------------+ 1 row in set (0.00 sec) -
查看binlog格式
mysql [(none)]>select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 1 row in set (0.00 sec) -
查看双一标准之二
mysql [(none)]>select @@sync_binlog; +---------------+ | @@sync_binlog | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec)
binlog配置实践
-
创建日志目录
[root@localhost ~]# mkdir /data/binlog [root@localhost ~]# chown -R mysql.mysql /data/binlog/ -
修改配置文件
[root@localhost ~]# vim /etc/my.cnf server_id=6 --主要用于主从复制必须添加 log_bin=/data/binlog/mysql-bin --设置命名前缀，开启二进制日志功能 binlog_format=row -
重启数据库服务
[root@localhost ~]# systemctl restart mysqld -
查看二进制文件
[root@localhost ~]# ll /data/binlog/ total 8 -rw-r-----. 1 mysql mysql 154 Sep 30 13:36 mysql-bin.000001 -rw-r-----. 1 mysql mysql 30 Sep 30 13:36 mysql-bin.index
binlog内容
binlog是SQL层的功能，记录的是变更SQL语句，不记录查询语句。
- DDL ：原封不动的记录当前DDL(statement语句方式)。
- DCL ：原封不动的记录当前DCL(statement语句方式)。
- DML ：只记录已经提交的事务DML。
DML三种记录方式，由binlog_format（binlog的记录格式）参数控制：
- statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML
- row(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(需要工具分析)
- mixed（混合）MBR(mixed based replication)模式 ：以上两种模式的混合
面试题：SBR和RBR模式的对比
- STATEMENT：可读性较高，日志量少，不够严谨
- ROW：可读性很低，日志量大，足够严谨
假设update t1 set xxx=xxx where id>1000中，一共500w行，row模式怎么记录的日志？
- STATEMENT模式只记录update这条语句；
- ROW模式按行记录，将500w行一一记录下来。
例如：一张表包含id、name、intime三列，假设insert into t1 values（1，‘zs’,now()），为什么说row模式严谨？
- STATEMENT模式只会记录insert这条语句，在进行数据恢复时，拿出这条语句执行now()会变成当前的时间点而不是过去执行这条语句的时间点，不够严谨；
- ROW行模式记录的就不是insert这条语句，而会记录到当时的时间点，足够严谨。
event事件
event简介
event事件：二进制日志的最小记录单元。
-
对DDL,DCL来说，一条语句就是一个event
-
对DML语句来说：只记录已提交的事务
例如以下列子，一个事务有4个语句，1条语句就是1个event
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
event组成
event由三部分构成：
- 事件的开始标识（AT num）
- 事件内容
- 事件的结束标识（END_LOG_POS num）
event1 end_log_pos num= event at num（上一个事件的结束标识是下一个事件的开始标识）
位置号的作用是什么？
- 为了方便截取事件
binlog查看
查看binlog开启情况
mysql [(none)]>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.12 sec)
查看binlog总数
mysql [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
- flush logs：刷新一个新的binlog
查看mysql正在使用的binlog
mysql [(none)]>show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- file：当前MySQL正在使用的文件名
- Position：最后一个事件的结束位置号
查看binlog文件中event事件
SHOW BINLOG EVENTS
[IN ‘binlog_name’]
[FROM pos]
[LIMIT [offset,] row_count]
以下两条是每个binlog都有的MySQL用来识别它是一个二进制文件的标识（Format_desc是每一个日志文件的第一个事件，多用户没有意义，是MySQL识别binlog必要信息）。
mysql [(none)]>show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
- Log_name：binlog文件名
- Pos：开始的position
- Event_type：事件类型
- Server_id：mysql服务号标识
- End_log_pos：事件的结束位置号
- Info：事件内容
例如：在Linux命令行下过滤drop操作
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" | grep drop
查看binlog文件详细内容
显示binlog文件详细内容
mysqlbinlog /data/binlog/mysql-bin.000004
显示更详细的基于row模式的DML语句
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000004
只看某个库的binlog记录
mysqlbinlog -d database_name /data/binlog/mysql-bin.000004
只看某个时间段内的binlog记录
mysqlbinlog --start-datetime=‘2021-09-20 17:00:00’ --stop-datetime=‘2021-09-30 17:00:00’ /data/binlog/mysql-bin.000004
binlog截取
基于Position号进行日志截取
截取核心就是找截取的起点和终点，对数据恢复非常重要。
–start-position=321
–stop-postion=513
mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
案例：使用binlog日志进行数据恢复
- 模拟删库：创建数据库binlog，及t1，insert数据后删库
mysql [(none)]>create database binlog charset utf8; Query OK, 1 row affected (0.01 sec) mysql [(none)]>use binlog; Database changed mysql [binlog]>create table t1(id int); Query OK, 0 rows affected (0.16 sec) mysql [binlog]>insert into t1 values(1); Query OK, 1 row affected (0.07 sec) mysql [binlog]>commit; Query OK, 0 rows affected (0.00 sec) mysql [binlog]>insert into t1 values(2); Query OK, 1 row affected (0.01 sec) mysql [binlog]>commit; Query OK, 0 rows affected (0.00 sec) mysql [binlog]>insert into t1 values(3); Query OK, 1 row affected (0.00 sec) mysql [binlog]>commit; Query OK, 0 rows affected (0.00 sec) mysql [binlog]>drop database binlog; Query OK, 1 row affected (0.12 sec) - 恢复数据
首先查看使用的哪个binlog
基于mysql-bin.000004进行分析，查看事件，找到建库事件起点378，drop之前终点1493mysql [(none)]>show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 1591 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
截取binlog日志中建库信息并导出到/tmp/bin.sqlmysql [(none)]>show binlog events in 'mysql-bin.000004'; +------------------+------+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000004 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 219 | Query | 6 | 313 | create database test | | mysql-bin.000004 | 313 | Anonymous_Gtid | 6 | 378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 378 | Query | 6 | 491 | create database binlog charset utf8 | | mysql-bin.000004 | 491 | Anonymous_Gtid | 6 | 556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 556 | Query | 6 | 657 | use `binlog`; create table t1(id int) | | mysql-bin.000004 | 657 | Anonymous_Gtid | 6 | 722 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 722 | Query | 6 | 796 | BEGIN | | mysql-bin.000004 | 796 | Table_map | 6 | 843 | table_id: 220 (binlog.t1) | | mysql-bin.000004 | 843 | Write_rows | 6 | 883 | table_id: 220 flags: STMT_END_F | | mysql-bin.000004 | 883 | Xid | 6 | 914 | COMMIT /* xid=22 */ | | mysql-bin.000004 | 914 | Anonymous_Gtid | 6 | 979 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 979 | Query | 6 | 1053 | BEGIN | | mysql-bin.000004 | 1053 | Table_map | 6 | 1100 | table_id: 220 (binlog.t1) | | mysql-bin.000004 | 1100 | Write_rows | 6 | 1140 | table_id: 220 flags: STMT_END_F | | mysql-bin.000004 | 1140 | Xid | 6 | 1171 | COMMIT /* xid=24 */ | | mysql-bin.000004 | 1171 | Anonymous_Gtid | 6 | 1236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1236 | Query | 6 | 1310 | BEGIN | | mysql-bin.000004 | 1310 | Table_map | 6 | 1357 | table_id: 220 (binlog.t1) | | mysql-bin.000004 | 1357 | Write_rows | 6 | 1397 | table_id: 220 flags: STMT_END_F | | mysql-bin.000004 | 1397 | Xid | 6 | 1428 | COMMIT /* xid=26 */ | | mysql-bin.000004 | 1428 | Anonymous_Gtid | 6 | 1493 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1493 | Query | 6 | 1591 | drop database binlog | +------------------+------+----------------+-----------+-------------+---------------------------------------+ 25 rows in set (0.11 sec)
由于恢复过程中也会产生二进制信息会造成额外资源占用，因此可以临时关闭该窗口的二进制记录。mysqlbinlog --start-position=378 --stop-position=1493 /data/binlog/mysql-bin.000004 >/tmp/bin.sqlmysql [(none)]>set sql_Log_bin=0; mysql [(none)]>source /tmp/bin.sql
案例：恢复业务中其中一个库
案例：
- 备份策略每天全备，有全量的二进制日志；
- 业务中一共10个库，其中一个被误drop；
- 需求：在其他9个库正常工作过程中进行数据恢复。
分析：
- 数据量小：使用测试库将全备恢复，然后将故障点之前日志追加到数据库，再将该库导入到原生产环境；
- 数据量大：从备份中将单独的库截取出来（考验sed功力），再使用mysqlbinlog -d把该库的日志截出，而后导入到原生产环境。
binlog新特性GTID
GTID(Global Transaction ID)
GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
- 5.6 版本新加的特性，5.7中做了加强
- 5.6 中不开启，没有这个功能.
- 5.7 中的GTID，即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’
GTID重要参数
编辑配置文件打开GTID功能并重启MySQL服务
[root@localhost ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
[root@localhost ~]# systemctl restart mysqld
创建一个库gtid，可以发现gtid前端部分为server_uuid（2f315686-1c25-11ec-8499-000c29dff3fd），:1代表这个第一个事务
mysql [(none)]>create database gtid charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql [(none)]>show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 | 326 | | | 2f315686-1c25-11ec-8499-000c29dff3fd:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
server_uuid查看方法（server_uuid对每个实例都是唯一的，要保证每个数据库之间的server_uuid是不同的）。
- select @@server_uuid;
mysql [gtid]>select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 2f315686-1c25-11ec-8499-000c29dff3fd | +--------------------------------------+ 1 row in set (0.00 sec) - 数据目录下的auto.cnf文件
[root@localhost ~]# cat /data/mysql/auto.cnf [auto] server-uuid=2f315686-1c25-11ec-8499-000c29dff3fd
创建一个表t1，发现此时的gtid后半段变为：1-2，表示此时有两个事务
mysql [(none)]>use gtid
Database changed
mysql [gtid]>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
mysql [gtid]>show master statusl
-> ^C
mysql [gtid]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 488 | | | 2f315686-1c25-11ec-8499-000c29dff3fd:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
begin；insert；commit在原始binlog代表三个事件，但在gtid中只表示为一个事务。
mysql [gtid]>begin;
Query OK, 0 rows affected (0.00 sec)
mysql [gtid]>insert into t1 values(1);
Query OK, 1 row affected (0.10 sec)
mysql [gtid]>commit;
Query OK, 0 rows affected (0.01 sec)
mysql [gtid]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 1067 | | | 2f315686-1c25-11ec-8499-000c29dff3fd:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
gtid给每个事务一个唯一的编号，因此根据gitd进行截取日志，就只需关注事务编号信息即可。
mysql [(none)]>mysql [(none)]>show binlog events in 'mysql-bin.000005';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000005 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:1' |
| mysql-bin.000005 | 219 | Query | 6 | 326 | create database gtid charset utf8 |
| mysql-bin.000005 | 326 | Gtid | 6 | 391 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:2' |
| mysql-bin.000005 | 391 | Query | 6 | 488 | use `gtid`; create table t1(id int) |
| mysql-bin.000005 | 488 | Gtid | 6 | 553 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:3' |
| mysql-bin.000005 | 553 | Query | 6 | 651 | use `gtid`; create table t2 (id int) |
| mysql-bin.000005 | 651 | Gtid | 6 | 716 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:4' |
| mysql-bin.000005 | 716 | Query | 6 | 814 | use `gtid`; create table t3 (id int) |
| mysql-bin.000005 | 814 | Gtid | 6 | 879 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:5' |
| mysql-bin.000005 | 879 | Query | 6 | 951 | BEGIN |
| mysql-bin.000005 | 951 | Table_map | 6 | 996 | table_id: 221 (gtid.t1) |
| mysql-bin.000005 | 996 | Write_rows | 6 | 1036 | table_id: 221 flags: STMT_END_F |
| mysql-bin.000005 | 1036 | Xid | 6 | 1067 | COMMIT /* xid=16 */ |
| mysql-bin.000005 | 1067 | Gtid | 6 | 1132 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:6' |
| mysql-bin.000005 | 1132 | Query | 6 | 1204 | BEGIN |
| mysql-bin.000005 | 1204 | Table_map | 6 | 1249 | table_id: 222 (gtid.t2) |
| mysql-bin.000005 | 1249 | Write_rows | 6 | 1289 | table_id: 222 flags: STMT_END_F |
| mysql-bin.000005 | 1289 | Xid | 6 | 1320 | COMMIT /* xid=20 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
20 rows in set (0.00 sec)
基于GTID截取binlog
具备GTID后,截取查看某些事务日志（对大binlog非常方便）:
–include-gtids
–exclude-gtids
例如：截取事务1-3
mysqlbinlog --include-gtids='2f315686-1c25-11ec-8499-000c29dff3fd:1-3' /data/binlog/mysql-bin.000005
GTID的幂等性
幂等性：保证主从复制一致性，避免重复性操作。
开启GTID后，MySQL恢复Binlog时，重复GTID的事务不会再执行。因此使用source恢复GTID截取出来的日志文件，是不会恢复成功的（因为日志中的操作已经有GTID）。
就想恢复？咋办？
–skip-gtids --在恢复时加入该参数，使其生成新的gtid号码
# mysqlbinlog --skip-gtids --include-gtids='2f315686-1c25-11ec-8499-000c29dff3fd:1-3' /data/binlog/mysql-bin.000005 > /tmp/binlog.sql
> set sql_log_bin=0;
> source /tmp/binlog.sql
> set sql_log_bin=1;
binlog其他操作
清理日志
自动清理
查看自动清理时间expire_logs_days
mysql [(none)]>show variables like "%expire%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON |
| expire_logs_days | 0 |
+--------------------------------+-------+
2 rows in set (0.16 sec)
- expire_logs_days 0 ：自动清理时间，0表示永不过期。建议设置为 全备周期+1
设置expire_logs_days参数：
-
临时设置
set global expire_logs_days=8; -
永久设置
vim my.cnf expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog。
手动清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO ‘mysql-bin.000010’;
注意：不要手动 rm binlog文件，如果rm删除binlog文件：
- my.cnf，binlog关闭掉,启动数据库
- 把数据库关闭，开启binlog，启动数据库
之后会删除所有binlog，并从000001开始重新记录日志。
注意：reset master;命令，在主从关系中，主库执行此操作，主从环境必崩！
日志滚动
-
flush logs; 触发日志滚动
-
重启mysql 触发日志滚动
-
日志文件达到1G大小(max_binlog_size)，触发日志滚动（建议更改小一点）
mysql [(none)]>select @@max_binlog_size; +-------------------+ | @@max_binlog_size | +-------------------+ | 1073741824 | +-------------------+ 1 row in set (0.00 sec) -
备份时，加入参数也可以自动滚动
案例：使用binlog恢复数据
故障环境介绍
创建数据库db，创建表t1，并在表中录入数据；
误删库：drop database db;
没有备份，日志都在，如何恢复？
- 思路：找到建库语句和删库之前的所有日志进行恢复。
模拟故障环境
mysql [(none)]>drop database if exists db ;
mysql [(none)]>create database db charset utf8;
mysql [(none)]>use db;
mysql [db]> create table t1 (id int);
mysql [db]>insert into t1 values(1),(2),(3),(4),(5),(6);
mysql [db]>commit;
mysql [db]>update t1 set id=30 where id=3;
mysql [db]>commit;
mysql [db]>delete from t1 where id=4; --误操作
mysql [db]>commit;
mysql [db]>insert into t1 values(7),(8),(9);
mysql [db]>commit;
mysql [db]>drop database db; --误操作
无GTID恢复
-
查看当前使用的binlog文件
mysql [(none)]>show master status; -
查看该binlog文件的事件
mysql [(none)]>show binlog events in 'mysql-bin.000001'; -
截取第一段数据日志（delete之前）
[root@localhost ~]# mysqlbinlog --start-position=375 --stop-position=1164 /data/binlog/mysql-bin.000001 > /tmp/bin1.sql -
截取第二段数据日志（delete之后）
[root@localhost ~]# mysqlbinlog --start-position=1478 --stop-position=1672 /data/binlog/mysql-bin.000001 > /tmp/bin2.sql -
恢复数据
mysql [(none)]> set sql_log_bin=0; mysql [(none)]> source /tmp/bin1.sql mysql [(none)]> source /tmp/bin2.sql mysql [(none)]> set sql_log_bin=1;
有GTID恢复
-
查看当前使用的binlog文件
mysql [(none)]>show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000001 | 1823 | | | 2f315686-1c25-11ec-8499-000c29dff3fd:1-22 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) -
查看该binlog文件的事件
mysql [(none)]>show binlog events in 'mysql-bin.000001'; +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000001 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:15' | | mysql-bin.000001 | 219 | Query | 6 | 310 | drop database if exists db | | mysql-bin.000001 | 310 | Gtid | 6 | 375 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:16' | | mysql-bin.000001 | 375 | Query | 6 | 476 | create database db charset utf8 | | mysql-bin.000001 | 476 | Gtid | 6 | 541 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:17' | | mysql-bin.000001 | 541 | Query | 6 | 635 | use `db`; create table t1 (id int) | | mysql-bin.000001 | 635 | Gtid | 6 | 700 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:18' | | mysql-bin.000001 | 700 | Query | 6 | 770 | BEGIN | | mysql-bin.000001 | 770 | Table_map | 6 | 813 | table_id: 224 (db.t1) | | mysql-bin.000001 | 813 | Write_rows | 6 | 878 | table_id: 224 flags: STMT_END_F | | mysql-bin.000001 | 878 | Xid | 6 | 909 | COMMIT /* xid=12 */ | | mysql-bin.000001 | 909 | Gtid | 6 | 974 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:19' | | mysql-bin.000001 | 974 | Query | 6 | 1044 | BEGIN | | mysql-bin.000001 | 1044 | Table_map | 6 | 1087 | table_id: 224 (db.t1) | | mysql-bin.000001 | 1087 | Update_rows | 6 | 1133 | table_id: 224 flags: STMT_END_F | | mysql-bin.000001 | 1133 | Xid | 6 | 1164 | COMMIT /* xid=14 */ | | mysql-bin.000001 | 1164 | Gtid | 6 | 1229 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:20' | | mysql-bin.000001 | 1229 | Query | 6 | 1299 | BEGIN | | mysql-bin.000001 | 1299 | Table_map | 6 | 1342 | table_id: 224 (db.t1) | | mysql-bin.000001 | 1342 | Delete_rows | 6 | 1382 | table_id: 224 flags: STMT_END_F | | mysql-bin.000001 | 1382 | Xid | 6 | 1413 | COMMIT /* xid=16 */ | | mysql-bin.000001 | 1413 | Gtid | 6 | 1478 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:21' | | mysql-bin.000001 | 1478 | Query | 6 | 1548 | BEGIN | | mysql-bin.000001 | 1548 | Table_map | 6 | 1591 | table_id: 224 (db.t1) | | mysql-bin.000001 | 1591 | Write_rows | 6 | 1641 | table_id: 224 flags: STMT_END_F | | mysql-bin.000001 | 1641 | Xid | 6 | 1672 | COMMIT /* xid=18 */ | | mysql-bin.000001 | 1672 | Gtid | 6 | 1737 | SET @@SESSION.GTID_NEXT= '2f315686-1c25-11ec-8499-000c29dff3fd:22' | | mysql-bin.000001 | 1737 | Query | 6 | 1823 | drop database db | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ 30 rows in set (0.00 sec) -
截取binlog日志
分析：截取15到21号事务，且20号事务为delete误操作，排除不要mysqlbinlog --skip-gtids --include-gtids='2f315686-1c25-11ec-8499-000c29dff3fd:15-21' --exclude-gtids='2f315686-1c25-11ec-8499-000c29dff3fd:20' /data/binlog/mysql-bin.000001 > /tmp/binlog.sql -
恢复数据
mysql [(none)]>set sql_log_bin=0; mysql [(none)]>source /tmp/binlog.sql
慢日志（slow_log）
slow_log作用
记录慢SQL语句的日志，是定位低效SQL语句的工具日志。可以通过慢日志，将影响数据库性能的语句查找出来。
slow_log开启
slow_log相关参数
-
slow_query_log参数，MySQL默认关闭
mysql [(none)]>select @@slow_query_log; +------------------+ | @@slow_query_log | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) -
慢日志文件位置及名字
mysql [(none)]>select @@slow_query_log_file; +--------------------------------+ | @@slow_query_log_file | +--------------------------------+ | /data/mysql/localhost-slow.log | +--------------------------------+ 1 row in set (0.00 sec) -
查看慢查询时间，默认10s，建议改小
mysql [(none)]>select @@long_query_time; +-------------------+ | @@long_query_time | +-------------------+ | 10.000000 | +-------------------+ 1 row in set (0.00 sec) -
记录没走索引的语句
mysql [(none)]>select @@log_queries_not_using_indexes; +---------------------------------+ | @@log_queries_not_using_indexes | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.00 sec)
slow_log设置实践
编辑MySQL配置文件并重启数据库服务
[root@localhost ~]# 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
[root@localhost ~]# systemctl restart mysqld
mysqldumpslow
mysqldumpslow -s c -t 10 /data/mysql/slow.log
-s：排序条件，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序
-t：返回前面多少条数据
1828
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
