Upgrading from single server to new GTID-replicated master and slave MySQL 5.7

Question

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?

Answer 1

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.

1. 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

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

3. Prepare Prd02 as slave of Prd01

So we'll have a replication chain:

PrdOld --> Prd01 --> Prd02

4. Do a full mysqldump (all replicated databases) of PrdOld

5. 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, ...).

6. Restore your dump on Prd01 witch is propagated to Prd02 (it may generate some slave lag during this step)

7. 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, ...).

8. Enable master-master replication (known also circular replication) between Prd01 and Prd02

9. 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 ...