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常见命令
数据库sql命令
mysql> show master logs; # 查看日志文件列表 +------------------+-----------+ | Log\_name | File\_size | +------------------+-----------+ | mysql-bin.000009 | 143 | | mysql-bin.000010 | 143 | | mysql-bin.000011 | 646950 | | mysql-bin.000012 | 120 | +------------------+-----------+ 4 rows in set (0.01 sec) 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)
mysqlbinlog命令
首先,我们需要通过查看配置项的 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命令还支持日志筛选等参数.
常用参数： --start-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。 --stop-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复。 --start-position=N 从二进制日志中第1个位置等于N参量时的事件开始读。 --stop-position=N 从二进制日志中第1个位置等于和大于N参量时的事件起停止读。 --base64-output=DECODE-ROWS 会显示出row模式带来的sql变更 -d 与 --database 效果相同，指定一个数据库名称。 --offset=N，-o N 跳过前N个条目。
binlog模式
在上面我们讲到了,mysql发生数据变更后,才会将变更的语句,通过二进制形式存储,而通过存储语句的方式,mysql将其分为了3种方式.
行模式(row level)
binlog日志将会记录数据库中每一条的数据变更,例如当你delete 数据100万条时,会产生100万条记录,用于记录每一行数据的变更情况.
优点:此模式可以非常精确的记录每条记录的变更细节.不需要依赖sql的上下文关系,例如存储过程,触发器.
缺点:此模式会产生大量的日志内容.
语句模式(Statement Level)
mysql默认模式,和行模式不同的事,语句模式会直接记录mysql执行数据变更的语句,例如delete 100万数据,它只会记录该delete语句,如果需要调用binlog时,将会通过记录的这个语句,重新执行一遍delete.
混合模式(mix)
在此模式下,mysql会根据每条执行的语句,区分对待应用存储的模式.
当表结构发生变化时,将使用语句模式存储
当表数据发生update/delete操作时,使用行模式存储
数据库删库后,如何恢复数据
当数据库被删除后,我们可以通过每周/每天备份的数据库文件中,恢复之前的数据.
同时通过binlog,筛选出备份后未恢复的数据,通过mysqlbinlog命令导出sql,执行回去,即可恢复大部分的数据了.
