MySQL组复制:魔力解释v2

在本次讨论中,我将解释组复制是如何工作的。
这是一个理论上的讨论,我在其中详细解释了复制是什么以及它是如何工作的。
我将讨论什么是认证以及如何进行认证。什么是XCOM和GCS?
组复制是同步的吗?四种新的一致性保证是什么?它们是如何工作的?
单主模式的好处是什么?
这种复制的注意事项是什么?
为什么孟子要比图腾更有效?总是这样吗?
ipv4和ipv6呢
演讲结束后,听众应该对技术术语感到满意,并理解它是如何工作的。
别错过这次谈话,魔术师会揭晓他的把戏的!

展开查看详情

1. 1 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 1/188

2. 2 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 2/188

3. 3 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2   Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 3/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

4. 4 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 about.me/lefred Who am I ? file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 4/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

5. 5 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2   Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.23 devops believer living in Belgium 🇧🇪 https://lefred.be file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 5/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

6. 6 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Group Replication: heart of MySQL InnoDB Cluster file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 6/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

7. 7 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Group Replication: heart of MySQL InnoDB Cluster file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 7/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

8. 8 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 8/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

9. 9 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 9/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

10. 10 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 10/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

11. 11 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol (our implementation is close to Mencius) file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 11/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

12. 12 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol (our implementation is close to Mencius) GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 12/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

13. 13 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol (our implementation is close to Mencius) GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 13/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

14. 14 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol (our implementation is close to Mencius) GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution GR allows automatic distributed recovery file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 14/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

15. 15 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol (our implementation is close to Mencius) GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution GR allows automatic distributed recovery Supported on all MySQL platforms !! Linux, Windows, Solaris, OSX, FreeBSD file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 15/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

16. 16 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 terminology Write vs Writeset file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 16/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

17. 17 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Let's illustrate a table: file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 17/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

18. 18 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Now let's make a change file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 18/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

19. 19 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 and at commit time: file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 19/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

20. 20 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writesets Contain the hash for the rows PKs that are changed and in some cases the hashes of foreign keys or others dependencies that need to be captured (e.g. non NULL UKs). Writesets are gathered during transaction execution. file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 20/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

21. 21 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writesets Contain the hash for the rows PKs that are changed and in some cases the hashes of foreign keys or others dependencies that need to be captured (e.g. non NULL UKs). Writesets are gathered during transaction execution. Writes Called also write values, refers to the actual changes. Write values are also gathered during transaction execution. file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 21/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

22. 22 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 22/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

23. 23 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ mysql> insert into t2 values (1,2); file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 23/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

24. 24 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ mysql> insert into t2 values (1,2); pke: PRIMARY | test | t2 | 1 | 1 hash: 11853456929268668462 file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 24/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

25. 25 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ mysql> insert into t2 values (1,2); pke: PRIMARY | test | t2 | 1 | 1 hash: 11853456929268668462 mysql> update t2 set name=3 where id=1; file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 25/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

26. 26 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ mysql> insert into t2 values (1,2); pke: PRIMARY | test | t2 | 1 | 1 hash: 11853456929268668462 mysql> update t2 set name=3 where id=1; pke: PRIMARY | test | t2 | 1 | 1 hash: 10002085147685770725 pke: PRIMARY | test | t2 | 1 | 1 hash: 10002085147685770725 file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 26/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

27. 27 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples (2) +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | UNI | NULL | | | name2 | binary(1) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 27/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

28. 28 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples (2) +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | UNI | NULL | | | name2 | binary(1) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ mysql> insert into t3 values (1,2,3); file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 28/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.

29. 29 / 188 5/31/2019 Percona Live Austin 2019 - MySQL Group Replication... the Magic Explained v.2 Writeset - examples (2) +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | binary(1) | NO | PRI | NULL | | | name | binary(2) | YES | UNI | NULL | | | name2 | binary(1) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ mysql> insert into t3 values (1,2,3); pke: PRIMARY | test |t3 | 1 | 1 hash: 79134815725924853 pke: name | test |t3 | 2 hash: 11034644986657565827 file:///home/fred/ownCloud/Presentations/ORACLE/PerconaLive2019/MySQL Group Replication - the magic explained v2/MySQL Group Replication - Magic Explained.html#31 29/188 Copyright @ 2018 Oracle and/or its affiliates. All rights reserved.