一、实验环境

1、两台机器

  • MasterA:192.168.200.114
  • MasterB:192.168.200.115

2、实现思路

实现Mysql主主复制

二、安装mariadb

1、第一台

[root@masterA ~]# yum -y install mariadb mariadb-server

[root@masterA ~]# vim /etc/my.cnf
[mysqld]
server-id= 1      //标记ID
log-bin=mysql-binlog    //开启二进制日志
log-slave-updates=true   //将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
max_binlog_size=1024M
//masterA自增长ID
auto_increment_offset=1
auto_increment_increment=2   //奇数ID

replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql

max_connections=3000
max_connect_errors=30

skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8
wait_timeout=1800
interactive_timeout=1800
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

[root@masterA ~]# systemctl start mariadb
[root@masterA ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.115' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 |      245 |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2、第二台

[root@masterB ~]# yum -y install mariadb mariadb-server

[root@masterB ~]# vim /etc/my.cnf
[mysqld]
server-id       = 2
log-bin=mysql-binlog
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset = 2
auto_increment_increment = 2                            #偶数ID

replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8
wait_timeout=1800
interactive_timeout=1800
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

[root@masterB ~]# systemctl start mariadb
[root@masterB ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.114' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 |      486 |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

三、配置两台从服务器

1、第一台

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> change master to
    -> master_host='192.168.200.115',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='123123',
    -> master_log_file='mysql-binlog.000001',
    -> master_log_pos=486;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.115
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000001
          Read_Master_Log_Pos: 486
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 532
        Relay_Master_Log_File: mysql-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

2、第二台

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> change master to
                            master_host='192.168.200.114',
                            master_port=3306,
                            master_user='repl',
                            master_password='123123',
                            master_log_file='mysql-binlog.000001',
                            master_log_pos=245;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.114
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 532
        Relay_Master_Log_File: mysql-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

四、测试主主复制

第一台
MariaDB [(none)]> create database test01;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test01             |
+--------------------+
5 rows in set (0.00 sec)

第二台
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test01             |
+--------------------+
5 rows in set (0.00 sec)
第二台
MariaDB [(none)]> create database test02;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test01             |
| test02             |
+--------------------+
6 rows in set (0.00 sec)

第一台
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test01             |
| test02             |
+--------------------+
6 rows in set (0.00 sec)

有以上的测试结果说明主主复制成功

实现Mysql主主高可用方案

1、第一台

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

[root@masterA ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id LVS_MASTER-A
}

vrrp_script mysql {
    script "/opt/mysql.sh"
    interval 2
    weight -5
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 51
    priority 100
    nopreempt   //VIP宕机修复后不会转移
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        mysql
    }
    virtual_ipaddress {
        192.168.200.254
    }
}

[root@masterA ~]# vim /opt/mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi

[root@masterA ~]# chmod +x /opt/mysql.sh
[root@masterA ~]# systemctl start keepalived
[root@masterA ~]# ip a | grep eno16777736
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.200.114/24 brd 192.168.200.255 scope global eno16777736
    inet 192.168.200.254/32 scope global eno16777736
[root@masterA ~]# tail -f /var/log/messages
Oct  7 16:42:43 localhost Keepalived_vrrp[27658]: Sending gratuitous ARP on eno16777736 for 192.168.200.254
Oct  7 16:42:43 localhost Keepalived_vrrp[27658]: Sending gratuitous ARP on eno16777736 for 192.168.200.254
Oct  7 16:49:02 localhost systemd: Stopped firewalld - dynamic firewall daemon.
Oct  7 17:00:47 localhost systemd: Starting Session 24 of user root.
Oct  7 17:00:47 localhost systemd: Started Session 24 of user root.
Oct  7 17:00:47 localhost systemd-logind: New session 24 of user root.
Oct  7 17:01:01 localhost systemd: Starting Session 25 of user root.
Oct  7 17:01:01 localhost systemd: Started Session 25 of user root.
Oct  7 18:01:01 localhost systemd: Starting Session 26 of user root.
Oct  7 18:01:01 localhost systemd: Started Session 26 of user root.

2、第二台

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

[root@masterB ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id LVS_MASTER-B
}

vrrp_script mysql {
    script "/opt/mysql.sh"
    interval 2
    weight -5
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        mysql
    }
    virtual_ipaddress {
        192.168.200.254
    }
}

[root@masterB ~]# vim /opt/mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi

[root@masterB ~]# chmod +x /opt/mysql.sh
[root@masterB ~]# systemctl start keepalived
[root@masterB ~]# tail -f /var/log/messages
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: WARNING - default user 'keepalived_script' for script execution does not exist - please create.
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP_Instance(VI_1) removing protocol VIPs.
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: Using LinkWatch kernel netlink reflector...
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP_Instance(VI_1) Entering BACKUP STATE
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Oct 16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP_Script(mysql) succeeded
Oct 16 18:01:01 localhost systemd: Started Session 26 of user root.
Oct 16 18:18:39 localhost systemd: Started Session 27 of user root.
Oct 16 18:18:39 localhost systemd-logind: New session 27 of user root.

测试

1、第一台

#开启keepalived服务
[root@masterA ~]# systemctl start keepalived
#过滤ip
[root@masterA ~]# ip a | grep inet
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
    inet 192.168.200.111/24 brd 192.168.200.255 scope global eno16777728
    inet 192.168.200.254/32 scope global eno16777728
#关闭mysql服务
[root@masterA ~]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL............ SUCCESS!

2、第二台

[root@masterB ~]# ip a | grep inet
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
    inet 192.168.200.112/24 brd 192.168.200.255 scope global eno16777728
    inet 192.168.200.254/32 scope global eno16777728
01-31 17:04