MySQL日志管理之二进制日志

MySQL二进制日志的概念：

二进制日志采用二进制格式（01）来存储日志信息（导致数据发生变化的SQL语句），二进制日志记录整个数据库的修改过程。
MySQL的二进制日志主要就是起一个备份的功能。二进制日志≈备份

MySQL二进制日志的作用：

备份功能：通过“重放”功能生成数据的副本。

MySQL二进制日志的格式：

有三种格式，分别是：

基于“语句”记录：statement，记录语句，日志量较少，节约空间，但记录信息不详细。
基于“行”记录：row，记录数据，生成的日志量较大，但是安全性高，（建议使用的格式）。-
混合模式：mixed, 让系统自行判定该基于哪种方式进行

例如：

查看当前MySQL默认使用的日志格式:


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

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

设置二进制日志的格式

#设置二进制日志的格式：
可以用过变量或者在配置文件中指定
binlog_format=STATEMENT|ROW|MIXED

MySQL二进制日志的构成:

MySQL的二进制日志由两类文件构成，分别是：

日志文件：属于二进制格式：mysql|mariadb-bin.文件名后缀
如： on.000001,mariadb-bin.000002
索引文件：属于文本格式，mysql|mariadb-bin.index，记录当前已有的二进制日志文件列表

- sql_log_bin=ON|OFF： #是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项

- log_bin=/PATH/BIN_LOG_FILE： #指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以

- binlog_format=STATEMENT|ROW|MIXED： #二进制日志记录的格式，mariadb5.5默认STATEMENT

- max_binlog_size=1073741824： #单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值

- binlog_cache_size=4m  #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）


- max_binlog_cache_size=512m  #限制用于缓存多事务查询的字节大小。

- sync_binlog=1|0： #设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

- expire_logs_days=N： #二进制日志可以自动删除的天数。 默认为0，即不自动删除

MySQL二进制日志的管理：

查看是否开启二进制日志：

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

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
#sql_log_bin默认都是开启的

#如果只开启了sql_log_bin而没有开启log_bin，那么二进制日志这个功能也是没法开启的。

bin_log和sql_bin_log的区别

log-bin和sql_log_bin都能影响二进制日志的开启和关闭（两个都需要开启才能开启二进制日志功能）。

log-bin和sql_log_bin功能并非一样的。

sql_log_bin：是一个可以动态修改的变量（session），只能临时修改

log_bin:表示服务器选项和服务器变量（两个同名）
链接：https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html

服务器变量（log_bin）：一个全局变量，不支持动态修改，只能查看。用于查看log_bin是否开启
服务器选项(log_bin)：log_bin=path/name
功能：log_bin：表示开启二进制日志，生成的二进制日志会默认放到mysql存放数据的目录，且文件名为主机名-xxxx的形式
log_bin=path/name:表示开启二进制功能并且指定二进制日志的存放位置和文件名前缀。

例如：开启二进制日志：

[root@CentOS8 ~]# cat /etc/my.cnf | egrep  -v '^#.*'

[mysqld]
log-bin=mysql-bin  #开启了二进制日志并指名了二进制文件的前缀名（建议把二进制日志放在单独位置）
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-grant-tables
skip-networking

注意：

mysql5.6或者8.0开始二进制日志的时候只需要只当log_bin这个服务选项就行了
MYSQL5.7只设置了全局系统变量log_bin，没有设置全局系统变量server_id的话，那么MySQL启动不了，而且错误日志没有任何错误信息。

查看二进制日志：

查看目前存在的二进制日志：SHOW {BINARY | MASTER} LOGS
查看正在使用的二进制日志：show master status；
查看指定二进制日志中的内容：SHOW BINLOG EVENTS [IN 'log_name']

例如：

查看目前系统中所用的二进制文件

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

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

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> SHOW BINLOG EVENTS in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 219 | Query          |         1 |         294 | BEGIN                                 |
| mysql-bin.000001 | 294 | Table_map      |         1 |         355 | table_id: 104 (hellodb.teachers)      |
| mysql-bin.000001 | 355 | Write_rows     |         1 |         400 | table_id: 104 flags: STMT_END_F       |
| mysql-bin.000001 | 400 | Xid            |         1 |         431 | COMMIT /* xid=53 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)

删除二进制文件

删除指定的二进制日志文件：
删除所有的二进制文件

删除指定二进制文件

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
TO 'log_name':清除这个日志之前的日志
BEFORE datetime_expr：根据事件来清理

删除mysql-bin.000002之前的二进制日志文件
mysql> PURGE master logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       154 |
+------------------+-----------+
9 rows in set (0.00 sec)

删除所有二进制文件，只留下一个数字后缀为 .000001 的空二进制日志文件

RESET MASTER [TO num];
#删除所有二进制日志文件，并重新生成日志文件，
文件名从num开始记数，默认从1开始.

一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #

例如：清空所有二进制文件

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       154 |
+------------------+-----------+
9 rows in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

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

刷新二进制日志文件：

FLUSH LOGS; 触发二进制日志的重新生成

例如：生成新的二进制日志文件

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

mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

二进制日志的客户端命令工具：mysqlbinlog

mysqlbinlog的使用选项：

mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime= 
 --base64-output[=name]
        -v -vvv

[root@CentOS8 ~]# mysqlbinlog /data/logbin/mysql-bin.000002   -v
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
BINLOG '
J1gnYxMBAAAAPQAAAGMBAAAAAGgAAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEI
fgFOQg==
J1gnYx4BAAAALgAAAJEBAAAAAGgAAAAAAAEAAgAE//AHAAQAdG9tMRsC1e5W1Q==
'/*!*/;
### INSERT INTO `hellodb`.`teachers`
### SET
###   @1=7
###   @2='tom1'
###   @3=27
###   @4=2
# at 401
......

生成新的二进制日志文件：

1.单个日志文件大小达到了设定的限定值
2.重启MySQL服务也会生成新的二进制日志文件
3.执行flush logs命令刷新二进制日志

posted on 2022-09-19 00:33 背对背依靠阅读(319) 评论(0) 编辑收藏举报

刷新页面返回顶部

背对背依靠