mysql tech tour 2015 - 5.7 replication
TRANSCRIPT
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |Tuesday, October 20, 2015 Oracle Confidential – Restricted
MySQL 5.7 Replication Update
1
The Latest and Greatest MySQL 5.7 Replication Features and More!
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor StatementThe 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.
Tuesday, October 20, 2015 Oracle Confidential – Restricted 2
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Tuesday, October 20, 2015 Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program AgendaBackground
Replication Features in MySQL 5.7
News From The Lab!
The Road Ahead!
Conclusion
1
2
3
4
5
Tuesday, October 20, 2015 Oracle Confidential – Restricted 4
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 20, 2015 Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Background: Replication Components
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 6
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Background: Replication Components• Binary Log– File based logical log that records the changes on the master.– Statement or Row based format (may be intermixed).– Each transactions is split into groups of events.– Control events: Rotate, Format Description, Gtid, ...
Tuesday, October 20, 2015
Layout of a Binary Log File
BEGIN ...E1 E2 COMMIT BEGIN ...E1 E2 COMMIT
Oracle Confidential – Restricted 7
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
M S
S
S
S
M
write clients read clientsread clients
write clients
Morereads?More
slaves!
Read scale-outBackground: What is Replication Used For?
Tuesday, October 20, 2015 Oracle Confidential – Restricted 8
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
M M?
write clients write clients
Morewrites?More
Masters?
M?
M?
MySQL Fabric helps sharding your data with MySQL...
What about write scale-out?
Tuesday, October 20, 2015 Oracle Confidential – Restricted 9
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
C
B
A
C
B
ACrash
C
B
A
B is thenew master
Uh Oh! Whew!
Redundancy: If master crashes, promote slave to masterBackground: What is Replication Used For?
Tuesday, October 20, 2015 Oracle Confidential – Restricted 10
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
M
S
write clients
business intelligent client applicationsreporting client applicationsbig queries client applications
On-line Backup and ReportingBackground: What is Replication Used For?
Tuesday, October 20, 2015 Oracle Confidential – Restricted 11
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
CB
BAAC
Image fromwww.ginkgomaps.com
Background: What is Replication Used For?
Tuesday, October 20, 2015 Oracle Confidential – Restricted 12
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Features in MySQL 5.72
Tuesday, October 20, 2015 Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Features in MySQL 5.7 Usability / Online
2
Tuesday, October 20, 2015
2.1
Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Reconfiguration of Global Transaction Identifiers
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 15
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Reconfiguration of Global Transaction Identifiers• The procedure is online.– Both reads and writes are allowed while global transaction identifiers are being
turned on or off in the entire replication cluster.
• No need to synchronize servers, ever.• No need to restart servers.• No need to change replication topology.–Works in arbitrary topologies.
• Should anything happen in the middle of the procedure, you can always roll back.
Tuesday, October 20, 2015 Oracle Confidential – Restricted 16
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Reconfiguration of Global Transaction Identifiers• Over simplified procedure to turn on global transaction identifiers:
• Details: http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html
Tuesday, October 20, 2015
1) SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; (on every server)2) SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; (on every server)3) wait for a bit longer than your replication lag4) SET @@GLOBAL.GTID_MODE = ON; (on every server)
Oracle Confidential – Restricted 17
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Reconfiguration of Replication Filters
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 18
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Reconfiguration of Replication Filters• Change Slave's Replication Filters dynamically.– No need to stop and restart the slave server for setting new replication filtering rules.– All slave filters are supported.– Values can be input in various character sets.
Tuesday, October 20, 2015
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB= (db1, db2)
Oracle Confidential – Restricted 19
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Switch/Fail-over to a new Master
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 20
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Change master from A to B without stopping the applier threads.Online Switch/Fail-over to a new Master
Tuesday, October 20, 2015
C
B
A
C
B
ACrash
C
B
A
B is thenew master
Uh Oh! Whew!
Oracle Confidential – Restricted 21
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Change master from A to B without stopping the applier threads.Online Switch/Fail-over to a new Master
Tuesday, October 20, 2015
C
B
A
C
B
ACrash
C
B
AUh Oh! Whew!
DBA did not need C to stop applying its relay log to change C's master from A to B.
Oracle Confidential – Restricted 22
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Online Switch/Fail-over to a new Master• Enables more online operations during fail-over:
• Stopping, changing master and restarting the receiver thread are all done while the applier thread is running.• Change applier properties while the receiver thread is working:
Tuesday, October 20, 2015
mysql> STOP SLAVE IO_THREAD;mysql> CHANGE MASTER TO MASTER_HOST='master2', …;mysql> START SLAVE IO_THREAD;
mysql> STOP SLAVE SQL_THREAD;mysql> CHANGE MASTER TO MASTER_DELAY=3600, …;mysql> START SLAVE SQL_THREAD;
Oracle Confidential – Restricted 23
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Improved Replication Monitoring
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 24
DBA
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Improved Replication Monitoring
• Monitoring through SQL.• Logically unrelated information into different places.• Extensible, can be adapted to new features. • More accurate and consistent identifier names.• Master-slave, Multi-source, Group Replication support.
Performance Schema Replication Tables
Tuesday, October 20, 2015 Oracle Confidential – Restricted 25
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Improved Replication Monitoring
Tuesday, October 20, 2015
Connection Configuration
Connection Status
Applier Configuration
Applier Status
(Slave) Receiver and Applier Status
Applier / CoordinatorStatus
WorkersStatus
Oracle Confidential – Restricted 26
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Features in MySQL 5.7 Usability / Online Performance
2
Tuesday, October 20, 2015
2.1
2.2
Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Features in MySQL 5.7 Usability / Online Performance
Dependability
2
Tuesday, October 20, 2015
2.1
2.2
2.3
Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Loss-less Semi-sync Replication
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 29
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Loss-less Semi-sync Replication
Tuesday, October 20, 2015
Master
Slave
T1: INSERT INTO t1 VALUES (1000)
ACK
Time
T2: SELECT * FROM t1; empty set
execute prepare binlog
relay log
commitexecute
Oracle Confidential – Restricted 30
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Loss-less Semi-sync Replication• Master waits for slave's ACK before committing (as opposed to: master
waits for slave's ACK after committing).– Therefore, concurrent transactions do not see changes while this transaction waits for
ack.
• Should a master fail, then any transaction that it may have externalized is also persisted on a slave.• User can choose between the original semi-sync behavior and the new one.
Tuesday, October 20, 2015
mysql> SET rpl_semi_sync_master_wait_point= [AFTER_SYNC|AFTER_COMMIT]
Oracle Confidential – Restricted 31
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Semi-sync Replication – Wait for Multiple ACKs
Tuesday, October 20, 2015
Insert...
Insert...
B
binary log
Insert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 32
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Semi-sync Replication – Wait for Multiple ACKs• Master does not commit transaction until it receives N ACKs from N slaves.• Dynamically settable:
Tuesday, October 20, 2015
mysql> SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N
Oracle Confidential – Restricted 33
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Semi-sync Replication – Wait for Multiple ACKs• Master does not commit transaction until it receives N ACKs from N slaves.• Dynamically settable:
Tuesday, October 20, 2015
mysql> SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N
Master
Slave 2
T1: COMMIT
ACKSlave 1
ACK
T1: COMMITsucceeds
mysql> SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= 2
Slave 3Time
relay log
relay log
relay log
Oracle Confidential – Restricted 34
ACK
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Features in MySQL 5.7 Usability / Online Performance
Dependability
Flexibility
2
Tuesday, October 20, 2015
2.1
2.2
2.3
2.4
Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Storing Global Transaction Identifiers in a Table
Tuesday, October 20, 2015
Insert...
BInsert...
relay log
Insert...
A
binary log
ClientSenderthread
Receiverthread
ApplierThreads
ReceiverMeta-data
Update
ApplierMeta-data
Update
Network
Oracle Confidential – Restricted 36
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Storing Global Transaction Identifiers in a Table
• Slaves can use GTIDs with binary logs disabled.– Slaves that are never candidates to become a master can still use GTIDs for auto
positioning.
Use Cases
Tuesday, October 20, 2015 Oracle Confidential – Restricted 37
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Storing Global Transaction Identifiers in a Table
• GTIDs are saved into a mysql system table (range-compressed).
• GTIDs are inserted into the table as transactions commit.• A compression thread runs periodically and compresses the table into
ranges.– New global variable controls the period: gtid_executed_compression_period.
Mechanics
Tuesday, October 20, 2015
CREATE TABLE gtid_executed( source_uuid CHAR(36) NOT NULL, interval_start BIGINT NOT NULL, interval_end BIGINT NOT NULL, PRIMARY KEY(source_uuid, interval_start) );
mysql> SET GLOBAL gtid_executed_compression_period= N;(N – number of transactions)
Oracle Confidential – Restricted 38
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Storing Global Transaction Identifiers in a Table
• Binary Log Enabled– Store GTID in binary log (transactionally).– Copy GTID set to table on binary log rotation.
• Binary Log Disabled– Store GTID in table (transactionally).– New transactions are not assigned a GTID.
• Always store GTIDs transactionally.
Mechanics
Tuesday, October 20, 2015 Oracle Confidential – Restricted 39
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Multi-Source Replication
Tuesday, October 20, 2015
S
M M Slave can have more than one master.M M
The need for gathering data in a central server:• Integrated backup;• Complex queries for analytics purposes;• Data HUB for inter-cluster replication.
Oracle Confidential – Restricted 40
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Multi-Source Replication• A server (slave) can replicate from multiple sources (masters).• Multiple channels (channel: connection thread, relay log, applier threads)
that can be stopped started individually.• Integrated with Multi-threaded Slave.– Each channel has its own multi-threaded applier set of threads.
• Integrated with the new P_S tables.– replication_applier_status_by_coordinator shows multiple entries, one per
channel/source applier.– replication_connection_status shows multiple entries, one per connection to different
sources.
Tuesday, October 20, 2015 Oracle Confidential – Restricted 41
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Multi-Source Replication• Integrated with GTIDs.• Integrated with crash-safe tables.– Progress state is stored in these tables for recoverability purposes.
• Virtually no limit on the number of sources (capped to 256, but can be changed if server binary is rebuilt).• Able to manage each source separately.
Tuesday, October 20, 2015 Oracle Confidential – Restricted 42
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Features in MySQL 5.7 Usability / Online Performance
Dependability
Flexibility
Misc
2
Tuesday, October 20, 2015
2.1
2.2
2.3
2.4
2.4
Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
“Smaller”, yet interesting, enhancements!• Multi-threaded applier is able to retry failed transactions.• Option to make the Multi-threaded applier preserve commit order.• SSL options for mysqlbinlog tool.• Rewrite DB rules for the mysqlbinlog tool.• Function to wait for transactions to be applied, regardless of the replication stream
they come from.• Options to track global transaction identifiers in the return packet of the mysql
protocol.• Support for XA transactions when the binary log is enabled.• Change in the defaults. E.g., binlog_format=ROW and sync_binlog=1.
Tuesday, October 20, 2015 Oracle Confidential – Restricted 44
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL Router• Motivation– The need for transparent integration with Fabric.• No need to upgrade existing connectors.• Use Fabric with connectors that do not support it (e.g.,
PHP, Ruby, Perl, C).
– The need for read-write and read-only automatic routing.• Do not need to know which server is the master.• Transparent fail-over support for a new master.
• End Result– Generic and versatile framework: MySQL Router.
Tuesday, October 20, 2015 Oracle Confidential – Restricted
NEW
M
Router
App
M M
Fabric
45
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL Router• Highlights– High Performance– Plugin Driven Architecture– Simple to setup, configure and deploy.
• Features– Connection routing and simple load balancing.– Seamless fail-over based on Fabric HA Groups.– Fail-over without Fabric (needs 3rd party tool to
handle fail-over itself).– reducing conflicts when deployed together with
Group Replication.
Tuesday, October 20, 2015 Oracle Confidential – Restricted
M
Router
App
M M
Fabric
NEW
46
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
The Road Ahead!4
Tuesday, October 20, 2015 Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
What is Next?• MySQL Group Replication– Rapid releases.– Improve performance, stability and usability.
• MySQL Replication Usability– Instrument even more replication and extend replication P_S tables– Simpler administrative commands
•MySQL Replication Performance– Continue to improve multi-threaded (slave) applier– Continue to improve semi-sync
• Orchestration and MySQL Router inter-operabilityTuesday, October 20, 2015 Oracle Confidential – Restricted 48
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Focus
MySQLReplication
Tuesday, October 20, 2015 Oracle Confidential – Restricted 49
Performance High Availability
Ease of Use Integration
Recoverability
Automation of Fail-over and crash recovery
Configurability
GTIDs
HPC optimizations
MySQL RouterIntegration
MoreModularizationPluggability
ModernizationOf Replication Interfaces
MySQL FabricIntegration
Multi-threaded Applier
Address ContentionPoints
MySQL Group Replication
LeverageConsensus
FurtherP_S Instrumentation
Simplify UI
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Summary7
Tuesday, October 20, 2015 Oracle Confidential – Restricted
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Summary• Replication feature set in MySQL 5.7 shows many improvements:– Semi-sync is more flexible and faster.– Less contention on the binary log.– Slave is faster and provides an inter-transaction parallelization scheme.–More online reconfiguration: Global Transaction Identifiers, filters, configuration.– Improved monitoring.– Even more flexible replication with multi-source enabling new deployment scenarios.– Enhancements all over: transaction retries, sequential commits, XA support, new
replication functions, security.• Lab releases provide a sneak peek at what is coming - a new replication plugin and
exciting new infrastructure: MySQL Group Replication and MySQL Router.
Tuesday, October 20, 2015 Oracle Confidential – Restricted 51
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Where to go from here?• Packages– http://dev.mysql.com– http://labs.mysql.com
• Reference Documentation– http://dev.mysql.com/doc/#manual
• Blogs from the Engineers (news, quirks, technical information and much more)– http://mysqlhighavailability.com
Tuesday, October 20, 2015 Oracle Confidential – Restricted 52