Best practices for MySQL High Availability Feb 2017

请于2017年2月7日上午10:00(东部标准时间上午10:00/太平洋标准时间上午7:00)(UTC-8)加入Percona的首席布道者Colin Charles,因为他提出了“MySQL高可用性的最佳实践”。
您如何在MySQL复制、MHA、DRBD、钨丝或Galera集群之间进行选择?您是否集成了起搏器和心跳(如Percona Replication Manager)?NDB集群是否适合您的工作负载?你总是在启用GTID的情况下运行吗?在云中工作会给混合带来更多乐趣,比如地理冗余。现在甚至有“现代”工具,如Orchestrator、Consul、Mariadb MaxScale和MySQL路由器来帮助实现自动化。


1.Best practices for MySQL High Availability Colin Charles, Chief Evangelist, Percona Inc. / | @bytebot on Twitter Percona Webminar 7 February 2017

2. whoami • Chief Evangelist (in the CTO office), Percona Inc • Founding team of MariaDB Server (2009-2016), previously at Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include Fedora Project (FESCO), • MySQL Community Contributor of the Year Award winner 2014 2




6. Uptime Percentile target Max downtime per year 90% 36 days 99% 3.65 days 99.5% 1.83 days 99.9% 8.76 hours 99.99% 52.56 minutes 99.999% 5.25 minutes 99.9999% 31.5 seconds 6

7.Estimates of levels of availability Level of Method Availability Simple replication 98-99.9% Master-Master/MMM 99% SAN 99.5-99.9% DRBD, MHA, Tungsten 99.9% Replicator NDBCluster, Galera Cluster 99.999% 7

8. HA is Redundancy • RAID: disk crashes? Another works • Clustering: server crashes? Another works • Power: fuse blows? Redundant power supplies • Network: Switch/NIC crashes? 2nd network route • Geographical: Datacenter offline/destroyed? Computation to another DC 8

9. Durability • Data stored on disks • Is it really written to the disk? • being durable means calling fsync() on each commit • Is it written in a transactional way to guarantee atomicity, crash safety, integrity? 9

10. High Availability for databases • HA is harder for databases • Hardware resources and data need to be redundant • Remember, this isn’t just data - constantly changing data • HA means the operation can continue uninterrupted, not by restoring a new/backup server • uninterrupted: measured in percentiles 10

11. Redundancy through client-side XA transactions • Client writes to 2 independent but identical databases • HA-JDBC ( • No replication anywhere 11

12. InnoDB “recovery” time •innodb_log_file_size • larger = longer recovery times • Percona Server 5.5 (XtraDB) - innodb_recovery_stats 12

13. Redundancy through shared storage • Requires specialist hardware, like a SAN • Complex to operate • One set of data is your single point of failure • Cold standby • failover 1-30 minutes • this isn’t scale-out • Active/Active solutions: Oracle RAC, ScaleDB 13

14. Redundancy through disk replication • DRBD • Linux administration vs. DBA skills • Synchronous • Second set of data inaccessible for use • Passive server acting as hot standby • Failover: 1-30 minutes • Performance hit: DRBD worst case is ~60% single node performance, with higher average latencies 14

15. Redundancy through MySQL replication • MySQL replication • Tungsten Replicator • Galera Cluster • MySQL Cluster (NDBCLUSTER) • Storage requirements are multiplied • Huge potential for scaling out 15

16. MySQL Replication • Statement based generally • Row based became available in 5.1, and the default in 5.7 • mixed-mode, resulting in STATEMENT except if calling • UUID function, UDF, CURRENT_USER/USER function, LOAD_FILE function • 2 or more AUTO_INCREMENT columns updated with same statement • server variable used in statement • storage engine doesn’t allow statement based replication, like NDBCLUSTER 16

17. MySQL Replication II • Asynchronous by default • Semi-synchronous plugin in 5.5+ • However the holy grail of fully synchronous replication is not part of standard MySQL replication (yet?) • MariaDB Galera Cluster is built-in to MariaDB Server 10.1 17

18. The logs • Binary log (binlog) - events that describe database changes • Relay log - events read from binlog on master, written by slave i/o thread • master_info_log - status/config info for slave’s connection to master • relay_log_info_log - status info about execution point in slave’s relay log 18

19. Semi-synchronous replication • semi-sync capable slave acknowledges transaction event only after written to relay log & flushed to disk • timeout occurs? master reverts to async replication; resumes when slaves catch up • at scale, Facebook runs semi-sync: http:// replication-at-facebook.html 19

20. MySQL Replication in 5.6 • Global Transaction ID (GTID) • Server UUID • Binary log (binlog) checksums • Ignore (master) server IDs • Crash safe binlog and relay (filtering) logs • Per-schema multi-threaded • Time delayed replication slave • Parallel replication (per • Group commit in the binary database) log 20

21. Replication: START TRANSACTION WITH CONSISTENT SNAPSHOT • Works with the binlog, possible to obtain the binlog position corresponding to a transactional snapshot of the database without blocking any other queries. • by-product of group commit in the binlog to view commit ordering • Used by the command mysqldump--single-transaction -- master-data to do a fully non-blocking backup • Works consistently between transactions involving more than one storage engine • • Percona Server made it better, by session ID, and also introducing backup locks 21

22. Multi-source replication • Multi-source replication - (real-time) analytics, shard provisioning, backups, etc. • @@default_master_connection contains current connection name (used if connection name is not given) • All master/slave commands take a connection name now (like CHANGE MASTER “connection_name”, SHOW SLAVE “connection_name” STATUS, etc.) 22

23. Global Transaction ID (GTID) • Supports multi-source replication • GTID can be enabled or disabled independently and online for masters or slaves • Slaves using GTID do not have to have binary logging enabled. • (MariaDB) Supports multiple replication domains (independent binlog streams) • Queries in different domains can be run in parallel on the slave. 23

24.Why MariaDB GTID is different compared to 5.6? • MySQL 5.6 GTID does not support multi-source replication • Supports —log-slave-updates=0 for efficiency • Enabled by default • Turn it on without having to restart the topology 24

25. Parallel replication • Multi-source replication from different masters executed in parallel • Queries from different domains are executed in parallel • Queries that are run in parallel on the master are run in parallel on the slave (based on group commit). • Transactions modifying the same table can be updated in parallel on the slave! • Supports both statement based and row based replication. 25

26. All in… sometimes it can get out of sync • Changed information on slave directly • --replication-ignore-db with fully qualified queries • Statement based replication • Binlog corruption on master • non-deterministic SQL (UPDATE/ DELETE with LIMIT and without • PURGE BINARY LOGS issued and ORDER BY) not enough files to update slave • triggers & stored procedures • read_buffer_size larger than max_allowed_packet • Master in MyISAM, slave in InnoDB (deadlocks) • Bugs? 26

27. Replication Monitoring • Percona Toolkit is important • pt-slave-find: find slave information from master • pt-table-checksum: online replication consistency check • executes checksum queries on master • pt-table-sync: synchronise table data efficiently • changes data, so backups important 27

28. Replication Monitoring with PMM • 28

29. mysqlbinlog versions • ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 56, event_type: 30 • 5.6 ships with a “streaming binlog backup server” - v.3.4; MariaDB 10 doesn’t - v.3.3 (fixed in 10.2 - MDEV-8713) • GTID variances! 29