<link rel="stylesheet" id="gtranslate-style-css" href="https://websetnet.b-cdn.net/wp-content/plugins/gtranslate/gtranslate-style24.css" type="text/css" media="all">

How to set up MySQL Cluster on Ubuntu

The MySQL NDB Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB (Network DataBase).
In this tutorial, we will show you how to set up MySQL Cluster on an Ubuntu 16.04 VPS.
We will use a total of 4 virtual servers, one for the management, one SQL node and two data nodes:

  • 172.16.1.1 – management VPS
  • 172.16.1.2 – SQL VPS
  • 172.16.1.3 – data1 VPS
  • 172.16.1.4 – data2 VPS

Install and Configure MySQL Cluster on the Management VPS

Download the latest version of MySQL Cluster available at http://dev.mysql.com/downloads/cluster/ to a directory of your virtual server and extract it using the following commands:

cd /opt/
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64.tar.gz -O mysql-cluster.tar.gz
tar -xvzf mysql-cluster.tar.gz -C /opt/
mv mysql-cluster-* mysql

Run the following commands:

cd /opt/mysql/
cp bin/ndb_mgm* /usr/local/bin/
chmod 755 /usr/local/bin/ndb_mgm*

Create a directory named ‘mysql-cluster’

mkdir -p /var/lib/mysql-cluster/

Then, create a new configuration named ‘config.ini’:

vi /var/lib/mysql-cluster/config.ini

add the following lines to it:

[ndbd default]
NoOfReplicas=2
# Memory to allocate for data storage
DataMemory=2G   
# Memory to allocate for index storage
IndexMemory=512M   

[mysqld default]
 
[ndb_mgmd default]
 
[tcp default]
 
# Management VPS
[ndb_mgmd]
# Enter the hostname or IP address of the Management VPS
hostname=172.16.1.1

# SQL VPS
[mysqld]
# Enter the hostname or IP address of the SQL VPS
hostname=172.16.1.2
 
# Data1 VPS
[ndbd]
# Enter the hostname or IP address of the Data1 VPS
hostname=172.16.1.3
DataDir= /var/lib/mysql-cluster
 
# Data2 VPS
[ndbd]
# Enter the hostname or IP address of the Data2 VPS
hostname=172.16.1.4
DataDir=/var/lib/mysql-cluster

Do not forget to change the hostnames/IP addresses according to your actual server hostnames/IP addresses.

Install and Configure MySQL Cluster on the SQL VPS

Check if there are MySQL packages already installed:

dpkg -l | grep mysql

rc  libmysqlclient18:amd64               5.5.49-0ubuntu0.14.04.1                  amd64        MySQL database client library
ii  mysql-client                         5.7.16-0ubuntu0.16.04.1                  all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-5.7                     5.7.16-0ubuntu0.16.04.1                  amd64        MySQL database client binaries
ii  mysql-client-core-5.7                5.7.16-0ubuntu0.16.04.1                  amd64        MySQL database core client binaries
ii  mysql-common                         5.7.16-0ubuntu0.16.04.1                  all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                         5.7.16-0ubuntu0.16.04.1                  all          MySQL database server (metapackage depending on the latest version)
rc  mysql-server-5.5                     5.5.49-0ubuntu0.14.04.1                  amd64        MySQL database server binaries and system database setup
ii  mysql-server-5.7                     5.7.13-0ubuntu0.16.04.2                  amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7                5.7.13-0ubuntu0.16.04.2                  amd64        MySQL database server binaries

Remove these packages and their configurations using:

apt-get purge mysql-c* mysql-s*

Or, remove packages and configuration files listed by the ‘dpkg -l | grep mysql’ command above using:

apt-get purge mysql-client mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.5 mysql-server-5.7 mysql-server-core-5.7 libmysqlclient18

Download the latest version of MySQL Cluster available at ‘http://dev.mysql.com/downloads/cluster/’ to the opt directory of your virtual server and extract it using the following commands:

cd /opt/
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64.tar.gz -O mysql-cluster.tar.gz
tar -xvzf mysql-cluster.tar.gz -C /opt/
Run the following commands:
mv mysql-cluster-* mysql
mv mysql /usr/local/
cd /usr/local/mysql/
mv bin/* /usr/local/bin/
rm -rf bin/
ln -s /usr/local/bin /usr/local/mysql/
cp support-files/mysql.server /etc/init.d/mysql
systemctl enable mysql
mysqld --initialize
chown -R root:mysql /usr/local/mysql/
chown -R mysql /usr/local/mysql/data/

Edit the MySQL configuration file (/etc/my.cnf):

vi /etc/my.cnf

add the following lines:

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

ndbcluster
ndb-connectstring=172.16.1.1

[mysql_cluster]

ndb-connectstring=172.16.1.1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Log in to the management VPS via SSH and run the following command:

ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/

In order to configure ndb_mgmd to start on boot, add the following command to ‘/etc/rc.local’:

vi /etc/rc.local

and add:

ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/

before the ‘exit 0’ line and save the file.
Start the MySQL service on the SQL VPS:

service mysql start

In order to improve the security of your MySQL installation, run:

/usr/local/mysql/bin/mysql_secure_installation

Install and Configure MySQL Cluster on the Data VPSes

Check if there are MySQL packages already installed using:

dpkg -l | grep mysql

Remove them, including the configuration files:

apt-get purge mysql-client mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.5 mysql-server-5.7 mysql-server-core-5.7 libmysqlclient18

Download and extract the latest version of MySQL Cluster to the opt directory on the data1 VPS:

cd /opt/
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64.tar.gz -O mysql-cluster.tar.gz
tar -xvzf mysql-cluster.tar.gz -C /opt/

Run the following commands:

mv mysql-cluster-* mysql
mv mysql /usr/local/
chown -R root:mysql /usr/local/mysql/
cd /usr/local/mysql/
mv bin/* /usr/local/bin/
rm -rf bin/
ln -s /usr/local/bin /usr/local/mysql/

Edit the MySQL configuration file:

vi /etc/my.cnf

add the following lines:

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

ndbcluster
ndb-connectstring=172.16.1.1

[mysql_cluster]

ndb-connectstring=172.16.1.1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Run the following commands:

mkdir /var/lib/mysql-cluster
chown -R mysql /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
ndbd --initial
echo "ndbd" > /etc/init.d/ndbd
chmod 755 /etc/init.d/ndbd

Repeat the same procedure on the Data2 VPS.

In order to check the MySQL cluster status, run the following command on the management VPS:

ndb_mgm -e show

The output should look like this:

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @172.16.1.3  (mysql-5.7.16 ndb-7.5.4, Nodegroup: 0, *)
id=4    @172.16.1.4  (mysql-5.7.16 ndb-7.5.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @172.16.1.1  (mysql-5.7.16 ndb-7.5.4)

[mysqld(API)]   1 node(s)
id=2    @172.16.1.2  (mysql-5.7.16 ndb-7.5.4)

That is it. The set up of MySQL cluster is now complete.

 

Source

Leave a Comment