首页 > 数据库技术 > 详细

mysql二进制日志

时间:2017-02-12 16:58:36      阅读:264      评论:0      收藏:0      [点我收藏+]

binary log日志简介
查看日志格式

show variables like ‘binlog_format‘;

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

二进制日志的内容结构
二进制日志的内容由以下三部分组成
(1) 文件头 :格式描述事件
(2)文件主体:二进制日志事件
(3)文件尾:日志轮换事件
格式描述事件包含的内容:服务器版本,通用头长度,文件状态信息
binlog事件体:日志文件,位置,事件类型(query,xid,user_var),服务器id,结束位置,详细语句信息
文件尾:事件轮换事件
什么时候轮换?
重启数据库
binlog文件达到最大尺寸
flush logs 操作
异常轮换
奔溃或者非正常关闭(kill杀进程)数据库时,轮换日志表示未正常的关闭(没有轮换记录)
用户数据库奔溃的系统自动修复,或者dba干扰重放
mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19730 |
| mysql-bin.000002 |    765307 |
| mysql-bin.000003 |       624 |
| mysql-bin.000004 |       327 |
| mysql-bin.000005 |       409 |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> show binlog events in mysql-bin.000005;
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                       |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4      |
| mysql-bin.000005 | 106 | Query       |       250 |         207 | use `hk`; insert into test values(4,now()) |
| mysql-bin.000005 | 207 | Query       |       250 |         308 | use `hk`; insert into test values(5,now()) |
| mysql-bin.000005 | 308 | Query       |       250 |         409 | use `hk`; insert into test values(6,now()) |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19730 |
| mysql-bin.000002 |    765307 |
| mysql-bin.000003 |       624 |
| mysql-bin.000004 |       327 |
| mysql-bin.000005 |       452 |
| mysql-bin.000006 |       106 |
+------------------+-----------+

mysql> show binlog events in mysql-bin.000005;
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                       |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4      |
| mysql-bin.000005 | 106 | Query       |       250 |         207 | use `hk`; insert into test values(4,now()) |
| mysql-bin.000005 | 207 | Query       |       250 |         308 | use `hk`; insert into test values(5,now()) |
| mysql-bin.000005 | 308 | Query       |       250 |         409 | use `hk`; insert into test values(6,now()) |
| mysql-bin.000005 | 409 | Rotate      |       250 |         452 | mysql-bin.000006;pos=4                     |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
5 rows in set (0.00 sec)

mysql> show binlog events in mysql-bin.000006;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000006 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
ps -ef|grep mysql
mysql      2759      1  0 Feb07 ?        00:00:16 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root       5161   4530  0 08:06 pts/0    00:00:00 grep mysql
[root@centos1 ~]# kill -9 2759
[root@centos1 ~]# service mysqld status;
mysqld 已死,但 pid 文件仍存
[root@centos1 log]# service mysqld start
正在启动 mysqld:               
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19730 |
| mysql-bin.000002 |    765307 |
| mysql-bin.000003 |       624 |
| mysql-bin.000004 |       327 |
| mysql-bin.000005 |       452 |
| mysql-bin.000006 |       106 |
| mysql-bin.000007 |       106 |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> show binlog events in mysql-bin.000006;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000006 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in mysql-bin.000007;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000007 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
mysql-bin.000006 没有轮换事件

mysql二进制的复制有3中格式
1.基于语句的复制

配置选项 :binlog_format=STAEMENT
特点:相对落后,把主库的语句放到从库再执行一次,速度较慢,无法保证所有语句都正确复制,但是二进制日志相对较小

2.基于行的复制
配置选项 : binlog_format=row
特点:将主库的改动记为二进制日志的一行,保存的是物理的更新数据,更加方便,速度相对较快,保证所有语句都能正确复制
但二进制日志文件相对较大
3.混合类型
binlog_format=mixed
大多情况下是statement 格式,只有特定情况下是row 格式

 

mysql二进制日志

原文:http://www.cnblogs.com/HKUI/p/6391254.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!