MySQL 日志管理(上)

MySQL 日志管理(上)

1、错误日志作用

记录MySQL启动及工作过程中，状态、报错、警告。

2、设置错误日志

1)修改配置文件，并重启MySQL

配置错误日志

[root@localhost ~]# vim /etc/my.cnf
log_error=/data/3306/data/mysql.log #这里的路径和文件名称可以随便定义

重启MySQL生效

[root@localhost ~]# systemctl restart mysqld

2)查看错误日志

mysql> select @@log_error;
+---------------------------+
| @@log_error |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)

注意：查看错误日志关注[ERROR]的上下文.

MySQL 二进制日志

1、二进制日志作用

数据恢复必备的日志。

主从复制依赖的日志。

2、MySQL 二进制日志设置

1)修改配置文件

[root@localhost ~]# vim /etc/my.cnf
server_id=6
log_bin=/data/3306/binlog/mysql-bin

配置说明

server_id 是5.7之后开二进制日志必加的参数

log_bin= 打开二进制功能

/data/3306/binlog/ 指定存放路径

mysql-bin 文件名前缀

2)创建目录并授权

[root@localhost 3306]# mkdir -p /data/3306/binlog/
[root@localhost 3306]# chown -R mysql.mysql /data/3306/*

3)重启数据库

[root@localhost 3306]# systemctl restart mysqld
[root@localhost binlog]# ll binlog/
total 8
-rw-r----- 1 mysql mysql 768 Aug 14 20:02 mysql-bin.000001
-rw-r----- 1 mysql mysql 35 Aug 14 18:18 mysql-bin.index

配置说明

mysql-bin 是在配置文件配置的前缀

000001 MySQL每次重启，重新生成新的

3、二进制日志内容

除了查询类的语句，都会记录，即所有数据库变更类的语句。

1)记录语句的种类

DDL（数据定义语言）：create、drop

DCL（数据控制语言）：grant 、revoke

DML（数据操作语言）：insert、update、delete

2)不同语句的记录格式说明

DDL、DCL直接以语句（statement）方式记录 .

DML 语句有三种模式：SBR、RBR、MBR

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)

配置说明

statement---->SBR：做什么记录什么，即SQL语句

row---------->RBR：记录数据行的变化（默认模式，推荐）

mixed-------->MBR：自动判断记录模式

SBR和RBR的区别

二进制日志工作模式

1、配置二进制日志工作模式

1）修改配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
binlog_format='ROW'

2）重启数据库

[root@localhost ~]# systemctl restart mysqld

2、查看二进制日志工作模式

mysql> show variables like "binlog%";
+--------------------------------------------+--------------+
| Variable_name | Value |
+--------------------------------------------+--------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
+--------------------------------------------+--------------+
15 rows in set (0.00 sec)

3、二进制日志三种模式的区别

1）ROW: 基于行的复制

优点：所有的语句都可以复制，不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被 修改成什么了

缺点：binlog 大了很多，复杂的回滚时 binlog 中会包含大量的数据；主服务器上执行update语句时， 所有发生变化的记录都会写到 binlog 中；比如有这样一条update语句：update product set owner_member_id=‘d’ where owner_member_id=‘a’,执行之后，日志中记录的不是这条update语句 所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情 况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大。

2）Statement: 基于sql语句的复制

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能

缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每 条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能 够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入， 使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在 statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使 用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。

3）mixed模式 ：row 与 statement 结合

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待 记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一 样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以 row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是 update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

二进制日志事件

1、二进制日志事件简介

二进制日志内容以事件（binlog events）为最小记录单元。

对于DDL和DCL，一个语句就是一个事件。

对于DML（标准的事务语句），只记录已提交的事务的DML语句

begin ; 事件1
a 事件2
b 事件3
commit; 事件4

2、事件的构成（为了截取日志）

[root@localhost binlog]# mysqlbinlog mysql-bin.000001
# at 219 事件开始的位置(position)
end_log_pos 319 事件结束的位置(position)
#200219 14:28:12 事件发生的时间
create database qfedu 事件内容

3、二进制日志的基础查看

1）查看二进制日志的配置信息

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/log/mysql/mysql-bin |
| log_bin_index | /var/log/mysql/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.00 sec)

字段说明

log_bin 开启二进制日志的开关

log_bin_basename 位置

sql_log_bin 临时开启或关闭二进制日志的小开关

2）查看二进制日志的基本信息

打印出当前MySQL的所有二进制日志，并且显示最后使用到的 position

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 316 |
+------------------+-----------+
1 row in set (0.00 sec)

查看当前正在使用的二进制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 316 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> show master status;（常用）
+------------------+----------+--------------+------------------+---------------
----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------
----+
| mysql-bin.000001 | 316 | | |
|
+------------------+----------+--------------+------------------+---------------
----+
1 row in set (0.00 sec)

查看二进制日志的事件信息

mysql> show master status;
+------------------+----------+--------------+------------------+---------------
----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------
----+
| mysql-bin.000001 | 316 | | |
|
+------------------+----------+--------------+------------------+---------------
----+
1 row in set (0.00 sec)
mysql> 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.29-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 |
|
| mysql-bin.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 6 | 316 | create
database qfedu |
+------------------+-----+----------------+-----------+-------------+-----------
----------------------------+
4 rows in set (0.00 sec)

4、二进制日志内容的查看和截取

1)内容查看命令

[root@localhost ~]# mysqlbinlog /data/3306/binlog/mysql-bin.000001
[root@localhost ~]# mysqlbinlog --base64-output=decode-rows -vvv
/data/3306/binlog/mysql-bin.000001

记不住参数可以去mysqlbinlog --help中查看

2)日志的截取

截取语法:

–start-position 开始截取 pod 点

–stop-position 结束截取 pod 点

[root@localhost ~]# mysqlbinlog --start-position=xxx --stop-position=xxx
/data/3306/binlog/mysql-bin.000001 >/data/bin.sql

3)数据恢复实例

准备数据

mysql> create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use binlog;
Database changed
mysql> create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> update t1 set id=10 where id>10;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |
| 10 |
+------+
6 rows in set (0.00 sec)

删除数据

mysql> drop database binlog;
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.000001 | 1610 | | |
|
+------------------+----------+--------------+------------------+---------------
----+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
起点：
| mysql-bin.000001 | 381 | Query | 6 | 497 | create
database binlog charset utf8mb4 |
终点：
| mysql-bin.000001 | 1575 | Query | 6 | 1673 | drop
database binlog

截取日志

[root@localhost ~]# mysqlbinlog --start-position=381 --stop-position=1575 /data/3306/binlog/mysql-bin.000001>/data/bin.sql

恢复日志

mysql> set sql_log_bin=0; # 临时关闭当前会话的binlog记录
mysql> source /data/bin.sql;
mysql> set sql_log_bin=1; # 打开当前会话的binlog