MySQL 8数据库复制技术介绍

1、MySQL 8数据库复制技术MySQL 8.0: Whats New In ReplicationIntroductionUse CasesEnhancements in MySQL 8ConclusionProgram Agenda12342Introduction13Database ReplicationServerBServerAAppINSERT .INSERT .INSERT .Replication4“The process of generating and reproducingmultiple copies of data at one or more sites.”,Da

2、tabase Systems: A Practical Approach to Design, Implementation, and Management, Thomas M. Connolly, Carolyn E. Begg, Third Edition, 2002.MySQL Database Replication: OverviewINSERT .ServerBbinary logINSERT .relay logINSERT .ServerAbinary logAppReceiver Meta-data UpdateApplier Meta-data UpdateINSERT .

3、Comm.Framework5MySQL Database Replication: OverviewINSERT .ServerBbinary logINSERT .relay logINSERT .ServerAbinary logAppReceiver Meta-data UpdateApplier Meta-data UpdateINSERT .Persistent log bufferThreaded applierSend, Receive,ACK, NACK,Heartbeating,.Comm.FrameworkCapture statements or data change

4、s.Persistent log buffer6MySQL Database Replication: Some NotesBinary LogLogical 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.Layout of

5、 the Binary Log.GTIDBEGINE1E2.COMMITGTIDBEGINE1E2.COMMIT7MySQL Database Replication: Some NotesCoordination Between ServersABSince 3.23ABsemi-synchronous (plugin)ACBgroup replication (plugin)transactionstransactionsSince 5.5asynchronous (native)transactions, membership, coordination8acksSince 5.7.17

6、And now in MySQL 8 as of 8.0.1Use cases29Clustering Made PracticalReplicate Automate I ntegrate ScaleEnhance10ReplicateGroup ReplicationFor highly available infrastructures where:the number of servers has to grow or shrink dynamically;with as little pain as possible.ABCDE11AutomateGroup ReplicationS

7、ingle-primary modeAutomatic PRIMARY/SECONDARY role assignmentAutomatic new PRIMARY election on PRIMARY failuresAutomatic setup of read/write modes on PRIMARY and SECONDARIESAutomatic global consistent view of which server is the PRIMARYSSS*SPSNext PrimaryOld Primary12IntegrateBinary LogLogical repli

8、cation logExtract, transform and load.MySQL fits nicely with othertechnologies.INSERT .ServerAReplication logAppINSERT .HadoopKafkaSolrLucene.Transform13ScaleAsynchronous ReplicationReplicate between clustersFor disaster recoveryRead ReplicasFor read-scale out. Deploy asynchronous read replicas conn

9、ected to the clusterABCJKLDIZM.14InnoDB ClusterEnhanceS115S2S3S4SMMMApplicationMySQL ConnectorMySQL RouterApplicationMySQL ConnectorMySQL RouterMySQL ShellHAReplicaSet 1InnoDB Cluster Integrated SolutionGroup Replication for high availability.Asynchronous Replication for Read Scale-out.One-stop shel

10、l to deploy and manage thecluster.Seamlessly and automatically route the workload to the proper database server in the cluster.Hide failures from the application.Enhancementsin MySQL 3.4Binary Log Metadata Operations Monitoring PerformanceOther3.516New Metadata in the Binary LogEasy to ex

11、tract, transform and load into other systems.ServerAGtid_log_event Transaction length, Commit timestamps Table_map event Types, primary key, Sign, charset, Write_rowsCommitBinary logAppINSERT .17New MetadataEasy to decode what is in thebinary log.Reduces impedance while connecting MySQL to other sys

12、tems using the binary log.Capturing data changes throughthe binary log is simplified.Also more stats showing where the data is/was at a certain point in time.Enhancementsin MySQL 3.4Binary Log Metadata Operations Monitoring PerformanceOther3.518Multi-Source Replication FiltersReplicate, F

13、ilter, Aggregate, QueryABCusersbiogroupsuserspostscommentsusersbiogroupspostscommentsonly users19Preventing Updates On Replicas that Leave the ClusterAutomatic protection against involuntarily tainting of offline replicasBCABCAA joins a single primary cluster and it allows update operations prior to

14、 joining.A becomes a secondary and is automatically set to read only.BCAA leaves the group and remains read only until the DBA reverts.20Primary Election WeightsChoose next primary by assigning election weights to the candidates.Bw=100Cw=90Aw=50Bw=100Cw=90Aw=50Cw=90Aw=50B is the primary.B is not in

15、the group anymore.C has higher weight than A. C is elected the new primary.21AppsAppsOnline and Automatic Cluster Performance ManagementNew options to fine tune the cluster automatic flow control in 8.0.BCABCABCAB is struggling to keep up.BA iasnsdtrCugthglriontgtlteo themselvkeeseapuutopm. atically

16、.B is not struggling any more.System adjusted Itself.B has a chance to catch up.Apps22Enhancementsin MySQL 3.4Binary Log Metadata Operations Monitoring PerformanceOther3.623Monitor Lag With Microsecond PrecisionThrough the entire asynchronous topologyABCDHow much time does my data take to

17、 reachD coming from A?24Monitor Lag With Microsecond PrecisionFrom the immediate masterABCDHow much time does my data originated inA takes to flow from B to C?25Monitor Lag with Microsecond PrecisionFor each stage of the replication applier processDDataWorker ThreadsRelay LogCoordinator ThreadReceiv

18、erThread26Per Stage Timestamps User can monitor how much time it takes for a specific transaction to traverse the pipeline.Version, Role and moreGlobal Cluster Stats Available on Every ServerABCA is primary.B, C are secondaries. A: 5.7.20B, C: 8.0.3A is primary.B, C are secondaries. A: 5.7.20B, C: 8

19、.0.3A is primary.B, C are secondaries.A: 5.7.20B, C: 8.0.327Query one Replica, Get statusof allEvery replica reports cluster-wide information about roles and versions of the members of the cluster.Also available at any replica arecluster-wide status.Enhancementsin MySQL 3.4Binary Log Meta

20、data Operations Monitoring PerformanceOther3.528Highly Efficient Replication ApplierWrite set parallelization5000045000400003500030000250002000015000100005000012464128256Updates/second Applies on the Replica81632Number of Clients on the MasterApplier Throughput: Sysbench Update IndexCOMMIT_ORDERWRIT

21、ESETWRITESET_SESSION29Highly Efficient Replication Applier30Write set parallelizationWRITESET dependency tracking allows applying a single threadedworkload in parallel. Delivers the best throughput of the three dependency trackers, at any concurrencylevel.WRITESET_SESSION in addition to writesets tr

22、acks sessions dependencies as well. Two transactions executed on the same session are always scheduled in execution order on replica servers.Fast Group Replication recovery time to catch up.Replica quickly online by using WRITESETFast Group Replication Recovery109876543210Sysbench RW at 33% capacity

23、 (workload: 9K TPS on 64 threads)Sysbench RW at 66% capacity (workload: 18K TPS on 64 threads)Time to cacth-up per time of worload missing (ratio)Group Replication Recovery Time: SysbenchUpdate Index (durable settings)MySQL 5.7.20MySQL 8.0.3109876543210Sysbench RW at 33% capacity (workload: 4K TPS o

24、n 64 threads)Sysbench RW at 66% capacity (workload: 8K TPS on 64 threads)Time to cacth-up per time of worload missing (ratio)Group Replication Recovery Time: SysbenchRW (durable settings)MySQL 5.7.20MySQL 8.0.331High Cluster ThroughputMore transactions per second while sustaining zero lag on any rep

25、lica8641632Number of Clients on the MasterAsynchronous Replication Sustained Throughput(Sysbench Update Index, non-durable settings)MySQL 5.7MySQL 8.0.3864Updates per second1632Number of Clients on the MasterAsynchronous Replication Sustained Throughput(Sysbench Update Index, durable settings)MySQL

26、5.7MySQL 8.0.345000400003500030000250002000015000100005000032High Cluster Throughput33More transactions per second while sustaining zero lag on any replicaAt lower thread count, the throughput of the system doubles in MySQL 8.0 comparedto MySQL 5.7 on durable settings.At lower thread count, the thro

27、ughput of the system more than doubles in MySQL 8.0compared to MySQL 5.7 on non-durable settings.Replicate only changed fields of documents (Partial JSON Updates)Efficient Replication of JSON Documents2000010000030000400005000060000Entire JSONPartial JSONBytes per transactionBinary Log Space Per Tra

28、nsactionFULLMINIMAL34Numbers are from a speciallydesigned benchmark:tables have 10 JSON fields,each transaction modifiesaround 10% of the data0.0500.01000.01500.02000.02500.0Transactions per secondThroughput on the Master: Partial JSON vs Complete JSON48163264Replicate only fields of the document th

29、at changed (Partial JSON Updates)Efficient Replication of JSON Documents0100020003000500040006000700080009000Throughput on the Slave: Partial JSON vs Ccomplete JSON48163264F1ULLMIN2IMAL COMPLETE JSONF3ULLMIN4IMAL PARTIAL JSONFULLMINIMAL COMPLETE JSONFULLMINIMAL PARTIAL JSON35Enhancementsin MySQL 83.

30、Binary Log Metadata Operations Monitoring PerformanceOther3.536Changes to defaults in MySQL 837High performance replication enabled out-of-the-boxBinary log is on by default.Logging of slave updates is on by default.Replication metadata is stored in InnoDB tables by default instead of fil

31、es.Row-based applier uses hash scans to find rows instead of table scans.Transaction write-set extraction is on by default.Binary log expiration is set to 30 days by default.Server-id is set to 1 by default instead of 0.Other MySQL 8 Replication Enhancements38Monitoring: Monitor replication even whe

32、n disk fullMonitoring: Current query being applied, even for row-based replicationMonitoring: Replication filters statistics in performance schemaMonitoring: Group Replication threads instrumented and shown inperformance schemaRecoverability: Recover DDL and binary log together after a crashOther My

33、SQL 8 Replication Enhancements39Operations: Restore global transaction identifiers metadata on a non-empty serverOperations: Specify binary log file number after RESET MASTEROperations: Specify when binary log files are automatically purged (withsecond precision)Operations: SAVEPOINT support when wr

34、ite sets are being extractedTroubleshooting: Dynamic and high performance debugging of groupreplication inter-node messagingRoadmap340GR 0.8.0 labs (beta)replication stream compre sionSSL supportIP whitelistingerror log enhancementssplit brain handlingThe Road to MySQL 8 Group Replication and InnoDB ClustersMySQL 5.6.10GR 0.2.0 labsHello world!GR 0.3.0 labssupport for corosync 2.xServer side changesGR 0.4.0 labsversion handling.cauto-infields handli grecovery enha