MySQL 5.7 cluster with Galera on RedHat 7.4


Galera Cluster is a master-master replication, this means it keeps same data on all the nodes. For this purpose it transfers data continuously between nodes (IST or SST). There are 2 methods with which you can transfer the data. Using Percona XtraBackup or rsync. We are going to use rsync.
                
       yum install rsync

We need to setup SELinux in permissive mode for required ports so that nodes can communicate. For this purpose we are going to use semanage. We can install this by given command.

       yum install policycoreutils-python

Also you firewall should not stop communication between the nodes, you can use telnet utility to check if nodes can communicate with each other on specific ports. If its not already installed, you can install it.
          
      yum install telnet

To set SELinux to permissive mode, complete the following steps:

      semanage port -a -t mysqld_port_t -p tcp 4567
      semanage port -a -t mysqld_port_t -p tcp 4568
      semanage port -a -t mysqld_port_t -p tcp 4444

To disable SELinux for mysql run the following command:
    
        semanage permissive -a mysqld_t 

Now lets add Galera repository. Add a text file /etc/yum.repos.d/galera.repo in your favourite editor and add the following:

         [galera]
        name = Galera
        baseurl = http://releases.galeracluster.com/galera-3/redhat/7/x86_64
        gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
        gpgcheck = 1

        [mysql-wsrep]
        name = MySQL-wsrep
        baseurl =  http://releases.galeracluster.com/mysql-wsrep-5.7/redhat/7/x86_64
        gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
Now install galera and MySQL.

        yum install galera-3
        yum install mysql-wsrep-5.7

 Lets now setup mysql configuration. Edit the file /etc/my.cnf and add the following.

        user=mysql
       binlog_format=ROW
       bind-address=0.0.0.0
       default_storage_engine=innodb
       innodb_autoinc_lock_mode=2
       innodb_flush_log_at_trx_commit=0
       innodb_buffer_pool_size=10G
       wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
       wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
       wsrep_cluster_name="galera_cluster"
       wsrep_cluster_address="gcomm://<node1 IP>,<node 2 IP>, <node 3 IP>"
       wsrep_node_address="<node IP address>"
       wsrep_node_name=<node name>
       wsrep_sst_method=rsync

 Make sure your /usr/lib64/galera-3/libgalera_smm.so exists at this location. if not try to find it. for wsrep_cluster_address you can start with single node ip also. You can setup innodb_buffer_pool_size as per RAM available in your system.


 You can now bootstrap the mysql by using the following script file.
     /usr/bin/mysqld_bootstrap

When you bootstrap mysql, it creates a temporary password for you and put it in log file. You can use following command to find your temporary password.

       grep 'temporary password' /var/log/mysqld.log

If you want to make your mysql server more secured, you can run the following command:

      mysql_secure_installation

It will ask you few questions and then you are good to go.

 Now login to mysql and check the cluster size. 

      mysql -uroot -p -e "show status like 'wsrep_cluster_size'"

 this command should show you size as 1 if there is no other node active. Do not restart or stop MySQL for now.

Adding more nodes.


 You can install mysql and galera as shown above in other nodes. In /etc/my.cnf, you need to change wsrep_node_name,wsrep_node_address. This time you need not to find the temporary password or bootstrap your node.Simply start your mysql node using following command.

       systemctl start mysql 

 Its good practice to run the mysql_secure_installation after this.
 
      mysql_secure_installation

Your password should be same as you setup in your primary node. Now you can check the cluster size on both the nodes. It should increase with each node added into cluster.

      mysql -uroot -p -e "show status like 'wsrep_cluster_size'"

Comments

Post a Comment

Popular posts from this blog

Restore single database from full Xtrabackup backup

Percona Xtrabackup does not have any direct way to import single database. I have found a script to restore single database written by Phil Buescher . I have made few changes in the script. There are few perquisite to run the script. You can download the script from here . You need to use mysql-utility mysqlfrm to get the structure of the table from backup. But mysqlfrm works only for innodb tables. Prerequisite:     1. Table should be innodb engine.     2. innodb_file_per_table option should be enabled on the source server        before taking the backup.     3. If you have any view inside your backup directory, script will fail. It will        print the name of the file where it failed. You need to go inside backup directory and delete all the files related to those views. For restoring DB we need to have all the following backups. I am referring to the directory structure creat...
Read more

MySQL default Password

Recently, I was setting up MySQL Galera cluster on redhat 7.4. I installed MySQL 5.7 and Galera cluster on the system according to the instructions provided in galera support page ( Galera Cluster setup ). I have made a blog post for installation and setup here . But when I started the mysql, I was not able to login as I did not setup any password for mysql root account. When I tried logging in without password, I got access denied. After looking for the answers on internet, I found out that MySQL creates a temporary password and write it in log files. It is clrearly mentioned in mysql documentation here . All I needed to do to find this password is to run the following command.      grep 'temporary password' /var/log/mysqld.log Problem solved !
Read more