Mysql dual host hot standby and implementation of Mysql dual host and high availability with preserved

There are two ways of mysql hot standby:

Hot standby of master-slave server
Master master server hot standby
The following is an example of dual hot standby of the primary and primary servers. The primary and secondary configurations are similar.

Two centos7, ip respectively:

172.17.1.136

172.17.3.134

 

1, mysql creating database

This step should be performed by both hosts. Both hosts need to be installed. For mysql installation, please refer to "CentOS7 install mysql 8.0.12" or search by yourself.

After the installation, use the command to log in. The password is the password you configured:

mysql -u root -p

Create a test database:

mysql> CREATE DATABASE `test`;


Use the test library (use test;) to create an empty test table:

mysql> create table tb_mobile( mobile VARCHAR(20) comment'phone number', time timestamp DEFAULT now() comment'time' );

Note: it is mentioned in the online data that the two mysql data must be consistent before configuration. However, since both mysql are newly installed this time, they have not been tested.

 

2, Server configuration

1. Create synchronization user

Using mysql library,

mysql> use mysql

Then create a linked user for the other host. The account must be granted the REPLICATION SLAVE permission, because mysql8 cannot have the IDENTIFIED BY 'password' in the authorization statement. Therefore, the creation of the user and the granting of the permission need to be executed separately:

1) Execute create user and empower on 136:

mysql> CREATE USER 'replicate'@'172.17.3.134' IDENTIFIED WITH 'mysql_native_password' BY 'TestBicon@123';


Because the permission authentication mechanism of mysql8 has changed, there will be an exception prompt when using the following command to create users

create user 'replicate'@'172.17.3.134' identified by 'TestBicon@123';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.3.134';

 

2) On 134:

mysql> CREATE USER 'replicate'@'172.17.1.136' IDENTIFIED WITH 'mysql_native_password' BY 'TestBicon@123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.1.136';

[note] after making permission changes to the user, remember to reload the permission and write the permission information into the database from memory.

mysql> flush privileges;

After creation, we can use the other host to access the local database to see whether it can be successfully linked [Note: when linking the other host, you need to exit the local mysql or open a new terminal first]:

mysql -h 172.17.3.134 -u replicate -p    //Test each other in each host
mysql -h 172.17.1.136 -u replicate -p

If the login is successful, the two servers can be hot standby.

 

2. Modify mysql configuration file

After mysql is installed, a my.cnf configuration file will be generated in the / etc / directory (if there is no copy from the / usr/share/mysql Directory: cp /usr/share/mysql/my-medium.cnf /etc/my.cnf). Find the configuration file and edit it (vim /etc/my.cnf). Add (modify) the following content in [mysqld]:

1) 172.17.1.136 host

server-id=1 # Unique id
innodb_flush_log_at_trx_commit=2 # 1 by default
log-bin=mysql-bin-1 #binlog log file name (can be named arbitrarily)
#binlog-do-db=test # Database for logging (if not, all libraries are synchronized)
binlog-ignore-db=mysql,information_schema,performance_schema,sys # Database without logging
sync_binlog=1 #Enable binlog log synchronization function
slave_skip_errors=1146 # Skip 1146 error

2) 172.17.3.134 host

server-id=2 # Unique id
innodb_flush_log_at_trx_commit=2 # 1 by default
log-bin=mysql-bin-2 #binlog log file name (can be named arbitrarily)
#binlog-do-db=test # Logged database
binlog-ignore-db=mysql,information_schema,performance_schema,sys # Database without logging
sync_binlog=1 #Enable binlog log synchronization function
slave_skip_errors=1146 # Skip 1146 error

3, Restart and configure synchronization

1. Restart mysql on two hosts

The two hosts execute respectively:

systemctl restart mysqld

perhaps

service mysqld restart

2. View the status of two servers as primary servers

mysql> show master status\G

3. Use the change mster statement to specify the synchronization location

Enter the MySQL operation interface (mysql-uroot-p), and execute the following instructions first:

mysql> unlock tables;

Otherwise, when stop slave; is executed, the following exception will be reported: ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction.

 

1) 172.17.1.136 host

It is very important to stop the slave service thread first. Otherwise, the following operations will not succeed.

mysql> stop slave;

then:

mysql> change master to master_host='172.17.3.134',master_user='replicate',master_password='TestBicon@123',
master_log_file='mysql-bin-2.000001',master_log_pos=749;

Restart the slave service thread on the server:

mysql> start slave;

2) 172.17.3.134 host

It is very important to stop the slave service thread first. Otherwise, the following operations will not succeed.

mysql> stop slave;

then:

mysql> change master to master_host='172.17.1.136',master_user='replicate',master_password='TestBicon@123',
master_log_file='mysql-bin-1.000001',master_log_pos=749;


Restart the slave service thread on the server:

mysql> start slave;

4. View the slave server status

mysql> show slave status\G


If the following two statuses are yes, the configuration is successful:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4, Verification

Insert / delete data and other operations in mysql of the two hosts, and the other will automatically synchronize the operations.

5, Configure keepalived to realize mysql dual primary and high availability

The above configuration can realize the double-click hot standby of MySQL. The two hosts provide services to the outside respectively. If the service link fails to one host mysql, it cannot automatically switch to another host to realize high availability. If this can meet the needs or do not need to switch, the configuration can not continue. We will configure keepalived to make it highly available.

1. Download and install keepalived

Official website: http://www.keepalived.org (take version 2.0.6 as an example)

Installation location / usr/local / cd /usr/local/

Download and unzip using the following command:

wget http://www.keepalived.org/software/keepalived-2.0.6.tar.gz
tar -xzvf keepalived-2.0.6.tar.gz -C keepalived

Before installation, you need to use the following command to install related dependencies:

yum -y install curl gcc openssl-devel libnl3-devel net-snmp-devel
yum -y install libnfnetlink-devel
yum -y install ipvsadm

Then install keepalived:

cd keepalived
./configure
make && make install

PS: if there is a problem (exception) during compilation, make corresponding modifications according to the exception information. If there is a lack of XX devel and other information, just install XX devel. Otherwise, please determine the operation according to the exception information.

After compiling and installing, the startup script will be generated according to the system environment, as follows:

cat /usr/lib/systemd/system/keepalived.service

Add keepalived to system service and write configuration file in / etc/sysconfig /, and add keepalived command to / usr/bin and / usr/sbin (note in the following command is ignored in use):

cp /usr/local/keepalived/keepalived/etc/init.d/keepalived /etc/init.d/keepalived  #It's better to check whether the maintained has execution permission
mkdir /etc/keepalived/
cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/sbin/keepalived /usr/bin/  #It is convenient to view the version of keepalived in the future. You can view it with keepalived - v
cp /usr/local/sbin/keepalived /usr/sbin/

Modify the profile after copying:

vim /etc/keepalived/keepalived.conf

The following is the configuration content. It can not be copied directly according to the actual modification

1) 172.17.1.136 host

! Configuration File for keepalived
 
global_defs {
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt #No preemption mode. Only the machines with high priority can be set, and the machines with low priority can not be set
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.17.3.210
    }
}
 
virtual_server 172.17.3.210 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60 #Session hold time
    protocol TCP
 
    real_server 172.17.1.136 3306 {
        weight 3
        notify_down /root/shutdown.sh #Script executed after service down detected
        TCP_CHECK {
            nb_get_retry 3    #Number of reconnections
            connect_timeout 10 #Connection timeout
            delay_before_retry 3 #Reconnection interval
            connect_port 3306 #Health check port
        }
    }
}


2) 172.17.3.134 host

! Configuration File for keepalived
 
global_defs {
   router_id LVS_DEVEL # String identifying this node, usually hostname
   vrrp_skip_check_adv_addr
   #vrrp_strict #It's better to comment out this, otherwise the browser of the physical machine may not be able to access the application
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
 
vrrp_instance VI_1 {
    #state MASTER
    state BACKUP
    interface eno16777736
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress { # Block limited to 20 IP addresses @IP
        172.17.3.210
    }
}
 
#Virtual server definition block
virtual_server 172.17.3.210 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
 
    real_server 172.17.3.134 {
        weight 3
        notify_down /root/shutdown.sh
        TCP_CHECK {
            nb_get_retry 3    #Number of reconnections
            connect_timeout 10 #Connection timeout
            delay_before_retry 3 #Reconnection interval
            connect_port 3306 #Health check port
        }
    }
}


Write the script shutdown.sh to execute after the detection service is down:

vim /root/shtdown.sh

The content is

#!/bin/bash

killall keepalived

Note: this script is used by the notify down option in the above configuration file. Keepalived uses the notify down option to check the service status of the real server. When the real server service fails, this script will be triggered. As we can see, the script is a command to kill the keepalived process by killing keepalived, thus realizing the automatic failover of MySQL. In addition, we don't need to worry about the data update operation provided by two MySQL at the same time, because in the configuration of maintained on each mysql, only the IP+VIP of the local MySQL is available, not the IP+VIP of the two mysql

 

2. Start keepalived and view the log

chkconfig keepalived on
service keepalived start    #Start service
service keepalived stop        #Out of Service
service keepalived restart    #Restart service
 
systemctl enable keepalived.service        #Set startup

View log after startup:

tail -f /var/log/messages

3. Test

Pass vip login test on remote client

mysql -h 172.17.3.210 -uroot -p

4. Others

1) Modify log location

Modify the log file storage location / var/log/keepalived/keepalived.log

echo 'local0.* /var/log/keepalived/keepalived.log' >>/etc/rsyslog.conf

Restart the log service

systemctl restart rsyslog

2) keepalived configuration parameters

Global definition block

1. Email notification (notification, SMTP server, SMTP connect timeout): it is used to send email alarm in case of service failure, optional, not recommended. It is recommended to use the third independent monitoring service, such as nagios comprehensive monitoring instead.

2. lvs_id: lvs load balancer ID, in a network, its value should be unique.

3. Router ID: the name of the user ID node, usually hostname

4. Curly bracket {}: used to separate definition blocks, which must appear in pairs. If the write is missed, the keepalived runtime will not get the expected result. It is easy to miss curly braces at the end of the definition block due to nesting, which requires special attention.

VRRP instance definition block

vrrp? Sync? Group: synchronizes the vrrp level to determine the number of routing instances contained in a FailOver. In the case of two load balancers, once a load balancer fails, you need to automatically switch to another load balancer instance

group: at least one VRRP instance must be included. The name of the VRRP instance must be consistent with the definition of vrrp_instance

vrrp? Instance: vrrp instance name

1> State: instance state. There are only two states: MASTER and BACKUP, and they need to be all capitalized. In preemption mode, MASTER is in working state and BACKUP is in standby state. When the server of MASTER fails, the service of BACKUP will automatically switch its status from BACKUP to MASTER. When the service of the failed MASTER is restored, BACKUP is restored from the MASTER to the BACKUP state.

2> Interface: the network card interface that provides external services, that is, the network card interface bound by VIP. For example: eth0, eth1. At present, the mainstream servers have two or more interfaces (corresponding to the external network and the internal network respectively). When selecting the network card interface, it must be verified clearly.

3> Mcast SRC IP: local IP address

4> Virtual router ID: the ID number of the virtual route. Each node must be set the same. You can select the last IP segment to use. The same VRID is a group. It will determine the MAC address of the multicast.

5> Priority: node priority, value range 0-254, MASTER higher than BACKUP

6> Advert'int: the time interval between the synchronization check between MASTER and BACKUP node, in seconds

7> LVS ﹣ sync ﹣ daemon ﹣ interface: monitoring interface between load balancers, similar to HA HeartBeat's heart cord. But its mechanism is better than Heartbeat, because it does not have the problem of "split brain". It uses the priority mechanism to avoid this trouble. In DR mode, LVS sync daemon interface and service interface interface interface use the same network interface

8> Authentication: authentication type and authentication password. There are two types: PASS and AH. PASS is usually used. It is said that AH has problems in use. Verify that the password is clear text. Only when the same vrrp instance MASTER and BACKUP use the same password can they communicate normally.

9> Smtp_alert: whether to activate email notification in case of failure

10> Nopreempt: no preemption of services. By default, when the MASTER service is hung up, BACKUP will automatically upgrade to MASTER and take over its tasks. When the MASTER service is restored, the BACKUP service upgraded to MASTER will automatically reduce to BACKUP and give the work right to the original MASTER. When nopreempt is configured, MASTER will not preempt the service from hang up to recovery.

11> Virtual? IPAddress: virtual IP address pool, which can have multiple IPS, each IP occupies a row, and does not need to specify a subnet mask. Note: this IP must be consistent with our vip settings.

Virtual server virtual server definition block

Virtual server: define a virtual server. This ip is one of the virtual IPAddress definitions, followed by a space, and then add the port number of the virtual service.

1> Delay? Loop: interval of health examination in seconds

2> Lb_algo: load balancing and scheduling algorithm. wlc or rr is commonly used in Internet applications

3> Lb_kind: load balanced forwarding rule. It includes Dr, NAT and Tun, and generally uses routing (DR) forwarding rules.

4> Persistence Ou timeout: http service session hold time in seconds

5> Protocol: Forwarding Protocol, divided into TCP and UDP

real_server: real server IP and port, multiple can be defined

1> Weight: load weight, the higher the value, the higher the priority of forwarding

2> Notify down: script executed after the service is stopped

3> TCP "check: service validity detection

*Connect? Port: service connection port

*Connect Ou timeout: service connection timeout, in seconds

*NB get retry: service connection failure retries

*Delay? Before? Retry: retry connection interval in seconds

Tags: Operation & Maintenance MySQL Database network vim

Posted on Mon, 11 May 2020 05:17:19 -0400 by alex clone