Introduction
High availability in relation to a database means that the particular database has the ability to cope with errors from the host. It is also important for the database to be able to recover from the failures from the MySQL, the operating system or the hardware maintenance that may otherwise cause a downtime.
High availability solutions depend on the type of application being deployed, your environment’s best practices or the level of availability required. With MySQL, just like the typical cluster environment, there are:
- Management node which is used to monitor and configure the clients
- Data nodes which are used to store data and share/replicate
- SQL nodes which are the interfaces to connect to all the nodes
Before You Get Started
- At least three VPS instances running Ubuntu 18.04 configured in a virtual private network environment
- Non root users with sudo privileges configured on each VPS.
- wget and tar packages installed in the VPS.
Steps
Update the system packages in all 3 nodes.
$ sudo apt update -y && sudo apt upgrade -y
Configure the management node
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz
Extract the compressed file into a new folder.
$ mkdir mysql $ tar -xzvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz -C mysql --strip-components=1
Copy the cluster management server binary files; ndb_mgm and ndb_mgmd into your local binary files folder and change the mode of the files to executable.
$ sudo cp /mysql/bin/ndb_mgm* /usr/local/bin $ sudo chmod +x /usr/local/bin/ndb_mgm*
Create a configuration file called config.ini in a newly created mysql-cluster directory for the management cluster node. This file will identify the SQL node and the management node’s IP addresses.
It will also identify the data node’s IP address and their data directories.
$ sudo mkdir -p /var/lib/mysql-cluster $ nano /var/lib/mysql-cluster/config.ini
Special Note: Remember to replace the IP addresses with ones in your environment setup. As for the below configuration code, if you only have one data node, the NoOfReplicas should be set to 1 because you only have one data node where a table replica will be stored.
[ndbd default] NoOfReplicas=1 DataMemory=80M IndexMemory=18M [mysqld default] [ndb_mgmd default] [tcp default] # Cluster Control / Management node [ndb_mgmd] hostname=192.168.56.10 # Data Node [ndbd] hostname=192.168.56.11 DataDir= /var/lib/mysql-cluster # Data Node 1 [ndbd] HostName=192.168.56.12 DataDir=/var/lib/mysql-cluster # SQL Node [mysqld] hostname=192.168.56.40
Now start the cluster management node and enable it on start up. Remember all the other nodes ought to be active otherwise you will receive an error on the non-existing IP addresses.
$ sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/MySQL Cluster Management Server mysql-5.6.31 ndb-7.4.12$ sudo echo'ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/' >> /etc/rc.local
Special Note: If you get a permissions error, try logging in as root (sudo su) and run the command again.
Configure the data node
Install the libaio1 package from linux which enables making multiple simultaneous system I/O calls.
$ sudo apt install libaio1
Create a new group and user called mysql
$ groupadd mysql $ useradd -g mysql mysql
Download the mysql-cluster package just as you did in the management node.
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz $ mkdir mysql $ tar -xzvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz -C mysql --strip-components=1
This time, you will move the entire mysql folder to /usr/local directory.
$ sudo mv mysql/ /usr/local
Change directory into the moved folder and run the system databases creation script mysql_install_db
$ cd /usr/local/mysql $ ./scripts/mysql_install_db --user=mysql
Copy the mysql service file to the init.d folder and enable the mysql service on start up.
$ sudo cp support-files/mysql.server /etc/init.d/mysql $ sudo systemctl enable mysql
Move the local binary files to your local binary directory and create a new symlink.
$ sudo mv bin/* /usr/local/bin/ $ sudo rm -rf bin/ $ sudo ln -s /usr/local/bin /usr/local/mysql/
Change the mysql directory to root owner in the mysqlgroup and data directory to mysql owner in mysql group.
$ sudo chown -R root:mysql . $ sudo chown -R mysql data
Now create a new configuration file for your data node to define the cluster and management node to which it belongs to.
$ sudo mkdir -p /var/lib/mysql-cluster $ sudo chown -R mysql /var/lib/mysql-cluster
Start the data node
$ ndbd --initial 2018-11-01 00:51:08 [ndbd] INFO -- Angel connected to '192.168.56.20:1186' 2018-11-01 00:51:23 [ndbd] INFO -- Angel allocated nodeid: 2$ sudo systemctl start mysql
You can now create a password for your mysql usage
$ sudo mysql_secure_installation
Configure the SQL node
You need to first install the mysql cluster package and move the folder to your /usr/local directory.
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz $ mkdir mysql $ tar -xzvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz -C mysql --strip-components=1 $ mv mysql /usr/local/
Run the install database script file.
$ cd /usr/local/mysql $ sudo ./scripts/mysql_install_db --user=mysql
Copy the mysql service file and enable it on boot
$ cp support-files/mysql.server /etc/init.d/mysql $ sudo systemctl enable msql
Move the local binary files to your local binary directory and create a new symlink.
$ sudo mv bin/* /usr/local/bin/ $ sudo rm -rf bin/ $ sudo ln -s /usr/local/bin /usr/local/mysql/
Finally, create a my.cnf file and paste in the following code while replacing your management nodes IP address.
$ vim /etc/my.cnf
# MySQL Config [mysqld] datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql # Run ndb storage engine ndbcluster # IP address management node ndb-connectstring=192.168.56.20 [mysql_cluster] # IP address management node ndb-connectstring=192.168.56.20 # MySQL Pid and Log [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Save changes, exit then start the mysql service and configure your password.
$ sudo systemctl start mysql $ sudo mysql_secure_installation $ mysql -u root -p
To verify that your installation works, you will create a sample database in your SQL node and check whether it will be replicated in the data node.
In the same SQL VPS, create a new database (name it whatever your want)
mysql> CREATEDATABASE vegetables; Query OK, 1 row affected (0.02 sec)
Log into the data node and check on the databases, the created database in the SQL node should have been replicated.
$ mysql -u root -p mysql> SHOWDATABASES;
Conclusion
You have successfully set up your MySQL cluster replication for high availability. You can shut down your cluster status from the management node from the ndb_mgm console by using the shutdown command.
Finally, to monitor the same from the ndb_mgm console, you can use the show command. The MySQL cluster replication has proved to provide fault tolerance, automated failover, and elasticity.
Check out these top 3 Linux hosting services
- Check the recommendations for the best VPS and get a suitable one.
