MariaDB+Keepalived 搭建双主HA数据库服务

1、安装mysql

在linux版本下，mysql称为mariadb，可以选择在线安装，或编译安装。MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

用国内镜像源替换官方的MariaDB镜像源，目前MariaDB最新版本10.4，这里选10.3作为稳定版安装，本人项目或者测试环境，都是以MariaDB为主。

vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum install mariadb mariadb-server -y
systemctl restart mariadb
systemctl enable mariadb
mysql_secure_installation
# 该命令为重置root密码，并做一些安全配置,若不做配置，后续将无法使用sell进入mysql

2、配置主备server的my.cnf

通过my.cnf可以优化或者定制更高级的mysql使用，具体参考另外一篇文章==todo==，这里只写简单主主同步配置

主服务器

[mysqld]
# 数据目录可以使用默认也可以自行定义，所有的binlog以及db物理文件都在datadir里面后期可以通过挂载存储扩容
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1
# 开启日志模式
log-bin=mysql-bin
relay-log=mysql-relay-bin

# 双主模式下，防止两边插入插入时，自增键冲突，主服务器自增规则：1，3，5奇数自增
auto_increment_offset=1
auto_increment_increment=2
log_slave_updates =1
# 忽略一些测试表
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%

#出现错误后忽略，若不跳过，出现任何同步错误，slave-IO进程会终止
slave-skip-errors=all

备服务器

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

server-id=2

log-bin=mysql-bin
relay-log=mysql-relay-bin

# 双主模式下，防止两边插入插入时，自增键冲突，备服务器自增规则：2，4，6奇数自增
auto_increment_offset=2
auto_increment_increment=2
log_slave_updates =1

replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%

#出现错误后忽略，若不跳过，出现任何同步错误，slave-IO进程会终止
slave-skip-errors=all

3、创建新账户

在主服务器，备服务器分别创建用于管理主备的msyql帐号，请勿用root账户，以免出现安全问题！

mysql -u root -psd123321sd
create user 'sync_acct'@'192.168.100.%' identified by '*&@jall190';
grant replication slave on *.* to 'sync_acct'@'192.168.100.%';

# 以下两条配置可以实现在shell中mysql -usync_acct -p*&@jall190 直接登录，否则会提示如下：
# ERROR 1045 (28000): Access denied for user 'sync_acct'@'localhost' (using password: YES)，登录失败
# grant select,insert,update,delete on 
GRANT ALL PRIVILEGES ON *.* TO sync_acc@"127.0.0.1" IDENTIFIED BY "*&@jall190" WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO sync_acc@"localhost" IDENTIFIED BY "*&@jall190" WITH GRANT OPTION;

flush privileges;
exit

# 查看可本地登录的记录
MariaDB [(none)]> select HOST,User from mysql.user;
+-----------------------+-----------+
| HOST                  | User      |
+-----------------------+-----------+
| 127.0.0.1             | root      |
| 127.0.0.1             | sync_acc  |
| 192.168.100.%         | sync_acct |
| ::1                   | root      |
| localhost             | root      |
| localhost             | sync_acc  |
| localhost.localdomain | root      |
+-----------------------+-----------+

4、查看主服务器bin-log信息

#对数据库进行只读锁定（防止查看二进制日志同时有人对数据库修改操作）
MariaDB [(none)]> flush tables with read lock;
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      504 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> unlock tables;

以上的file名称：mysql-bin.000002跟position：504在一下主备需要用到

5、分别在主备服务器上配置主-主模式

（1）在备服务器上，配置db1-192.168.100.5作为master

# 直接在命令行上敲
MariaDB [(none)]> change master to 
master_host='192.168.100.5', 
master_port=41210,
master_user='sync_acct', 
master_password='*&@jall190', 
#指明初始复制时的mysql1中的binlog文件
master_log_file='mysql-bin.000003',
#指明初始复制时binlog文件的位置
master_log_pos=655;

MariaDB [(none)]> start slave;

从服务器开启slave线程后，通过show slave status\G可以看到一些信息，这里\G是表示按竖立显示

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.5
                  Master_User: sync_acct
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 4041005
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 4041030
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  ......

以上信息可知

#这个是指Slave连接到Master的状态，当前IO线程的状态为等待master发送事件，该字段显示mysql不同状态的不同信息，而且信息简单易读
Slave_IO_State: Waiting for master to send event：

Slave_IO_Running: Yes，显示slave的I/O线程是否被启动并成功地连接到主服务器上。
Slave_SQL_Running: Yes，显示slave上用于读取Relay_Log的SQL线程是否被启动

另外，备份Slave_IO_State信息对于在主服务器上的状态，用show processlist查看主服务器显示master已经发送所有的binlog到salve，并等待主服务器更新这个binlog

MariaDB [(none)]> show processlist\G;
*************************** 1. row ***************************
      Id: 24
    User: sync_acct
    Host: 192.168.100.6:35474
      db: NULL
 Command: Binlog Dump
    Time: 1143
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000

以上说明，主-备模式成功配置，但还不是主-主模式

（2）在(1)上，已经配置好主-从同步，且已经验证可正常写入同步，这里，将配置主-主模式，在主服务器db2-192.168.100.5上，配置db2-192.168.100.6作为master

# 直接在命令行上敲
MariaDB [(none)]> change master to 
master_host='192.168.100.6', 
master_user='sync_acct', 
master_password='*&@jall190', 
# 以下两个字段的值，务必在从服务器上，用show master status 查看相关值
master_log_file='mysql-bin.000003',
master_log_pos=587;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.6
                  Master_User: sync_acct
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 587
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

（3）在第（1）、（2）已经成功完成主-主模式，但也仅仅是说明msyql主主配置正常，但还未通过数据写入来测试其正确性，可通过以下简单的两个步骤测试主-主模式数据的同步

主服务器上新建一个databases，创建一个简单表，观察slave是否同步新建情况
在刚新建的表里插入一条记录，观察slave对应的表是否也多一条记录
在备服务器上drop 掉刚建的数据库，然后在salve重复以上操作，观察master上数据同步情况

# 这里仅简单给出一些命令，不再具体展开
create database erp_app;
use erp_app;
create table if not exists apps_name(
id int(4) not null primary key auto_increment,
app_log_name char(20) not null,
log_path char(200) not null,
log_date timestamp default current_timestamp
);
show tables
#插入数据，因为id是自增，无需自行插入，使用null交给mysql自动插入相应id号
insert into apps_name values(null,'BI-Access-Log','/opt/data/apps_log/',null);

使用python插入数据做测试使用，后面可作为验证主-主故障切换使用

import time
import pymysql
db_info={
            'host':'192.168.100.5',
            'port':3306,
            'user':'root',
            'password':'****',
            'db':'erp_app',
            'charset':'utf8'
        }

try:
    conn = pymysql.connect(**db_info)
    insert_sql = ("insert into apps_name "
                  "(id,app_log_name,log_path,log_date) "
                  "values(null,%(app_log_name)s,%(log_path)s,null)")
    insert_data={
        'app_log_name':'BI-Access-Log',
        'log_path':'/opt/data/apps_log/'
        }
    
    with conn.cursor() as cur:
        while True:
            resl=cur.execute(insert_sql,insert_data)
            print(resl)
            time.sleep(2)
            conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()

finally:
    conn.close()

在第2点配置my.cnf，设定了自增键规则，也可以验证主、从插入数据时，其自增键的情况

在主服务器重新插入四条，id为奇数id：

MariaDB [erp_app]> select * from apps_name;
+----+---------------+
| id | app_log_name  |
+----+---------------+
|  1 | BI-Access-Log |
|  3 | BI-Access-Log |
|  5 | BI-Access-Log |
|  7 | BI-Access-Log |
+----+---------------+

清空之前测试的数据，在备服务器重新插入四条，id为偶数id：

MariaDB [erp_app]>  select * from apps_name;
+----+---------------+
| id | app_log_name  |
+----+---------------+
|  2 | BI-Access-Log |
|  4 | BI-Access-Log |
|  6 | BI-Access-Log |
|  8 | BI-Access-Log |
+----+---------------+

但是这种配置自增算法有bug，只在双主模式下，假设现在要三台服务器都作为主，互相同步，那么现有的自增键策略无法扩展，其实有两种可行方案：

第一种：

 # 通过增大步长，例如有10台，步长值=主服务器数量，解决自增键冲突
 # 服务器1
auto-increment-increment = 10
auto-increment-offset   = 1
 # 服务器2
auto-increment-increment = 10
auto-increment-offset   = 2
 # 服务器3
auto-increment-increment = 10
auto-increment-offset   = 3
 ......

#服务器10
auto-increment-increment = 10
auto-increment-offset   = 10

第二种：数据库表不设置自增字段，由程序逻辑用UUID实现id唯一值，个人推荐此方式，不受限制。

注意：

在主从模式下，或者主-主模式下，测试数据过程中，例如在master上进行drop erp_app时，若slave不存在erp_app数据库（主从分离连接后，从已先删除），slave将无法进行同步删除操作，后续的同步操作也无法正常进行，show slave status\G，看到有报错提示db不存在无法drop的信息：

1 2	Last_SQL_Errno: 1008 Last_SQL_Error: Error 'Can't drop database 'erp_app'; database doesn't exist' on query. Default database: 'erp_app'. Query: 'drop database erp_app'

需在两台服务器上进行如下设置，==如果已经在my.cnf配置文件配好slave-skip-errors=all可跳过一下设置==

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

# 将同步指针向移动下一个位置，跳过异常，以便mysql可继续执行下一个同步操作
MariaDB [(none)]> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

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

（4）查看binlog,relay-log,创建数据库对应的物理存储文件

[root@localhost mysql]# ls
aria_log.00000001  ibtmp1             mysql-bin.000003  mysql-bin.000010  mysql-bin.index
aria_log_control   localhost.pid      mysql-bin.000004  mysql-bin.000011  mysql-relay-bin.000050
erp_app            master.info        mysql-bin.000005  mysql-bin.000012  mysql-relay-bin.000051
ib_buffer_pool     multi-master.info  mysql-bin.000006  mysql-bin.000013  mysql-relay-bin.index
ibdata1            mysql              mysql-bin.000007  mysql-bin.000014  mysql.sock
ib_logfile0        mysql-bin.000001   mysql-bin.000008  mysql-bin.000015  performance_schema
ib_logfile1        mysql-bin.000002   mysql-bin.000009  mysql-bin.000016  relay-log.info

6、keepalived配置以及VIP漂移策略

（1）keepalived配置/etc/keepalived/keepalived.conf，权限必须为644

! 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   
   #以下两行处理脚本执行权限问题
   script_user root
   enable_script_security 

   router_id hdA # 备服务器hdB，主、备的router_id必须不同，否则VRRP无法选举
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
   
}

# 检测db主-主同步脚本
vrrp_script chk_mariadb_sync {
    script "/etc/keepalived/check_db_sync.sh"
    interval 2
    weight 10
}

vrrp_instance VI_1 {
	# 非抢占模式
    state BACKUP
    nopreempt
    interface ens33
    virtual_router_id 51
    priority 100 # 备服务器99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 89287201
    }
    virtual_ipaddress {
        192.168.100.7
    }
}

track_script {
     chk_mariadb_sync
   }

（2） mariaDB 同步状态检测脚本 /etc/keepalived/check_db_sync.sh

注意，这里别用root账户，保证一定安全，chmod 744 check_db_sync.sh

#!/bin/bash
mysql_bin=/usr/bin/mysql
user=sync_acc
pw=passPass
host=127.0.0.1
port=3306
# Seconds_Behind_Master
sbm=60


io_thread_state=`$mysql_bin -h $host -P $port -u$user -p$pw -e 'show slave status\G'  2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
echo $io_thread_state

sql_thread_state=`$mysql_bin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
echo $sql_thread_state

SBM=`$mysql_bin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
echo $SBM

#Check for $mysql_bin
if [ ! -f $mysql_bin ];then
        echo 'the path of mysqlbin is incorrect,please check msyql path'
        exit 2
fi


# check mysql status whether is dead
service mariadb status &>/dev/null
if [ $? -ne 0 ];then
        pkill keepalived
        echo 'mysql is dead'
        exit 1
fi

# -z 表示如果$IOThread变量为空，说明数据库服务不可用，已down
if [[ -z "$io_thread_state" ]];then
		pkill keepalived
		echo 'mysql is dead'
        exit 1
fi


if [[ "$io_thread_state" == "Connecting" && "$sql_thread_state" == "Yes" ]];then
		echo 'master is down,but slave still works'
        exit 0
        
        # Seconds_Behind_Master timeout
        elif [[ $SBM -ge $sbm ]];then
                pkill keepalived
                exit 1
        else
                exit 0
fi

VIP切换到正常的主服务逻辑：

1）主A，主B db数据库正常，IO线程同步正常，vip被主A占用

2）主A数据库down后，如检测脚本所示，脚本把主A 的keepalived服务kill掉，此时VIP飘移到主B服务器，实现故障转移，对于主B，它的线程连接主A超时，无法同步，但仍可对外提供db服务：

1
2
3

Slave_IO_State: Reconnecting after a failed master event read
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

3）当主A 恢复数据库后，主A重启 keepalived服务，因为配置为非抢占模式，故此时还是由主B对外提供db服务

4）如果主A、主B服务都down了？你应该在此之前准备好相关邮件或者短信监控并人工介入

补充：VIP漂移策略不一定按上述情况，可自行加入数据库相关的监控指标来确定脚本，也可用python作为脚本

7、在防火墙写入ACL，开放相关端口

个人发现csdn上绝对大部分博客教程，尤其在前期配置环境这一类文章，一律跳过防火墙设置，直接停掉防火墙，以便快速部署，部署成功后，大部分文章会忽略最后加入防火墙设置，事实上一旦形成这种“偷懒的”习惯，在生成环境很容易出现“服务器、数据、漏洞安全”，即使服务内网使用。而且当前网络安全形势突出，在参与2019护网行动以及等保工作中，对安全有了较深刻体会。

1) 端口加入防火墙，防火墙设置分为centos7.5 firewalld和centos6或者redhat6.5的iptables，因为本人长期使用centos发行版，这里给出的是firewalld的设置

firewall-cmd --state
systemctl start firewalld
systemctl enable firewalld
# 测试端口连通性，很多人会用telnet测试端口是否打开，但centos默认没有telnet服务，其实针对tcp层连通性测试，使用http协议也一样，而且系统自带wget命令，非常方便测试
在备服务器上，关闭防火墙的两种情况
[root@localhost ~]# wget http://192.168.100.5:3306
--2019-08-16 10:50:59--  http://192.168.100.5:3306/
Connecting to 192.168.100.5:3306... connected.

# 打开防火墙，默认并未放通3306端口
[root@localhost ~]# wget http://192.168.100.5:3306
--2019-08-16 10:51:10--  http://192.168.100.5:3306/
Connecting to 192.168.100.5:3306... failed: No route to host.

# 限制仅192.168.100.0/24网段能访问端口3306
firewall-cmd --permanent --zone=public --add-rich-rule="rule family="ipv4" source address="192.168.100.0/24" port protocol="tcp" port="3306" accept"

# 更新防火墙规则
firewall-cmd --reload或firewall-cmd --complete-reload
(两者的区别就是第一个无需断开连接，就是firewalld特性之一动态添加规则，第二个需要断开连接，类似重启服务)

2) 开放VRRP协议，用于keepalived 主备状态检测

# 主备都运行下面的命令，从组播地址224.0.0.18，可以看出VRRP用了组播协议
# 入方向
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
# 出方向
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --out-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload