Tag: redundant

Categories
Applications Cluster Linux MySQL

MySQL-Cluster group replication

Follow this to setup a MySQL database cluster with group replication. Group replication is where the database across the cluster is constantly synced, so you can write or read from any database and it will be replicated across the other ones.

This increases the redundancy and read throughput.

Assumptions

Following are the assumptions that are made.

Hostname of service DB server 1: service-db1
Hostname of service DB server 2: service-db2
Hostname of service DB server 3: service-db3

IP of service DB server 1: 172.32.0.10
IP of service DB server 2: 172.32.0.20
IP of service DB server 3: 172.32.0.30

MySQL port: 3306
MySQL cluster port: 33061

Install MySQL on all three servers

Add the MySQL community repo

Download the deb file

curl -OL https://repo.mysql.com//mysql-apt-config_0.8.15-1_all.deb

Install using dpkg

sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

Select mysql-5.7

Update the apt repo

sudo apt update

Install MySQL server

sudo apt install mysql-server

Start MySQL service

sudo systemctl start mysql && sudo systemctl enable mysql

Do secure installation

sudo mysql_secure_installation

Open configuration

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Edit configuration

OptionOld valueNew value
bind-address127.0.0.10.0.0.0

Add /etc/hosts entries to make hostnames resolvable

Open hosts file

sudo vim /etc/hosts

Add the following configuration after changing the IPs appropriately

172.32.0.10 service-db1
172.32.0.20 service-db2
172.32.0.30 service-db3

Add firewall configuration

sudo ufw allow from 172.32.0.54 to any port 3306 proto tcp
sudo ufw allow from 172.32.0.175 to any port 3306 proto tcp
sudo ufw allow from 172.32.0.166 to any port 3306 proto tcp

sudo ufw allow out to 172.32.0.54 port 3306 proto tcp
sudo ufw allow out to 172.32.0.175 port 3306 proto tcp
sudo ufw allow out to 172.32.0.166 port 3306 proto tcp

sudo ufw allow out to 172.32.0.54 port 33061 proto tcp
sudo ufw allow out to 172.32.0.175 port 33061 proto tcp
sudo ufw allow out to 172.32.0.166 port 33061 proto tcp

sudo ufw allow from 172.32.0.54 to any port 33061 proto tcp
sudo ufw allow from 172.32.0.175 to any port 33061 proto tcp
sudo ufw allow from 172.32.0.166 to any port 33061 proto tcp

Prepare first MySQL server for group replication

Edit configuration

Open the file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following content

disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add=’group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=”40d2393e-a59b-11ea-8acd-0242ac110003″
group_replication_start_on_boot=off
group_replication_local_address= “service-db1:33061″
group_replication_group_seeds= “service-db1:33061,service-db2:33061,service-db3:33061″
group_replication_bootstrap_group=off
group_replication_ip_whitelist=”service-db1,service-db2,service-db3”

The server_id changes to 2 and 3 and group_replication_local_address changes to the hostname and make it resolvable using /etc/hosts file of the current instance for all the servers in mysql cluster, and also change the whitelist IP range.

Login to mysql

sudo mysql -u root

Get UUID

SELECT UUID();

Replace UUID in the configuration file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

The UUID to be changed is at group_replication_group_name section

Restart MySQL server

sudo systemctl restart mysql

Login to mysql

sudo mysql -u root

Run the following commands

Turn off bin log

SET SQL_LOG_BIN=0;

Create replication user

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘123456789’;

Give appropriate permission

GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;

Flush permissions

FLUSH PRIVILEGES;

Turn on bin log

SET SQL_LOG_BIN=1;

Enable user for replication

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’123456789′ FOR CHANNEL ‘group_replication_recovery’;

Bootstrap the replication group

SET GLOBAL group_replication_bootstrap_group=ON;

Start the replication

START GROUP_REPLICATION;

Stop the bootstrap

SET GLOBAL group_replication_bootstrap_group=OFF;

Check if the node is online

SELECT * FROM performance_schema.replication_group_members;

Change the mysqld.cnf file group_replication_start_on_boot to on

Cluster second MySQL server to the group

Edit configuration

Open the file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following content

disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add=’group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=”40d2393e-a59b-11ea-8acd-0242ac110003″
group_replication_start_on_boot=off
group_replication_local_address= “service-db2:33061″
group_replication_group_seeds= “service-db1:33061,service-db2:33061,service-db3:33061″
group_replication_bootstrap_group=off
group_replication_ip_whitelist=”service-db1,service-db2,service-db3”

The server_id changes to 2 and 3 and group_replication_local_address changes to the hostname and make it resolvable using /etc/hosts file of the current instance for all the servers in mysql cluster, and also change the whitelist IP range. Change the UUID to be the same as the first server

Restart MySQL server

sudo systemctl restart mysql

Login to mysql on master server (first server)

sudo mysql -u root

Run the following commands

Turn off bin log

SET SQL_LOG_BIN=0;

Create replication user

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘123456789’;

Give appropriate permission

GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;

Flush permissions

FLUSH PRIVILEGES;

Turn on bin log

SET SQL_LOG_BIN=1;

Enable user for replication

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’123456789′ FOR CHANNEL ‘group_replication_recovery’;

Start the replication

START GROUP_REPLICATION;

Disable read only mode

SET GLOBAL super_read_only=0;

Check if the node is online

SELECT * FROM performance_schema.replication_group_members;

Cluster third MySQL server to the group

Edit configuration

Open the file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following content

disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add=’group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=”40d2393e-a59b-11ea-8acd-0242ac110003″
group_replication_start_on_boot=off
group_replication_local_address= “service-db3:33061″
group_replication_group_seeds= “service-db1:33061,service-db2:33061,service-db3:33061″
group_replication_bootstrap_group=off
group_replication_ip_whitelist=”service-db1,service-db2,service-db3”

The server_id changes to 2 and 3 and group_replication_local_address changes to a resolvable url or IP address of the current instance for all the servers in mysql cluster, and also change the whitelist IP range. Change the UUID to be the same as the first server

Login to mysql on master server (first server)

sudo mysql -u root

Run the following commands

Turn off bin log

SET SQL_LOG_BIN=0;

Create replication user

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘123456789’;

Give appropriate permission

GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;

Flush permissions

FLUSH PRIVILEGES;

Turn on bin log

SET SQL_LOG_BIN=1;

Enable user for replication

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’123456789′ FOR CHANNEL ‘group_replication_recovery’;

Start the replication

START GROUP_REPLICATION;

Disable read only mode

SET GLOBAL super_read_only=0;

Check if the node is online

SELECT * FROM performance_schema.replication_group_members;

That’s it. Your MySQL servers are now clustered with redundancy.

Categories
Applications Linux Lsync NFS Rsync Ubuntu UFW

NFS server with Lsyncd in Ubuntu

Make the attached volumes ready

Create folders

sudo mkdir /mnt/upload_disk1sudo mkdir /mnt/upload_disk2sudo mkdir /mnt/backup_disk1sudo mkdir /mnt/backup_disk2

Create partition

sudo fdisk /dev/nvme0n1sudo fdisk /dev/nvme1n1sudo fdisk /dev/nvme2n1sudo fdisk /dev/nvme3n1

Make filesystem

sudo mkfs.xfs /dev/nvme0n1p1sudo mkfs.xfs /dev/nvme1n1p1sudo mkfs.xfs /dev/nvme2n1p1sudo mkfs.xfs /dev/nvme3n1p1

Add fstab entry

sudo vim /etc/fstab

Add the following content

/dev/nvme0n1p1 /mnt/upload_disk1 xfs defaults,nofail 0 0
/dev/nvme3n1p1 /mnt/upload_disk2 xfs defaults,nofail 0 0
/dev/nvme1n1p1 /mnt/backup_disk1 xfs defaults,nofail 0 0
/dev/nvme2n1p1 /mnt/backup_disk2 xfs defaults,nofail 0 0

Setup NFS

Update apt repo

sudo apt update

Install NFS server

sudo apt install nfs-kernel-server

Add configuration for NFS

sudo vim /etc/exports

Add the following content

/mnt/upload_disk1 *(rw,async,no_subtree_check,no_root_squash,no_all_squash)/mnt/backup_disk1 *(rw,async,no_subtree_check,no_root_squash,no_all_squash)

Change permission

sudo chmod 777 /mnt/*

Restart NFS server

sudo systemctl restart nfs-kernel-server.service

Setup backup using Lsyncd

Install Lsyncd

sudo apt install lsyncd

Make directory for configuration

sudo mkdir /etc/lsyncd

Create configuration file

sudo vim /etc/lsyncd/lsyncd.conf.lua

Add the following content

—— User configuration file for lsyncd.
—- Simple example for default rsync, but executing moves through on the target.
—- For more examples, see /usr/share/doc/lsyncd*/examples/–settings
{
    logfile = “/var/log/lsyncd/lsyncd.log”,
    statusFile = “/var/log/lsyncd/lsyncd.status”,statusInterval = 2
}
— Slave server configuration
sync {
       default.rsync,
       delete= false,
       source= “/mnt/upload_disk1”,
       target= “/mnt/upload_disk2”,
       rsync={
                archive = true,
                verbose  = true
             }
}
sync {
       default.rsync,
       delete= false,
       source= “/mnt/backup_disk1”,
       target= “/mnt/backup_disk2”,
       rsync={
                 archive = true,
                 verbose  = true
             }
}

Restart service

sudo systemctl restart lsyncd

Setup firewall rules

sudo ufw allow from 172.32.0.0/24 to any port nfs