MySQL学习【第12篇】MySQL日志管理

一、MySQL日志简介

日志文件	选项	文件名/表名称	程序
错误	--log-error	host_name.err	N/A
常规	--general_log	host_name.log general_log	N/A
慢速查询	--slow_query_log --long_query_time	host_name-slow.log slow_log	mysqldumpslow
二进制	--log-bin --expire-logs-days	host_name-bin.000001	mysqlbinlog
审计	--audit_log --audit_log_file	audit.log	N/A

1、 错误日志

1）作用： 记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。

2）默认位置：

$MYSQL_HOME/data/

3）开启方式:（MySQL安装完后默认开启)

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err

#查看方式
mysql> show variables like 'log_error';

2、一般查询日志

1）作用： 记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。

2）默认位置：

$MYSQL_HOME/data/

3）开启方式:（MySQL安装完之后默认不开启）

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=on
general_log_file=/application/mysql/data/$hostnamel.log

#查看方式
mysql> show variables like '%gen%';

3、二进制日志

1）作用： 记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录 记录所有DDL、DCL等语句 总之，二进制日志会记录所有对数据库发生修改的操作

2）二进制日志模式: statement：语句模式（默认模式）； row：行模式，即数据行的变化过程； mixed：以上两者的混合模式。

企业推荐使用row模式

3）优缺点:

statement模式：

优点：简单明了，容易被看懂，就是sql语句，记录时不需要太多的磁盘空间。 缺点：记录不够严谨。

row模式：

优点：记录更加严谨。 缺点：有可能会需要更多的磁盘空间，不太容易被读懂。

4）binlog的作用

• 如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
• 数据的备份恢复
• 数据的复制

二、二进制日志管理实践

 1、开启二进制row模式

1）添加开启二进制配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin	#配置完成后路径不要修改，有坑
#binlog_format=row
#server_id=1		#注意:在mysql5.7中开启binlog必须要加上server-id

2）重启数据库
[root@db02 ~]# /etc/init.d/mysqld restart

3）开启binlog_format=row并重启数据库
[root@db02 ~]# ll /application/mysql/data/    #重启会新增一个binlog
-rw-rw---- 1 mysql mysql      120 Mar 27 03:49 mysql-bin.000002

2、二进制日志的操作

1）物理查看，会产生两个日志文件
[root@db02 ~]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql      120 Mar 27 03:22 mysql-bin.000001		
-rw-rw---- 1 mysql mysql       19 Mar 27 03:22 mysql-bin.index

2）命令行查看
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> show master status;	#查看binlog信息
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001';

 3、事件介绍

• 在binlog中最小的记录单元为event
• 一个事务会被拆分成多个事件（event）

4、事件（event）特性

• 每个event都有一个开始位置（start position）和结束位置（stop position）。
• 所谓的位置就是event对整个二进制的文件的相对位置。
• 对于一个二进制日志中，前120个position是文件格式信息预留空间。
• MySQL第一个记录的事件，都是从120开始的。

5、row模式下二进制日志分析及数据恢复

###statement模式###

1）创建一个binlog库
mysql> create database binlog;

2）使用binlog库
mysql> use binlog

3）创建binglog_table表
mysql> create table binlog_table(id int);

4）查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      331 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5）插入数3条数据
mysql> insert into binlog_table values(1),(2),(3);
mysql> select * from binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

6）查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      565 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#提交（如果开启自动提交则不需要输入）
mysql> commit;

7）查看binglog日志
[root@db02 ~]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001

=========================================================================================
###row（行级）模式###
1）创建一个binlog1库
mysql> create database binlog1;

2）使用binlog库
mysql> use binlog1

3）创建binglog_table表
mysql> create table binlog1(id int);

4）插入数3条数据
mysql> insert into binlog1 values(1),(2),(3);
mysql> select * from binlog1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

#提交（如果开启自动提交则不需要输入）
mysql> commit;

5）查看binglog日志
[root@db02 ~]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002

=========================================================================================
###删除数据库###

1）创建一个test库
mysql> create database binlog_test;

2）使用test库
mysql> use binlog_test

3）创建binglog_test表
mysql> create table binlog_test(id int);

4)插入数3条数据
mysql> insert into binlog_test values(1);
mysql> insert into binlog_test values(2);
mysql> insert into binlog_test values(3);

5)查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1408 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6）删除数据1
mysql> delete from binlog_test where id=1;

7）更改数据2为22
mysql> update binlog_test set id=22 where id=2;

8）查询当前数据
mysql> select * from binlog_test;
+------+
| id   |
+------+
|   22 |
|    3 |
+------+
2 rows in set (0.00 sec)

9）删表
mysql> drop table binlog_test;

10）删库
mysql> drop database binlog_test;

6、恢复数据到delete之前

1）查看binlog事件
mysql> show binlog events in 'mysql-bin.000002';
#或使用mysqlbinlog来查看
[root@db02 data]# pwd
/application/mysql/data
[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
或
[root@db02 data]# mysqlbinlog /application/mysql/data/mysql-bin.000002
[root@db02 data]# mysqlbinlog /application/mysql/data/mysql-bin.000002|grep -v SET

7、截取二进制日志

1）查看二进制日志后，发现delete语句开始位置是1487（不是新表找创表语句）
[root@db02 data]# mysqlbinlog --start-position=120 --stop-position=1408 /application/mysql/data/mysql-bin.000002 >/tmp/test_binlog.sql

2）临时关闭binlog
mysql> set sql_log_bin=0;

3）执行sql文件
mysql> source /tmp/test_binlog.sql

4）查看删除的库
mysql> show databases;

5）进binlog_test库
mysql> use binlog_test

6)查看删除的表
mysql> show tables;

7查看表中内容
mysql> select * from binlog_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

至此恢复完成！

 8、删除、刷新binlog

刷新binlog日志
1）flush logs;
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）

删除二进制日志
1）原则：在存储能力范围内，能多保留则多保留，基于上一次全备前的可以选择删除

=========================================================================================
1、根据存在时间删除日志
#临时生效
set global expire_logs_days = 7;

#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2、使用purge命令删除
purge binary logs before now() -interavl 3day;

3、根据文件名删除
purge binary logs to 'mysql-bin.000010';

4、使用reset master
mysql> reset master;