MySQL is highly available. MySQL is master-slave + maintained.

Posted by blufish on Mon, 02 Dec 2019 16:52:41 +0100

1. Environment: centos7.5 MySQL 5.7
192.168.3.222 master
192.168.3.158 slave
192.168.3.223 VIP
Two: first, make two masters
To operate both machines
2.1: domain name resolution:

###vim /etc/hosts (both)
192.168.3.222 master
192.168.3.158 slave

2.2: Download MySQL 5.7

### wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
### rpm -ivh mysql57-community-release-el7-8.noarch.rpm
### yum install mysql-server

2.3: start and change password

### systemctl start mysqld
### grep pass /var/log/mysqld.log
### Mysqladmin - U root - p'bjzy, z0kdcq 'password' cherry ` 123 '/ / modify password

2.4: modify the configuration of two machines

modify master Configuration on
### vim /etc/my.cnf
    [mysqld]
    log-bin
    server-id=1
    gtid_mode=ON
    enforce_gtid_consistency=1
//Modify the configuration on the slave
### vim /etc/my.cnf
    [mysqld]
    log-bin
    server-id=2
    gtid_mode=ON
    enforce_gtid_consistency=1

2.5: restart and authorize two machines
Restart both machines
### systemctl restart mysqld
Enter the main database to get the binlog file name and offset

   mysql> show master status;

Create user in master

   mysql> grant replication slave on *.* to 'iii'@'192.168.3.158' identified by  'Cherry321`';

Execute on slave

   change master to master_host='192.168.3.158',master_user='iii',master_password='Tiger321`',master_port=3306,master_log_file='master-bin.000003',master_log_pos=194;                  //Master bin. 00000 3 ', master log POS = 194 is the file name and offset 

start slave

   mysql> start slave;

View from library

   mysql>show slave status\G;              //Slave? IO? Running: Yes slave? SQL? Running: Yes is successful

Three: after the dual master is set up, make a virtual IP on the master and bind the network card
3.1: copy a network card file on the master

cp /etc/sysconfig/network-scripts/ifcfg-ens33  /etc/sysconfig/network-scripts/ifcfg-ens37
//The configuration file is as follows
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
NAME=ens33
UUID=9f0c5e90-b5e5-44f4-95ce-9f97d19bf880
DEVICE=ens33
ONBOOT=yes
IPADDR=192.168.3.223
NETMASK=255.255.255.0
GATEWAY=192.168.3.1
DNS1=8.8.8.8
DNS2=114.114.114.114

3.2 restart the network card

###systemctl restart network

IV: Download keepalived and modify its configuration file

4.1: Download both machines keepalived
yum -y install keepalived

4.2: To modify the configuration file, it's better to backup it before modification...
[root@master keepalived]# vim keepalived.conf
! Configuration File for keepalived      //Notation

global_defs {

  router_id mysql-1         //Indicates the identity of the server running keepalived 

}

vrrp_instance VI_1 {
   state BACKUP                     //Specify the role of keepalived. Both configurations here are backup. This means that backup will decide the master or slave according to the priority
   interface ens33    //Specify the interface of HA detection network
   virtual_router_id 51  //Virtual routing ID, which is a number (value between 0 and 255, used to distinguish VRRP multicast of multiple instances). The same VRRP instance uses a unique ID
                                  //Ensure that it is the same as slave, and different clusters in the same network, otherwise conflicts will occur
   priority 100         //Used to select a master. To become a master, the value range of this item is 1-255. The default value is 100, and the slave value is 50
   advert_int 1       //Interval between sending vrrp packets
   nopreempt          //No preemption
   authentication {
       auth_type PASS
       auth_pass 1111
   }

   virtual_ipaddress {               //VIP address

       192.168.3.223

   }

}

virtual_server 192.168.3.223 3306 {      //To set up a virtual server, you need to specify the virtual IP address and service port.
   delay_loop 2             //Operation check time, in seconds
   lb_algo rr    //Set backend scheduling algorithm
   lb_kind DR        
   persistence_timeout 60
   protocol TCP

   real_server 192.168.3.222 3306 {    //To configure a service node, you need to specify the real IP address and port of the real server.
       weight 3             //Configure the weights of the service node. The weights are identified by numbers. The larger the number is, the higher the weights are. Set the weights to distinguish servers without performance
       notify_down   /root/keepalived_check_mysql.sh                //Script location after the MySQL service of realserver is detected to be down
       TCP_CHECK {
            connect_timeout 3                   //Connection timeout
            nb_get_retry 3                         //Reconnection times
            delay_before_retry 3                //Reconnection interval
            connect_port 3306                 //Health check port
       }
   }
}

The following is the configuration of slave

4.3: slave Also revised keepalived Configuration
[root@slave ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {

  router_id mysql-2

}

vrrp_instance VI_1 {
   state BACKUP
   interface ens33
   virtual_router_id 51
   priority 50
   advert_int 1
   authentication {
       auth_type PASS
       auth_pass 1111
   }

   virtual_ipaddress {

       192.168.3.223

   }

}

virtual_server 192.168.3.223 3306 {
   delay_loop 2
   lb_algo rr
   lb_kind DR
   persistence_timeout 60
   protocol TCP

   real_server 192.168.3.158 3306 {
       weight 3
       notify_down   /root/keepalived_check_mysql.sh
       TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
       }
   }
}

V: add mysql simple state detection script after two machines are modified

### vim /root/keepalived_check_mysql.sh
#!/bin/bash
/usr/bin/mysql -h 192.168.3.222 -uroot -p'Tiger`123' -e "show status;" &>/dev/null
if [ $? -ne 0 ] ;then
        systemctl stop keepalived
fi

Plus executable rights

### chmod 755 /root/keepalived_check_mysql.sh

Start keepalived and close master to check IP hop

Topics: Operation & Maintenance MySQL network vim RPM