MySQL日志管理
错误日志
log_error
#作用
记录错误,警告,状态
#查看方式
默认: DATADIR/hostname.err
找日志[ERROR]的上下文
#自定义配置
[root@mysql data]# vim /etc/my.cnf
log_error=/tmp/mysql.log
重启生效
#查看数据库日志路径
mysql> select @@log_error;
二进制日志
作用:
SQL层日志,存储的是SQL语句,属于逻辑层日志.
#配置方法
[root@mysql data]# vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin
说明:
/data/binlog : 提前定制好的目录,而且要有mysql.mysql的权限
mysql-bin : 二进制日志文件名的前缀
binlog_format=row --->5.7版本默认配置是row,可以省略
重启后生效
二进制文件记录内容
记录的数据库所有变更类的操作日志
DDL
DCL
DML
#DDL和DCL
直接以SQL语句的方式来记录.
#DML
记录的是已提交的事务DML语句
DML记录格式(statement,row,mixed),通过binlog_format=row参数控制
statement(SBR):以语句模式记录日志,做说明命令,记录什么命令
row(RBR) :行模式,记录数据行的变化
mixed(MBR) :混合模式
二进制日志记录单元
#event事件
二进制日志的最小单元
DDL :
create database oldguo; 事件1
对于DDL等语句是每一个语句就是一个事件
DML: 一个事务包含了多个语句
begin; 事件1
a 事件2
b 事件3
commit; 事件4
#event事件的开始和结束号码
作用,方便我们从日志中截取我们想要的日志事件.
二进制日志的管理
#查看二进制日志位置
mysql> show variables like '%log_bin%';
#查看所有已存在的二进制日志
mysql> show binary logs;
mysql> flush logs;#滚动一个新的日志
#查看正在使用的二进制日志
mysql> show master status;
#查看二进制日志事件
mysql> create database binlog charset utf8mb4;
mysql> use binlog
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 501 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> 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.26-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 | 335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid | 6 | 400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 400 | Query | 6 | 501 | use `binlog`; create table t1(id int) |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
6 rows in set (0.00 sec)
mysql> commit;
mysql> 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.26-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 | 335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid | 6 | 400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 400 | Query | 6 | 501 | use `binlog`; create table t1(id int) |
| mysql-bin.000002 | 501 | Anonymous_Gtid | 6 | 566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 566 | Query | 6 | 640 | BEGIN |
| mysql-bin.000002 | 640 | Table_map | 6 | 687 | table_id: 108 (binlog.t1) |
| mysql-bin.000002 | 687 | Write_rows | 6 | 727 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 727 | Xid | 6 | 758 | COMMIT /* xid=14 */ |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
11 rows in set (0.00 sec)
mysql>
#查看二进制日志内容
[root@mysql binlog]# mysqlbinlog /data/binlog/mysql-bin.000002
#翻译数据行
[root@mysql binlog]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000002
#截取二进制日志
[root@mysql binlog]# mysqlbinlog --start-position=219 --stop-position=335 /data/binlog/mysql-bin.000002
#单独过滤某个库的二进制日志
[root@mysql binlog]# mysqlbinlog -d ceshi mysql-bin.000002
#日志的删除
(1) 自动清理
mysql> select @@expire_logs_days;
(2) 手工清理
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'
(3) 全部清空
reset master;
通过二进制日志恢复数据
#模拟数据
mysql> create database ceshi charset utf8mb4;
mysql> use ceshi
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql>
#模拟故障
mysql> drop database ceshi;
#分析和截取binlog
mysql> show master status; --确认使用的是哪一个日志
mysql> show binlog events in 'mysql-bin.000002'; --查看事件
#找到启点和终点进行恢复
[root@mysql binlog]# mysqlbinlog --start-position=823 --stop-position=1420 /data/binlog/mysql-bin.000002 > /mnt/bin.sql
#恢复binlog
mysql> set sql_log_bin=0; --临时关闭恢复时产生的新日志
mysql> source /mnt/bin.sql
mysql> set sql_log_bin=1; --恢复日志
binlog的gtid记录模式的管理
GTID介绍
对于binlog中的每一个事务,都会生成一个GTID号码
DDL,DCL 一个event就是一个事务,就会有一个GTID号
DML语句来讲,begin到commit,是一个事务,就是一个GTID号
#GTID的组成
severi_uuid:TID
[root@mysql binlog]# cat /data/mysql/data/auto.cnf
[auto]
server-uuid=d980595d-c934-11e9-bbfc-000c29d70b6d
[root@mysql binlog]#
TID是一个:自增长的数据,从1开始
d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15
#GTID的幂等性
如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过
会影响到binlog恢复和主从复制.
GTID的开启和配置
[root@mysql binlog]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
[root@mysql binlog]# /etc/init.d/mysqld restart
[root@mysql binlog]# mysql -uroot -p123456
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> create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 | 329 | | | d980595d-c934-11e9-bbfc-000c29d70b6d:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> use gtid;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 744 | | | d980595d-c934-11e9-bbfc-000c29d70b6d:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> drop database gtid;
#基于GTID的binlog恢复
#截取日志
[root@mysql binlog]# cd /data/binlog/
[root@mysql binlog]# mysqlbinlog --skip-gtids --include-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:1-3' mysql-bin.000003 >/mnt/gtid.sql
--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
#恢复
mysql> set sql_log_bin=0;
mysql> source /mnt/gtid.sql;
mysql> set sql_log_bin=1;
#GTID相关参数
2.6.7 GTID相关的参数
#跳过GTID检查
--skip-gtids
#包含
--include-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:6','d980595d-c934-11e9-bbfc-000c29d70b6d:8'
#排除
--exclude-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:6','d980595d-c934-11e9-bbfc-000c29d70b6d:8'
慢日志
#作用
记录运行较慢的语句,优化过程中常用的工具日志
#配置
## 开关
slow_query_log=1
## 文件位置及名字
slow_query_log_file=/data/mysql/slow.log
## 设定慢查询时间
long_query_time=0.1
## 没走索引的语句也记录
log_queries_not_using_indexes
[root@mysql binlog]# 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@mysql binlog]# /etc/init.d/mysqld restart
#分析慢语句
[root@mysql ~]# cd /data/mysql/
[root@mysql mysql]# vim slow.log
[root@mysql mysql]# mysqldumpslow -s c -t 10 /data/mysql/slow.log