Mysql5.7主主双主复制配置+keepalived实现高可用

Mysql主主双主复制互为主从配置。
两台服务器：
服务器A： 192.168.1.100
服务器B：192.168.1.200
首先下载 mysql-5.7.28
下载完之后，解压：

tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.28-linux-glibc2.12-x86_64 mysql-5.7.28

添加用户和组 ：

groupadd mysql
useradd -g mysql mysql

建立数据目录：

mkdir -p /data1/mysql/data
mkdir -p /data1/mysql/replica

更改目录权限：

chown -R mysql:mysql /usr/local/mysql-5.6.46
chown -R mysql:mysql /data1/mysql

配置my.cnf:

[root]# vim /etc/my.cnf


[client]
port            = 3306 
socket          =/data1/mysql/mysql.sock
default-character-set=utf8

[mysqld]
server-id=2
basedir=/usr/local/mysql-5.7.28
datadir=/data1/mysql/data
socket=/data1/mysql/mysql.sock
log-bin=/data1/mysql/replica/mysql_bin
relay-log=/data1/mysql/replica/relay_bin
relay-log-index=/data1/mysql/replica/relay-bin.index
binlog_format=mixed
innodb_file_per_table = 1
auto-increment-increment=2
# 这里需要注意，两台服务器不能一样，一个设置为1，一个设置为2即可
auto-increment-offset=1 
#skip-grant-tables

将Mysql加入系统环境变量：

vim /etc/profile
#在文件的末尾添加内容：
PATH=$PATH:/usr/local/mysql-5.6.46/bin
export PATH

初始化数据库：

./bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql-5.6.46/data

如果报如下错误：
[ERROR] Can't read from messagefile '/usr/share/mysql/english/errmsg.sys'

则进行如下操作：

cp share/english/errmsg.sys /usr/share/mysql/english/errmsg.sys

覆盖对应文件，重新执行即可。

将MySQL设置为系统服务并启动服务:

cp support-files/mysql.server /etc/init.d/mysqld

修改/etc/init.d/mysqld配置：

#一般修改如下两个配置即可
basedir=/usr/local/mysql-5.7.28
datadir=/data1/mysql/data

启动mysql：

/etc/init.d/mysqld start

一般启动后，mysql默认会分配一个密码，如果忘记了，可以将my.cnf中：
#skip-grant-tables
注释去掉即可，直接进入mysql：

mysql -uroot

重新设置密码，注意的是，mysql5.7中mysql.user表中没有了password字段，而是authentication_string
修改密码和远程登录：

mysql> user mysql;
mysql> update user set host='%',authentication_string=password('123456') where user='root';
mysql> flush privileges;

再把my.cnf免密登录配置注释，重启mysql:

service mysqld restart

重新进入后，当我们切换到mysql库时，报如下错误：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
进入mysql，执行：

mysql -uroot -p123456
mysql> alter user 'root'@'%' identified by '123456';
mysql> flush privileges;

即可。

上面两台服务器操作一直，除了my.cnf中自增配置不一样外。

配置复制账号

在服务器A（192.168.1.100）上执行

 mysql > GRANT REPLICATION SLAVE ON *.* TO 'slave'@192.168.1.200' IDENTIFIED BY '123456';
mysql> flush privilegs;

在服务器B（192.168.1.200）上执行

 mysql > GRANT REPLICATION SLAVE ON *.* TO 'slave'@192.168.1.100' IDENTIFIED BY '123456';
mysql> flush privilegs;

开始同步配置

在服务器A（192.168.1.100）上执行：

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

在服务器B（192.168.1.200）上执行：

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

在服务器A（192.168.1.100）上执行：

mysql> change master to master_host='192.168.1.200',master_user='slave',master_password='123456',master_log_file='mysql-bin.00009 ',master_log_pos=688;

在服务器B（192.168.1.200）上执行：

mysql> change master to master_host='192.168.1.100',master_user='slave',master_password='123456',master_log_file='mysql-bin.000011 ',master_log_pos=154;

两台服务器都执行如下命令：

mysql> start slave;

开启同步：

查看同步状态：

在A（192.168.1.100）服务器上执行：

mysql>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000011
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay_bin.000007
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 734
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 2db8f891-0c6a-11eb-8b8e-00155d36c701
             Master_Info_File: /data1/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

在B（192.168.1.200）服务器上执行：

mysql>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay_bin.000006
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 521
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 622ebc02-0d2c-11eb-ae22-00155d36c702
             Master_Info_File: /data1/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

同步状态正常，同步成功。

keepavlied配置：
A服务器（192.168.1.100）
keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_script chk_mysql {
        script "/etc/keepalived/chk_mysql.sh"
        interval 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    unicast_src_ip 192.168.1.100
    unicast_peer {
        192.168.1.200
    }
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_mysql
    }


 virtual_ipaddress {
    192.168.1.155
 }
}

keepavlied配置：
B服务器（192.168.1.200）
keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_script chk_mysql {
        script "/etc/keepalived/chk_mysql.sh"
        interval 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    unicast_src_ip 192.168.1.200
    unicast_peer {
        192.168.1.100
    }
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_mysql
    }


 virtual_ipaddress {
    192.168.1.155
 }
}

这样，通过192.168.1.155即可访问，当其中一台mysql出现问题之后，会切换到另外一台，完成高可用。