CentOS 7.x mariadb clustering

From Notes_Wiki

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:



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