β

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

IT运维中文博客 115 阅读

一直以来,碰到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;

先停掉同步: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 |
+----+---------------------+
 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 |
+----+---------------------+
 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.

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                              |
+--------------------------------------------------+
#与插入的时间是一致的
作者:IT运维中文博客
Beebol's & Vaster's Blog

发表评论