CentOS 7.x mariadb clustering

Home > CentOS > CentOS 7.x > Databases > MariaDB > CentOS 7.x mariadb clustering

A MySQL Cluster has three types of nodes. Each node has a dedicated type. We avoid setting up multiple roles (data, sql, etc.) on the same node.

Management Node

This node is responsible for coordination among various data and SQL nodes. Without this node we would have to make too many changes across the node for any small change (new node addition, node down, etc.). Management node takes care of entire cluster coordination.

Data Nodes

These are the nodes where SQL database actually gets stored

SQL Nodes

These are the nodes where SQL clients (Mariadb clients) connect and run queries. These nodes do not store database locally, but instead receive/send data from/to data nodes.

For a demo setup we can use five servers as follows:

• Management Node
  1. mysql1 = 192.168.1.
• Data Nodes
  1. mysql2 = 192.168.1.61
  2. mysql3 = 192.168.1.62
• SQL Nodes
  1. mysql4 = 192.168.1.63
  2. mysql5 = 192.168.1.64

Setup Management Node

To setup management node use following steps:

1. Remove mysql server if already installed

   yum -y remove mariadb-libs

2. Install required packages

   yum -y install perl-Data-Dumper

3. Download Mysql Cluster packages

   wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
   tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

4. Install Mysql Cluster

   rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
   rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
   rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

5. Create a new directory for the configuration files

   mkdir -p /var/lib/mysql-cluster

6. Create new configuration file for the cluster management at '/var/lib/mysql-cluster/config.ini' with following contents:

   [ndb_mgmd default]
   # Directory for MGM node log files
   DataDir=/var/lib/mysql-cluster
   
   [ndb_mgmd]
   #Management Node mysql1
   HostName=192.168.1.60
   
   [ndbd default]
   NoOfReplicas=2      # Number of replicas.  The number of nodes must be multiple
   # of this.  So for two replicas we should have either
   # 2 or 4 data nodes etc.  But three data nodes wont work
   DataMemory=256M     # Memory allocate for data storage
   IndexMemory=128M    # Memory allocate for index storage
   #Directory for Data Node
   DataDir=/var/lib/mysql-cluster
   
   [ndbd]
   #Data Node mysql2
   HostName=192.168.1.61
   
   [ndbd]
   #Data Node mysql3
   HostName=192.168.1.62
   
   [mysqld]
   #SQL Node mysql4
   HostName=192.168.1.63
   
   [mysqld]
   #SQL Node mysql5
   HostName=192.168.1.64
   

7. Start the Management Node

   ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini

8. To stop management node use:

   ndb_mgm -e shutdown

9. To monitor the node use:

   ndb_mgm
   show

Setup the MySQL Cluster Data Nodes

To setup MySQL cluster data nodes use: (Setup procedure is same for all the data nodes)

1. Remove mysql server if already installed

   yum -y remove mariadb-libs

2. Install require packages

   yum -y install perl-Data-Dumper

3. Download the MySQL Cluster package

   wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
   tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

4. Install the MySQL Cluster packages

   rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
   rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
   rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

5. To configure Data Node, Create a new configuration file '/etc/my.cnf' with following lines:

   [mysqld]
   ndbcluster
   ndb-connectstring=192.168.1.60     # IP address of Management Node
   
   [mysql_cluster]
   ndb-connectstring=192.168.1.60     # IP address of Management Node
   

6. Then create the new directory for the database data that we defined in the management node config file "config.ini"

   mkdir -p /var/lib/mysql-cluster

7. start the data node/ndbd:

   ndbd

Setup SQL Node

To setup Mariadb cluster SQL node use: (Setup procedure is same for all the SQL nodes)

1. Remove mysql server if already installed

   yum -y remove mariadb-libs

2. Install require packages

   yum -y install perl-Data-Dumper

3. Download the MySQL Cluster package

   wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
   tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

4. Install the MySQL Cluster packages

   rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
   rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
   rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

5. To Configure the SQL Node, Create /etc/my.cnf with following contents:

   [mysqld]
   ndbcluster
   ndb-connectstring=192.168.1.60       # IP address for server management node
   default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL
   
   [mysql_cluster]
   ndb-connectstring=192.168.1.60       # IP address for server management node#::
   

6. Start the SQL Node by starting the MySQL server

   service mysql start

7. Change the default MySQL password that stored in ".mysql_secret" file in root directory. Do this on all SQL nodes.

   cat /root/.mysql_secret
   mysql_secure_installation

8. Login to Management server

   ndb_mgm
   ndb_mgm> show

9. Check various status

   ndb_mgm -e show
   ndb_mgm -e "all status"
   ndb_mgm -e "all report memory"

10. Testing the Cluster. To perform a test on our new MySQL Cluster, we have to login to the SQL Nodes mysql4 or mysql5 servers using:

    ssh root@192.168.1.64

11. On SQL nodes: Give remote access for root user

    mysql -u root -p
    CREATE USER 'root'@'%' IDENTIFIED BY 'root';
    select user, host, password from mysql.user;
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*94CC7BF027327993D738E11...(Encrypted PASSWORD)' WITH GRANT OPTION;
    flush privileges;
    exit;

12. create a new database from mysql4 server and you will see the database on mysql5 too.

Refer:

• https://www.howtoforge.com/tutorial/how-to-install-and-configure-mysql-cluster-on-centos-7/

Home > CentOS > CentOS 7.x > Databases > MariaDB > CentOS 7.x mariadb clustering