Mysql – changes on multimaster replication and activate GTID

gtidMySQLreplication

I've been reading a lot about the changes I planned to do to our DB landscape
but I need some confirmation or recommendation to be sure the work I'll do goes without causing a disaster.

Here is what we have on all hosts:

  • debian8
  • MySQL server version: 5.7.16-log MySQL Community Server (GPL)

Replication scene all anonymous:

Master Slave

S3 some DB's       > S4 on channel1
S2 one 150GB DB    > S4 on channel2
S2 one 150GB DB    > S5 one only channel

All replications work well and are max a few sec. behind master. The replicated DBs I use to do backups.

The 150GB DB on S2 is HA and heavily accessed, but it's possible to shut down for let's say < 10min. S3 DB's are not that critical but a < 10min downtime is highly appreciated.

The bigger picture is, S2 is an old machine and needs to go off grid. I like to have the 150GB DB on S4 and S5 in circular replication in order to do load balancing, failover and backups. S3 DB's needs to be replicated to one of S4 or S5 to do backups.

While that changes I like to change all replications to GTID. That change to GTID is the point I'm worried about.

Here is how I think I have to do all that:

  1. on S2 take the services off that access the DB

  2. take all S2 – S5 read only SET @@global.read_only = ON;

  3. show slave status on S4 and S5

    Q: what do I have to watch for before I shutdown the server?

  4. Shutdown all servers

  5. change Mysqld section in config file

    S2, S3, S4, S5

    gtid-mode = ON

    S4, S5

    log-bin = mysql-bin

    server-id = xx

  6. Start S3

  7. Start S4 with --skip-slave-start

  8. Start S5 with --skip-slave-start

  9. try to connect with replication user across all machines as needed

  10. S4 and S5 Change master to … the corresponding machines with
    corresponding user, password, channel and

    MASTER_AUTO_POSITION = 1;

  11. Start Slave for channel1 on S4

  12. Start Slave for channel2 on S4

  13. watch logfiles

  14. Start Slave for channel1 on S5

  15. if logfiles doesn't show any errors
    restart all servers

  16. make the services that use DB's point to new Servers

I hope I got all together. Any recommendations highly appreciated.

Best Answer

OK, for anybody reading this in future.

I did the first step changing all server to use GTID based replication and it worked perfect.

I did it the way as I wrote in the original post.

Right now I have same landscape as before, just using GTID.

This night I'll go to activate circular replication between S4 and S5 and if all goes well shutdown S2

I keep posting for the world after.

Related Question