Migrate 2 DC MySQL cluster to 3 DC

MySQL & MariaDB Percona XtraDB Cluster 5.x
,
1

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?

2

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.

3

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

4

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

5

@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

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

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 ?

8

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

9

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

10

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

11

@matthewb I have two DC Percona MySQL cluster with master-master setup, with each master having two slaves in each dc. Currently MySQL Ver 14.14 Distrib 5.7.31-34, now I want to upgrade to version 8.0. I’m planning to upgrade passive dc first (1master and 2slaves) and will monitor for few days only then switch traffic to the passive dc (which then becomes active) and then upgrade the other dc(which still is on version 5.7). So ideally there might be few days where the cluster will have two versions running , is this recommended ? Or can I workaround this scenario where I want to monitor the new version and only then switch traffic(considering I have completed testing in non-prod environments).