Positioning MySQL replication with GTID

mysql-fabric is a set of tools to setup resiliend and scalable mysql infrastructures.

You can create fabric groups (a set of replicated servers) with automatic failover and various policies.

Shortly:

#mysqlfabric group create mycluster
#mysqlfabric group add mycluster db-1:3306
#mysqlfabric group add mycluster db-2:3306
#mysqlfabric group add mycluster db-3:3306

And now pick a master: fabric will configure replication on all the nodes

#mysqlfabric group promote mycluster --slave_id DB_1_ID

Now, unless db-1 is a blank page, you’re likely to get an error 🙁

Fabric is trying to replicate ALL the changes happened on db-1 since its creation (included “CREATE USER root …”) to all slaves.

The solution is to
1 – get the last transaction id used for configuration;
2- tell to the slaves to skip everything until then.

It is done via

-- stop replication first, and reset what have been done until now (hopefully nothing ;)
STOP SLAVE; 
RESET MASTER;
-- tell the slave to skip the first 10 transactions from the server with id 9f36...
 SET @@GLOBAL.GTID_PURGED = '9f367fff-d91e-11e4-8ffe-0242ac110017:1-10'; 
-- now restart the slave and check that everything is fine
START SLAVE; 

SHOW SLAVE STATUS \G

Lascia un commento