MySQL InnoDB Cluster on Ubuntu 20.04 – Infinys Cloud

A few weeks ago, there was request from the our client for about how to implement MySQL InnoDB Cluster on their Virtual Machines at our cloud server Infinys Cloud. For fulfill that POC (purpose of concept), we are prepare some server and the step installation like as below :

ServerIPSoftwareRole
APP Server + MySQL Router117.xxx.xxx.1MySQL RouterClient / APP Server
MYSQL-CL-01117.xxx.xxx.2MySQL Server, MySQL ShellPrimary/Master DB Server
MySQL-CL-02117.xxx.xxx.3MySQL Server, MySQL ShellRead Only DB Server 1
MySQL-CL-03117.xxx.xxx.4MySQL Server, MySQL ShellRead Only DB Server 2

A. Setting up MySQL Innodb Cluster

The Innodb Cluster settings below are carried out on 3 machines, namely MYSQL-CL-01, MySQL-CL-02, and MySQL-CL-03

The steps are as follows:

  1. Update Host
  • Run sudo nano /etc/hosts

Register MYSQL-CL-01, MySQL-CL-02, and MySQL-CL-03 with the IP that has been registered above. For example one of the computer names uses the IP 127.0.0.1 then it must be replaced with the IP of the server instead of 127.0.0.1

2. Install MySQL Server Engine

  • Run sudo apt update
  • Run sudo apt-get install mysql-server
  • sudo systemctl status mysql.
  • Run sudo mysql_secure_installation . Run this and follow all the steps.

After all the steps are done, it means that the MySQL Server installation stage is complete.

3. Create User untuk dipakai di Replikasi System

  • Run, sudo mysql -u root p
  • Execute Query : Select user,host.plugin from mysql.user;
mysql> Select user, host, plugin 
user 
debi an— sys —maint 
mysql . infoschema 
from mysql . user; 
mysql . session 
host 
local host 
local host 
local host 
local host 
local host 
plugin 
caching 
caching 
caching 
caching 
sha2 
sha2 
sha2 
sha2 
password 
password 
password 
pa s s word 
mysql . sys 
100 t 
rows in sec 
auth socket 
(0.00 sec)
  • CREATE USER ‘usr_replication’@’%’ IDENTIFIED WITH mysql_native_password BY ‘XXXXXXX’;

XXXXXXX = Password.

  • GRANT ALL PRIVILEGES ON *.* To ‘usr_replication’ with GRANT OPTION;
  • Flush Privilges;
  • Execute Query : Select user,host.plugin from mysql.user;

4. Install MySQL Shell

  • Run sudo apt-install snapd
  • Run snap install mysql-shell
  • Run sudo apt-get update

B. Configure Group Replication

  1. Create Instance MYSQL-CL-01
  • Login to server MYSQL-CL-01
  • Run mysqlsh MYSQL-CL-01 -u – usr_replication and type the password
  • Run Dba.help (); To see all commands that provided.
ACC IibuCeEIIOI: unkncwn attribute: 
dba . help ( ) 
che ckIns C anceConfi guy a C i on 
dba 
InnoDB cluster and 
repli caseC management functions . 
DESCRIPTION 
Entry point for AdminAPI 
sets. 
InnoDB clusters 
fun cc ions , 
including InnoDB clusters 
and 
repl i ca 
The dba. configure instance function can be used Co configure a MySQL 
instance with the settings required Co use iC in an InnoDB cluster. 
InnoDB clusters can be created with Che 
Once created, InnoDB cluster management 
dba. get cluster() function. 
InnoDB Repli case Cs 
dba. create cluster() function. 
objects can be obtained with Che 
The dba. configure replica sec instance function can be used Co 
configure a MySQL instance with the 
replicaset . 
ReplicaSeCs can be created with Che 
Once created, repli caseC management 
settings required Co use iC in a 
dba. create replica sec() function. 
objects can be obtained with Che 
dba. get replica set O 
S andboxe s 
Utility functions are 
can be used to create 
PROPERTIES 
verb3se 
function . 
provided Co create sandb3X MySQL 
Cest clusters and replicasets . 
instances , 
whi ch 
Cont s debug me s sage 
operations . 
FUNCTIONS 
ve rbos i C y 
for 
s andbox 
re laced dba 
check instance configuration (instance [ , 
options ) 
Validates an instance for MySQL InnoDB Cluster usage . 
configure instance ( [instance] [ , options] ) 
Validates 
usage . 
configure local 
Validates 
usage . 
and configures an instance for MySQL InnoDB Cluster 
instance (instance [ , options] ) 
and configures a local instance for MySQL 
options ) 
for use in an 
InnoDB Cluster 
InnoDB 
configure replica sec instance ( [instance] [ , 
Validates and configures an instance 
ReplicaSet . 
create cluster (name [ , options] ) 
Creates a MySQL InnoDB cluster .
  • Run, dba.configure_local_instance(‘usr_replication@117.xxx.xxx.2’) and type the password
  • Instance server MYSQL-CL-01 completed.

2. Create Instance MYSQL-CL-02

  • Login ke server MYSQL-CL-02
  • Run mysqlsh -u – usr_replication and type in the password.
  • run, dba.configure_local_instance(‘usr_replication@117.xxx.xxx.3’) type in the password
  • Instance server MYSQL-CL-02 completed.

3. Create Instance MYSQL-CL-03

  • Login ke server MYSQL-CL-03
  • Run mysqlsh -u – usr_replication and type in the password.
  • run, dba.configure_local_instance(‘usr_replication@117.xxx.xxx.4’) type in the password
  • Instance server MYSQL-CL-02 completed.

4.Create New Cluster and Register Instance to that new Cluster

  • Login ke server MYSQL-CL-01
  • run mysqlsh -u – usr_replication dan masukkan password
  • Run this to create New Cluster : cluster=dba.createCluster(‘clusterName’) ->In this test i use the name ProdCluster
  • cluster.addInstance(‘usr_replication@@117.xxx.xxx.3’)
  • cluster.addInstance(‘usr_replication@@117.xxx.xxx.4’)
  • After all servers have been registered into the cluster, we can find out their status by running dba.status ();

C. Install MySQL Router

MySQL Router recommend to be installed on same server with the application used. So it means that MySQL Router can be installed on more than one server.

  1. MySQL Router Install
  • Run, sudo apt-get mysql-router
  • Run, mysqlrouter –user root –bootstrap usr_replication@MYSQL-CL-01  –directory /tmp/myrouter

2.Verifiying the MySQL Router Works

  • Run mysql -uroot -p -P 6446
  • If you can connect to the database, it means that our MySQL Router installation has been successful


Indonesia Version

Beberapa minggu yang lalu, ada permintaan dari klien kami tentang cara mengimplementasikan MySQL InnoDB Cluster pada Mesin Virtual mereka di server cloud kami Infinys Cloud. Untuk memenuhi POC (tujuan konsep) tersebut, kami menyiapkan beberapa server seperti pada tabel di bawah ini:

ServerIPSoftwareRole
APP Server + MySQL Router117.xxx.xxx.1MySQL RouterClient / APP Server
MYSQL-CL-01117.xxx.xxx.2MySQL Server, MySQL ShellPrimary/Master DB Server
MySQL-CL-02117.xxx.xxx.3MySQL Server, MySQL ShellRead Only DB Server 1
MySQL-CL-03117.xxx.xxx.4MySQL Server, MySQL ShellRead Only DB Server 2

A. Setting up MySQL Innodb Cluster

Setting Innodb Cluster dibawah ini, dilakukan di 3 mesin yaitu MYSQL-CL-01, MySQL-CL-02, dan MySQL-CL-03

Langkah-langkah sebagai berikut :

  1. Update Host
  • Jalankan sudo nano /etc/hosts

Daftarkan MYSQL-CL-01, MySQL-CL-02, dan MySQL-CL-03 dengan IP yang sudah di daftarkan diatas. Bila misalnya salah satu nama komputer itu menggunakan IP 127.0.0.1 maka harus di ganti dengan IP dari server tersebut bukan 127.0.0.1

2. Install MySQL Server Engine

  • Jalankan sudo apt update
  • Jalankan sudo apt-get install mysql-server
  • sudo systemctl status mysql. Untuk mengetahui bahwa installasi sudah berhasil dan mysql harus dalam keadaan active (running)
  • Jalankan sudo mysql_secure_installation . Untuk setup root dan beberapa permission MySQL server lainnya.

Setelah semua langkah dilakukan, artinya tahapan Instalasi MySQL Server sudah selesai.

3. Create User untuk dipakai di Replikasi System

  • Jalankan, sudo mysql -u root p
  • Eksekusi Query : Select user,host.plugin from mysql.user;
mysql> Select user, host, plugin 
user 
debi an— sys —maint 
mysql . infoschema 
from mysql . user; 
mysql . session 
host 
local host 
local host 
local host 
local host 
local host 
plugin 
caching 
caching 
caching 
caching 
sha2 
sha2 
sha2 
sha2 
password 
password 
password 
pa s s word 
mysql . sys 
100 t 
rows in sec 
auth socket 
(0.00 sec)
  • CREATE USER ‘usr_replication’@’%’ IDENTIFIED WITH mysql_native_password BY ‘XXXXXXX’;

XXXXXXX = Password.

  • GRANT ALL PRIVILEGES ON *.* To ‘usr_replication’ with GRANT OPTION;
  • Flush Privilges;
  • Eksekusi Query : Select user,host.plugin from mysql.user;

4. Install MySQL Shell

  • Jalankan sudo apt-install snapd
  • snap install mysql-shell
  • sudo apt-get update

B. Configure Group Replication

  1. Create Instance MYSQL-CL-01
  • Login ke server MYSQL-CL-01
  • Jalankan mysqlsh MYSQL-CL-01 -u – usr_replication dan masukkan password
  • Jalankan Dba.help (); Untuk melihat perintah apa yang bisa kita lakukan.
ACC IibuCeEIIOI: unkncwn attribute: 
dba . help ( ) 
che ckIns C anceConfi guy a C i on 
dba 
InnoDB cluster and 
repli caseC management functions . 
DESCRIPTION 
Entry point for AdminAPI 
sets. 
InnoDB clusters 
fun cc ions , 
including InnoDB clusters 
and 
repl i ca 
The dba. configure instance function can be used Co configure a MySQL 
instance with the settings required Co use iC in an InnoDB cluster. 
InnoDB clusters can be created with Che 
Once created, InnoDB cluster management 
dba. get cluster() function. 
InnoDB Repli case Cs 
dba. create cluster() function. 
objects can be obtained with Che 
The dba. configure replica sec instance function can be used Co 
configure a MySQL instance with the 
replicaset . 
ReplicaSeCs can be created with Che 
Once created, repli caseC management 
settings required Co use iC in a 
dba. create replica sec() function. 
objects can be obtained with Che 
dba. get replica set O 
S andboxe s 
Utility functions are 
can be used to create 
PROPERTIES 
verb3se 
function . 
provided Co create sandb3X MySQL 
Cest clusters and replicasets . 
instances , 
whi ch 
Cont s debug me s sage 
operations . 
FUNCTIONS 
ve rbos i C y 
for 
s andbox 
re laced dba 
check instance configuration (instance [ , 
options ) 
Validates an instance for MySQL InnoDB Cluster usage . 
configure instance ( [instance] [ , options] ) 
Validates 
usage . 
configure local 
Validates 
usage . 
and configures an instance for MySQL InnoDB Cluster 
instance (instance [ , options] ) 
and configures a local instance for MySQL 
options ) 
for use in an 
InnoDB Cluster 
InnoDB 
configure replica sec instance ( [instance] [ , 
Validates and configures an instance 
ReplicaSet . 
create cluster (name [ , options] ) 
Creates a MySQL InnoDB cluster .
  • Jalankan, dba.configure_local_instance(‘usr_replication@117.xxx.xxx.2’) dan masukkan password
  • Instance server MYSQL-CL-01 selesai.

2. Create Instance MYSQL-CL-02

  • Login ke server MYSQL-CL-02
  • Jalankan mysqlsh -u – usr_replication dan masukkan password
  • Jalankan, dba.configure_local_instance(‘usr_replication@117.xxx.xxx.3’) dan masukkan password
  • Instance server MYSQL-CL-02 selesai.

3. Create Instance MYSQL-CL-03

  • Login ke server MYSQL-CL-03
  • Jalankan mysqlsh -u – usr_replication dan masukkan password
  • Jalankan, dba.configure_local_instance(‘usr_replication@117.xxx.xxx.4’) dan masukkan password
  • Instance server MYSQL-CL-03 selesai.

4.Create New Cluster and Register Instance to that new Cluster

  • Login ke server MYSQL-CL-01
  • Jalankan mysqlsh MYSQL-CL-01 -u – usr_replication dan masukkan password
  • Jalankan cluster=dba.createCluster(‘clusterName’) -> didalam test ini saya menggunakan nama ProdCluster
  • cluster.addInstance(‘usr_replication@@117.xxx.xxx.3’)
  • cluster.addInstance(‘usr_replication@@117.xxx.xxx.4’)
  • Setelah semua server sudah di daftarkan kedalam Cluster tersebut, maka kita bisa mengetahu statusnya dengan menjalankan dba.status();

C. Install MySQL Router

MySQL Router sebaiknya di install satu server dengan aplikasi yang digunakan. Jadi MySQL Router bisa diinstall lebih dari satu server.

  1. MySQL Router Install
  • Jalankan , sudo apt-get mysql-router
  • Jalankan, mysqlrouter –user root –bootstrap usr_replication@MYSQL-CL-01  –directory /tmp/myrouter

2.Verifiying the MySQL Router Works

  • Login dengan mysql -uroot -p -P 6446
  • Bila bisa connect artinya instalasi MySQL Router sudah berhasil/.

Other References :

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.