Join the DZone community and get the full member experience. Table of Contents Join For Free Originally Written by MySQL 5.6 allows you to execute replicated events in parallel as long as data is split across several databases. This feature is named “Multi-Threaded Slave” (MTS) and it is easy to enable by setting Skipping replication errorsWhen replication stops with an error, a frequent approach is to “ignore now and fix later.” This means you will run Then the day when I hit: mysql> show slave status; [...] Last_SQL_Error: Worker 0 failed executing transaction '' at master log mysql-bin.000017, end_log_pos 1216451; Error 'Duplicate entry '1001' for key 'PRIMARY'' on query. Default database: 'db1'. Query: 'INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5320, '49123511666-22272014664-85739796464-62261637750-57593947547-00947134109-73607171516-11063345053-55659776318-82888369235', '11400300639-05875856680-20973514928-29618434959-69429576205')' Exec_Master_Log_Pos: 1005432 I tried to use the trick: mysql> set global sql_slave_skip_counter=1; mysql> start slave; But: mysql> show slave status; [...] Last_SQL_Error: Worker 0 failed executing transaction '' at master log mysql-bin.000017, end_log_pos 1216451; Error 'Duplicate entry '1001' for key 'PRIMARY'' on query. Default database: 'db1'. Query: 'INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5320, '49123511666-22272014664-85739796464-62261637750-57593947547-00947134109-73607171516-11063345053-55659776318-82888369235', '11400300639-05875856680-20973514928-29618434959-69429576205')' Exec_Master_Log_Pos: 1005882 Note that the position reported with The issue is that the positions reported by
So the solution to my problem is first to make sure that there is no execution gap, and only then to skip the offending event. There is a specific statement for the first part: mysql> start slave until sql_after_mts_gaps; And now I can finally skip the error and restart replication: mysql> set global sql_slave_skip_counter=1; mysql> start slave; mysql> show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes The last thing to do is of course to resync the slave. BackupsIf you cannot trust the output of For instance, if you run The only
option then seems to be: run GTIDs to the rescue!The solution for both issues is to use GTIDs. They help when you want to skip an event because when using GTIDs, you must explicitly specify the transaction you will be skipping. It doesn’t matter whether there are execution holes. They also help for backups because ConclusionIf your application uses several databases and if you’re fighting with replication lag, MTS can be a great feature for you. But although GTIDs are not technically necessary, you’ll be exposed to tricky situations if you don’t use them. Is everything rosy when using both GTIDs and MTS? Not exactly… But that will be the topic for a separate post! By the way, if you are in the Brussels area this weekend, come see me and other great speakers at the MySQL and friends devroom at FOSDEM! Replication (computing) MySQL Database Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here. Opinions expressed by DZone contributors are their own. What is multi source replication in MySQL?MySQL multi-source replication enables a replica to receive transactions from multiple immediate sources in parallel. In a multi-source replication topology, a replica creates a replication channel for each source that it should receive transactions from. Is MySQL multi threaded?MySQL is fully multithreaded, and makes use of all CPUs made available to it. Not all CPUs may be available; modern operating systems should be able to utilize all underlying CPUs, but also make it possible to restrict a process to a specific CPU or sets of CPUs. Is MySQL replication single threaded?Writing some data to server A, it would take three hops to end up on server D (via server B and C). Since (traditional) MySQL replication is single threaded, any long running query in the replication may stall the whole ring. What are the types of replication in MySQL?There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You can also use a third variety, Mixed Based Replication (MBR). |