Troubleshooting MySQL InnoDB Cluster pada OpenStack Charm

Tutorial ini troubleshooting incident MySQL InnoDB Cluster yang tidak sehat pada OpenStack Charm.

1. Gejala

Contoh error:

Error : MySQL InnoDB Cluster not healthy: None

2. Coba Reboot Cluster dari Complete Outage

juju run-action mysql-innodb-cluster/0 reboot-cluster-from-complete-outage
juju run-action mysql-innodb-cluster/1 reboot-cluster-from-complete-outage
juju run-action mysql-innodb-cluster/2 reboot-cluster-from-complete-outage

3. Cek Status Cluster

juju run-action mysql-innodb-cluster/0 cluster-status
juju run-action mysql-innodb-cluster/1 cluster-status
juju run-action mysql-innodb-cluster/leader --wait cluster-status
juju show-action-output XX

Backup/dump jika diperlukan:

juju run-action mysql-innodb-cluster/0 mysqldump

4. Ambil Leader Data

juju run --unit mysql-innodb-cluster/1 leader-get

Data penting yang dicari:

cluster-password: <CLUSTER_PASSWORD>
mysql.passwd: <MYSQL_ROOT_PASSWORD>

5. Install MySQL Shell

wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_8.0.34-1ubuntu22.04_amd64.deb
sudo dpkg -i mysql-shell_8.0.34-1ubuntu22.04_amd64.deb

6. Masuk ke Node Leader dengan mysqlsh

mysqlsh clusteruser@<MYSQL_NODE_IP> --js

Setelah login:

var cluster = dba.getCluster("jujuCluster")

7. Remove Instance Bermasalah dari Cluster

Pastikan node mana yang bermasalah, lalu remove dengan force:

cluster.removeInstance('root@<MYSQL_NODE_IP_1>:3306',{force: true})
cluster.removeInstance('root@<MYSQL_NODE_IP_2>:3306',{force: true})
cluster.removeInstance('root@<MYSQL_NODE_IP_3>:3306',{force: true})

8. Bersihkan Node Bermasalah

Masuk ke node bermasalah, login MySQL:

mysql -u root -p

Jalankan SQL:

set GLOBAL super_read_only = OFF;
STOP GROUP_REPLICATION;
RESET SLAVE ALL;
DROP DATABASE mysql_innodb_cluster_metadata;

9. Tambahkan Instance Kembali ke Cluster

Masuk ke MySQL Shell lagi, lalu tambahkan node:

cluster.addInstance({user: "clusteruser", host: "<MYSQL_NODE_IP_1>", port: 3306, password: "<CLUSTER_PASSWORD>"});
cluster.addInstance({user: "clusteruser", host: "<MYSQL_NODE_IP_2>", port: 3306, password: "<CLUSTER_PASSWORD>"});
cluster.addInstance({user: "clusteruser", host: "<MYSQL_NODE_IP_3>", port: 3306, password: "<CLUSTER_PASSWORD>"});

10. Command SQL Tambahan

select * from performance_schema.replication_group_members;
show master status;
SHOW REPLICAS;

Jika perlu bootstrap group replication manual:

dba.configureInstance();
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

11. Jika Vault Ikut Error

Setelah MySQL sehat, biasanya Vault perlu dicek/unseal ulang:

juju ssh vault/0
export VAULT_TOKEN=<VAULT_TOKEN>
vault operator unseal <UNSEAL_KEY_1>
vault operator unseal <UNSEAL_KEY_2>
vault operator unseal <UNSEAL_KEY_3>
vault status