【mysql】一直都被误认为影响主从一致性的now()函数

时间:2015-12-15 12:12:28      作者:beebol      标签: mysql now() statement 主从复制      分类: mysql

一直以来，碰到now()函数，都认为是不安全函数，在STATEMENT-BASED下会导致主从数据不一致。今天一同事告诉我才特别注意下这个问题，其实它不会。首先来做个实验：

mysql> show variables like '%format%';
+---------------------+-------------------+
| Variable_name       | Value             |
+---------------------+-------------------+
| binlog_format       | STATEMENT         |+---------------------+-------------------+
+---------------------+-------------------+

当然需要在statement replication下，row模式就不用说了，肯定不会影响。

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

先停掉同步：stop slave; 然后在主上进行insert操作：

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1(dt)values(now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;               
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2015-12-14 18:51:29 |
+----+---------------------+
1 row in set (0.00 sec)

过几秒后，再start slave开启同步 再看看是否一致

mysql> start slave ;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2015-12-14 18:51:29 |
+----+---------------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

发现时间是一致的，没有出现一致性问题。 然后官网也有说明：

 Statements using any of the following functions cannot be replicated properly using statement-based replication:

    LOAD_FILE()

    UUID(), UUID_SHORT()

    USER()

    FOUND_ROWS()

    SYSDATE() (unless both the master and the slave are started with the --sysdate-is-now option)

    GET_LOCK()

    IS_FREE_LOCK()

    IS_USED_LOCK()

    MASTER_POS_WAIT()

    RAND()

    RELEASE_LOCK()

    SLEEP()

    VERSION() However, all other functions are replicated correctly using statement-based replication, including NOW() and so forth.

However, all other functions are replicated correctly using statement-based replication, including NOW() and so forth.

now()函数和自增主键应该是有差不多实现方式的，仔细观察binlog会发现。自增主键会带一个 SET INSERT_ID=1/!/;

但同时也会带着一个时间  (now()是不是用的这个时间，待考证) SET TIMESTAMP=1450090289/!/;

然后确认下这个时间点：

mysql> SELECT FROM_UNIXTIME( 1450090289, '%Y-%m-%d_%H:%i:%s' ) ;
+--------------------------------------------------+
| FROM_UNIXTIME( 1450090289, '%Y-%m-%d_%H:%i:%s' ) |
+--------------------------------------------------+
| 2015-12-14_18:51:29                              |
+--------------------------------------------------+#与插入的时间是一致的
#与插入的时间是一致的