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:
- MySQL server version: 5.7.16-log MySQL Community Server (GPL)
Replication scene all anonymous:
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:
on S2 take the services off that access the DB
take all S2 - S5 read only SET @@global.read_only = ON;
show slave status on S4 and S5
Q: what do I have to watch for before I shutdown the server?
Shutdown all servers
change Mysqld section in config file
S2, S3, S4, S5
gtid-mode = ON
log-bin = mysql-bin
server-id = xx
Start S4 with
Start S5 with
try to connect with replication user across all machines as needed
S4 and S5 Change master to ... the corresponding machines with corresponding user, password, channel and
MASTER_AUTO_POSITION = 1;
Start Slave for channel1 on S4
Start Slave for channel2 on S4
Start Slave for channel1 on S5
if logfiles doesn't show any errors restart all servers
make the services that use DB's point to new Servers
I hope I got all together. Any recommendations highly appreciated.