关于mysql binlog二进制

阅读原文时间：2023年07月09日阅读：1

binlog

在mysql中,当发生数据变更时,都会将变更数据的语句,通过二进制形式,存储到binlog日志文件中.

通过binlog文件,你可以查看mysql一段时间内,对数据库的所有改动.

也可以通过binlog文件,进行数据恢复,以及集群同步.

binlog常用配置参数

[binlog]

log_bin = mysql-bin # {on | off | base_name}指定是否启用记录二进制日志或者指定一个日志路径

sql_log_bin = on # { on | off }    指定是否启用记录二进制日志

expire_logs_days=7 #  指定自动删除二进制日志的时间，即日志过期时间

log_bin_index= /usr/local/mysql/mysql-bin``.index # 指定mysql-bin.index文件的路径

binlog_format = mixed # { mixed | row | statement }    指定二进制日志基于什么模式记录

max_binlog_size = 100M #   指定二进制日志文件最大值

binlog_cache_size = 4M #   指定事务日志缓存区大小

max_binlog_cache_size= 64M #   指定二进制日志缓存最大大小

sync_binlog = 0 # { 0 | n }   指定写缓冲多少次，刷一次盘

binlog常见命令

mysql> show master status; # 查看最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs; #刷新日志文件,产生新编号的日志文件
Query OK, 0 rows affected (0.01 sec)

mysql> reset master; # 清空所有binlog日志
Query OK, 0 rows affected (0.00 sec)

首先,我们需要通过查看配置项的 log-bin配置和datadir 配置项,获取到binlog文件存储位置,在宝塔中,默认为"/www/server/data/mysql-bin****"文件

通过mysqlbinlog命令,即可查看具体日志信息:

我们先创建一个简单的表,来进行测试

CREATE TABLE `test`.`test` ( `id` INT NOT NULL AUTO_INCREMENT , ` ```name``` ` ```VARCHAR(32)NOT NULL ``, age``INT(10)` `NOT` `NULL` , sex TINYINT(1)`NOT` `NULL` `,PRIMARY KEY ``(id`)) ENGINE = InnoDB;``

创建新的数据

INSERT INTO `test` (`id`, ` ```name``` `, `age`, `sex`) VALUES (``NULL``, '仙士可'``,``'11'``, '1'``), (``NULL``, '宁成龙'``, '33'``, '1'``), (``NULL``, '宁成龙22'``, '1'``, '12'``)

查看binlog文件

[root@localhost data]``# /www/server/mysql/bin/mysqlbinlog mysql-bin.000015

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200415 21:39:32 server id 1  end_log_pos 120 CRC32 0x05fef589  Start: binlog v 4, server v 5.6.44-log created 200415 21:39:32 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

lA6XXg8BAAAAdAAAAHgAAAABAAQANS42LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAACUDpdeEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYn1

/gU``=

'/*!*/;

# at 120

#200417 22:19:10 server id 1  end_log_pos 383 CRC32 0x7f61e7b6  Query   thread_id=26    exec_time=0     error_code=0

use ` ```test``` `/*!*/;

SET TIMESTAMP=1587133150/*!*/;

SET @@session.pseudo_thread_id=26/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1342177280/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8mb4 *``//``*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=224,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE TABLE ` ```test``` `.` ```test``` ` ( ` ```id``` ` INT NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(32) NOT NULL ,  `age` INT(10) NOT NULL ,  `sex` TINYINT(1) NOT NULL ,    PRIMARY KEY  (` ```id``` `)) ENGINE = InnoDB

/*!*/;

# at 383

#200417 22:20:26 server id 1  end_log_pos 462 CRC32 0xb0c717e0  Query   thread_id=64    exec_time=0     error_code=0

SET TIMESTAMP=1587133226/*!*/;

BEGIN

/*!*/;

# at 462

# at 494

#200417 22:20:26 server id 1  end_log_pos 494 CRC32 0x90b2c86f  Intvar

SET INSERT_ID=1/*!*/;

#200417 22:20:26 server id 1  end_log_pos 718 CRC32 0x1969fb9b  Query   thread_id=64    exec_time=0     error_code=0

SET TIMESTAMP=1587133226/*!*/;

INSERT INTO ` ```test``` ` (` ```id``` `, `name`, `age`, `sex`) VALUES (NULL, '仙士可'``,``'11'``, '1'``), (NULL, '宁成龙'``, '33'``, '1'``), (NULL, '宁成龙22'``, '1'``, '12'``)

/*!*/;

# at 718

#200417 22:20:26 server id 1  end_log_pos 749 CRC32 0x359c1fc1  Xid = 569

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@localhost data]``#

可以看到,binlog日志中记录了创建数据表,和insert数据的记录.

同时,mysqlbinlog命令还支持日志筛选等参数.