Setting up MySQL InnoDB cluster on Private Network
Tags: mysql, cluster, database, linux
Published: 2020-02-26 02:27:22

## Introduction
Out off all the tutorial out there, all the cluster setup are shown for non private networks. since the setup is for private network, u need to skip certain stuff or might as well add few stuffs. 


## First thing first, the checklist:
- All the server nodes in the same  **Private Network**.
- Minimum of 3 Servers nodes.
- MySQL 8.0 community version installed on all those servers over here [MySQL Installation](https://dev.mysql.com/doc/refman/8.0/en/linux-installation.html).
- MySQL Shell installed on one those servers [MySQL Shell Installation](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html).

## Server Setup Details
- App Server : apt 127.0.0.4
- DB Server node 1 : dbt1 127.0.0.1
- DB Server node 2 : dbt2 127.0.0.2
- DB Server node 3 : dbt3 127.0.0.3

After checked all the checklist, start off with the hosts config inoder to allow the private network can talk to each other.

- run `sudo nano /etc/hosts`
- set the nodes internal ip into the host .

  ```
  127.0.0.1 db1
  127.0.0.2 db2
  127.0.0.3 db3
  ```
- remove all iptables config by running `sudo iptables -F` or set the iptables properly
- set the **firewalld** permanent to allow `3306` `33060` `33061` `33062` 
- configure the **my.cnf** settings on `/etc/my.cnf`
- backup the mysql database (incase of emergency)
- create a user specifically for clusteradmin access to all the server nodes

### Setting up MySQL Innodb Cluster
- start setting up the cluster by running the `$ mysqlshell`
- run this on the cli `$ mysqlshell` 
- run this on the mysqlshell 
  - `dba.configureInstance('clusteradmin@127.0.0.1')` to configure dbt1 for mysql clustering     
  - `dba.configureInstance('clusteradmin@127.0.0.2')` to configure dbt2 for mysql clustering
  - `dba.configureInstance('clusteradmin@127.0.0.3')` to configure dbt3 for mysql clustering.
  - on the same mysqlshell run this command `\c clusteradmin@127.0.0.1` . to connect to the first master nodes which is the dbt1.
  - run and type this in the mysqlshell `cluster=dba.createCluster('clusterName')` to instantiate and create the cluster group.
  - after that we need to add the instance to the group by running this commands
    - `cluster.addInstance('clusteradmin@127.0.0.2')` this commands is for adding the second db nodes to the cluster group of `clusterName`.
      - just key in the password of clusteradmin user and choose `clone` to clone from the master nodes.
    - `cluster.addInstance('clusteradmin@127.0.0.3')` this commands is for adding the third db nodes to the cluster group of `clusterName`.
      - just key in the password of clusteradmin user and choose `Incremental Recovery` to get the metadata from the master nodes.
      
 - after all the instance successfully added, you can run `cluster.status()` in the connected mysqlshell to check if the clustering working. if the output as shown 3 nodes are available, then the clustering is working .

### Setting up the MySQL Router
Always make sure that you have installed the mysql-router into your application server. following this tutorial [MySQL Router Installation](https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-installation-linux.html).

Steps to Configure: 
- if you have mysqld installed and running make sure that mysqld on your app server is not running by run the command  `$ sudo systemctl stop mysqld`
- just run this command to bootsrapping the router to the cluster server `$ mysqlrouter --bootstrap mysqlrouter@127.0.0.1:3306 --conf-use-sockets --directory=/tmp/mysqlrouter`
- change directory to `/tmp/mysqlrouter`
- run `bash start.sh`
- Press `Enter` on your keyboard
- it will create the router sockets automatically for you.
- configure your MySQL client at `/etc/my.cnf` with :
   ```
   [client]
   socket=/tmp/mysqlrouter/mysql.sock
   ```

### Verifiying the MySQL Router Works
- configure your application by using the new port given .
- try to logged into the cluster through router config by running `mysql -uroot -p -P 6446`
- see if the database shown on the output is has the same amount of tables in the cluster 

## sources
1. [MySQL innodb cluster setup](https://lefred.be/content/mysql-innodb-cluster-with-2-data-centers-for-disaster-recovery-howto/)
2. [Port for MySQL innodb cluster](https://dev.mysql.com/doc/mysql-port-reference/en/mysql-ports-reference-tables.html)
3. [Firewall Provisions ](https://lefred.be/content/mysql-innodb-cluster-automatic-provisioning-firewall-and-selinux/)
4. [MySQL Installation ](https://dev.mysql.com/doc/refman/8.0/en/linux-installation.html)
5. [what is host file](https://www.techopedia.com/definition/25142/hosts-file)
6. [MySQL Router Installation](https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-installation-linux.html)
7. [MySQL Shell Installation](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html)