MySQL Binlog二进制日志配置管理(逻辑层)

二进制日志:(逻辑层)
# 二进制日志到底记录了什么?
    已提交的数据记录,以event(事件)的形式记录到二进制文件中,所有的DDL、DCL、DML(insert、update、delete)

# 二进制日志记录格式有哪些?
    statement:  语句模式(就是记录执行的SQL语句),DDL、DCL只能以语句模式记录。
    row:        数据行模式(就是数据行的变化过程),只针对DML语句有效。
    mixed:      以上两者的混合模式

# binlog的作用?
    备份恢复,数据复制
    只要有全量的binlog文件,可以将数据库恢复到任意时间点

# row与statement优缺点?
    1、语句模式在批量操作时,日志量更少
    2、row模式记录数据更加严谨
    3、mixed不用
查看默认日志格式:
#5.7版本默认以ROW数据行格式记录
mysql> show variables like '%format%';
+---------------------------+-------------------+
| Variable_name             | Value             |
+---------------------------+-------------------+
| binlog_format             | ROW               |
| date_format               | %Y-%m-%d          |
| datetime_format           | %Y-%m-%d %H:%i:%s |
| default_week_format       | 0                 |
| innodb_default_row_format | dynamic           |
| innodb_file_format        | Barracuda         |
| innodb_file_format_check  | ON                |
| innodb_file_format_max    | Barracuda         |
| time_format               | %H:%i:%s          |
+---------------------------+-------------------+

重点:

二进制日志慢日志

配置错误日志:
#配置文件内指定错误日志存放路径(默认存放在数据路径下)
log_error=/var/log/mysql.log
日志配置参数:(骚操作)
  • log-bin=[base_name] :该参数表示是否开启binary log。默认情况下MySQL会使用host_name-bin.xxxx作为文件的名字,其中xxxx是以数字递增的后缀。如果该参数指定了 base_name,则二进制文件会以base_name.xxxx来命名。

  • binlog-do-db=db_name: 该参数决定了哪些库下的修改会被记录到bin log中其行为与replicate-do-db类型,在基于SQL语句复制的环境下,只记录在当前数据库下的修改。

  • binlog-ignore-db=db_name:该参数决定了在bin log中忽略的数据库,其行为与 replicate-ignore-db类型。

开启MySQL二进制日志:

mkdir /usr/local/mysql/blog/ && chown mysql.mysql /usr/local/mysql/blog/

#通过配置文件开启binlog,并指定以row类型记录,启动binlog功能就要加入server_id,这是5.7版本的bug。(mysql-bin是日志的前缀名)
vim /usr/local/mysql/conf/my.cnf

log_bin=/usr/local/mysql/blog/mysql-bin        指定binlog日志存放路径及文件名前缀
binlog_format=row                              指定以row类型记录
sync_binlog=1                                  每次commit时都会刷新日志到磁盘
server_id=1

systemctl restart mysqld

binlog查询:

前154个位置是二进制文件默认大小

#查询二进制存放的位置,或者通过配置文件查看
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /usr/local/mysql/binlog/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
+---------------------------------+-----------------------------------------+
查看使用的binlog文件:
#可以刷新二进制文件,类似于nginx日志的切割
flush logs;
#这个命令可以看到一共有多少二进制日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
#这个命令可以看到二进制日志文件,并且可显示正在写入的二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
events二进制日志事件:
对于DDL\DCL来讲,一条语句就是一个事件
对于DML来讲,是一个事务有多个事件,一个事务中有多个语句

每一个event,都有一个开始位置(position),结束位置(position)
查看binlog的事件信息:
#可以看到有两个事件
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    |         0 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |         0 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
测试binlog记录:
#创建一个good的库并创建一张表,然后查询二进制文件,查询类语句不会记录
mysql> create database good charset utf8;
mysql> use good;
mysql> create table t1 (id int)engine=innodb;

#然后查看二进制文件内容,发现多出来四条新记录,记录的第二条与第四条操作的是语句
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    |         0 |         123 | Server ver: 5.7.22-log, Binlog ver: 4             |
| mysql-bin.000002 | 123 | Previous_gtids |         0 |         154 |                                                   |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         0 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'              |
| mysql-bin.000002 | 219 | Query          |         0 |         326 | create database good charset utf8                 |
| mysql-bin.000002 | 326 | Anonymous_Gtid |         0 |         391 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'              |
| mysql-bin.000002 | 391 | Query          |         0 |         502 | use `good`; create table t1 (id int)engine=innodb |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------+
查看文件最大自动滚动大小值:
##### mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
binlog什么时候会滚动出一个新文件?
#刷新会自动滚动出新文件
flush logs;

#重启MySQL时会自动滚动新文件
systemctl restart mysqld

#达到max_binlog_size伐值时,会自动生成新文件
mysql> show variables like '%%max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)

#备份时,加入-F会自动滚动新文件
binlog文件保存的时间?
# 默认保留 30天的binlog日志文件
mysql> show variables like '%binlog_ex%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set (0.00 sec)
清空所有binlog文件:
#清空所有binlog文件,从000001开始重新记录,一般是在全量备份之后可以执行
reset master;
根据名称清理binlog文件:
  • 可以在 master中执行这条指令 , 将 binlog 文件删除至 mysql-bin.000009 前
purge master logs to 'mysql-bin.000009';
根据时间清理binlog文件:
  • 将 binlog文件删除至此日期前
purge master logs before '2014-11-18 00:00:00';
查看binlog详细内容:
#这是mysql的命令,不是SQL语句,后面跟上那个文件就看那个文件的详细内容
mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000002

#常用参数使用(类似于以人类可读形式显示)
mysqlbinlog --base64-output=decode-rows -vvv /usr/local/mysql/binlog/mysql-bin.000002

#指定查看olda库详细的binlog日志语句
mysqlbinlog --base64-output=decode-rows -vvv -d olda /usr/local/mysql/binlog/mysql-bin.000003
查看relay-log详细内容:
mysqlbinlog -vvv /usr/local/mysql-8.0.16/data/olda-study0-relay-bin.000002 
「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论
高级运维 中级运维 运维简历 运维简历 DB简历
加入我们
  • 站长QQ:885097398 一键联系
  • 扫一扫加站长QQ
    Linux运维交流群