mysql binlog相关知识点有哪几点
|
本篇内容主要讲解“mysql binlog相关知识点有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql binlog相关知识点有哪些”吧! 1.- 逻辑日志,记录的是数据库内部的所有变动（sql语句 行的改变） - server层日志，binlog不仅仅记录innodb的变动，也记录myisam存储引擎的变动。 - innodb redo 是存储引擎层，和binlog不是一层，redo只记录innodb的变化。 - binlog不记录 select show 等查询语句,记录dml ddl等数据库变动的语句. 2.binlog的作用 - 可以查看mysql变更 - mysql的复制架构（主从） - mysql备份恢复（可以通过binlog进行数据的补齐或者回滚） 3.binlog的位置 log_bin = /home/mysql3306/mysql3306/mysql-bin #binlog路径+binlog前缀名 如：mysql-bin.000001 4.相关参数简介 (1).log-bin 决定了msyql 的binlog的名字，生成的binlog名字为mysql-bin.000001 (2).binlog_format 规定binlog的格式，binlog有三种格式statement，row以及mixed，默认使用默认使用statement，建议使用row格式 (3).expire_logs_days 过期时间 建议大家根据业务设置7D-30D (4).binlog_do_db 此参数表示只记录指定数据库的二进制日志 (5).binlog_ignore_db 此参数表示不记录指定的数据库的二进制日志 (6).sync_binlog(binlog落盘策略) 0---每一秒刷一次磁盘 1---每次事务提交都刷一次磁盘 n---（100 200 500）每n次提交落盘一次 innodb_flush_log_at_trx_commit =1 sync_binlog=1 双1配置，数据库的安全性是最高的，不会丢事务。 (7).binlog_checksum 为 {CRC32|NONE} 写binlog时，会将内容生成校验位，之后存储在binlog中。 默认情况下，服务器记录事件的长度以及事件本身，并使用它来验证事件是否正确写入。 也可以通过设置 binlog_checksum系统变量来使服务器为事件写入校验和。 (8).max_binlog_size binlog文件的最大值，默认和最大是1GB，并不能严格限定二进制文件的大小 (9).max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小 当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时 就会报错：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage” (10).binlog_cache_size = 4M 线程级参数，不能设置太大 5.binlog格式 (1).statement mysql> show variables like '%binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 执行语句: insert into wwj.t1 values(2,'mxt2'); insert into wwj.t1 values(3,'mxt3'); insert into wwj.t1 values(4,'mxt4'); 查看binlog 点击(此处)折叠或打开 mysql> show binlog events; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 12023307 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 12023307 | 154 | | | mysql-bin.000001 | 154 | Anonymous_Gtid | 12023307 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 219 | Query | 12023307 | 293 | BEGIN | | mysql-bin.000001 | 293 | Query | 12023307 | 398 | insert into wwj.t1 values(13,'mxt3') | | mysql-bin.000001 | 398 | Xid | 12023307 | 429 | COMMIT /* xid=275 */ | | mysql-bin.000001 | 429 | Anonymous_Gtid | 12023307 | 494 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 494 | Query | 12023307 | 568 | BEGIN | | mysql-bin.000001 | 568 | Query | 12023307 | 673 | insert into wwj.t1 values(14,'mxt4') | | mysql-bin.000001 | 673 | Xid | 12023307 | 704 | COMMIT /* xid=276 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ Log_name： binlog文件名 Pos：binlog的position点，写入binlog的日志的字节数，就是pos的大小，在每次新生成binlog的时候，pos点会重置，但是在一个binlog内部，这个pos是单调递增的。 xid：分布式事务ID，Innodb支持分布式事务，Innodb支持的单实例内部的，server层和存储引擎层的分布式事务，还有多节点分布式事务（ndb） Server_id：实例server_id 解析binlog工具 /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > /tmp/all.bin 点击(此处)折叠或打开 # at 293 #180325 4:14:20 server id 12023307 end_log_pos 398 CRC32 0xb9d2f949 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1521922460/*!*/; insert into wwj.t1 values(13,'mxt3') /*!*/; # at 398 查看当前的binlog pos点的位置 mysql> show master status; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 15 Current database: *** NONE *** +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 704 | | | | +------------------+----------+--------------+------------------+-------------------+ 应用binlog /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > /tmp/all.bin mysql -uroot -proot -S /tmp/mysql3306.sock < /tmp/all.bin mysql > source /tmp/all.bin 应用binlog需要注意的事项 如果应用多个binlog 错误做法 mysqlbinlog mysql-bin.000001 | mysql -S -u -p mysqlbinlog mysql-bin.000002 | mysql -S -u -p 正确做法 mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -S -u -p 或者 mysqlbinlog mysql-bin.000001 mysql-bin.000002 > /tmp/all.bin mysql -S -u -p < /tmp/all.bin (2).row mysql> show variables like "binlog_format"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ row格式binlog：row格式binlog记录的是mysql的行的改变前后的数据 比如在stament格式中，update数据，记录的是update语句 而且row格式中，记录的行的改变的前后，如果update1000数据，记录一千行的数据改变 mysqlbinlog -v mysql-bin.000001 > /tmp/all.bin 增加伪sql mysqlbinlog -vv mysql-bin.000001 > /tmp/all.bin 增加数据类型 点击(此处)折叠或打开 # at 334 #180325 5:51:04 server id 12023307 end_log_pos 458 CRC32 0x1be59376 Delete_rows: table id 108 flags: STMT_END_F BINLOG ' SMi2WhMLdrcALwAAAE4BAAAAAGwAAAAAAAEAA3d3agACdDEAAgMPArQAAsKvIYM= SMi2WiALdrcAfAAAAMoBAAAAAGwAAAAAAAEAAgAC//wBAAAAA3d3avwCAAAABG14dDL8AwAAAARt eHQz/AQAAAAEbXh0NPwFAAAABG14dDL8BgAAAARteHQz/AcAAAAEbXh0NPwNAAAABG14dDP8DgAA AARteHQ0dpPlGw== '/*!*/; ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='wwj' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt2' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt4' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt2' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt4' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ ### DELETE FROM `wwj`.`t1` ### WHERE ### @1=14 /* INT meta=0 nullable=0 is_null=0 */ ### @2='mxt4' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */ # at 458 其他解析方法 解析不带行数据 /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000001 如果binlog解析出来需要恢复到数据库，那么需要注意不要加--base64-output=DECODE-ROWS。 /usr/local/mysql/bin/mysqlbinlog start-datetime 开始时间 stop-datetime 停止时间 /usr/local/mysql/bin/mysqlbinlog --start-datetime="2017-04-20 9:00:00" --stop-datetime="2017-07-20 18:00:00"/home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql start-position 开始时间的position stop-position 停止时间的position row格式优点： -记录行改变，而不是SQL，能最大限度保证数据安全性 -row格式可以用于闪回（恢复数据） 在线上的环境中，推荐使用row格式的binlog -row格式update delete会对磁盘 网络造成一个比较大的压力 (3).mixed 一般情况下使用statment格式，在遇到特殊情况（造成主从不一致）改成使用row格式记录。 mixed格式在旧版本bug比较多 不推荐使用 6.清理binlog reset master; //删除master的binlog，线上不要使用 purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件 purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件 如果想直接删除binlog rm binlog.000002 (不要删除当前正在使用的binlog，也就是编号最大的binlog) [root@namenode mysql3306]# cat mysql-bin.index 不会自动更新 /home/mysql3306/mysql3306/mysql-bin.000001 /home/mysql3306/mysql3306/mysql-bin.000002 主从复制如果删掉了，容易从库报错（保证从库不再同步该binlog的数据） 7.binlog和redo的区别 - redo 物理逻辑日志 物理：数据页 逻辑： 数据页的改变 binlog 是逻辑日志 记录的SQL或者是行改变 - redo是innodb存储引擎层 binlog是server层 - redo时时刻刻都在写入文件 binlog是 提交事务的写入（具体写入策略跟具体参数有关） 到此，相信大家对“mysql binlog相关知识点有哪些”有了更深的了解，不妨来实际操作一番吧！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
