Andrew Morgan on Databases

On 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.