There are many high availability schemes for MySQL, such as dual master, MHA, MMM, etc. here is just the simplest dual master high availability scheme.

1, Configure MySQL as master-slave

1. Environmental preparation

Note: MySQL has been deployed, please refer to the blog Centos deploy MySQL 5.7 Deploy.

2. Enable binary log and relay log

#The host mysql01 configuration file is as follows:
[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=1          #Server? ID must be unique
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
log-bin=/usr/local/mysql/data/log_bin            #Specify binary log file
relay-log=/usr/local/mysql/data/relay-bin        #Specify relay log
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=1

#The host mysql02 configuration file is as follows:
[root@mysql02 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=2 
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
log-bin=/usr/local/mysql/data/log_bin
relay-log=/usr/local/mysql/data/relay-bin
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=2

Note: only server ID and auto increment offset are different between mysql01 and mysql02

There are self growth fields in mysql. When performing the primary and primary synchronization of the database, you need to set two related configurations for self growth: Auto increment offset and auto increment increment. Auto increment increment indicates the amount of each increment of the self growing field, and its default value is 1. Its value should be set to the total number of servers in the whole structure. I use two servers here, so the value is set to 2. Auto increment offset is used to set the starting point (i.e. the initial value) of the automatic growth in the database. Because the two servers have set the automatic growth value 2 once, their starting points must be different, so as to avoid the primary key conflict when the two servers synchronize data.

Note: you can add "binlog do DB = database name" configuration item (multiple can be added) in my.cnf file to specify the database to be synchronized

3. Set mysql02 as the slave server of mysql01

1) Create authorized user on mysql01

[root@mysql01 ~]# mysql -uroot -p123.com
mysql> grant replication slave on *.* to rep@'192.168.20.%' identified by '123.com';

2) View the current binlog status information of mysql01

mysql> show master status\G
*************************** 1. row ***************************
             File: log_bin.000001     #This value will use
         Position: 609         #This value will use
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

3) Specify mysql01 as the master on mysql02, and enable the slave function

#Specify the master address
mysql> change master to master_host='192.168.20.2',
    -> master_user='rep',
    -> master_password='123.com',
    -> master_log_file='log_bin.000001',    #Must be the same as the name viewed on the master
    -> master_log_pos=609;      #As above, this value is also found on the master
#Start the slave function
mysql> start slave;
#Confirm configuration successful
mysql> show slave status\G            #View slave status
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.2
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000001
          Read_Master_Log_Pos: 609
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000001
             Slave_IO_Running: Yes               # This value must be Yes
            Slave_SQL_Running: Yes             # This value must also be Yes
#As long as the above two values are yes, there is no problem with the master and slave,
#The IO thread reads binary logs from the master to the local relay logs;
SQL Thread is to convert the contents of the local relay log to sql Statement and execute.

4. Set mysql01 as the slave server of mysql02

#The operations on mysql02 are as follows:
mysql> grant replication slave on *.* to rep@'192.168.20.%' identified by '123.com';
mysql> flush privileges;
mysql> show master status\G          #Get the required file and Position
*************************** 1. row ***************************
             File: log_bin.000002
         Position: 609
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

#The operations on the host mysql01 are as follows:
#Specify mysql02 as the master
mysql> change master to master_host='192.168.20.3',
    -> master_user='rep',
    -> master_password='123.com',
    -> master_log_file='log_bin.000002',
    -> master_log_pos=609;
mysql> start slave;       #start slave
mysql> show slave status\G          #View slave status
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.3
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000002
          Read_Master_Log_Pos: 609
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000002
                #Make sure the following two values are yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5. Test master master synchronization

1) Host mysql01 create test data

mysql> create database test;
mysql> use test
mysql> create table t1(id int,name varchar(4));
mysql> insert into t1 values(1,'a'),(2,'b');
#Confirm the data of mysql01
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

2) Confirm mysql02 is synchronized and insert new data

#The following operations are performed on the host mysql02
mysql> select * from t1;        #Make sure the data is synchronized
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
#Insert data test
mysql> insert into t1 values(3,'c'),(4,'d');

mysql> select * from t1;        #Determine the latest data
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

3) Confirm that mysql01 can synchronize the data of mysql02

#Query on mysql01 whether to synchronize the data on the mysql02 host
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

At this point, the update data on any MySQL will be synchronized to another mysql, and MySQL synchronization is completed.

Note: if the primary MySQL server already exists, only later business development can build a secondary server. Before configuring database synchronization, copy the database to be synchronized from the MySQL server to the secondary server (for example, backup the database on the primary MySQL first, and then restore it from the MySQL server).

2, Configure keepalived high availability

1. Install keepalived

Both nodes need to execute the following command to install keepalived.

[root@mysql01 ~]# yum -y install keepalived

2. Modify the maintained configuration file of mysql01

[root@mysql01 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql-01      #Value must be unique here
}

vrrp_instance VI_1 {
    state BACKUP       #The specified role is backup, and the roles of both MySQL servers are backup. Setting backup will determine the master and slave according to the priority

    interface ens33      #Specify the network card that hosts the virtual IP
    virtual_router_id 51         #Specify a group, the values within the same cluster must be consistent. And cannot conflict with other groups in the LAN
    priority 100          #Priority range: 0-100
    advert_int 1     #The time interval of sending vrrp package, i.e. how often to conduct master election (it can be considered as the time interval of health examination)
    nopreempt             #Do not preempt, that is, allow a node with low priority as the master,     
    authentication {          #Authentication area
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {         #VIP area, specify VIP address
        192.168.20.20
    }
}

virtual_server 192.168.20.20 3306 {    #To set up a virtual server, you need to specify the virtual IP address and service port, which are separated by spaces
    delay_loop 2         #Set the operation check time in seconds
    lb_algo rr      #Set backend scheduling algorithm
    lb_kind DR    #Set lvs to achieve load balancing mechanism, with NAT, TUN, DR three modes, DR mode is the most efficient
    persistence_timeout 60     #Session hold time in seconds
    protocol TCP     #Specifies the type of forwarding protocol, including TCP and UDP

    real_server 192.168.20.2 3306 {          #Configure the service node, which is the real IP address of the local machine
        weight 1     #Set weights
    notify_down /etc/keepalived/bin/mysql.sh    #The script executed after the MySQL service of real_server is down is detected.
    TCP_CHECK {
        connect_port 3306    #Health check port
        connect_timeout 3       #Connection timeout
        retry 3    #retry count
        delay_before_retry 3      #Reconnection interval
     }
   }
}
#Prepare the specified script
[root@mysql01 keepalived]# pwd
/etc/keepalived
[root@mysql01 keepalived]# mkdir bin
[root@mysql01 keepalived]# vim bin/mysql.sh
#!/bin/bash
pkill keepalived    #Stop the keepalived service
[root@mysql01 keepalived]# chmod +x bin/mysql.sh    #Give script execution permission
[root@mysql01 ~]# systemctl start keepalived        #Start the keepalived service
#Make sure that the ens33 network card has a virtual Ip

[root@mysql01 ~]# ip a show ens33    #You must use the ip a command to view it, but not the ifconfig command
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:c0:39:80 brd ff:ff:ff:ff:ff:ff
    inet 192.168.20.2/24 brd 192.168.20.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.20.20/32 scope global ens33     #You can see that the specified VIP has been bound to ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::659e:9312:318a:e52b/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

#Send the keepalived configuration file to mysql02 host
[root@mysql01 ~]# scp /etc/keepalived/keepalived.conf root@192.168.20.3:/etc/keepalived/

3. Modify the maintained configuration file of mysql02

#Modify the configuration file sent from msyql01
[root@mysql02 keepalived]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql-02         #Change router ID, which must be unique in the hot standby group
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90             #Change priority
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.20.20
    }
}

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

    real_server 192.168.20.3 3306 {        #Change to the IP address and listening port of this computer
        weight 1
    notify_down /etc/keepalived/bin/mysql.sh
    TCP_CHECK {
        connect_port 3306
        connect_timeout 3
        retry 3
        delay_before_retry 3
     }
   }
}
#Prepare required scripts
[root@mysql01 keepalived]# pwd
/etc/keepalived
[root@mysql02 keepalived]# mkdir bin
[root@mysql02 keepalived]# vim bin/mysql.sh
#!/bin/bash
pkill keepalived
[root@mysql02 keepalived]# chmod +x bin/mysql.sh 
#Start keepalived
[root@mysql02 ~]# systemctl start keepalived

At this point, the dual primary effect of MySQL can be achieved (as long as the node where the VIP is located and the MySQL service port cannot be connected, the VIP will switch to another node, even if the MySQL server is down for recovery, the VIP will not be preempted). Although there are two MySQL databases, they use the virtual IP address provided by keepalived to provide external services. No matter which server the virtual IP address falls on, data consistency can be guaranteed, because they are the master and slave of each other, and the state of keepalived is backup, and non preemption is set (reduce the switching times of VIP), which can greatly avoid keepaliv The brain crack of ED.