MySQL dual master and one slave + keepalived high availability + NFS deployment

Article directory

• MySQL dual master and one slave + keepalived high availability + NFS deployment
• • 1. Deploy NFS
  • • • 1.1 Install nfs
      • 1.2 Create MySQL user
      • 1.3 Turn off the firewall
      • 1.4 Create shared directory
      • 1.5 Configure export
  • 2. Deploy the main MySQL
  • • • 2.1 mysql-master01 configuration
      • 2.2 mysql-master02 configuration
      • 2.3 will compile and install MySQL from source code on two main databases
      • 2.4 Configuring the configuration file for master-slave replication
  • 3. Deploy keepalived high availability
  • • • 3.1 Configuring the status check script and modifying the main configuration file
      • 4. Deploy master and slave
  • Deployment completed

MySQL dual master and one slave + keepalived high availability + NFS deployment

Host name	ip	Database master-slave	High Available servers	NFS	System version
mysql-slave	192.168.234.33	From	None	None	CentOS8
mysql-master01	192.168.234.22	Master	Master	None	CentOS8
mysql-master02	192.168.234.123	Main	Backup	None	CentOS8
nfs	192.168.234.100	None	None	Yes	CentOS8

vip is set to 192.168.234.234

1. Deploy NFS

1.1 Install nfs

[root@nfs ~]# yum install -y nfs-utils

//Start service
[root@nfs ~]# systemctl start rpcbind nfs-server

1.2 Create MySQL user

//Note: The user IDs of the master and NFS must be consistent.
[root@nfs ~]# groupadd -r -g 234 mysql
[root@nfs ~]# useradd -r -M -s /sbin/nologin -g 234 -u 234 mysql

1.3 Turn off the firewall

[root@nfs ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@nfs ~]# setenforce 0
[root@nfs ~]# sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

1.4 Create shared directory

[root@nfs ~]# mkdir -p /data/nfs/mysql
[root@nfs ~]# chown -R mysql:mysql /data/nfs/mysql/

1.5 Configure export

[root@nfs ~]# vim /etc/exports
[root@nfs ~]# cat /etc/exports
/data/nfs/mysql 192.168.234.22(rw,no_root_squash) 192.168.234.123(rw,no_root_squash)

//Install nfs service on mysql-master01
[root@mysql-master01 ~]# yum install -y nfs-utils
[root@mysql-master01 ~]# systemctl enable --now nfs-server


//Install nfs service on mysql-master02
[root@mysql-master02 ~]# yum install -y nfs-utils
[root@mysql-master02 ~]# systemctl enable --now nfs-server


//Verify whether nfs is built successfully
[root@nfs ~]# showmount -e 192.168.234.22
Export list for 192.168.234.22:
[root@nfs ~]# showmount -e 192.168.234.22
Export list for 192.168.234.22:
[root@nfs ~]# showmount -e 192.168.234.123
Export list for 192.168.234.123:
[root@nfs ~]# showmount
Hosts on nfs:

2. Deploy the main MySQL

2.1 mysql-master01 configuration

//Create users and directories
[root@mysql-master01 ~]# mkdir -p /data/mysql
[root@mysql-master01 ~]# groupadd -r -g 234 mysql
[root@mysql-master01 ~]# useradd -r -M -s /sbin/nologin -g 234 -u 234 mysql
[root@mysql-master01 ~]#
[root@mysql-slave ~]# chown -R mysql.mysql /data/mysql/


//Mount to nfs directory
[root@mysql-master01 ~]# mount -t nfs 192.168.234.100:/data/nfs/mysql /data/mysql
[root@mysql-master01 ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
······················slightly

192.168.234.100:/data/nfs/mysql nfs4 17G 2.0G 16G 12% /data/mysql ##Mounted successfully

2.2 mysql-master02 configuration

//Create users and directories
[root@mysql-master02 ~]# mkdir -p /data/mysql
[root@mysql-master02 ~]# groupadd -r -g 234 mysql
[root@mysql-master02 ~]# useradd -r -M -s /sbin/nologin -g 234 -u 234 mysql
[root@mysql-master02 ~]#
[root@mysql-slave ~]# chown -R mysql.mysql /data/mysql/


//Mount to nfs directory
[root@mysql-master02 ~]# mount -t nfs 192.168.234.100:/data/nfs/mysql /data/mysql
[root@mysql-master02 ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
······················slightly

192.168.234.100:/data/nfs/mysql nfs4 17G 2.0G 16G 12% /data/mysql ##Mounted successfully

2.3 will compile and install MySQL from source code on two main databases

For detailed steps on source code compilation and installation, please refer to “MySQL Basics”. Only the main steps are explained here

//Note: The difference between the msyql-master02 operation and msyql-master01 is that it does not need to be initialized anymore. Use the master initialization to run mysql.

//mysql-master01 operation
[root@mysql-master01 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql/
[root@mysql-master01 local]# cd
[root@mysql-master01 ~]# echo Xkq7?5Vu1lfT > mysql_password

//Start service
[root@mysql-master01 ~]# service mysqld start

//set password
[root@mysql-master01 ~]# mysql -uroot -p
Enter password:
mysql> set password = password('1');

//Out of service
//Note: mysql-master01 and msyql-master02 can only open one at the same time
[root@mysql-master01 ~]# service mysqld stop

msyql-master02 operation

//Note: The difference between the msyql-master02 operation and msyql-master01 is that there is no need to initialize here. Use the main initialization to run mysql.


//You can see that the password has been synchronized
[root@mysql-master02 ~]# mysql -uroot -p1
[root@mysql-master02 ~]# service mysqld start


//Out of service
[root@mysql-master02 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!

Verification

//Create a database on master01 to see if master02 is successfully synchronized
[root@mysql-master01 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@mysql-master01 ~]# mysql -uroot -p1

mysql> create database kiwi;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
 + -------------------- +
| Database |
 + -------------------- +
| information_schema |
| kiwi |
| mysql |
| performance_schema |
| sys |
 + -------------------- +
5 rows in set (0.00 sec)

mysql> exit
Bye
[root@mysql-master01 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!


//View on master02
[root@mysql-master02 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@mysql-master02 ~]# mysql -uroot -p -e 'show databases;'
Enter password:
 + -------------------- +
| Database |
 + -------------------- +
| information_schema |
| kiwi |
| mysql |
| performance_schema |
| sys |
 + -------------------- +
//You can see that the synchronization is successful

2.4 Configuration file for configuring master-slave replication

//master01 configuration file
[root@mysql-master01 mysql]# vim /etc/my.cnf
[root@mysql-master01 mysql]# cat /etc/my.cnf
##Append the following lines
server-id=10
log-bin = mysql_bin
[root@mysql-master01 ~]#


//Add master02 configuration file
[root@mysql-master02 ~]# vim /etc/my.cnf
[root@mysql-master02 ~]# cat /etc/my.cnf
##Append the following lines
server-id=10
log-bin = mysql_bin
[root@mysql-master02 ~]#


//Create an authorized user on mysql-master01
[root@mysql-master01 ~]# mysql -uroot -p1

mysql> create user 'kiwi'@'192.168.234.%' identified with mysql_native_password by '1';
Query OK, 0 rows affected (0.01 sec)


mysql> grant replication slave on *.* to 'kiwi'@'192.168.234.%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
 + ------------------ + ---------- + --------------- + ---- --------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 + ------------------ + ---------- + --------------- + ---- --------------- + ------------------ +
| mysql_bin.000001 | 154 | | | |
 + ------------------ + ---------- + --------------- + ---- --------------- + ------------------ +
1 row in set (0.00 sec)


//Since NFS is done, you only need to check whether there is master status on master02.
[root@mysql-master02 ~]# mysql -uroot -p1 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
 + ------------------ + ---------- + --------------- + ---- --------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 + ------------------ + ---------- + --------------- + ---- --------------- + ------------------ +
| mysql_bin.000002 | 154 | | | |
 + ------------------ + ---------- + --------------- + ---- --------------- + ------------------ +
[root@mysql-master02 ~]#

3. Deploy keepalived high availability

3.1 Configuring the status check script and modifying the main configuration file

//Written on mysql-master01
[root@mysql-master01 ~]# vim /kiwi_scripts/check_mysql.sh
[root@mysql-master01 ~]# cat /kiwi_scripts/check_mysql.sh
#!/bin/bash
mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
if [ $mysql_status -lt 1 ]; then
    systemctl stop keepalived
fi

//Configure on mysql-master01
[root@mysql-master01 kiwi_scripts]# vim /etc/keepalived/keepalived.conf
[root@mysql-master01 kiwi_scripts]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {<!-- -->
   router_id msyql-master01_master
}

vrrp_script mysql_check {<!-- -->
    script "/kiwi_scripts/check_mysql.sh"
    interval 1
}

vrrp_instance VI_1 {<!-- -->
    stateMASTER
    interface ens160
    virtual_router_id 111
    priority 100
    advert_int 1
    authentication {<!-- -->
        auth_type PASS
        auth_pass kiwi111
    }
    virtual_ipaddress {<!-- -->
        192.168.234.234
    }
    track_script {<!-- -->
        mysql_check
    }
}

virtual_server 192.168.234.234 3306 {<!-- -->
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocolTCP

    real_server 192.168.234.22 3306 {<!-- -->
        weight 1
        TCP_CHECK {<!-- -->
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
         }
        }
    

    real_server 192.168.234.123 3306 {<!-- -->
        weight 1
        TCP_CHECK {<!-- -->
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
         }
        }
        
        
        
//Write script on mysql-master02
[root@mysql-master02 kiwi_scripts]# cat notify_mysql.sh
#!/bin/bash
case "$1" in
  master)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -lt 1 ];then
            service mysqld start
        fi
  ;;
  backup)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -gt 0 ];then
            service mysqld start
        fi
  ;;
  *)
        echo "Usage:$0 master|backup"
  ;;
esac


//mysql-master02 main configuration file
[root@mysql-master02 ~]# vim /etc/keepalived/keepalived.conf
[root@mysql-master02 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {<!-- -->
   router_id mysql-master02_slave
}

vrrp_instance VI_1 {<!-- -->
    state BACKUP
    interface ens160
    virtual_router_id 111
    priority 80
    advert_int 1
    authentication {<!-- -->
        auth_type PASS
        auth_pass kiwi111
    }
    virtual_ipaddress {<!-- -->
        192.168.234.234
    }
    notify_master "/kiwi_scripts/notify_mysql.sh master"
    notify_backup "/kiwi_scripts/notify_mysql.sh backup"
}

virtual_server 192.168.234.234 3306 {<!-- -->
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocolTCP

    real_server 192.168.234.22 3306 {<!-- -->
        weight 1
        TCP_CHECK {<!-- -->
            connect_port 8080
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
         }
        }
    

    real_server 192.168.234.123 3306 {<!-- -->
        weight 1
        TCP_CHECK {<!-- -->
            connect_port 8080
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
         }
        }

4. Deploy master and slave

//Modify configuration file
[root@mysql-slave ~]# cat /etc/my.cnf
[mysqld]
 basedir = /usr/local/mysql
 datadir = /opt/data
 socket = /tmp/mysql.sock
 port=3306
 pid-file = /opt/data/mysql.pid
 user=mysql
 skip-name-resolve

server-id=20
relay-log=mysql-relay-bin


//Start synchronization
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.234.234',
    -> MASTER_USER='kiwi',
    -> MASTER_PASSWORD='1',
    -> MASTER_LOG_FILE='mysql-bin.000008',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;


//View status
mysql> show slave status\G
*************************** 1. row ********************* *******
               Slave_IO_State:
                  Master_Host: 192.168.234.234
                  Master_User: kiwi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:

Deployment completed