In this article I am going to discuss how to setup a high availability MySQL Version 8 cluster running on Ubuntu 16. In order to achieve this, my setup consisted of 4 virtual machines running on Vmware Workstation 15 with bridged networking and fresh installs of Ubuntu 16 configured with static IP addresses.
Install MySQL Server
The first part is to install MySQL server onto the 3 chosen virtual machines. You will need to perform the following on all 3 machines.
first add the MySQL repository and then configure it:
|
1 2 3 |
wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb dpkg -i mysql-apt-config_0.8.10-1_all.deb |
once the above commands have been entered, issue the below:
|
1 2 3 |
apt-get update apt-get install mysql-server |
Configure Innodb Cluster
Configure the Bind-Address of the MySQL server (for each of the 3 virtual machines)
|
1 |
nano /etc/mysql/mysql.conf.d/mysqld.cnf |
in this file you will need to add the bind-address this is the IP address of the virtual machine in question for example my IP for one of my Virtual machines was 192.168.178.56, once you have done this you will need to restart MySQL
|
1 2 3 |
Bind-address = ip of server systemctl restart mysql |
Create a new MySQL admin user
you will need to do this on all 3 virtual machines, we need to create a user that has all access privilesge to MySQL in the below example my user was called joe.
|
1 2 3 |
CREATE USER 'joe'@'%' IDENTIFIED BY 'mypassword'; GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%' WITH GRANT OPTION; Flush Privileges; |
Hosts File
You now need to edit the hosts file on each of the 3 virtual machines, in this hosts file you will need to add each of the 3 virtual machines IP and Name, you also need to remove the loopback address of 127.0.1.1 that Ubuntu creates.
Configure the Instance
In order to create and manage the cluster instance you can download the MySQL Shell at the following location: https://dev.mysql.com/downloads/shell/
I downloaded the zip version for Windows 10 which does not need to be installed. once opened you will need to connect to the instance that will be your primary one.
|
1 |
\c joe@ubuntu16-mysql:3306 |
The above is an example and you will need to change to your details accordingly, you will be prompted for the password, if all being well you will be preseneted with the below image showing what instance you are connected too:
once connected you we now need to configure the instance issue the below command, and accept the prompts. you will need to do this for each of the 3 virtual machines, but once you have done one you can type the other remote machines in the brackets as parameters.
|
1 |
dba.configureInstance(); |
|
1 |
dba.configureInstance("joe@ubuntu16-mysql2:3306"); |
do the same for the last one.
Create the Cluster
we are now in the position of creating the cluster, issue the below command, you only need to do it just the once. This will create a cluster in the name of myCluster.
|
1 |
var cluster = dba.createCluster("myCluster") |
Let’s check the status.
|
1 2 3 |
var cluster = dba.getCluster(); cluster.status(); |
this will output the following:
The Router
With our cluster now configured we need to configure the final virtual machine to be our Router, the Router is responsible for allowing your application to talk to the MySQL cluster.
first add the MySQL repository and then configure it, accept all the defaults.
|
1 2 3 |
wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb dpkg -i mysql-apt-config_0.8.10-1_all.deb |
once the above commands have been entered, issue the below:
|
1 |
sudo apt-get install mysql-router |
Bootstrap the Router
Once installed we now need to bootstrap the router, change the name and host name of your server
|
1 |
mysqlrouter --bootstrap joe@ubuntu16-mysql1:3306 -d /tmp/myrouter |
this is the below output from running the above:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Please enter MySQL password for joe: # Bootstrapping MySQL Router instance at '/tmp/myrouter'... - Checking for old Router accounts - No prior Router accounts found - Creating mysql account 'mysql_router1_ybjkhdjvyve8'@'%' for cluster management - Storing account in keyring - Adjusting permissions of generated files - Creating configuration /tmp/myrouter/mysqlrouter.conf # MySQL Router configured for the InnoDB cluster 'myCluster' After this MySQL Router has been started with the generated configuration $ mysqlrouter -c /tmp/myrouter/mysqlrouter.conf the cluster 'myCluster' can be reached by connecting to: ## MySQL Classic protocol - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ## MySQL X protocol - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470 |
Start the bootstrap
|
1 2 3 |
joe@ubuntu16-mysqlrouter:~$ cd /tmp/myrouter/ joe@ubuntu16-mysqlrouter:/tmp/myrouter$ ./start.sh |
Done, that’s all. Your cluster is now configured and should one of your virtual machines fail your database application will continue to function.