日志管理
日志介绍
图片.png
错误日志
3306 [(none)]>select @@log_error; 日志查询
+-------------+
| @@log_error |
+-------------+
| stderr |
+-------------+
show variables like 'log_error';
vim /etc/my.cnf
log_error=/data/mysql/mysql.log
log_timestamps=system ## 修改时区 默认UTC 不是中国时区
日志中 查看ERROR类型
二进制日志
(1)备份恢复必须依赖二进制日志
(2)主从环境必须依赖二进制日志
注意：MySQL默认是没有开启二进制日志的。
基础参数查看:
开关:
[(none)]>select @@log_bin;
日志路径及名字
[(none)]>select @@log_bin_basename;
服务ID号:
[(none)]>select @@server_id;
二进制日志格式:
[(none)]>select @@binlog_format; ##默认ROW
双一标准之二:
[(none)]>select @@sync_binlog;
必须设置
log_bin=1
log_bin_basename=/data/mysql/mysql-bin ##建议跟数据目录分开
server_id=6 ##5.7以后版本必须 之前如果不是主从模式 不需设置
binlog_format=row ##记录日志的格式
简化设置 只有5.7以后可以
log_bin=/data/binlog/mysql-bin
mkdir /data/binlog
chown -R mysql.mysql /data/binlog
systemctl restart mysqld
记录语句
DDL ：原封不动的记录当前DDL(statement语句方式)。 数据库增删改 原封不动记录语句
DCL ：原封不动的记录当前DCL(statement语句方式)。增删改授权用户角色 原封不动记录语句
DML ：只记录已经提交的事务DML
binlog_format（binlog的记录格式）参数影响
（1）statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。
（2）ROW(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
（3）mixed（混合）MBR(mixed based replication)模式 ：以上两种模式的混合
SBR与RBR模式的对比
STATEMENT：可读性较高，日志量少，但是不够严谨
ROW ：可读性很低，日志量大，足够严谨
update t1 set xxx=xxx where id>1000 ? -->一共500w行，row模式怎么记录的日志
为什么row模式严谨？
id name intime
insert into t1 values(1,'zs',now())
我们建议使用：row记录模式
事件
二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
事件组成
三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用是什么？
为了方便我们截取事件
查询二进制日志
3306 [(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 |
+---------------------------------+------------------------------+
3306 [(none)]>show binary logs; ##查看二进制日志点
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
3306 [(none)]>flush logs; ##刷新二进制日志 切割日志
3306 [(none)]>show master status; ##查询当前正在使用的
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
3306 [test]>show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 6 | 313 | create database test |
| mysql-bin.000002 | 313 | Anonymous_Gtid | 6 | 378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 378 | Query | 6 | 475 | use `test`; create table t1(id int) |
| mysql-bin.000002 | 475 | Anonymous_Gtid | 6 | 540 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 540 | Query | 6 | 612 | BEGIN |
| mysql-bin.000002 | 612 | Table_map | 6 | 657 | table_id: 108 (test.t1) |
| mysql-bin.000002 | 657 | Write_rows | 6 | 697 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 697 | Xid | 6 | 728 | COMMIT /* xid=25 */ |
| mysql-bin.000002 | 728 | Anonymous_Gtid | 6 | 793 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 793 | Query | 6 | 865 | BEGIN |
| mysql-bin.000002 | 865 | Table_map | 6 | 910 | table_id: 108 (test.t1) |
| mysql-bin.000002 | 910 | Write_rows | 6 | 950 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 950 | Xid | 6 | 981 | COMMIT /* xid=29 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
Log_name：binlog文件名
Pos：开始的position *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****
补充:
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
show binlog events in 'mysql-bin.000002' limit 5,5; ##从第5个开始 显示5个事件
show binlog events in 'mysql-bin.000002' from 154 limit 5; ##从154事件后面显示5个事件
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop 过滤关键字
mysqlbinlog mysql-bin.000002 ##查看大致信息
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002 ## 查看详细信息
mysqlbinlog -d test mysql-bin.000002 ##查看test库的信息
mysqlbinlog --start-datetime='2020-11-23 10:56:38' mysql-bin.000002 ##查看指定时间段之后的信息
mysqlbinlog --start-position=728 --stop-position=950 mysql-bin.000002 ##根据事件截取日志
案例
核心就是找截取的起点和终点
--start-position=321
--stop-position=513
mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
案例: 使用binlog日志进行数据恢复
模拟:
1.
[(none)]>create database binlog charset utf8;
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;
恢复:
[(none)]>show master status ;
[(none)]>show binlog events in 'mysql-bin.000002';
mysql-bin.000002 | 1299 | Query | 6 | 1412 | create database binlog charset utf8 |
| mysql-bin.000002 | 1412 | Anonymous_Gtid | 6 | 1477 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1477 | Query | 6 | 1578 | use `binlog`; create table t1(id int) |
| mysql-bin.000002 | 1578 | Anonymous_Gtid | 6 | 1643 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1643 | Query | 6 | 1717 | BEGIN |
| mysql-bin.000002 | 1717 | Table_map | 6 | 1764 | table_id: 109 (binlog.t1) |
| mysql-bin.000002 | 1764 | Write_rows | 6 | 1804 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000002 | 1804 | Xid | 6 | 1835 | COMMIT /* xid=118 */ |
| mysql-bin.000002 | 1835 | Anonymous_Gtid | 6 | 1900 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1900 | Query | 6 | 1974 | BEGIN |
| mysql-bin.000002 | 1974 | Table_map | 6 | 2021 | table_id: 109 (binlog.t1) |
| mysql-bin.000002 | 2021 | Write_rows | 6 | 2061 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000002 | 2061 | Xid | 6 | 2092 | COMMIT /* xid=122 */ |
| mysql-bin.000002 | 2092 | Anonymous_Gtid | 6 | 2157 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 2157 | Query | 6 | 2231 | BEGIN |
| mysql-bin.000002 | 2231 | Table_map | 6 | 2278 | table_id: 109 (binlog.t1) |
| mysql-bin.000002 | 2278 | Write_rows | 6 | 2318 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000002 | 2318 | Xid | 6 | 2349 | COMMIT /* xid=125 */ |
| mysql-bin.000002 | 2349 | Anonymous_Gtid | 6 | 2414 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 2414 | Query | 6 | 2512 | drop database binlog
[root@db01 binlog]#mysqlbinlog --start-position=1234 --stop-position=2414 mysql-bin.000002 >/tmp/bin.sql
select @@sql_log_bin
[(none)]>set sql_log_bin=0; ##关闭二进制日志记录
[(none)]>source /tmp/bin.sql
面试案例:
1. 备份策略每天全备,有全量的二进制日志
2.业务中一共10个库,其中一个被误drop了
3. 需要在其他9个库正常工作过程中进行数据恢复
gtid
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
日志中 有 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
开启命令
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true ##强制gtid的一致性
systemctl restart mysqld
3306 [(none)]>system cat /data/mysql/auto.cnf ##uuid的自动生成
[auto]
server-uuid=735827a9-2008-11eb-a242-5254001adc46
3306 [aaa]>show binlog events in 'mysql-bin.000003'; 出现uuid
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:1' |
| mysql-bin.000003 | 219 | Query | 6 | 310 | create database aaa |
| mysql-bin.000003 | 310 | Gtid | 6 | 375 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:2' |
| mysql-bin.000003 | 375 | Query | 6 | 470 | use `aaa`; create table t1(id int) |
| mysql-bin.000003 | 470 | Gtid | 6 | 535 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:3' |
| mysql-bin.000003 | 535 | Query | 6 | 606 | BEGIN |
| mysql-bin.000003 | 606 | Table_map | 6 | 650 | table_id: 108 (aaa.t1) |
| mysql-bin.000003 | 650 | Write_rows | 6 | 690 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000003 | 690 | Table_map | 6 | 734 | table_id: 108 (aaa.t1) |
| mysql-bin.000003 | 734 | Write_rows | 6 | 774 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000003 | 774 | Xid | 6 | 805 | COMMIT /* xid=13 */
如果删除 数据库aaa后 还原
mysqlbinlog --skip-gtids --include-gtids='735827a9-2008-11eb-a242-5254001adc46:1-3' mysql-bin.000003 >/tmp/aaa.sql
或者
mysqlbinlog --include-gtids='735827a9-2008-11eb-a242-5254001adc46:1-4' --exclude-gtids='735827a9-2008-11eb-a242-5254001adc46:4' /data/binlog/mysql-bin.000003 >/tmp/aaa.sql
set sql_log_bin=0;
source /tmp/aaa.sql
mysqlbinlog --skip-gtids --include-gtids='735827a9-2008-11eb-a242-5254001adc46:1-3' mysql-bin.000003 >/tmp/aaa.sql
mysqlbinlog --include-gtids='735827a9-2008-11eb-a242-5254001adc46:1-3' mysql-bin.000003 >/tmp/aaa1.sql
两个文件对比 只是将uuid那行 注释掉了
案例
create database uuu;
create table t1(id int);
insert into t1 values (1),(2),(3);
commit;
delete from t1 where id=2;
commit;
insert into t1 values (5),(6),(7);
commit;
恢复 id=2的数据
3306 [uuu]>show binlog events in 'mysql-bin.000003';
mysql-bin.000003 | 959 | Gtid | 6 | 1024 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:5' |
| mysql-bin.000003 | 1024 | Query | 6 | 1115 | create database uuu |
| mysql-bin.000003 | 1115 | Gtid | 6 | 1180 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:6' |
| mysql-bin.000003 | 1180 | Query | 6 | 1275 | use `uuu`; create table t1(id int) |
| mysql-bin.000003 | 1275 | Gtid | 6 | 1340 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:7' |
| mysql-bin.000003 | 1340 | Query | 6 | 1411 | BEGIN |
| mysql-bin.000003 | 1411 | Table_map | 6 | 1455 | table_id: 111 (uuu.t1) |
| mysql-bin.000003 | 1455 | Write_rows | 6 | 1505 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000003 | 1505 | Xid | 6 | 1536 | COMMIT /* xid=99 */ |
| mysql-bin.000003 | 1536 | Gtid | 6 | 1601 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:8' |
| mysql-bin.000003 | 1601 | Query | 6 | 1672 | BEGIN |
| mysql-bin.000003 | 1672 | Table_map | 6 | 1716 | table_id: 111 (uuu.t1) |
| mysql-bin.000003 | 1716 | Delete_rows | 6 | 1756 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000003 | 1756 | Xid | 6 | 1787 | COMMIT /* xid=104 */ |
| mysql-bin.000003 | 1787 | Gtid | 6 | 1852 | SET @@SESSION.GTID_NEXT= '735827a9-2008-11eb-a242-5254001adc46:9' |
| mysql-bin.000003 | 1852 | Query | 6 | 1923 | BEGIN |
| mysql-bin.000003 | 1923 | Table_map | 6 | 1967 | table_id: 111 (uuu.t1) |
| mysql-bin.000003 | 1967 | Write_rows | 6 | 2017 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000003 | 2017 | Xid | 6 | 2048 | COMMIT /* xid=106 */
导出配置
mysqlbinlog --skip-gtids --include-gtids='735827a9-2008-11eb-a242-5254001adc46:5-9' --exclude-gtids='735827a9-2008-11eb-a242-5254001adc46:8' mysql-bin.000003 > /tmp/uuu.sql
set sql_log_bin=0;
source /tmp/uuu.sql
GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
就想恢复?怎么办?
--skip-gtids
二进制日志其他操作
自动清理
show variables like '%expire%';
expire_logs_days 0
自动清理时间,是要按照全备周期+1
set global expire_logs_days=8;
永久生效:
my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog
手动删除 主从复制慎用
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; ##删除3天之前的
PURGE BINARY LOGS TO 'mysql-bin.000010'; ##删除10号之前的所有日志 但是不保存10号
注意:不要手工 rm binlog文件
1. my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
reset mater； 将日志 从000001开始 重新开始 主从复制 使用必崩 要重新搭建主从
flush log ； 或者 重启mysqld 切割日志 默认1G 自动切割
3306 [uuu]>select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 | 1024M 1G 切割
+-------------------+
慢日志
记录慢SQL语句的日志,定位低效SQL语句的工具日志
3306 [uuu]>select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
操作开关:
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/mysql/slow.log
设定慢查询时间:
long_query_time=0.1
没走索引的语句也记录:
log_queries_not_using_indexes
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
systemctl restart mysqld
分析
mysqldumpslow -s c -t 10 /data/mysql/slow.log
-s 排序条件
-c 次数
-t 10 top10 前10条
[root@db01 binlog]# mysqldumpslow -s c -t 3 /data/mysql/slow.log
Reading mysql slow query log from /data/mysql/slow.log
Count: 1 Time=9.24s (9s) Lock=0.00s (0s) Rows=1497420.0 (1497420), root[root]@localhost
select * from t_100w
Count: 1 Time=411.72s (411s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
CALL rand_data(N)
Count: 1 Time=1.58s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select * from t_100w where id=N
# 第三方工具(自己扩展)
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
toolkit工具包中的命令:
./pt-query-diagest /data/mysql/slow.log
Anemometer基于pt-query-digest将MySQL慢查询可视化
wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum install -y percona-toolkit-3.2.1-1.el7.x86_64.rpm
pt-query-digest /data/mysql/slow.log
使用Anemometer基于pt-query-diges
网友评论