1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.