再来理解一下杀手级新特性:gtid
1.一个事务,就会给一个gtid编号。来看看例子:
成都创新互联-云计算及IDC服务提供商,涵盖公有云、IDC机房租用、雅安机房托管、等保安全、私有云建设等企业级互联网基础服务,来电联系:028-86922220
MySQL> show master status; +---------------+----------+--------------+------------------+------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------------+ | binlog.000017 | 58105 | | | b526042e-89a7-11e8-bddb-000c29d7d637:1-1304086 | +---------------+----------+--------------+------------------+------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into student(id) values(5); Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------------+ | binlog.000017 | 58432 | | | b526042e-89a7-11e8-bddb-000c29d7d637:1-1304087 | +---------------+----------+--------------+------------------+------------------------------------------------+ 1 row in set (0.00 sec) mysql> begin -> ; Query OK, 0 rows affected (0.00 sec) mysql> insert into student(id) values(6); Query OK, 1 row affected (0.00 sec) mysql> insert into student(id) values(7); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------------+ | binlog.000017 | 58918 | | | b526042e-89a7-11e8-bddb-000c29d7d637:1-1304088 | +---------------+----------+--------------+------------------+------------------------------------------------+ 1 row in set (0.00 sec) mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> insert into student(id) values(8); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------------+ | binlog.000017 | 58918 | | | b526042e-89a7-11e8-bddb-000c29d7d637:1-1304088 | +---------------+----------+--------------+------------------+------------------------------------------------+ 1 row in set (0.00 sec) mysql> quit Bye [root@yw-gz-hd-test-211 ~]# cat /data/mysql3308/auto.cnf [auto] server-uuid=b526042e-89a7-11e8-bddb-000c29d7d637
仔细看看上面的例子,当前gtid:b526042e-89a7-11e8-bddb-000c29d7d637:1304086,然后我插入一条数据,执行一个事务,gtid加1,变成b526042e-89a7-11e8-bddb-000c29d7d637:1304087,然后我用begin commit,标志另外一个事务,插入2条数据,gtid加1,变成b526042e-89a7-11e8-bddb-000c29d7d637:1304088,然后我rollback,gtid不加1.
到这里,我们停下来,思考一下。。。。。。。。。。。
gtid是什么东东?明白了吧,有所领悟了吧。
2.为什么说gtid能避免重复执行,避免丢数据呢?
来看下下面这个例子:
mysql> SET @@SESSION.GTID_NEXT= 'b526042e-89a7-11e8-bddb-000c29d7d637:1304076'/*!*/; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id=4; +----+------+------+---------+---------+---------------------+---------------------+ | id | name | age | textcol | blobcol | birday | birday2 | +----+------+------+---------+---------+---------------------+---------------------+ | 4 | 3 | 3 | 3 | NULL | 2018-07-25 10:42:29 | 2018-07-25 10:42:33 | +----+------+------+---------+---------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `student` ( -> `id` int NOT NULL AUTO_INCREMENT , -> `name` varchar(255) NULL , -> `age` int NULL , -> `textcol` text NULL , -> `blobcol` blob NULL , -> `birday` timestamp NULL ON UPDATE CURRENT_TIMESTAMP , -> `birday2` datetime NULL ON UPDATE CURRENT_TIMESTAMP , -> PRIMARY KEY (`id`) -> ) -> ; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------------+ | binlog.000017 | 58105 | | | b526042e-89a7-11e8-bddb-000c29d7d637:1-1304086 | +---------------+----------+--------------+------------------+------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into studnet(id) values(5); ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'b526042e-89a7-11e8-bddb-000c29d7d637:1304076'.
这个信息量有点大。一点点理解,首先我
SET @@SESSION.GTID_NEXT= 'b526042e-89a7-11e8-bddb-000c29d7d637:1304076'
但是当前gtid是b526042e-89a7-11e8-bddb-000c29d7d637:1-1304086 。什么意思,就是说1304076 事务包含在1-1304086。也就是说,这个事务已经执行了,不需要再执行了。理解了吗?
思考一下。。。。。。。。。
这个理解通了,你就会发现,为什么我发布了一个create table 命令,查询返回Query OK, 0 rows affected (0.00 sec)
就是什么作用也没有。意思就是说,这个命令create table 我已经执行过了,所以不需要执行。
再来看看错误:ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'b526042e-89a7-11e8-bddb-000c29d7d637:1304076'.
意思就是说,你每执行一个命令,都必须先执行 set @@SESSION.GTID_NEXT = xxxxx。
为什么会这样呢?
思考一下。。。。。。
新闻名称:再来理解一下杀手级新特性:gtid
转载来源:http://pwwzsj.com/article/gpgjsh.html