目录
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.sql
mysql [(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:返回前面多少条数据