MySQL dual master configuration

Environmental preparation:

　　OS：CentOS 7
master：192.168.1.10
backup：192.168.1.20
  VIP：192.168.1.30

One, install MySQL database.

Install mysql on master and backup, it will start automatically after installation, the mysql root password is 123456

Two, modify the MySQL configuration file:

1.The master configuration file is as follows:

1.The master configuration file is as follows:

# vim /etc/my.cnf #Add
server_id = 1 #Backup is set to 2
log-bin = /data/mysql/mysql-bin
log-bin-index=/data/mysql/my-bin.index
binlog-ignore -db = mysql,information_schema #Ignore the library written to the binlog log
auto-increment-increment = 2 #Field change increment value
auto-increment-offset = 1 #Initial field ID is 1
slave-skip-errors = all #Ignore all Copy errors

# systemctl restart mysqld

2.The backup configuration file is as follows:

The configuration on the master side and the backup side only differs in server_id, and everything else is the same.

3.Create a data synchronization user and view the log bin log and pos position:

1.> Create a mysql synchronization account on the master and view the log bin log and pos location:

# mysql -uroot -p123456

mysql> GRANT  REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED  BY 'repl';

mysql> flush  privileges;

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB        | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      618 |              | mysql,information_schema |                  |
+------------------+----------+--------------+--------------------------+-------------------+

The master configuration is as follows:

# mysql -uroot -p123456

mysql> change master to
    -> master_host='192.168.1.20',                #这里填backup的IP
    -> master_user='repl',
    -> master_password='repl',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=618;

mysql> start slave;

2.> Create a mysql synchronization account configuration on backup as follows:

# mysql -uroot -p123456

mysql> GRANT  REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED  BY 'repl';

mysql> flush  privileges;

mysql> change master to
    -> master_host='192.168.1.10',                #这里填master的IP
    -> master_user='repl',
    -> master_password='repl',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=618;

mysql> start slave;
---------------------

View the synchronization status separately:

master view:

mysql> show slave status\G;
*************************** 1.row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.20
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1082
              Relay_Log_File: test2-relay-bin.000002
                Relay_Log_Pos: 784
        Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
---------------------

backup view:

mysql> show slave status\G;
*************************** 1.row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 618
              Relay_Log_File: test3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
---------------------

If Slave_IO and Slave_SQL are YES, the master-master synchronization is successful.

 Fourth, MySQL master and master synchronization test

Insert data test on the master:

mysql> create database testdb;

mysql> use testdb;

mysql> create table user (number INT(10),name VARCHAR(255));

mysql> insert into user values(01,'testid');

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user          |
+----------------+
---------------------

View on backup:

mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb              |
+--------------------+

mysql> use testdb;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user          |
+----------------+

mysql> select number,name from user;
+--------+------+
| number | name |
+--------+------+
|      1 | testid  |
+--------+------+
---------------------

You can see that it has been successfully synchronized, and the user table data is also inserted in the backup, and the same is synchronized, and the dual-master configuration is no problem.

Five, configure keepalived to achieve dual-system hot backup

1.Master install keepalived and configure:

# yum install -y keepalived

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
  notification_email {
    [email protected]
  }
  notification_email_from [email protected]
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id MYSQL_HA
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0 #Change according to the actual network interface
    virtual_router_id 51
    priority 100
    #Priority , master is set to 100 advert_int 1
    nopreempt #Do not actively preempt resources, only set
    authentication on the master {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.30
    }
}

virtual_server 192.168.1.30 3306 {
    delay_loop 2
    #lb_algo rr
    #lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.10 3306 {#Detect local mysql
        weight 3
        notify_down /tmp/mysql.sh #When the mysql service is down, execute this script to kill keepalived to realize the switch
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

Backup install keepalived and configure:

# yum install -y keepalived

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
  notification_email {
    [email protected]
  }
  notification_email_from [email protected]
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id MYSQL_HA
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0 #Change according to the actual network interface
    virtual_router_id 51
    priority 90
    #Priority , backup is set to 90 advert_int 1
    #nopreempt #Active preemption of resources
    authentication {
        auth_type PASS
        auth_pass 1111
    } 
    virtual_ipaddress {
        192.168.1.30
    } 
} 

virtual_server 192.168.1.30 3306 {
    delay_loop 2
    #lb_algo rr
    #lb_kind NAT
    persistence_timeout 50
    protocol TCP
   
    real_server 192.168.1.20 3306 {#Detect local mysql
        weight 3
        notify_down /tmp/mysql.sh #When the mysql service is down, execute this script and kill keepalived realizes switching
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        } 
    } 
}

Edit mysql.sh on master and backup

# vim /tmp/mysql.sh

#!/bin/bash
pkill keepalived

# chmod +x !$
# systemctl start keepalived

Two mysql servers are authorized to allow root remote login:

# mysql -uroot -p123456789

mysql> grant all on *.* to 'root'@'192.168.1.%' identified by '123456';

mysql> flush privileges;

Test the high availability
through the VIP connection through the mysql client to see if the connection is successful.
Here I use another machine on the same network segment to test the connection:

# mysql -h192.168.1.30 -uroot -p123456

Type 'help;' or '\h' for help.Type '\c' to clear the current input statement.

mysql> select * from test.user;
+--------+------+
| number | name |
+--------+------+
|      1 | testid  |
+--------+------+
1 row in set (0.01 sec)
---------------------

You can see that the connection is successful and there is no problem with querying the data.Stop the mysql service on the master and whether you can switch to the backup normally, you can use the ip addr command to check which server the VIP is on.

Check if there is a VIP on the master, you can see that the VIP is on the master

# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
      valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
      valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.10/24 brd 192.168.1.255 scope global noprefixroute eth0
      valid_lft forever preferred_lft forever
    inet 192.168.1.30/32 scope global eth0
      valid_lft forever preferred_lft forever
    inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute
      valid_lft forever preferred_lft forever
---------------------

Stop the mysql service on the master:

# systemctl stop mysqld

# ps axu |grep keepalived
root      11074  0.0  0.0 112708  988 pts/1    S+  15:28  0:00 grep --color=autokeepalived

# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
      valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
      valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.10/24 brd 192.168.1.255 scope global noprefixroute eth0
      valid_lft forever preferred_lft forever
    inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute
      valid_lft forever preferred_lft forever
---------------------

It can be seen that keepalived was stopped after the mysql service was stopped, and the VIP was not on the master.

Check if there is a VIP on the backup, you can see that the VIP is on the backup.

# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
      valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
      valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:33:80:d5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.20/24 brd 192.168.1.255 scope global noprefixroute eth0
      valid_lft forever preferred_lft forever
    inet 192.168.1.30/32 scope global eth0
      valid_lft forever preferred_lft forever
    inet6 fe80::4b20:2e16:a957:f9a1/64 scope link noprefixroute
      valid_lft forever preferred_lft forever
---------------------

Check the /var/log/messages log, you can see the main-standby switching process:

Apr  8 15:27:16 hosts systemd: Stopping MySQL Server...
Apr  8 15:27:16 hosts Keepalived_healthcheckers[11048]: TCP connection to [192.168.1.10]:3306 failed.
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: TCP connection to [192.168.1.10]:3306 failed.
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: Check on service [192.168.1.10]:3306 failed after 1 retry.
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: Removing service [192.168.1.10]:3306 from VS [192.168.1.30]:3306
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: IPVS (cmd 1160, errno 2): No such destination
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: Executing [/tmp/mysql.sh] for service [192.168.1.10]:3306 in VS [192.168.1.30]:3306
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: Lost quorum 1-0=1 > 0 for VS [192.168.1.30]:3306
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: Remote SMTP server [127.0.0.1]:25 connected.
Apr  8 15:27:19 hosts Keepalived_vrrp[11049]: VRRP_Instance(VI_1) sent 0 priority
Apr  8 15:27:19 hosts Keepalived_vrrp[11049]: VRRP_Instance(VI_1) removing protocol VIPs.
Apr  8 15:27:19 hosts Keepalived[11047]: Stopping
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: IPVS (cmd 1156, errno 2): No such file or directory
Apr  8 15:27:19 hosts Keepalived_healthcheckers[11048]: Stopped
Apr  8 15:27:20 hosts Keepalived_vrrp[11049]: Stopped
Apr  8 15:27:20 hosts Keepalived[11047]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Apr  8 15:27:27 hosts systemd: Stopped MySQL Server.
---------------------

Recover from the failure of the master server and see if it actively preempts resources and becomes the active server.

Start the mysql service and keepalived service on the master:

# systemctl start mysqld

# systemctl start keepalived

# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
      valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
      valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.10/24 brd 192.168.1.255 scope global noprefixroute eth0
      valid_lft forever preferred_lft forever
    inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute
      valid_lft forever preferred_lft forever
---------------------

It can be seen that even if the master fails to recover, there is no preemption of resources and the VIP is still on the backup.This is because the master has been configured in nopreempt mode (nopreempt).

However, it should be noted that:

The nopreempt parameter can only be used when the state is BACKUP, so the state of master and backup must be set to BACKUP when configuring, so that the non-preemptive mode of keepalived can be realized!

In other words:

* When one of the state states is MASTER and the other is BACKUP, adding or not adding the parameter nopreempt will have the same effect. That is to say, who is to preempt VIP resources is determined based on priority, which is the preemption mode!

* When the state state is set to BACKUP, if the nopreempt parameter is not configured, then the priority priority is also used to determine who preempts VIP resources, that is, it is also a preemptive mode.

* When the state state is set to BACKUP, if the nopreempt parameter is configured, then the priority priority will not be considered, it is a non-preemptive mode! That is, only when the machine where the vip is currently located fails, another machine can take over the vip.
 Even if the machine with the highest priority returns to normal, it will not take the initiative to snatch back the VIP.It can only switch back the VIP until the other party fails.