首页 / MYSQL / MySQL重新学之binlog
MySQL重新学之binlog
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL重新学之binlog,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含13518字,纯文字阅读大概需要20分钟。
内容图文
![MySQL重新学之binlog](/upload/InfoBanner/zyjiaocheng/518/b9ad5a8edaa042668e2af6b15928bf86.jpg)
测试的大版本号为5.7,小版本为5.7.24,默认是没有开启binlog的
修改 my.ini
,新增两个配置:
# 开启bin log
server-id=1
log-bin=mysql-bin
测试的表和数据:
create table T(ID int primary key, c int);
update T set c=c+1 where ID=2;
重启MySQL,发现多了两个 mysql-bin.
打头的文件,使用MySQL命令查询:
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\Program\hecg\mysql-5.7.24-winx64\data\mysql-bin |
| log_bin_index | D:\Program\hecg\mysql-5.7.24-winx64\data\mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
使用
show binary log;
查看二进制文件信息:mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 421 | +------------------+-----------+ 1 row in set (0.00 sec)
使用
show master status;
查看当前正在写入的binlog文件:mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
使用
show binlog events;
查看binlog记录的事件:mysql> show binlog events; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000001 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000001 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000001 | 390 | Xid | 1 | 421 | COMMIT /* xid=36 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 7 rows in set (0.00 sec)
有一点需要注意的时候,
show master status;
里面的Position
刚好是最后一个Event的End_log_pos
。如果有多个binlog,查看事件可以指定binlog名称:
mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000001 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000001 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000001 | 390 | Xid | 1 | 421 | COMMIT /* xid=36 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 7 rows in set (0.00 sec)
使用
flush logs;
产生一个新编号的binlog文件:一般是在备份工作完成之后,产生一个新的binlog记录后续的增量记录mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 468 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec) ## 重新查看之前binlog文件的Event,发现多了一行记录 mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ ... | mysql-bin.000001 | 421 | Rotate | 1 | 468 | mysql-bin.000002;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 8 rows in set (0.00 sec) ## 重新查看当前的binlog是哪个 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ### 查看最新binlog中的记录 mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec)
使用
reset master;
清空所有的binlog日志:mysql> reset master; Query OK, 0 rows affected (0.02 sec) ## 重新查看,还原成了初始状态 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ## 找不到之前的binlog了 mysql> show binlog events in 'mysql-bin.000002'; ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log ## binlog中的数据也变成了初始化的数据 mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec)
执行两遍更新语句,查看binlog事件:
update T set c=c+1 where ID=2; mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000002 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 1 | 421 | COMMIT /* xid=62 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 1 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 486 | Query | 1 | 558 | BEGIN | | mysql-bin.000002 | 558 | Table_map | 1 | 603 | table_id: 108 (test.t) | | mysql-bin.000002 | 603 | Update_rows | 1 | 657 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 657 | Xid | 1 | 688 | COMMIT /* xid=69 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 12 rows in set (0.00 sec)
指定位置查询Event:
mysql> show binlog events in 'mysql-bin.000002' from 154; +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000002 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 1 | 421 | COMMIT /* xid=62 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 1 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 486 | Query | 1 | 558 | BEGIN | | mysql-bin.000002 | 558 | Table_map | 1 | 603 | table_id: 108 (test.t) | | mysql-bin.000002 | 603 | Update_rows | 1 | 657 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 657 | Xid | 1 | 688 | COMMIT /* xid=69 */ | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ 10 rows in set (0.00 sec)
指定位置查询,偏移2条,查询4条数据:
mysql> show binlog events in 'mysql-bin.000002' from 154 limit 2,4; +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000002 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 1 | 421 | COMMIT /* xid=62 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 1 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ 4 rows in set (0.00 sec)
MySQL重新学之binlog
标签:block tar ESS erro server-id key lock dex master
本文系统来源:https://www.cnblogs.com/HeCG95/p/12168788.html
内容总结
以上是互联网集市为您收集整理的MySQL重新学之binlog全部内容,希望文章能够帮你解决MySQL重新学之binlog所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。