Archive for MySQL Replication

MySQL Replication: What’s New in MySQL 5.7 and Beyond – webinar replay

MySQL Replication LogoLuís Soares and I recently hosted a webinar which explained the latest developments in MySQL Replication.

The webinar replay is now available to download from here.

Details:

Continuing in the footsteps of its predecessor, MySQL 5.7 is set to be a groundbreaking release. In this webinar, the engineers behind the product provide insights into what’s new for MySQL replication in the latest 5.7 Development Milestone Release and review the early access features available via labs.mysql.com. The next generation of replication features cover several technical areas such as better semi-synchronous replication, an enhanced multithreaded slave (per-transaction parallelism), improved monitoring with performance schema tables, online configuration changes, options for fine-tuning replication performance, support for more-advanced topologies with multisource replication, and much more. This is also a great chance to learn about MySQL Group Replication – the next generation of active-active, update-anywhere replication for MySQL.

Seize the opportunity to learn how you will be able to leverage MySQL 5.7 replication to grow your business.





Upcoming Webinar – MySQL Replication: What’s New in MySQL 5.7 and Beyond

MySQL Replication LogoOn Tuesday 25th November, Luís Soares and I will be hosting a webinar which explains the latest developments in MySQL Replication. As always the webinar is free but please register here.

Details:

Continuing in the footsteps of its predecessor, MySQL 5.7 is set to be a groundbreaking release. In this webinar, the engineers behind the product provide insights into what’s new for MySQL replication in the latest 5.7 Development Milestone Release and review the early access features available via labs.mysql.com. The next generation of replication features cover several technical areas such as better semi-synchronous replication, an enhanced multithreaded slave (per-transaction parallelism), improved monitoring with performance schema tables, online configuration changes, options for fine-tuning replication performance, support for more-advanced topologies with multisource replication, and much more. This is also a great chance to learn about MySQL Group Replication – the next generation of active-active, update-anywhere replication for MySQL.

Seize the opportunity to learn how you will be able to leverage MySQL 5.7 replication to grow your business.

When:

  • Tue, Nov 25: 09:00 Pacific time (America)
  • Tue, Nov 25: 10:00 Mountain time (America)
  • Tue, Nov 25: 11:00 Central time (America)
  • Tue, Nov 25: 12:00 Eastern time (America)
  • Tue, Nov 25: 15:00 São Paulo time
  • Tue, Nov 25: 17:00 UTC
  • Tue, Nov 25: 17:00 Western European time
  • Tue, Nov 25: 18:00 Central European time
  • Tue, Nov 25: 19:00 Eastern European time
  • Tue, Nov 25: 22:30 India, Sri Lanka
  • Wed, Nov 26: 01:00 Singapore/Malaysia/Philippines time
  • Wed, Nov 26: 01:00 China time
  • Wed, Nov 26: 02:00 日本
  • Wed, Nov 26: 04:00 NSW, ACT, Victoria, Tasmania (Australia)

Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.





MySQL Replication – Multi-Threaded Slave just got a whole lot faster

MySQL Replication Logo
A new MySQL MySQL 5.7 Multi-threaded slave labs load has now been published on MySQL Labs. This represents a signifficant step in the process of speeding up the processing of replication events on the slave – letting it keep up with the master.

The original implementation of MTS made a simple assumption about what events could safely be applied in parallel (with no dependency on the ordering). That assumption was that different schemas had no dependencies on each other and so if you had 5 schemas then one event from each schema could be applied in parallel (this is described in MySQL 5.6 GA – Replication Enhancements). This approach delivered a much higher throughput on the slave but only if you were using multiple schemas – perfect for many deployments (such as multi-tenant systems) but if you only had a single database then it didn’t help.

Last year we released a new version which removed that dependency on multiple schemas – allowing events to be applied to the same schema in parallel. For this implementation the new assumption was that if a set of transactions were part of the same binlog group commit on the master then the locking on the master has already determined that they’re indepent of each other and so that same set of events can be applied in parallel on the slave.

The latest enhancement is to allow the user to tune how many transactions are likely to be in each of those BGC buckets. This is done at the expense of delaying commits on the master and can be controlled by specifying the longest time that the system should wait (binlog-group-commit-delay) and the maximum number of transactions that should be committed together (binlog-group-commit-count). Initial benchmark results are very possitive – a 6X throughput (if willing to tolerate a small impact to the latency on the master) or 3X with no impact to the master.

Multi-Threaded slave performance gains
This is a feature where the mileage will definitely vary depending on your application and how you tune these two new parameters and so I’d certainly recommend that you try it out for yourselves. As always we’re anxious to get your feedback – for example through comments on this post or on the MySQL Replication forum.





Advanced MySQL Replication Architectures and Latest Developments – On-Demand webinar + Q&A

MySQL Replication Logo
We recently hosted a live webinar covering advanced MySQL Replication topics as well as the latest developments. The webinar charts and replay are now available here. Below, you’ll find the questions raised by the audience together with the responses given.

More details on what was covered…

The biggest Web sites in the world rely on MySQL Replication to scale-out and provide High Availability for their data. Extend your knowledge of how MySQL Replication works and what you can achieve with it; join us for this technical webinar to explore some of the more advanced replication architectures as well as some of the latest product developments:

  • Replication topologies, including master-slave, circular and multi-master
  • Load balancing and query splitting
  • Data aggregation with multi-source replication
  • Global Transaction IDs and auto-failover with recovery
  • Getting the best replication throughput
  • Heterogeneous replication with the Binlog API

Questions & Answers

  • Is the server_uuid constant in the lifetime of a MySQL server?: Yes, server_uuid it’s persisted in a file in the data_dir.
  • How are the servers syncronized after a failure?: When slave connects to master it will inform master which GTIDs it already has, (whether just received or actually or applied), and the master will send what is missing.
  • Are all the features being described available now in the latest community MySQL version?: Everything that is described is available to the community. Some features are currently in Development Milestone or labs releases but that is made clear in the charts.
  • What is the maximum number of Slaves per Master and are there restrictions to the distance between Master and Slave?: There is no maximum number of slaves per master, that value depends on hardware and workload. There is an overhead on the master for each of the slaves (though this is reducing); you always have the option of replicating to another one or MySQL Servers and then use them as replication masters to fan out to many more slaves (those servers can even use the Black Hole storage engine so that they don’t even store the data. No distance limits, but with longer distances the network latency will increase. If using the default asynchronous replication, this latency has not effect other than possibly the slave(s) running slightly further behind but if using semi-synchronous replication then the transactions will take longer to commit.
  • Is there a maximum number of worker threads that can be configured, or is it just dependient on your server hardware?: The maximum number of worker thread is 1024, but the real limitation is hardware.
  • Is there a form of automatic client rerouting as a result of Switch/Failover?: Yes – MySQL Fabric which is also covered in this session.
  • what’s the resolution of the timestamp?: Replication timestamp resolution is microseconds.
  • Are all of these replication features available in both Synchronous and Asynchronous modes?: There is no synchronous mode, but they all work with both asynchronous and semi-synchronous replication.
  • Why is semi-sync replication only available as plugin? It makes it harder to setup with the present restrictions.: By implementing features as plugins, we can evolve the software faster by implementing them in new modules rather than in the large, complex MySQL Server code base. It also means that we can iterate more frequently as it doesn’t need to be tied to a MySQL Server release.
  • Does semi-synchronous replication wait for all slaves or just a single one?: You can specify how many slaves need to respond with the rpl_semi_sync_master_wait_slave_count option.
  • In case of one master one slave, where slave can overtake master role in case of crash. Won’t it introduce split-brain scenario ? Does mysql have rollback settings in case of master crash?: Monitoring tools should ensure that the master has crashed crashed, or if it suspects that it became irresponsible then it should kill the master before performing the failover. Alternatively, take a look at MySQL Fabric.
  • For now I have impression that if I want clients to automatically load balance read & writes between masters/slaves then I should do this using mysql proxy. Is this still the best practice?: What language are you using – this functionality is built into some connectors (for exampe for PHP and Java)? Alternatively you could look at MySQL Fabric or hardware or software load ballancers.
  • How are autoincrement columns handled in MySQL Fabric when you have mulitiple HA Groups?: The first thing to point out is that an auto-increment column cannot be used as the sharding key. You can use auto_increment_increment and auto_increment_offset to make sure that you don’t repeat values on different shards (e.g. if you have 2 shards then odd values coule be on one and even on the other.
  • Is MySQL Fabric queried for every transaction or query (and so becomes a single-point-of-falire)?: No, the connectors hold a cache of the routing data and so will use that rather than constantly querying the MySQL Fabric process.
  • Does the Replication include DDL changes, not just DML?: Yes it does.
  • How do I scale out write operation in MySQL 5.6: That’s where MySQL Fabric comes in, when we reach the write saturation point on master.
  • Is NDB storage engine is good option for write operation scale out?: It can be a great solution but it will depend on how your data is structured and accessed. Take a look at theMySQL Cluster Evaluation Guide – Designing, Evaluating and Benchmarking MySQL Cluster as this will help you figure out if MySQL Cluster (NDB) is going to be the right option for your application
  • But fabric still in beta version right?: Correct – it isn’t ready for production yet (at the time of writing this is true but be sure to check if you’re reading this laster!).
  • Are the benchmarks of sync_binlog done with SSD or HDD machines?: SSD – see this blog post. Should we expect the same results with HDDs?: Yes, but with different orders of magnitude. Note that all benchmarks were made with SSDs, so we are comparing equal hardware on 5.5 and 5.6.




Advanced MySQL Replication Architectures and Latest Developments – free webinar

MySQL Replication Logo
This Thursday (20th March 2014) we’ll be hosted a free webinar covering advanced MySQL Replication topics as well as the latest developments. As always, the webinar is free but you need to register here – even if you can’t join live, you’ll then be sent a link to the replay.

More details on what to expect…

The biggest Web sites in the world rely on MySQL Replication to scale-out and provide High Availability for their data. Extend your knowledge of how MySQL Replication works and what you can achieve with it; join us for this technical webinar to explore some of the more advanced replication architectures as well as some of the latest product developments:

  • Replication topologies, including master-slave, circular and multi-master
  • Load balancing and query splitting
  • Data aggregation with multi-source replication
  • Global Transaction IDs and auto-failover with recovery
  • Getting the best replication throughput
  • Heterogeneous replication with the Binlog API

WHO:

  • Andrew Morgan, Principal MySQL Product Manager
  • Lars Thalmann, Director, MySQL Replication, Backup and Connectors

WHEN:

  • Thu, Mar 20: 09:00 Pacific time (America)
  • Thu, Mar 20: 10:00 Mountain time (America)
  • Thu, Mar 20: 11:00 Central time (America)
  • Thu, Mar 20: 12:00 Eastern time (America)
  • Thu, Mar 20: 13:00 São Paulo time
  • Thu, Mar 20: 16:00 UTC
  • Thu, Mar 20: 16:00 Western European time
  • Thu, Mar 20: 17:00 Central European time
  • Thu, Mar 20: 18:00 Eastern European time
  • Thu, Mar 20: 21:30 India, Sri Lanka
  • Fri, Mar 21: 00:00 Singapore/Malaysia/Philippines time
  • Fri, Mar 21: 00:00 China time
  • Fri, Mar 21: 01:00 日本
  • Fri, Mar 21: 03:00 NSW, ACT, Victoria, Tasmania (Australia)




Webinar – What’s New in MySQL Replication

MySQL Replication logoOn Friday (22/11/2013), we hosted webinar covering the new replication features in the MySQL 5.7 DMR as well as in MySQL Labs. You can now view the webinar replay here.

It’s a very exciting time for MySQL Replication! MySQL 5.6 contains numerous new replication features and Oracle recently announced…

  • The second Development Milestone Release of MySQL 5.7, introducing yet again new replication features and enhancements including optimized multi-threaded slave, better consistency with semi-synchronous replication, and improved monitoring with new Performance Schema tables.
  • Early access to multi-source replication via labs.mysql.com

During the live webinar, attendees asked questions and the MySQL Replication engineers answered them; this Q&A is included here:

  • Can we have Master & Slave on different versions of MySQL? Like Master is with 5.1 & Slave with 5.6? In general, cross version replication to a next higher version on slave is supported unless the behaviour of SQL statements change in the different versions. For more details, please look at the documentation on MySQL Replication cross-version compatibility.
  • The MySQL Replication topology discussed here supports asynchronous replication. How is Synchronous replicaiton is possible with mysql? With the loss-less semi-synchronouse replication, you’re getting very close to synchronous replication in that when your transaction has committed, you can be sure that the change has been received by a slave and so is safe. If you want truly synchronous replication then you can use MySQL Cluster (the NDBCLUSTER storage engine); typically this synchronous replication would be within a data center but you can split the cluster if you have a real need. You can watch this demo video of MySQL Cluster.
  • When Slave says it is x seconds behind, what does that means? Does it means slave would require x seconds to recover and reach the master’s current position? When the slave SQL thread is actively processing updates, this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.
  • What is default setting for the rpl_semi_sync_master_wait_point setting. after_sync
  • What is the best mechanism in MySQL Community Edition for backing up InnoDB tables? mysqldump has –single-transaction option, which is mainly useful for InnoDB tables with REPEATABLE READ isolation level. In short, for Community Edition mysqldump is the best solution. But for Enterprise Edition, there is MySQL Enterprise Backup.
  • How can I verify that my slave DB is in sync with the master? Is there something similar to the archive log sequence number verification in Oracle. we know there are some paramerts from show slave status output. But is there any other way to verify the sync status using MySQL tables? In MySQL 5.6, if you set master_info_repository and relay_log_info_repository to TABLE the status information is put into the mysql.slave_relay_log_info and mysql.slave_master_info tables. And in MySQL 5.7 the status is put in performance_schema you can query the status using SQL.
  • Does multi-source replication require the use of GTIDs? No – you just need to make sure that GTIDs are either enabled on the slave and all masters or disabled on all of them.
  • If multi-source can work without GTIDs, how do you prevent a transaction executed on A which is replicated to B and C, from being replicated back again to C and B via the replication channel from B to C and C to B ? Without GTIDs, the transactions are re executed (in the above scenario) if the schema developer *doesn’t* take care of fixing the keys. If there is a conflict on a key, the Slave errors out. So, In short, MSR doesn’t involve itself with conflict detection and resolution. When using positions, the schema developer has to make sure that multisourced slave receives correct data to avoid reexecution of transactions.
  • In loss-less semi-sync, how can the transaction be written in the binary log of the master without the master committing the transaction? Does this stall the transaction on the master (until a commit is written to the binary log)? When using this feature, a commit has two phases (which is same as an XA transaction) – first there is a prepare phase and then the commit phase. The binary log is written after the prepare phase but before commit phase(internally, it is part of the commit phase but happens before the engine commit).
  • With parallel replication, is the binary logs order preserved on the slave (compared to the master), even if the transactions are applied in parallel? Asked in another way, can 2 slaves of the same master have different binlogs? No, the order will be different when multi-threaded slave feature is on.
  • Are you working with an easier way to create a replication setup – like automatically copying the master database to the slave (something like you had for MyISAM before)? Take a look at MySQL Utilities as there are lots of new tools in there 2 help setup, monitor and manage replication.
  • You guys have multiple blogs and it is a shame to not present this information in a common place. That’s been something that we’ve been discussing as well. Of course they get aggregated through planet.mysql.com and there is also a summary blog pointing to the others.
  • In Loss-Less Semi-Sync replication, what happens if the slave dies, before it receives the change in the relay. Is there a timeout which then commits and ack the data to the master? Yes, there is a timeout. After the timeout, it commits and falls back to normal asynchronous replication. The timeout is specified by the variable rpl_semi_sync_master_timeout. Currently you cannot turn off this behavior, but you can set the timeout very very high. For more information, see the configuration guide.




MySQL 5.7.2 DMR and Labs – new replication features

With today’s announcement of the second MySQL 5.7 Development Milestone Release and a new labs release it’s a very exciting time for MySQL Replication. MySQL 5.6 contained a lot of new content to make replication faster, easier to use and more reliable (Global Transaction Identifiers, Multi-Threaded Slaves, Binary Log Group Commit, Optimized Row Based Replication, Crash Safe Replication, Replication Event Checksums, Time Delayed Replication & Informational Logs) and now we want to improve things even further.

The new DMR has something for everyone.

With the improvements to Semi-Synchronous Replication, the application developer can be confident that when a transaction has been commited, the changes have been safely copied to one or more slaves and so whatever happens, that change will not be lost. Further, we now prevent other application threads seeing those changes until they’ve been received by the slave and so the application cannot start acting on the new data until it’s known to be safe. This is an important improvement in consistency which moves more of the onus from the application developer onto the database.

DBAs want replication to be fast – in particular for the slave(s) not to fall behind the master. MySQL 5.6 made some massive improvements in this area – both on the master and the slave. A number of users though were unable to exploit the Multi-Threaded Slave (MTS) feature as relied on the use of multiple schemas (databases) to get changes applied in parallel. In the new MySQL 5.7 DMR we’ve included a new option for MTS where changes can be applied in parallel – even within the same schema. A second performance feature improves throughput on the master – where the dump thread no longer needs to lock the binary log – refer to this engineering Blog on Dump Thread Enhancement in MySQL 5.7.2 for more details.

DBAs also want to monitor the status of replication and for years have relied on the SHOW SLAVE STATUS command. As replication has evolved, SHOW SLAVE STATUS has become less suitable – we needed a solution that could properly model the more sophisticated replication architectures (including GTIDs and MTS) now possible. The approach we’ve taken is to provide this information through the performance_schema.

Note that the earlier MySQL 5.7 DMR added non-blocking SHOW SLAVE STATUS, idempotent and –rewrite-db options for mysqlbinlog – these are still available in the new DMR.

The new features are described in a little more detail in the following sections (together with links to more technical content from the MySQL Engineeing team).

Loss-less Semi-Synchronous Replication

Intra-Schema Semi-Synchronous Replication

Intra-Schema Semi-Synchronous Replication

When using semi-synchronous replication in previous releases, the processing of the transaction on the master would wait for the slave’s acknowledgement after the change had been written to the storage engine but before the commit was acknowledged to the client. This satisfied the requirement that the application could be confident that if a transaction has been commited (and an acknowledgement received for the commit) then the update would not be lost. It did however leave a window where a user on another connection could read the new data from the master (because it has been written to the storage engine and the locks have been released) before the change had been received by the slave and safely stored in its relay log – that user could then start acting on that data but if the master failed at that point then that original update could still be lost and so the user would be acting on what is now inaccurate data.

This feature removes the above race condition by making the master wait for the update to be received by the slave before writing it to the storage engine and releasing the locks.

The functionality is enabled by default and shouldn’t have any negative impacts (for example on peformance) but it you’d like to disable it then you can do so using rpl_semi_sync_master_wait_point = AFTER_SYNC.

You can read more details on this in this Loss-less Semi-Synchronous Replication on MySQL 5.7.2 blog from the MySQL engineering team.

Intra-Schema Multi-Threaded Slaves

DBAs want replication to be fast – in particular for the slave(s) not to fall behind the master. If there is a short but heavy burst of writes on the master then the slave falls behind (and there is a risk of lost data if the master fails during this period) but if the write-rate is sustained then the slave would fall further and further behind indefinitely. The ongoing challenge has been that the master gets faster and faster as more cores and clients are added but applying these changes asynchronously on the slave is more complex as you need to maintain some form of ordering in order to always have a consistent data set.

The earliest solution to maintaining ordering/consistency was for the slave to apply all of the changes serially, in a single thread – this ensured that changes were applied in the same order as on the master and so guaranteed the slave always contained a consistent view. Unfortunately this meant that the slave applier thread could only exploit a single core which is very wasteful in modern systems.

MySQL 5.6 made some massive improvements for many use cases by allowing the slave to apply updates in parallel using multiple threads. The assumption made was that data was held in multiple schemas (databases) and that there were no dependencies between the data in those different schemas. In this way all of the updates for a schema would be applied in order by a single thread (ensuring consistency) but updates to other schemas could be handled by additional threads. This allowed the slave to work many times faster but was limited to those use cases that met the assumptions.

In the second MySQL 5.7 DMR we introduce a new option that enables the slave to safely apply updates in parallel – even when all of the data is held within a single schema and no assumptions can be made about the independence between any rows from any tables. To avoid conflicts/divergence from the master, the slave must ensure that any transactions that are applied in parallel don’t read or write any overlapping rows. The good news is that this grouping on non-overlapping transactions is already being figured out on the master as part of the binary log group commit functionality (introduced in MySQL 5.6) as row level locking means that overlapping transactions cannot be part of the same group commit.

MySQL 5.7 adds a logical clock/counter to the master which is used to tag transactions that are part of the same group commit’s prepare phase. The slave can then use that information – knowing that it is safe to apply all of the transactions with the same logical clock value in any order (and so can use multiple threads).

Activating the functionality is very straight-forward and the key step is to set slave-parallel-type = LOGICAL_CLOCK but see this blog from the MySQL engineering team: using the intra-schema MTS functionality for more detailed instructions. Note that there is another blog in the series – that blog provides a detailed view of how the intra-schema MTS is implemented.

Replication Performance Schema

With the ever increasing sophistication of MySQL Replication, presenting all of the information in SHOW SLAVE STATUS has become unwieldy with data for multiple slave applier threads, GTID sets etc. What would be much more convenient is if this information were presented in tables so that the user could get exactly the information they needed using SQL queries (plus of course we can lay out the data in an understandable (relational) way and can access the information from stored procedures). MySQL 5.7.2 does just this by adding MySQL Replication tables to the MySQL performance_schema.

This feature introduces 6 new performance_schema tables: replication_connection_configuration, replication_connection_status, replication_execute_configuration, replication_execute_status, replication_execute_status_by_coordinator, replication_execute_status_by_worker.

Shivji from the MySQL engineering team has written a great blog post on what’s in the new performance_schema tables and how to interpret the results.

Multi-Source Replication – LABS

Multi-Source Replication

Multi-Source Replication

MySQL replication is very flexible in the way that networks of masters and slaves that can be built up; a master can replicate to multiple slaves, a master can itself be a slave of another master, you can create a replication ring…. The one caveat to this has always been that a slave server can only have a single master (MySQL Cluster is an exception to this rule).

Why might you want to do this? There are a few use cases around consolidating data from multiple MySQL Servers into one:
– Where each of the masters is for a different shard (where the application is responsible for the sharding) and you want to be able to run reports over all of that data to produce a consolidated view
– You want to avoid the expense of dedicating a slave server to each master server
– A remote location may require less throughput and so a single server can service all of the traffic for all of the data – the ‘super-slave’ gives it a low-overhead, local database to access all of the data
– The ‘super-slave’ is used as a point where you can perform backups for all of the data

It’s possible to have a slave time-slice between multiple masters with a bit of scripting but that isn’t an ideal solution. You can also add an additional repliation layer (such as writing your own code to use the binary log API) but it would be much simpler if it were built into MySQL itself.

In this labs (i.e. for test only, not to be deployed!) release we allow a slave to simultaneously receive and process replication events from multiple masters – exactly what our users have been asking for.

What’s more, we’ve also ensured that this new functionality is compatible with the other enhancements that have been made to the MySQL Replication architecture – this includes loss-less semi-synchronus replication; and intra-schema multi-threaded slaves (as well as the existing per-database MTS). This means that you don’t have to choose between all of these tempting features – the keys to the candy store are yours!

It’s likely that not all of the masters will be the same, have the same maintenance schedules etc. and so it makes sense to be able to manage each of the relationships independently. In this labs release you can manage each master independently, including the relevant server variables but the same replication filters are currently applied to the replication events from all of the masters – we recognise that this isn’t ideal but this is cuurently an early access release and it’s something we intend to address in future versions.

At the moment there’s no limit to the number of masters that can replicate to a specific slave – in the final version we’re likely to apply a configurable limit.

Because this further complicates the information that would need to be included in SHOW SLAVE STATUS, most of the detailed information is instead presented in the Performance Schema.

It should be pointed out that there is no conflict detection or resolution built into this feature – it is the responsibility of the application to make sure that the masters are working on distinct data sets (or that they’re comfortable with the results of any conflicts).

For more technical details, refer to this engineering blog post on multi-source replication.

Summary

There’s a lot of exciting new content in the MySQL 5.7.2 DMR (download here) and the Mulit-Source Replication labs release (download here). The reason these features has been released is that we value early feedback from our community and customers – please try them out and let us know what we’re getting right and what needs to be enhanced!





MySQL Cluster Asynchronous Replication – conflict detection & resolution

I was rooting through past blog entries and I stumbled accross a draft post on setting up multi-master (update anywhere) asynchronous replication for MySQL Cluster. The post never quite got finished and published and while the material is now 4 years old it may still be helpfull to some and so I’m posting it now. Note that a lot has happened with MySQL Cluster in the last 4 years and in this area, the most notable change has been the Enhanced conflict resolution with MySQL Cluster active-active replication feature introduced in MySQL Cluster 7.2 and if you’re only dealing with a pair of Clusters, that’s your best option as it removed the need for you to maintain the timestamp columns and backs out entire transactions rather than just the conflicting rows. So when would you use this “legacy” method? The main use case is when you want conflict detection/resolution among a ring of more than 2 Clusters. Note also that MySQL 5.6 (and so MySQL Cluster 7.3) added microsecond precision to timestamps and so you may not need the custom plugin that this post referred to.

Anyway, here’s the original post…

————————————————————
MySQL Cluster asynchronous replication allows you to run in a multi-master mode with the application making changes to both sites (or more than 2 sites using a replication ring). As the replication is asynchronous, if the application(s) modified the same row on both sites at ‘about the same time’ then there is a potential for a collision. Left to their own devices, each site would store (and provide to the application) different data indefinitely. This article explains how to use MySQL Cluster collision detection and resolution to cope with this.

Fig. 1 Multi-master replication leading to inconsistencies

Fig. 1 Multi-master replication leading to inconsistencies

Fig. 1 shows the timeline that can result in a conflict. The same or two different applications make a change to the same row in the table but to the 2 different instances of MySQL Cluster. Each cluster synchronously replicates the data amongst its local data node in order to provide local High Availability (everything there is safe!). At some point later (normally a fraction of a second), the changes are replicated to the remote site asynchronously – this delay opens a window for a conflict where Cluster 2 is updated by the application just before it receives the earlier update from Cluster 1. Cluster 2 will overwrite it’s row with the value (5) it has received but only after its earlier change (directly from the application) is written to the binary log ready for replication to Cluster 1 which in turn will cause that value (15) to be stored by Cluster 1. Each Cluster instance replicates what it believes to be the correct data to the other site – overwriting what that site had previously stored. In our example, that leaves one database holding the value 15 for key ‘A’ while the other stores 5.

It’s often the case that the application will tend to go to the same site during a particular time when accessing the same data and so the chances of a conflict are reduced but the application may still want to guard against (even rare) race conditions. If replication slows down (for example due to a backlog of updates to be applied) or stops temporarily (for example due to network failure to the geographically remote site) then the chances of a collision greatly increase.

For information on setting up multi-master asynchronous replication with MySQL Cluster, please take a look at Setting up MySQL Asynchronous Replication for High Availability.

Conflict Detection & Resolution using MySQL Cluster

MySQL Cluster provides two different schemes to handle these collisions/conflicts. The first scheme (referred to as “greatest timestamp wins”) detects that a conflict occurs and automatically resolves it (the change most recently received from the application is stored on both Clusters). The second scheme (referred to as “same value wins”) detects that a conflict has occurred but does not fix it – instead the conflict is recorded in such a way that the application (or user) can figure out how best to resolve it based on a full understanding of the schema, what the data means and how it’s used. It is up to the developer which approach they use (if any) – it is selected on a per-table basis.

Common prerequisite steps

These steps should be followed regardless of whether you want to use conflict resolution or conflict detection (where the application decides how to resolve it).

  1. Set up multi-master replication as described in Setting up MySQL Asynchronous Replication for High Availability
  2. Create the function “inttime” for use in the stored procedures as described in Creating a MySQL plugin to produce an integer timestamp Note that you will need to install inttime.so on each host

Setting up Automatic Conflict Resolution (Greatest timestamp wins)

This is the simplest way to handle conflicts with MySQL Cluster when implementing multi-master asynchronous replication (actually, the simplest is to do nothing and accept that if your application(s) update the same row at about the same time at both Clusters then those Clusters may be left with different data until the application(s) next update that row).

Remember that this mechanism works by checking that the timestamp field of the update received by the slave is later than the one already stored. In the example that follows, the ‘ts’ column is used for the timestamp.

Create the database on either cluster (replication will make sure that it appears in both Clusters):

mysql> create database clusterdb;

Before creating the application tables, set  up the ndb_replication system table (again, in either Cluster):

mysql> CREATE TABLE mysql.ndb_replication ( db VARBINARY(63), table_name VARBINARY(63), server_id INT UNSIGNED, binlog_type INT UNSIGNED, conflict_fn VARBINARY(128), PRIMARY KEY USING HASH (db, table_name, server_id) ) ENGINE=NDB PARTITION BY KEY(db,table_name);

mysql> insert into mysql.ndb_replication values ('clusterdb', 'tab1', 7, NULL, 'NDB$MAX(ts)');

After that, you can create the application table:

cluster1 mysql> use clusterdb;

cluster1 mysql> create table tab1 (NAME varchar(30) not null primary key,VALUE int, ts BIGINT UNSIGNED default NULL) engine=ndb;

To test that the basic replication is working for this table, insert a row into cluster1, check it’s there in cluster2, add a second row to cluster2 and make sure it’s visible in cluster1:

cluster1 mysql> insert into tab1 values ('Frederick', 1, 0);

cluster2 mysql> use clusterdb;
cluster2 mysql> select * from tab1;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| Frederick |     1 |    0 |
+-----------+-------+------+
1 row in set (0.00 sec)
cluster2 mysql> insert into tab1 values ('William',20,0);

cluster1 mysql> select * from tab1;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| Frederick |     1 |    0 |
| William   |    20 |    0 |
+-----------+-------+------+
2 rows in set (0.00 sec)

For both rows, the timestamp was set to 0 to represent ‘the start of time’, from this point on, whenever making a change to those rows, the timestamp should be increased. Later on on in this article, I’ll show how to automate that process.

We’re now ready to test that the conflict resolution is working; to do so replication is stopped (in both directions) to increase the window for a conflict and the same tuple updated on each Cluster. Replication is then restarted and then I’ll confirm that the last update wins on both clusters:

cluster1 mysql> slave stop;

cluster2 mysql> slave stop;

cluster1 mysql> update tab1 set VALUE=10,ts=1 where NAME='Frederick';

cluster2 mysql> update tab1 set VALUE=11,ts=2 where NAME='Frederick';

cluster1 mysql> slave start;

cluster2 mysql> slave start;

cluster1 mysql> select * from tab1;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| William   |    20 |    0 |
| Frederick |    11 |    2 |
+-----------+-------+------+
2 rows in set (0.00 sec)

clusrer2 mysql> select * from tab3;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| William   |    20 |    0 |
| Frederick |    11 |    2 |
+-----------+-------+------+
2 rows in set (0.00 sec)

This confirms that the later update (timestamp of 2) is stored in both Clusters – conflict resolved!

Automating the timestamp column

Manually setting the timestamp value is convenient when testing that the mechanism is working as expected could be a nuisance in a production environment (for example, you would need to get the clocks of all application nodes exactly in sync wherever in the world they’re located). This section describes how that can be automated using stored procedures (note that stored procedures don’t work when using the NDB API to make changes but in that situation it should be straight-forward to provide wrapper methods that manage the timestamp field). Note that the timestamp must be an integer field (and needs a high level of precision) and so you can’t use the regular MySQL TIMESTAMP type.

This mechanism assumes that you’ve built “inttime.so” and deployed it to the hosts running the mysqld processes for each cluster (refer to the prerequisite section).

cluster1 mysql> create trigger tab1_insert before insert on tab3 for each row set NEW.ts=inttime;
cluster1 mysql> create trigger tab1_update before update on tab3 for each row set NEW.ts=inttime;
cluster1 mysql> insert into tab1 (NAME,VALUE) values ('James',10),('David',20);
cluster1 mysql> select * from tab1;
+-----------+-------+------------------+
| NAME      | VALUE | ts               |
+-----------+-------+------------------+
| William   |    20 |                0 |
| David     |    20 | 1250090500370307 |
| James     |    10 | 1250090500370024 |
| Frederick |    11 |                2 |
+-----------+-------+------------------+
4 rows in set (0.00 sec)

cluster2 mysql> update tab1 set VALUE=55 where NAME='William';
cluster2 mysql> select * from tab1;
+-----------+-------+------------------+
| NAME      | VALUE | ts               |
+-----------+-------+------------------+
| James     |    10 | 1250090500370024 |
| Frederick |    11 |                2 |
| William   |    55 | 1250090607251846 |
| David     |    20 | 1250090500370307 |
+-----------+-------+------------------+
4 rows in set (0.00 sec)

Setting up Conflict Detection (Same timestamp wins)

With this method, conflicts are detected and recorded but not automatically resolved. The intent is to allow the application to decide how to handle the conflict based on an understanding of what the data means.

Create the database on either cluster (replication will make sure that it appears in both Clusters):

mysql> create database clusterdb;

Before creating the application tables, set  up the ndb_replication system table (again, in either Cluster):

mysql> CREATE TABLE mysql.ndb_replication ( db VARBINARY(63), table_name VARBINARY(63), server_id INT UNSIGNED, binlog_type INT UNSIGNED, conflict_fn VARBINARY(128), PRIMARY KEY USING HASH (db, table_name, server_id) ) ENGINE=NDB PARTITION BY KEY(db,table_name);

mysql> insert into mysql.ndb_replication values ('clusterdb', 'SubStatus', 7, NULL, 'NDB$OLD(ts)');

After that, you can create the application table and its associated exception table:

cluster1 mysql> use clusterdb;
cluster1 mysql> create table SubStatus$EX (server_id INT UNSIGNED,master_server_id INT UNSIGNED,master_epoch BIGINT UNSIGNED,count INT UNSIGNED,sub_id int not null,notes VARCHAR(30) DEFAULT 'To be resolved', PRIMARY KEY (server_id, master_server_id, master_epoch, count)) engine=ndb;
cluster1 mysql> create table SubStatus (sub_id int not null primary key, ActivationStatus varchar(20), ts BIGINT default 0) engine=ndb;

To test that the exception table gets filled in, add some rows to the table and then cause an update conflict (in a similar way to the conflict resolution example but after setting up the timestamp automation):

cluster1 mysql> create trigger SubStatus_insert before insert on SubStatus for each row set NEW.ts=inttime();
cluster1 mysql> insert into SubStatus (sub_id, ActivationStatus) values (1,'Active'),(2,'Deactivated');
cluster1 mysql> select * from SubStatus;
+--------+------------------+------------------+
| sub_id | ActivationStatus | ts               |
+--------+------------------+------------------+
|      1 | Active           | 1250094170589948 |
|      2 | Deactivated      | 1250094170590250 |
+--------+------------------+------------------+
2 rows in set (0.00 sec)

cluster2 myql> use clusterdb;
cluster2 mysql> select * from SubStatus;
+--------+------------------+------------------+
| sub_id | ActivationStatus | ts               |
+--------+------------------+------------------+
|      1 | Active           | 1250094170589948 |
|      2 | Deactivated      | 1250094170590250 |
+--------+------------------+------------------+
2 rows in set (0.00 sec)

cluster1 mysql> slave stop;

cluster2 mysql> slave stop;

...

(at this point, just go on to test as with the conflict resoultion example but in this case expect to see that the confict is not resolved but an entry is added into the conflict table).

Of course, you can always add a trigger on the conflict table and use that to spur the application into initiating its own conflict resolution algorithm.





Standalone MySQL Utilities Now GA! Includes running mysqlfailover as a daemon

MySQL Utilities are now GA - logoWith the release of MySQL Utilities 1.3.4, the standalone (not bundled with the MySQL WorkBench GUI) package is now Generally Available and fully supported. This post will focus on a very important change (the ability to run as a daemon rather than in a terminal) to the mysqlfailover utility which allows you to build a light-weight HA database solution using MySQL Replication.

For a general overview of MySQL Utilities, take a look at this recent webinar or for a deeper dive into using them to setup replication and adding auto-failover of the master function to slaves watch this video and worked example.

When we first released the mysqlfailover utility, the reaction was very positive but the feedback also told us that to really use this to provide High Availability in a production system two enhancements were critical:

  1. The ability to not have the database password visible when someone queries the status of the process (for example, using the ps command). This was addressed by allowing the connection string to be specified using a login-path (referring to an entry in .mylogin.cnf – see https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html) in place of <user>[:<passwd>]@<host>. This is a vital security enhancement added in MySQL Utilities 1.3.1.
  2. Allowing mysqlfailover to be run as a daemon rather than being tied to the terminal from where it had to be manually launched. This option is key to enabling the user to build a reslient HA system that doesn’t rely on mysqlfailover being launched from a terminal and that terminal then never going away.

The rest of this post focuses on how to run mysqlfailover as a daemon.

By default, mysqlfailover runs as an interactive program within the terminal it was run from; it constantly refreshes, providing a summary of the status of the replication topology as shown below.

mysql@mini servers]$ mysqlfailover --master=root@192.168.1.101:5001 
  --discover-slaves-login=root --rediscover

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Thu Aug  1 23:34:56 2013

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mini1-bin.000001  581

GTID Executed Set
1aca3d80-faf9-11e2-a214-0800272b8804:1-2

WARNING: Errant transaction(s) found on slave(s).
Replication Health Status
+----------------+-------+---------+--------+------------+---------+
| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+
| 192.168.1.101  | 5001  | MASTER  | UP     | ON         | OK      |
| 192.168.1.101  | 5002  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.101  | 5003  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.101  | 5004  | SLAVE   | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+

Q-quit R-refresh H-health G-GTID Lists U-UUIDs

To run mysqlfailover as a daemon, the first new command-line option you must provide is –daemon=start; as you’d expect this runs the process as a daemon. In this mode, you won’t get to see the output from mysqlfailover at your terminal and so you should make sure that you know what log file is being used – so it’s best to specify it with –log=<path-to-log-file>. You can also control what information is periodically written to the log file using –report-values=<list-of-attributes from health,gtid,uuid>.  As you’ll likely to want to be able to manage the daemon without having to be in the same directory (and also likely to manage it from scripts that are automatically run when the server starts and stops) it makes sense to specify where the process ID file should be stored using –pid=<path-to-pid-file>.

The final incantation might look something like the following:

mysqlfailover --master=root@192.168.1.101:5001 
  --discover-slaves-login=root 
  --rediscover 
  --log=/home/mysql/servers/mysqlfailover.log 
  --pidfile=/home/mysql/servers/mysqlfailover.pid 
  --daemon=start 
  --report-values=health,gtid,uuid

and the resulting log file could then contain information such as this:

2013-08-02 01:10:34 AM INFO Getting health for master: 192.168.1.101:5001.
2013-08-02 01:10:35 AM INFO Health Status:
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5001, role: MASTER, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO GTID Status - Transactions executed on the servers:
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5001, role: MASTER, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, gtid: 1db19050-faf9-11e2-a214-0800272b8804:1
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, gtid: 200f8139-faf9-11e2-a214-0800272b8804:1
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, gtid: 22842441-faf9-11e2-a214-0800272b8804:1
2013-08-02 01:10:35 AM INFO UUID Status:
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5001, role: MASTER, uuid: 1aca3d80-faf9-11e2-a214-0800272b8804
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, uuid: 1db19050-faf9-11e2-a214-0800272b8804
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, uuid: 200f8139-faf9-11e2-a214-0800272b8804
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, uuid: 22842441-faf9-11e2-a214-0800272b8804

As you’d expect, you can also stop the daemon:

mysqlfailover 
    --log=/home/mysql/servers/mysqlfailover.log 
    --pidfile=/home/mysql/servers/mysqlfailover.pid 
    --daemon=stop

and restart it:

mysqlfailover 
    --log=/home/mysql/servers/mysqlfailover.log 
    --pidfile=/home/mysql/servers/mysqlfailover.pid 
    --daemon=restart

The final option for daemon= is daemon=nodetach which is like start but the terminal that it’s run from will continue to view the output from the daemon.

One thing to note is that when the server is restarted, mysqlfailover needs to be started again and this is not something that is automatically configured when you run it as a daemon – rather, it’s your responsibility to ensure that it’s restarted (for example, including it in an init script.

Please try it out and (as always) let us know how you get on – the addition of these extra options is a direct result of the user feedback received for the earlier versions.





MySQL Utilities Webinar

MySQL Utilities logoOn Thursday, Dr Charles Bell and I will be presenting a webinar on MySQL Utilities; there will be a heavy focus on what you can acheive with them and how you should use them. As well as listening to the presentation, this is a great chance to get your questions answered by the experts (Israel Gomez from the engineering team will also be on-line to help with the questions). As always, the webinar is free but you should register in advance here. If the time isn’t convenient, register anyway and you’ll be sent a link to the replay when it’s available.

Abstract:

MySQL Utilities provide a collection of command-line utilities that are used for maintaining and administering MySQL databases, including:

  • Admin Utilities (Clone, Copy, Compare, Diff, Export, Import)
  • Replication Utilities (Setup, Configuration, Automated Slave Promotion)
  • General Utilities (Disk Usage, Redundant Indexes, Search Meta Data)

Andrew Morgan and Chuck Bell will guide you through these utilities, and many more, explaining what you can gain from these tools and how to use them (for example how a single command repeatedly checks your master server and in the event it fails, automatically promotes one of the slaves to be the new master). This is your opportunity to listen to the development lead describe what he and his team have created, and to get your questions answered by the experts.

WHO:
Andrew Morgan, Principal Product Manager
Chuck Bell, Software Development Manager

WHEN:
Thu, Jul 18: 09:00 Pacific time (America)
Thu, Jul 18: 10:00 Mountain time (America)
Thu, Jul 18: 11:00 Central time (America)
Thu, Jul 18: 12:00 Eastern time (America)
Thu, Jul 18: 16:00 UTC
Thu, Jul 18: 17:00 Western European time
Thu, Jul 18: 18:00 Central European time
Thu, Jul 18: 19:00 Eastern European time
Thu, Jul 18: 21:30 India, Sri Lanka
Fri, Jul 19: 00:00 Singapore/Malaysia/Philippines time
Fri, Jul 19: 00:00 China time
Fri, Jul 19: 01:00 日本
Fri, Jul 19: 02:00 NSW, ACT, Victoria, Tasmania (Australia)

The presentation will be approximately 60 minutes long followed by Q&A.