Migrate 2 DC MySQL cluster to 3 DC

Hello

I have two DC MySQL cluster with master-master setup. So I have one master and two slaves in each dc. Currently I want to add third DC so that I have a backup in a scenario such as a DC goes down. MySQL Ver 14.14 Distrib 5.7.31-34.
Can you please help me with the steps to achieve this?

Hello @shirisha,
I would recommend using Percona XtraDB Cluster (PXC) to create a 3-node cluster between your DCs. 1 MySQL server in each DC, connects to the other 2 DCs in a synchronously replicated setup. We have lots of documentation on our website for how to setup a 3-node PXC.

This version of MySQL is completely dead. You should start your new cluster using MySQL 8.0.

Hello @matthewb
What is the latest MySQL version that I can upgrade from Percona Server 5.7.31 ?
The latest tarball file I have for 8.0 version is 8.0.30, can I install this version or what is the latest version I can install w.r.t 8.0 version. I’m aware that we have 8.0.33 , but did not find the binary file for this version.
I was checking the below link.

Thanks,
Shirisha

@shirisha You can upgrade to any 8.0.* version from 5.7.
8.0.30 is a bit old. I would go for latest 8.0.36.

Please use Software Downloads - Percona and all versions, tarballs, are here.

@matthewb I ran the mysql upgrade checker utility and see the below warnings. Could you please help me here.

  1. Usage of utf8mb3 charset - MySQL version 5.7 was using default utf8mb3, do I need to alter these tables to set to utf8mb4 after upgrade to 8.0 or it’s automatically taken care by upgrade ?
    abc_user - schema’s default character set: utf8
    abc_user.acl - column’s default character set: utf8

  2. Zero Date, Datetime, and Timestamp values - Do I need to make any changes to the default values for these tables ?
    abc.xyz - column has zero default value: 0000-00-00

Thanks
Shirisha

  1. Change this now to use utf8mb4. All your tables/columns should be utf8mb4. mb3 variant will be removed from 8.0 soon.
  2. The all zero date (0000-00-00) is not valid in MySQL 8.0. Alter the default for that column now in 5.7 to a real date.

What is the best way to alter big tables to use utf8mb4. When I have tables with huge data and multiple columns needs to be altered to utf8mb4, what is the best way to do in production environment ?

In general default value for a timestamp column can be current_timestamp similarly do we have anything for date column ?

Unfortunately, no, there is no default “current” date.

Please look at pt-online-schema-change — Percona Toolkit Documentation