mysql binlog server-01 用途 使用 介绍及实战

binlog主要是两个方面的用途，一个是用于同步，另一个是用于数据恢复。做binlog server主要是用于数据的恢复，也可以用在数据抽取上。

先来看几个binlog的操作和维护。

1， show master status

显示最新的binlog文件的名称，以及最后一个event结束的位置。

[root@localhost][(none)]> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000014
         Position: 101028539
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: dfa55812-b786-11eb-9589-005056b21088:1-2255282,
dfa55812-b786-11eb-9589-005056b21090:1-444
1 row in set (0.00 sec)

2，show binary logs

显示目前保留的binlog日志及大小

[root@localhost][(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000012 | 495421378 |
| mysql-bin.000013 | 142702906 |
| mysql-bin.000014 | 101195661 |
+------------------+-----------+
3 rows in set (0.00 sec)

3，flush logs

刷新binlog即删除之前的binlog文件；切换binlog日志文件即产生一个新的binlog文件；

[root@localhost][(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000012 | 495421378 |
| mysql-bin.000013 | 142702906 |
| mysql-bin.000014 | 101385974 |
+------------------+-----------+
3 rows in set (0.00 sec)

[root@localhost][(none)]> flush logs;
Query OK, 0 rows affected (0.17 sec)

[root@localhost][(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000014 | 101386021 |
| mysql-bin.000015 |       235 |
+------------------+-----------+
2 rows in set (0.00 sec)

4，purge binary logs to ”

将指定文件之前的日志删除

[root@localhost][(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000014 | 101386021 |
| mysql-bin.000015 |    554890 |
+------------------+-----------+
2 rows in set (0.00 sec)

[root@localhost][(none)]> purge binary logs to 'mysql-bin.000015';
Query OK, 0 rows affected (0.04 sec)

[root@localhost][(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000015 |    784113 |
+------------------+-----------+
1 row in set (0.00 sec)

5，show binlog events in ”

查询指定binlog文件中的event

# 在执行的binlog 文件中，从执行的位置开始显示events
show binlog events in 'mysql-bin.000015' from 1476 limit 10;

6，reset master

清空binlog文件，这个，这个还是不演示了。

7，一个事务在binlog中的events

下面是一个完整的事务events，pos点从12586到13599。

对应的binlog文件如下。6个event分别对应 “at pos”（例如：# at 12668 ）

# at 12586
# at 12668
#220614 14:25:31 server id 5014088  end_log_pos 12744 CRC32 0x93ae0a92  Query   thread_id=61263 exec_time=0 error_code=0
SET TIMESTAMP=1655187931/*!*/;
BEGIN
/*!*/;
# at 12744
#220614 14:25:31 server id 5014088  end_log_pos 13208 CRC32 0x4cf23eba Rows_query
# INSERT INTO SYSTEM_AUDITLOG  ( id,entityId,operator,operatorId,operation,createTime,entityName,detailedOperation,systemAuditType,auditSource,orgId ) VALUES( 2974214233026044345,2974212745692291461,'未知操作人',2,'RECALLCONTRACT','2022-06-14 14:25:31.381','申请-我要减肥我要变美丽啦钱说话吗-1536504465834434561','操作人通过qiyuesuo-lpt撤回了文件，撤回原因：撤销','CONTRACT','OPEN',2879594415884259374 )
# at 13208
#220614 14:25:31 server id 5014088  end_log_pos 13306 CRC32 0xb2aa0422 Table_map: `qiyuesuodb`.`system_auditlog` mapped to number 62
# at 13306
#220614 14:25:31 server id 5014088  end_log_pos 13568 CRC32 0x7bd476ce Write_rows: table id 62 flags: STMT_END_F
### INSERT INTO `qiyuesuodb`.`system_auditlog`
### SET
###   @1=2974214233026044345 * LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2974212745692291461 * LONGINT meta=0 nullable=1 is_null=0 */
###   @3=2879594415884259374 * LONGINT meta=0 nullable=1 is_null=0 */
###   @4='未知操作人' * VARSTRING(300) meta=300 nullable=1 is_null=0 */
###   @5=2 * LONGINT meta=0 nullable=1 is_null=0 */
###   @6='RECALLCONTRACT' * VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @7='申请-我要减肥我要变美丽啦钱说话吗-1536504465834434561' * VARSTRING(1800) meta=1800 nullable=1 is_null=0 */
###   @8=NULL * VARSTRING(1500) meta=1500 nullable=1 is_null=1 */
###   @9='操作人通过qiyuesuo-lpt撤回了文件，撤回原因：撤销' * BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @10='CONTRACT' * VARSTRING(300) meta=300 nullable=0 is_null=0 */
###   @11='2022-06-14 14:25:31' * DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @12=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @13='OPEN' /* VARSTRING(48) meta=48 nullable=1 is_null=0 */
###   @14=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
# at 13568
#220614 14:25:31 server id 5014088  end_log_pos 13599 CRC32 0xdc1cd010 Xid = 5004724
COMMIT/*!*/;

8，解析binlog文件成sql文件并输出

解析整个文件

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --skip-gtids /data/mysql/mysql-bin.000016 > /data/backup/binlog16.sql

解析某个库的binlog

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=qiyuesuodb /data/mysql/mysql-bin.000016 > /data/backup/binlog16_qiyuesuo.sql

解析指定的时间段内的binlog

mysqlbinlog --no-defaults --base64-output=decode-rows -vv  --skip-gtids  --start-datetime="2022-06-14 14:28:30" --stop-datetime="2022-06-14 14:29:00" /data/mysql/mysql-bin.000016 > /data/backup/binlog16_1428-1429.sql

解析指定位点之间的binlog，此时两个位点之间必须是完整的事务，否则会报警告。

# 完成的事务
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --skip-gtids --start-position=12586 --stop-position=13599 /data/mysql/mysql-bin.000016 > /data/backup/binlog16_wanzheng.sql
# 不完整的事务
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --skip-gtids --start-position=12586 --stop-position=14724 /data/mysql/mysql-bin.000016 > /data/backup/binlog16_wanzheng.sql

有了对binlog的认识和基本操作，就可以考虑binlog server的事情了。