The Latest MySQL Replication Features

最近对MySQL复制的增强使操作更加简单,进一步减少了管理开销,提供了更好的用户体验,并使MySQL能够在规模和/或紧密耦合的网络基础设施(如
群集环境。
加入产品背后的工程师,了解最新和最伟大的复制功能,以及这些功能如何使您能够创建坚如磐石、可扩展和弹性十足的数据库服务,以满足最苛刻的工作和故障负载。利用这个机会扩展你的mysql知识,了解更多关于hot的信息
主题,如组复制/innodb cluster。

展开查看详情

1.Replication: What's new in MySQL 8 Tiago Jorge Luís Soares Senior Software Engineer Software Development Director MySQL Replication MySQL Replication Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Percona Live Europe 2018 1

2.Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes 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 upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 2

3.Program Agenda Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 3

4.Program Agenda 1 Introduction 2 Use Cases 3 Enhancements in MySQL 8 (and 5.7) 4 Roadmap 5 Conclusion Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 4

5.1 Introduction Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 5

6. Boom Web Explodes Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 6

7.Today... – Technology mesh. – All things distributed. – Large amounts of data to handle, transform, store. – Offline periods are horribly expensive, simply unaffordable. – Go green requires dynamic and adaptative behavior. – Much more data to store – e.g. social media, “Look at all of my pictures!”; Monitoring – Keeping logs for N years! ; IoT – and much more. – Moving, transforming and processing data quicker than anyone else means having an edge over competitors. – It is a zoo. Distributed coordination and monitoring is key. Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 7

8. Replication Database Replication “The process of generating and reproducing multiple copies of data at one or more sites.”, Database Systems: A Practical Approach to Design, Implementation, and Management, Thomas M. Connolly, Carolyn E. Begg, Third Edition, 2002. App INSERT ... Server Server A INSERT ... B INSERT ... Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 8

9.MySQL Database Replication: Overview App INSERT ... binary log relay log binary log INSERT ... INSERT ... INSERT ... Server Comm. Server Framework A B Receiver Applier Meta-data Meta-data Update Update Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 9

10.MySQL Database Replication: Overview Persistent Persistent App log buffer log buffer Threaded INSERT ... applier binary log relay log binary log INSERT ... INSERT ... INSERT ... Server Comm. Server Framework A B Capture Send, Receive, Receiver Applier statements ACK, NACK, Meta-data Meta-data or data Heartbeating, Update Update changes. ... Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 10

11.MySQL Database Replication: Some Notes Binary Log • Logical replication log recording master changes (binary log). • Row or statement based format (may be intermixed). • Each transaction is split into groups of events. • Control events: Rotate, Format Description, Gtid, and more. GTID BEGIN E1 E2 ... COMMIT GTID BEGIN E1 E2 ... COMMIT Layout of the Binary Log. Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 11

12.MySQL Database Replication: Some Notes Coordination Between Servers transactions A B Since 3.23 asynchronous (native) transactions A B Since 5.5 acks semi-synchronous (plugin) A B C Since 5.7.17 And in MySQL 8 as of 8.0.1 transactions, membership, coordination group replication (plugin) Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 12

13.2 Use cases Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 13

14.Clustering Made Practical Replicate A utomate I ntegrate S cale E nhance Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 14

15.Replicate Group Replication • For highly available infrastructures where: – the number of servers has to grow or shrink dynamically; – with as little pain as possible. A B C D E Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 15

16.Automate Group Replication • Single-primary mode – Automatic PRIMARY/SECONDARY role assignment – Automatic new PRIMARY election on PRIMARY failures – Automatic setup of read/write modes on PRIMARY and SECONDARIES – Automatic global consistent view of which server is the PRIMARY S S S* S P S Old Primary Next Primary Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 16

17.Integrate Binary Log Hadoop App INSERT ... Kafka • Logical replication log – Extract, transform and load. INSERT ... Solr – MySQL fits nicely with other Server Transform technologies. A Replication log Lucene ... Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 17

18.Scale D Asynchronous Replication A B C . . • Replicate between clusters . – For disaster recovery I • Read Replicas – For read-scale out. Deploy asynchronous read replicas connected to the cluster M . . J K L . Z Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 18

19.Enhance InnoDB Cluster Application Application • InnoDB Cluster Integrated Solution MySQL Connector MySQL Connector – Group Replication for high availability. MySQL Router MySQL Router – Asynchronous Replication for Read Scale- out. – One-stop shell to deploy and manage the M HA cluster. MySQL Shell M M – Seamlessly and automatically route the ReplicaSet 1 workload to the proper database server in the cluster. S1 S2 S3 S4 S… – Hide failures from the application. Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 19

20.3 Enhancements in MySQL 8 (and 5.7) 3.1 Binary Log Enhancements 3.2 Operations 3.3 Performance 3.4 Monitoring 3.5 Other Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 20

21.New Metadata in the Binary Log Easy to extract, transform and load into other systems. • New Metadata – Easy to decode what is in the binary log. App Gtid_log_event Transaction length, … – Further facilitates connecting INSERT ... MySQL to other systems using the Commit timestamps Server Table_map event binary log stream. A Types, primary key, … Sign, charset, … – Capturing data changes through Write_rows the binary log is simplified. Commit Binary log – Also more stats showing where the data is/was at a certain point in time. Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 21

22.3 Enhancements in MySQL 8 (and 5.7) 3.1 Binary Log Enhancements 3.2 Operations 3.3 Performance 3.4 Monitoring 3.5 Other Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 22

23.Multi-Source Replication Filters Replicate, Filter, Aggregate, Query users users only users bio A C posts groups comments B users bio groups posts comments Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 23

24. Backported to 5.7.20 Preventing Updates On Replicas that Leave the Cluster Automatic protection against involuntarily tainting of offline replicas A joins a single primary cluster and A leaves the group and remains it allows update operations prior read only until the DBA reverts. to joining. A becomes a secondary and is automatically set to read only. A A B C A B C B C Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 24

25. Backported to 5.7.20 Primary Election Weights Choose next primary by assigning election weights to the candidates. B is not in the group anymore. C has higher weight than A. B is the primary. C is elected the new primary. B w=100 A B C A C A C w=50 w=100 w=90 w=50 w=90 w=50 w=90 Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 25

26. New in 8.0.12 Backported to 5.7.24 Automatically Abort Replicas that Leave the Group Automatically Shutdown When Replica Leaves the Group Involuntarily A leaves the group involuntarily A shuts itself down, thus becoming (failed to apply changes, network effectively unavailble for both reads partitioned, etc). and write operations. A is a secondary and was automatically set to read only. A A A B C B C B C @@group_replication_exit_state_action={ READ_ONLY | ABORT_SERVER } Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 26

27. New in 8.0.13 Trigger Primary Election Online User tells current primary to give up its role and assign it to another server. Now I want A to be the primary, not B. B is the primary. A is the primary. B stepped down. A B C A B C A B C Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 27

28. New in 8.0.13 Change Group Mode Online User can specify on which mode the group operates. Now I want the group operating in multi-primary mode. Single-primary mode, Return to single-primary mode. B is the primary. A B C A B C A B C Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 28

29. New in 8.0.13 Relaxed Member Eviction User controls the amount of time to wait until others decide to evict a member from the group. Hold on. Lets wait for sometime! Maybe it is just too busy to talk to us I think A is not around anymore. right now... See… I told you. A is back! Lets kick it out of the group! Good thing we waited. OK. Lets give it some more time! A B C A B C A B C UNREACHABLE Wdnesday, 7th Nov 2018, Frankfurt am Main, GER Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Percona Live Europe 2018 29