二进制日志和数据更新的关系

二进制日志的本质是记录数据的改变

如果数据没有改变,就一定不记录二进制日志吗

记录数据改变的情况:
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;