Migrate from regular MySQL(AWS EC2)to Percona Cluster (GCP VM)in production.

Prerequisites:

1.we need one MySQL stand alone server .
2.we need percona setup installed in new server.

Mysql stand alone :

let start the activity in MySQL stand alone server.
1.we need to check MySQL status.
2. we need to configure the replication from MySQL stand alone to percona setup.
3.go to mysql.cnf file

vi /etc/mysql/mysql.cnf.d/mysqld.cnf
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
gtid_mode=ON
enforce_gtid_consistency=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

4.once configure the setup restart the services.

percona cluster setup:

1.we can install the percona packages

wget https://repo.percona.com/apt/percona-release_0.1-5.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-5.$(lsb_release -sc)_all.deb
apt-get update
apt-get upgrade
apt-get dist-upgrade
apt-get install percona-toolkit
apt-get install percona-xtradb-cluster-server

2.configure the percona setup.

vi /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

Performa all same activity on other nodes.

Note : wsrep_node_address provide the ip which respective of the server.

wsrep_cluster_address=gcomm://0.0.0.0.,1.1.1.1,2.2.2.2   #mention private ip in all 3 nodes.
wsrep_node_address=0.0.0.0
pxc_strict_mode=DISABLED
wsrep_sst_auth="sstuser:PASSW0RD"  #sstuser mentiond any thing.

Step 3:

mysql> CREATE USER ‘sstuser’@’localhost’ IDENTIFIED BY ‘PASSW0RD’;
mysql> GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘sstuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

after create the use we will start the MySQl services in other two nodes.

go and check wsrep in Three nodes.

show status like 'wsrep%';

step 4:

we can configure the replication from mysql stand alone to percona.

1.open percona server go to config file

vi /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

# Edit to your requirements.
[mysqld]
server-id=22
datadir=/var/lib/percona-xtradb-cluster
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
replicate-ignore-db=mysql
gtid_mode=ON
enforce_gtid_consistency=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE

step 5:

Mysql stand alone server master status:

1.show master status;

show master status;

+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — + — — — — — — — — — — — — — — — — — — — — +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — + — — — — — — — — — — — — — — — — — — — — +
| mysql-bin.000002 | 194 | | | 10517d2b-419e-11ea-9299–42010a000012:1 |
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — + — — — — — — — — — — — — — — — — — — — — +
1 row in set (0.00 sec)

2.Apply the change master in slave which is percona cluster:

we have to create a CHANNEL specific to each master. You will need to also name this channel, and I simply named the channels “master-142” and “master-143” to match their server_id‘s (as well as their IP addresses). Here is how you start replication for Master #1 (server_id=142).

Stop slave ;

SET GLOBAL gtid_purged="10517d2b-419e-11ea-9299-42010a000012:1";
CHANGE MASTER TO MASTER_HOST=’0.0.0.0', MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_AUTO_POSITION = 1 FOR CHANNEL ‘Database_name-142’;
start slave ;

check slave status:

show slave status\G;

Take the backup from MySQL stand alone server and restore to the percona setup until sink we can do that.