In a previous post, titled “Multi-threaded replication with MySQL 5.6: Use GTIDs,” I explained that using GTID replication is almost a requirement when using MySQL 5.6 MTS. Let’s see now how to perform the day-to-day operations when MTS and GTIDs are both enabled. (I’ll also be presenting a related webinar next week titled “Multi-threaded Replication in MySQL 5.6 and 5.7″).
Seeing the execution gaps
If you have a look at SHOW SLAVE STATUS while the slave is running, you may not be expecting such an output:
[...] Executed_Gtid_Set: 1381aa44-9a60-11e4-b6d8-94dbc999324d:1-2520:2522:2524:2526-2528:2531-2533:2536-2538:2540-2541:2544:2546-2547:2550-2551:2555:2565-2566:2569:2575-2577:2579-2581:2584-2586:2588:2590-2591:2595-2597:2599:2602:2604-2605:2607-2610:2613:2615-2620:2622-2624:2626-2627:2629:2631:2634:2636-2639:2641-2642:2644:2646-2647:2649:2651-2653:2657-2658:2661-2662:2666-2672:2676-2678:2680:2683-2684:2686-2693:2695:2701:2704:2706-2707:2709:2711:2713-2714:2717:2720-2722:2729-2730:2735:2744:2746:2749:2751-2752:2762:2764-2765:2768-2769:2771:2774:2776:2780-2782:2784:2786-2787:2789:2791:2793:2800:2803:2805-2807:2809:2811-2814:2816-2817:2819-2820:2822-2826:2828-2834:2837-2840:2842:2844-2845:2847:2850-2851:2853:2855:2857-2859:2861-2863:2865-2868:2870-2871:2873-2874:2878:2880-2884:2886-2888:2891:2893:2895-2896:2899:2903:2906-2907:2910:2912:2915-2918:2921-2923:2925-2926:2930:2932:2934:2936:2939-2940:2943-2944:2946:2948-2957:2966:2969-2970:2974:2976:2979-2980:2982:2985:2987:2989:2994:2996-2997:2999:3001:3003:3006:3008:3011-3013 [...]
Ouch! What does that insane list of GTIDs mean?
It is actually easy to understand as long as you know that the GTID of all executed transaction is tracked in Executed_Gtid_Set and that execution gaps are allowed with MTS.
Then 1-2520:2522:2524 simply means that transactions #1 to #2520 have been executed, as well as transactions #2522 and #2524, but not #2521 and #2523.
You can also see that a gap at a specific position will not last for long. If you run SHOW SLAVE STATUS an instant later, you will see:
[...] Executed_Gtid_Set: 1381aa44-9a60-11e4-b6d8-94dbc999324d:1-4095:4098:4100:4103-4105[...]
This time the first execution gap is for transaction #4096.
If you stop the writes on the master, all gaps will be filled once replication has caught up and you will simply see:
[...] Executed_Gtid_Set: 1381aa44-9a60-11e4-b6d8-94dbc999324d:1-20599 [...]
Dealing with replication errors
Say replication has stopped with an error and you want to skip the offending event. The only option with GTID replication is to inject an empty transaction, which in turn means you must know the GTID of the transaction you want to skip.
Let’s look at SHOW SLAVE STATUS:
Executed_Gtid_Set: 1381aa44-9a60-11e4-b6d8-94dbc999324d:1-1052769:1052805-1052806:1052808:1052810-1052811:1052814:1052816:1052819:1052823:1052825:1052827-1052828:1052830-1052831:1052835:1052838:1052840:1052842:1052844-1052846:1052848-1052849:1052851-1052852:1052855-1052857:1052859:1052862-1052863:1052867-1052868:1052870:1052873-1052875:1052878-1052879:1052882-1052883:1052885:1052887:1052890-1052892:1052896:1052901:1052905:1052908-1052909:1052911:1052915:1052917-1052918:1052922-1052923:1052927-1052929:1052931-1052933:1052937-1052938:1052940:1052943:1052946:1052948-1052949:1052953:1052955-1052956:1052958:1052962-1052964:1052967-1052969:1052972:1052975-1052977:1052979:1052981-1052983:1052985:1052987:1052989:1052991:1052993-1052995:1052999:1053001:1053003:1053005-1053016:1053018:1053020:1053024-1053026:1053029:1053032-1053034:1053037-1053038:1053040:1053043:1053045-1053046
So which transaction should we skip: probably 1381aa44-9a60-11e4-b6d8-94dbc999324d:1052770, right? This is the first transaction that could not be executed.
This is confirmed by looking at the Last_SQL_Error field:
Last_SQL_Error: Worker 0 failed executing transaction '1381aa44-9a60-11e4-b6d8-94dbc999324d:1052770' [...]
Once you know the GTID to skip, it is easy to restart replication (and fix the inconsistency later):
mysql> SET gtid_next='1381aa44-9a60-11e4-b6d8-94dbc999324d:1052770'; mysql> BEGIN;COMMIT; mysql> SET gtid_next='AUTOMATIC'; mysql> START SLAVE;
Taking backups
When using GTID replication, taking a backup from a multi-threaded slave is not difficult at all.
With Percona XtraBackup, simply add the --slave-info option as usual and you will get the list of executed GTIDs in the xtrabackup_slave_info file:
$ less xtrabackup_slave_info SET GLOBAL gtid_purged='1381aa44-9a60-11e4-b6d8-94dbc999324d:1-1095246:1095248-1095249:1095253-1095254:1095257-1095266:1095270-1095271:1095273:1095275:1095284-1095285:1095296:1095305:1095311-1095312:1095315-1095316:1095318:1095321:1095323-1095324:1095326-1095332:1095335:1095337-1095338:1095348-1095351:1095353-1095354:1095356:1095358-1095359:1095363-1095364:1095366:1095368-1095369:1095372:1095375-1095376:1095378:1095380-1095385:1095388:1095390-1095392:1095394-1095411:1095419:1095424-1095425:1095428:1095430-1095433:1095437-1095439:1095442:1095444-1095445:1095447:1095449:1095461-1095464:1095468:1095473:1095482-1095484:1095488-1095490:1095494:1095496-1095497:1095499-1095500:1095502:1095505:1095507:1095509:1095511-1095516:1095521:1095524-1095525:1095527:1095530-1095531:1095534-1095552:1095554:1095557:1095559-1095560:1095563:1095566:1095569:1095572-1095573:1095582:1095594-1095595:1095597:1095601-1095605:1095607-1095610:1095612-1095614:1095618:1095621-1095623:1095625-1095628:1095630:1095632:1095634:1095636:1095639-1095642:1095645:1095649:1095659:1095661:1095664-1095665:1095667-1095669:1095671:1095674'; CHANGE MASTER TO MASTER_AUTO_POSITION=1
Then starting replication on a new instance bootstrapped from this backup is easy:
mysql> SET GLOBAL gtid_purged='...'; mysql> RESET SLAVE ALL; mysql> CHANGE MASTER TO [...], MASTER_AUTO_POSITION=1; mysql> START SLAVE;
With mysqldump, simply discard the --dump-slave option as the list of executed GTIDs will be automatically added at the top of the dump:
-- MySQL dump 10.13 Distrib 5.6.22, for linux-glibc2.5 (x86_64) [...] -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='1381aa44-9a60-11e4-b6d8-94dbc999324d:1-1095246:1095248-1095249:1095253-1095254:1095257-1095266:1095270-1095271:1095273:1095275:1095284-1095285:1095296:1095305:1095311-1095312:1095315-1095316:1095318:1095321:1095323-1095324:1095326-1095332:1095335:1095337-1095338:1095348-1095351:1095353-1095354:1095356:1095358-1095359:1095363-1095364:1095366:1095368-1095369:1095372:1095375-1095376:1095378:1095380-1095385:1095388:1095390-1095392:1095394-1095411:1095419:1095424-1095425:1095428:1095430-1095433:1095437-1095439:1095442:1095444-1095445:1095447:1095449:1095461-1095464:1095468:1095473:1095482-1095484:1095488-1095490:1095494:1095496-1095497:1095499-1095500:1095502:1095505:1095507:1095509:1095511-1095516:1095521:1095524-1095525:1095527:1095530-1095531:1095534-1095552:1095554:1095557:1095559-1095560:1095563:1095566:1095569:1095572-1095573:1095582:1095594-1095595:1095597:1095601-1095605:1095607-1095610:1095612-1095614:1095618:1095621-1095623:1095625-1095628:1095630:1095632:1095634:1095636:1095639-1095642:1095645:1095649:1095659:1095661:1095664-1095665:1095667-1095669:1095671:1095674';
And then replication can be started like stated before.
Conclusion
Seeing the execution gaps in the output of SHOW SLAVE STATUS can be disturbing at first sight, and of course you may have to change a few habits, but overall there is no specific issue when using GTIDs and MTS.
Next week I will be presenting a free webinar on multi-threaded replication (Wednesday February 25th at 10 a.m. PST). If you are interested in learning more on the topic, feel free to register. It will also be recorded – you’ll be able to use that same link to watch the presentation and download my slides.
The post More on MySQL 5.6 multi-threaded replication and GTIDs (and Feb. 25 webinar) appeared first on MySQL Performance Blog.