Okay, here’s a challenge. Assume that you’ve a Master-Slave setup.
Both are GTID enabled and Slave is having auto_position set to 1. Which
means, it’s using GTID positions to get in sync with master.
Now, suppose someone executes some insert queries into one of the table with auto_increment key column in the Slave server directly . And so, in next few seconds, the Slave breaks down with the Duplicate Entry(1062) error. How you fix?
Since you’re smart, you inject some empty transactions to skip those GTID positions. Fair.
But, what if there are hundreds of thousands of inserts done directly and you can’t keep skip them manually. And, unfortunately there’s no alternative for ‘sql_slave_skip_counter=1000..’ in GTID mode. Also, you can’t use slave-skip-errors=1062 in config file in GTID mode. So, what’s the solution ?
It’s pretty simple. Add ‘slave-skip-errors=1062’ to the config file and restart MySQL. Wait.. But, in GTID mode will that work? Oh yes that works, but not yet.
Once MySQL is up, do RESET SLAVE ALL. But before take a note of everything inside master.info file or from the table.
And, now is the key point. Execute CHANGE MASTER command, but without auto position option. Use master_log_file and master_log_pos options. Start Slave. This should work without any errors. Once it’s in sync, you can enable auto_position option.
You can do the same for any other replication errors. But, be cautious. Do only if you’re aware of the reasons for the inconsistency.
Now, suppose someone executes some insert queries into one of the table with auto_increment key column in the Slave server directly . And so, in next few seconds, the Slave breaks down with the Duplicate Entry(1062) error. How you fix?
Since you’re smart, you inject some empty transactions to skip those GTID positions. Fair.
But, what if there are hundreds of thousands of inserts done directly and you can’t keep skip them manually. And, unfortunately there’s no alternative for ‘sql_slave_skip_counter=1000..’ in GTID mode. Also, you can’t use slave-skip-errors=1062 in config file in GTID mode. So, what’s the solution ?
It’s pretty simple. Add ‘slave-skip-errors=1062’ to the config file and restart MySQL. Wait.. But, in GTID mode will that work? Oh yes that works, but not yet.
Once MySQL is up, do RESET SLAVE ALL. But before take a note of everything inside master.info file or from the table.
And, now is the key point. Execute CHANGE MASTER command, but without auto position option. Use master_log_file and master_log_pos options. Start Slave. This should work without any errors. Once it’s in sync, you can enable auto_position option.
You can do the same for any other replication errors. But, be cautious. Do only if you’re aware of the reasons for the inconsistency.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.