二进制日志和数据更新的关系
二进制日志的本质是记录数据的改变
如果数据没有改变,就一定不记录二进制日志吗
记录数据改变的情况:
1.所有可能对数据结构造成改变的ddl语句
alter,create,drop,grant,revoke等
2. insert,delete,update语句可能对数据更新的语句
3.select绝大部分情况下是不会记录的
1.ddl语言
create table test2 (id ,int); 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.000007 | 106 | Query | 250 | 194 | use `hk`; create table test2 (id int) | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ mysql> drop table test2; Query OK, 0 rows affected (0.03 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.000007 | 106 | Query | 250 | 194 | use `hk`; create table test2 (id int) | | mysql-bin.000007 | 194 | Query | 250 | 271 | use `hk`; drop table test2 | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 删除一个不存在的表(没改变任何数据),日志会记录吗 drop table if exists test2; 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.000007 | 106 | Query | 250 | 194 | use `hk`; create table test2 (id int) | | mysql-bin.000007 | 194 | Query | 250 | 271 | use `hk`; drop table test2 | | mysql-bin.000007 | 271 | Query | 250 | 358 | use `hk`; drop table if exists test2 | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 确实记录了 对于 create,drop,grant,alter,revoke 也是 mysql> insert into test (time) values (now()); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+---------------------+ | id | time | +----+---------------------+ | 1 | 2017-02-05 18:44:17 | | 2 | 2017-02-05 20:10:33 | | 3 | 2017-02-05 21:06:35 | | 4 | 2017-02-08 07:48:57 | | 5 | 2017-02-08 07:49:24 | | 6 | 2017-02-08 07:49:29 | | 7 | 2017-02-08 13:30:57 | +----+---------------------+ mysql> insert into test values(7,now()) on duplicate key update id=7; Query OK, 0 rows affected (0.01 sec) show binlog events in 'mysql-bin.000007'; 在二进制日志里也确实可以看到记载了 mysql> delete from test where id=8; Query OK, 0 rows affected (0.03 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.000007 | 106 | Query | 250 | 194 | use `hk`; create table test2 (id int) | | mysql-bin.000007 | 194 | Query | 250 | 271 | use `hk`; drop table test2 | | mysql-bin.000007 | 271 | Query | 250 | 358 | use `hk`; drop table if exists test2 | | mysql-bin.000007 | 358 | Query | 250 | 449 | use `hk`; insert into test(id) values(7) | | mysql-bin.000007 | 449 | Query | 250 | 537 | use `hk`; delete from test where id=7 | | mysql-bin.000007 | 537 | Query | 250 | 638 | use `hk`; insert into test values(7,now()) | | mysql-bin.000007 | 638 | Query | 250 | 768 | use `hk`; insert into test values(7,now()) on duplicate key update id=7 | | mysql-bin.000007 | 768 | Query | 250 | 856 | use `hk`; delete from test where id=7 | | mysql-bin.000007 | 856 | Query | 250 | 979 | use `hk`; alter table test change id id int primary key auto_increment | | mysql-bin.000007 | 979 | Intvar | 250 | 1007 | INSERT_ID=7 | | mysql-bin.000007 | 1007 | Query | 250 | 1114 | use `hk`; insert into test (time) values (now()) | | mysql-bin.000007 | 1114 | Query | 250 | 1244 | use `hk`; insert into test values(7,now()) on duplicate key update id=7 | | mysql-bin.000007 | 1244 | Query | 250 | 1332 | use `hk`; delete from test where id=8 | +------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------+ 以上2条语句均未实际造成数据的改变 复制有延时,处理时间类型的数据 在statement下,sysdate()函数返回的时间并不安全,不能保证复制的精确性
2.隐式信息的记录
1.自定义变量
在主库
set @a:=1; mysql> select @a; +------+ | @a | +------+ | 10 | +------+ 在从库是查不到有关记录的 在主库 mysql> select * from test where id=@a; +----+---------------------+ | id | time | +----+---------------------+ | 10 | 2017-02-10 09:21:33 | +----+---------------------+ mysql> update test set time=now() where id=@a; Query OK, 1 row affected (0.01 sec) mysql> select * from test where id=@a; +----+---------------------+ | id | time | +----+---------------------+ | 10 | 2017-02-15 14:36:37 | +----+---------------------+ mysql> show binlog events in 'mysql-bin.000011'; +------------------+-----+-------------+-----------+-------------+--------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------+ | mysql-bin.000011 | 4 | Format_desc | 108 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 | | mysql-bin.000011 | 120 | Query | 108 | 203 | BEGIN | | mysql-bin.000011 | 203 | User var | 108 | 250 | @`a`=10 | | mysql-bin.000011 | 250 | Query | 108 | 366 | use `hk`; update test set time=now() where id=@a | | mysql-bin.000011 | 366 | Xid | 108 | 397 | COMMIT /* xid=29 */ | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------+
从日志中可以看到
复制时把自定义变量也写到了二进制日志里
在从库也不会有变量的内容
主库上的变量 在复制时 和从库的变量没任何关系
比如 关闭从库io进程
在主库修改记录 id为@a的
在从库定义一个@a为另外一个值
开启从库io进程
复制不会受到主从库的变量的影响
主库的变量在修改记录后再发生修改变量的值 也不会影响复制
2.随机函数
mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.7021004480329346 | +--------------------+ 每次都在变化 create table a (id int,rnd float(6,5)); mysql> insert into a values (1,rand()); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 在二进制日志中可以看到 | mysql-bin.000011 | 951 | Query | 108 | 1059 | use `hk`; create table a (id int,rnd float(6,5)) | | mysql-bin.000011 | 1059 | Query | 108 | 1134 | BEGIN | | mysql-bin.000011 | 1134 | RAND | 108 | 1173 | rand_seed1=753874615,rand_seed2=506421884 | | mysql-bin.000011 | 1173 | Query | 108 | 1274 | use `hk`; insert into a values (1,rand()) | | mysql-bin.000011 | 1274 | Xid | 108 | 1305 | COMMIT /* xid=77 */ | +------------------+------+-------------+-----------+-------------+--------------------------------------------------+
随机数根据伪随机种子产生
复制的精确性 是由随机种子一样来保证的
发现主从复制的结果是一样的
mysql> show variables like 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ mysql> set session binlog_format='row'; Query OK, 0 rows affected (0.01 sec) mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ mysql> insert into a values (2,rand()); Query OK, 1 row affected (0.01 sec) 没产生警告信息 查看二进制日志 | mysql-bin.000011 | 1305 | Query | 108 | 1375 | BEGIN | | mysql-bin.000011 | 1375 | Table_map | 108 | 1419 | table_id: 71 (hk.a) | | mysql-bin.000011 | 1419 | Write_rows | 108 | 1463 | table_id: 71 flags: STMT_END_F | | mysql-bin.000011 | 1463 | Xid | 108 | 1494 | COMMIT /* xid=86 */ | +------------------+------+-------------+-----------+-------------+--------------------------------------------------+ alter table a modify id int auto_increment primary key; mysql> insert into a(rnd) values(rand()); Query OK, 1 row affected (0.00 sec)
3.自增长主键
set session binlog_format='statement'; 查看自增主键复制的情况 mysql> insert into a(rnd) values(rand()); Query OK, 1 row affected, 1 warning (0.02 sec) | mysql-bin.000012 | 309 | Query | 108 | 384 | BEGIN | | mysql-bin.000012 | 384 | Intvar | 108 | 416 | INSERT_ID=4 | | mysql-bin.000012 | 416 | RAND | 108 | 455 | rand_seed1=196763153,rand_seed2=17450176 | | mysql-bin.000012 | 455 | Query | 108 | 558 | use `hk`; insert into a(rnd) values(rand()) | | mysql-bin.000012 | 558 | Xid | 108 | 589 | COMMIT /* xid=97 */ | +------------------+-----+-------------+-----------+-------------+---------------------------------------------+
INSERT_ID=4
就是主键Id
总结
statement模式下
1.自定义变量的处理,写入BinLog
2.rand()随机函数处理,伪随机种子写入Binlog
3.last_insert_id处理 ,id值写入binlog
4.auto_increment处理,该字段的值会写入binlog
row模式是保存更改的数据块,所以能保证复制的精确性
二.外部数据导入处理
load data file处理
statement 模式
mysql把系统文件保存在类似数据块中,标识为文件id,通过标识文件的id数据块导入,保证主从复制的精确性
row模式下
把对行的改变的最终结果保存为二进制数据,保证主从复制的精确性
load file处理
statement模式下 为不安全的,无法保证复制精确性
row模式
保存的是改变的最终结果,是二进制数据块,保证主从复制的精确性
3.与非同步库关联更新的关系
update users a ,hxf2.users b set a.email=b.email where a.uid=b.uid;
hxf2为非同步库
statement不能保证
row可以保证精确复制
存储过程与日志更新关系
查看存储过程
show procedure status
查看函数
show function status
触发器
事件(定时任务)
show events;
set global event_scheduler=1;
