Upgrading from single server to new GTID-replicated master and slave MySQL 5.7 I'm going from a single, non-GTID MySQL 5.6 server to a master-slave replicated setup on 5.7 (could be 8.0 too) with GTID on CentOS 7.x. Some details: format = ROW, minimal logging, file_per_table InnoDB

What's the best path to make the switch? The original server is in production, and I can stop it for discrete moments without much disruption.

If I set the new master and slave with replication from scratch and then import the database dumps (plain, no GTID data here) to the master, everything will get replicated right away, right?

My plan is this:


Stop the app associated with a database (WordPress blogs mainly, front-end caching can keep downtime unnoticed).
mysqldump individual database
Import dump into new master (replication propagates data to the slave)
Redirect app to new master, restart app


The slave will act as an emergency replacement and backup source while the master will bear the brunt of reads and writes. If load justifies it, maybe I will use the slave for reads and the master for writes in the future.

Am I too off in my plan?
I think your project is realistic.

I suggest a migration path below who can minimising the database downtime and make things smooth.

For this explanation, I named the Current database server PrdOld, the next database Master (RW) server Prd01 and the next database Slave (RO) Prd02.


As replication from 5.6 to 5.7 is supported, put your current MySql 5.6 database (PrdOld) as  a replication master


see at log_bin, relay_log, sync_binlog, server_id, expire_logs_days, binlog_cache_size, max_binlog_size my.cnf variables
don't search to enable GTID now
format=ROW and innodb_file_per_table are advisable

Prepare Prd01 as slave of PrdOld and master of Prd02
Prepare Prd02 as slave of Prd01


So we'll have a replication chain:


  PrdOld --> Prd01 --> Prd02



Do a full mysqldump (all replicated databases) of PrdOld
Write down File and Position from SHOW MASTER STATUS; command


For a simpler process I choose to enable replication right now from Prd01 to Prd02 but it's not mandatory, it depend of your database (size is a factor but not the only one) and activity (load, ...).


Restore your dump on Prd01 witch is propagated to Prd02 (it may generate some slave lag during this step)
Enable replication between PrdOld and Prd01


You have now three live and synced databases.

Observe for a while how things are working. And decide quietly when you want to switch the application db  connection context to prd01.
Then cut PrdOld to Prd01 replication mechanism and shutdown definitively PrdOld.

For the db HA aspect between Prd01 and Prd02 (in case of failure), choose to give some virtual IP to each db server (as IPmaster and IPslave) of use a load balancer in front of them (HAProxy, NGinx, ...).


Enable master-master replication (known also circular replication) between Prd01 and Prd02
Enable GTID mechanism


see at log_slave_updates, gtid_mode, enforce_gtid_consistency variables



Notice: You have to watch carefully the Seconds_Behind_Master indicator (during dump restore then after during production) in SHOW SLAVE STATUS\G. Big number is not necessary a problem is it doesn't stay for a while ...