Rebuilding MySQL Replication Group

When the MySQL replication group is compromised:
  1. Stop using any group member.
  2. Rebuild the replication group.

Additional actions are described below.

Backup Data in MySQL Database

Login to the last used member (the member in the zone in which admin uses Cluster Manager), run the following command to backup data in MySQL database. For example: 
mysqldump -uroot -pchangeme --set-gtid-purged=OFF --no-create-info --skip-triggers --ignore-table=masherysolar.audit_agents --ignore-table=masherysolar.migration_log masherysolar > /tmp/masherysolar.sql
Run the following command to copy backup data to admin workstation. For example: 
kubectl cp mysql-set-1-0:/tmp/masherysolar.sql ~/tmp/masherysolar.sql
Note: To find the last used member, run the following command. This example lists all services: 
kubectl get svc
The following example output shows all "cm-svc": 
NAME          TYPE           CLUSTER-IP     EXTERNAL-IP      PORT(S)
cm-svc-0      LoadBalancer   10.xxx.xx.34   35.xxx.xxx.45    8080:30908/TCP,8443:30291/TCP,7080:30820/TCP,7443:31435/TCP   29m
cm-svc-1      LoadBalancer   10.xxx.x.118   35.xxx.xxx.108   8080:32030/TCP,8443:31889/TCP,7080:30467/TCP,7443:30496/TCP   29m
cm-svc-2      LoadBalancer   10.xxx.x.100   35.xxx.xx.47     8080:31734/TCP,8443:31175/TCP,7080:32285/TCP,7443:30027/TCP   29m
If admin access cm-svc at "35.xxx.xxx.45", admin uses "cm-svc-0". In this case, the tml-sql pod is "mysql-set-0-0".

If mysqldump is stuck in last used member, make a backup of data in other members.

Undeploy All MySQL Members

Run the following command to undeploy all MySQL members: 
./undeploy-sql-pod.sh

Delete Persistent Volumes for MySQL Members

Run the following command to delete persistent volumes for MySQL members: 
kubectl delete pvc $(kubectl get pvc -o=jsonpath='{.items[?(@.metadata.labels.app=="mysql-svc")].metadata.name}')

Update Deployment Manifest

Update the deployment manifest to put the deployment in MySQL replication group rebuilding state.

Find the following line in the deployment manifest: 
"tml_sql_restore": "false",
Change this line to: 
"tml_sql_restore": "true",
Generate updated deployment scripts, for example: 
./compose manifest-multi-zones.json

Deploy All MySQL Members

To deploy all MySQL members, run the following command: 
./deploy-sql-pod.sh
Note: If the deployment of MySQL members is not successful, repeat the following steps:
  1. Undeploy all MySQL members.
  2. Delete Persistent Volumes for MySQL members.
  3. Deploy all MySQL members.

Copy MySQL Database Backup Data to Any member

To copy MySQL database backup data to any member, run the following command: 
kubectl cp ~/tmp/masherysolar.sql mysql-set-1-0:/tmp

Import Backup Data to MySQL Database

To import backup data, or restore data to MySQL database in the tml-sql pod, run the following command: 
mysql -uroot -pchangeme masherysolar < /tmp/masherysolar.sql

Restart Jetty (OnPremLoader application) process

For every tml-sql pod in the cluster, restart the Jetty process that runs the OnPremLoader application. For example, log into mysql-set-0-0 and then restart Jetty. For example: 
#log in to the mysql-set-0-0 pod
kubectl exec -it mysql-set-0-0 bash

#once logged into the pod, restart Jetty.
> service jetty restart