MySQL二进制日志系列

最新推荐文章于 2024-07-25 11:07:17 发布

AiDBA宝典

最新推荐文章于 2024-07-25 11:07:17 发布

阅读量530

点赞数

本文链接：https://blog.csdn.net/lihuarongaini/article/details/101299364

版权

MySQL二进制日志系列

总结

1、binary log记录的是已经提交commit 的各种DML和DDL语句，类似oracle的redo log(包含online redo log和archive redo log)中已经commit提交的数据

statement 格式的 binlog，最后会有 COMMIT；

row 格式的 binlog，最后会有一个 XID event；

2、binary log用于主从复制和基于时间点的恢复

3、在MySQL 5.7.3及其以后的版本中，若想开启二进制日志，除了log-bin参数外还必须加上server_id参数。否则会有如下报错

[ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id.

4、purge binary logs时，磁盘上的物理文件也一并删除了

5、binary log日志格式，可以简单认为就是执行过的事务中的sql语句。但又不完全是sql语句这么简单，而是执行的sql语句（增删改）及反向的信息，比如delete对应着delete本身和其反向的insert。使用mysqlbinlog解析binlog可以看到

6、binary log产生时间，事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中

7、binlog的保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

8、查看binary log的内容可以使用以下两种方式

mysqlbinlog mysql-bin.000002

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

mysql> show binlog events in 'mysql-bin.00002' from 504769752 limit 30,30;
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count       查询总条数(不指定就是所有行)

涉及参数

log_bin ：是否开启binary log

log_bin_basename ：binary log文件的存放路径和名称

log_bin_index ：binary log索引文件的存放路径和名称

expire_logs_days ：自动删除几天以前的binary log文件

innodb_page_size ：InnoDB表空间的页面大小，默认16K

sync_binlog ：MySQL服务器将二进制日志同步到磁盘的频率，即写日志的同时是否写入磁盘，此处写磁盘不是写表的数据文件，而是写mysql-bin.00000X日志文件

By default is set to 0, meaning that there is no delay

When sync_binlog is set to a value n greater than 1, the delay is applied after every n binary log commit groups.

binlog_group_commit_sync_delay ：二进制日志提交后需要等待多少微秒才能把二进制日志文件同步到磁盘，5.7.5默认是0，表示立即写

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_binary_log

binary log

A file containing a record of all statements that attempt to change table data. These statements can be replayed to bring slave servers up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although Oracle recommends always enabling it if you use replication or perform backups.

You can examine the contents of the binary log, or replay those statements during replication or recovery, by using the mysqlbinlog command. For full information about the binary log, see Section 5.4.4, “The Binary Log”. For MySQL configuration options related to the binary log, see Section 16.1.6.4, “Binary Logging Options and Variables”.

For the MySQL Enterprise Backup product, the file name of the binary log and the current position within the file are important details. To record this information for the master server when taking a backup in a replication context, you can specify the --slave-info option.

Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.

包含尝试更改表数据的所有语句的记录的文件。可以重播这些语句，以便在复制方案中使从属服务器保持最新，或者在从备份还原表数据后使数据库保持最新。可以打开和关闭二进制日志记录功能，但Oracle建议在使用复制或执行备份时始终启用它。

您可以使用mysqlbinlog命令检查二进制日志的内容，或在复制或恢复期间重播这些语句。有关二进制日志的完整信息，请参见第5.4.4节“二进制日志”。有关二进制日志的MySQL配置选项，请参见第16.1.6.4节“二进制日志记录选项和变量”。

对于MySQL Enterprise Backup产品，二进制日志的文件名和文件中的当前位置是重要的详细信息。要在复制上下文中进行备份时为主服务器记录此信息，可以指定--slave-info选项。

在MySQL 5.0之前，可以使用类似的功能，称为更新日志。在MySQL 5.0及更高版本中，二进制日志替换了更新日志

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

binary log

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication

Certain data recovery operations require use of the binary log.After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

二进制日志包含描述数据库更改的“事件”，例如表创建操作或对表数据的更改。它还包含可能已进行更改的语句的事件（例如，不匹配任何行的DELETE），除非使用基于行的日志记录。二进制日志还包含有关每个语句获取更新数据的时间长度的信息。

两个作用：

　　1，用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。

　　2，用于数据库的基于时间点的还原。

The binary log is not used for statements such as SELECT or SHOW that do not modify data.

二进制日志不用于不修改数据的SELECT或SHOW等语句

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

运行启用了二进制日志记录的服务器会使性能稍慢。但是，二进制日志使您能够设置复制和还原操作的好处通常会超过这种较小的性能下降。

The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size. A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files

在当前日志的大小达到max_binlog_size后，服务器还会自动创建新的二进制日志文件。如果您使用大型事务，则二进制日志文件可能会变得比max_binlog_size大，因为事务是以一个部分写入文件，而不是在文件之间分割

To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same base name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

为了跟踪已使用的二进制日志文件，mysqld还创建了一个二进制日志索引文件，其中包含所有使用的二进制日志文件的名称。默认情况下，它具有与二进制日志文件相同的基本名称，扩展名为“.index”。您可以使用--log-bin-index [= file_name]选项更改二进制日志索引文件的名称。在mysqld运行时，您不应手动编辑此文件; 这样做会让mysqld感到困惑。

Binary Logging Formats

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.

In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. It is important therefore that tables always use a primary key to ensure rows can be efficiently identified. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.

A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

MySQL中的复制功能最初基于从主设备到从设备的SQL语句传播。这称为基于语句的日志记录。您可以通过使用--binlog-format = STATEMENT启动服务器来使用此格式。

在基于行的日志记录中，主服务器将事件写入二进制日志，以指示各个表行的影响方式。因此，重要的是表始终使用主键来确保可以有效地识别行。您可以通过使用--binlog-format = ROW启动服务器来使用基于行的日志记录。

第三种选择也是可用的：混合日志记录。对于混合日志记录，默认情况下使用基于语句的日志记录，但在某些情况下，日志记录模式会自动切换到基于行的日志记录，如下所述。您可以通过使用选项--binlog-format = MIXED启动mysqld，使MySQL显式使用混合日志记录。

实验过程

[root@mydb ~]# cat /etc/my.cnf |grep log-bin -A1

log-bin=mysql-bin

server-id=1

mysql> show variables like '%log_bin%';

+---------------------------------+--------------------------------+

| Variable_name | Value |

+---------------------------------+--------------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/mysql-bin |

| log_bin_index | /var/lib/mysql/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+--------------------------------+

查看当前服务器所有的二进制日志文件

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207597 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207597 |

+------------------+-----------+

查看当前二进制日志文件状态

mysql> show master status

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> flush logs;

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 154 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 154 |

+------------------+-----------+

mysql> flush binary logs;

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 207644 |

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

删除某个日志之前的所有日志

mysql> purge binary logs to 'mysql-bin.000003';

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 154 |

+------------------+-----------+

[root@mydb ~]# ll /var/lib/mysql|grep mysql-bin

-rw-r----- 1 mysql mysql 177 Sep 29 17:17 mysql-bin.000003

-rw-r----- 1 mysql mysql 154 Sep 30 08:52 mysql-bin.000004

删除某个时间点之前的binary log

mysql> purge binary logs before '2018-09-30 12:00:00';

删除所有的binary log

mysql> reset master;

自动删除binary log

mysql> set global expire_logs_days=7;

设置expire_logs_days参数，设置自动清理，其默认值为0,表示不启用过期自动删除功能，如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除

查看binary log的内容

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

mysql运维-二进制日志BINARY LOG清理

1 几种方法

1.1 方法1：PURGE MASTER LOGS

语法：

PURGE { BINARY | MASTER } LOGS{ TO 'log_name' | BEFORE datetime_expr }

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。

实例：


PURGE BINARY LOGS TO 'mysql-bin.000002';
PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';

分别表示：删除mysql-bin.000002之前的日志（不包括mysql-bin.000002）

删除2014-04-28 23:59:59时间点之前的日志

具体演示：删除某个时间点之前的日志

具体演示：删除某个日志之前的日志

注意事项：

在删除binlog日志同时，也会清理mysql-bin.index的文件记录，清理完后命令中指定的日志文件成为第一个。
主从架构下，如果复制正在进行中，执行该命令是安全的，例如slave正在读取我们要删除的log，该语句将什么也不会做，并返回一个错误；如果复制是停止的，我们删除了一个slave还未读取的日志，则复制重新建立连接时将会失败。
建议操作步骤：

a 在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
b 使用SHOW MASTER LOGS获得主服务器上的一系列日志。
c 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是最新的，这是清单上的最后一个日志。
d 备份您将要删除的所有日志。（这个步骤是自选的，但是建议采用。）
e 清理除目标日志之外的所有日志。

1.2 方法2：手动删除binlog日志文件

1.2.1 查看日志文件：

1.2.2 删除文件mysql-bin.000009

[root@localhost data]# rm -f mysql-bin.000009

1.2.3 编辑mysql-bin.index

删除掉一行./mysql-bin.000009

1.3 方法3：指定过期天数（expire_logs_days）

该参数为全局可动态调整参数，默认值为0，即关闭，取值范围0-99。

1.3.1 参数的查看：


mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
---------------------

1.3.2 参数调整：

例如我们设置过期为60天

设置完后红色标注的3个binlog日志将会被删除。

设置参数:

刷新日志查看，上述三个日志被清除掉了。

设置参数:

mysql> set global expire_logs_days = 60;

Query OK, 0 rows affected (0.05 sec)

mysql> show variables like 'expire_logs_days';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| expire_logs_days | 60 |

+------------------+-------+

刷新日志查看，上述三个日志被清除掉了。

mysql> flush logs;

Query OK, 0 rows affected (0.07 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000013 | 4097 |

| mysql-bin.000014 | 54431 |

| mysql-bin.000015 | 729 |

| mysql-bin.000016 | 107 |

+------------------+-----------+

---------------------

1.3.3 注意事项

在双机复制环境下，应确保过期天数不应小于从机追赶主机binlog日志的时间。

1.3.4 触发过期删除的条件

每次进行 LOG flush的时候会自动删除过期的日志。
触发log flush的条件为：
1. 重启mysql；
　 2. BINLOG文件大小达到参数max_binlog_size限制；
　 3. 手工执行命令。

1.4 方法4：重置binlog（reset master）

语法：

RESET MASTER;

解释：

该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个以.000001为后缀新的二进制日志文件。

该语法一般只用在主从环境下初次建立复制时。

在主从复制进行过程中，该语句是无效的。

主从环境下的配置步骤：
a. 启动master和slave，开启replication(即复制)
b. 在master上运行一些测试的语句，看数据是否能够复制到 slave上面
c. 当复制运行正常的话，就在从上stop slave 然后执行 reset slave,去掉不需要的数据
d. 在master上面执行reset master 清除测试产生的数据

2 使用到的命令列表


show master logs;
PURGE BINARY LOGS TO 'mysql-bin.000001';
PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';
show variables like 'expire_logs_days';
set global expire_logs_days = 60;
flush logs;
RESET MASTER;

About Me

  ........................................................................................................................  
  ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除  
  ● 本文在itpub、博客园、CSDN和个人微 信公众号（  xiaomaimiaolhr  ）上有同步更新  
  ● 本文itpub地址：  http://blog.itpub.net/26736162   
  ● 本文博客园地址：  http://www.cnblogs.com/lhrbest   
  ● 本文CSDN地址：  https://blog.csdn.net/lihuarongaini   
  ● 本文pdf版、个人简介及小麦苗云盘地址：  http://blog.itpub.net/26736162/viewspace-1624453/   
  ● 数据库笔试面试题库及解答：  http://blog.itpub.net/26736162/viewspace-2134706/   
  ● DBA宝典今日头条号地址：  http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826   
  ........................................................................................................................  
  ● QQ群号：  230161599  （满）  、618766405   
  ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰  
  ● 联系我请加QQ好友  （   646634621   ）  ，注明添加缘由  
  ● 于 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成  
  ● 最新修改时间：2019-07-01 06:00 ~ 2019-07-31 24:00  
  ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解  
  ● 版权所有，欢迎分享本文，转载请保留出处  
  ........................................................................................................................  
  ●  小麦苗的微店  ：  https://weidian.com/s/793741433?wfr=c&ifr=shopdetail   
  ●  小麦苗出版的数据库类丛书  ：  http://blog.itpub.net/26736162/viewspace-2142121/   
  ●  小麦苗OCP、OCM、高可用网络班  ：  http://blog.itpub.net/26736162/viewspace-2148098/   
  ●  小麦苗腾讯课堂主页  ：  https://lhr.ke.qq.com/   
  ........................................................................................................................  
 使用   微 信客户端   扫描下面的二维码来关注小麦苗的微 信公众号（  xiaomaimiaolhr  ）及QQ群（DBA宝典）、添加小麦苗微 信，  学习最实用的数据库技术。  
 
  ........................................................................................................................  