centos7 keepalived+mysql双主高可用部署

centos7 keepalived+mysql双主高可用部署

一、简介

为什么要使用双主部署呢？

在一台mysql主机情况下，如果该主机存在宕机或者其他什么故障，直接会影响整个服务的不可用。为了确保整体服务的高可用性，不仅要在服务层实现多节点的集群部署，还需要在数据库层实现多节点集群部署。如有两台mysql数据库服务器，若其中有一台mysql服务器故障后，另一台可以立马接替工作。

Keepalived+mysql双主高可用方案原理？

Keepalived+mysql双主来实现MySQL-HA，我们必须保证两台MySQL数据库的数据完全一样，基本思路是两台 MySQL 互为主从关系，通过 Keepalived 配置虚拟 IP，实现当其中的一台MySQL 数据库宕机后，应用能够自动切换到另外一台 MySQL 数据库，保证系统的高可用。

---

二、环境准备

1.系统与软件版本

Linux OS：centos7.5

Mysql 版本：mysql 5.7.20

Keepalived版本：keepalived2.0.0

2.两台虚拟机和 虚拟ip

虚拟IP vip ：192.168.1.100

Mysql-master1 ：192.168.1.101

Mysql-master2 ：192.168.1.102

3.系统约定

安装包存放目录：/usr/local/src

Mysql安装目录：/usr/local/mysql

Mysql数据库数据存放目录：/data/mysql

Mysql数据库日志目录：/data/mysql/log

---

三、部署过程

1.Mysql数据安装

部署机器：192.168.1.101、192.168.1.102

如果有磁盘需要挂载到mysql数据库数据存放目录，先挂载磁盘

1.1上传mysql包并解压

cd /usr/local/src/

tar -xzvf /usr/local/src/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

1.2 移动解压文件夹并改名

mv /usr/local/src/mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql

注：次步如果移动不成功，可先把mysql-5.7.20-linux-glibc2.12-x86_64文件移到/usr/local/下，再手动重命名

1.3创建mysql数据保存目录

mkdir /data/mysql

1.4 新建mysql用户和组

groupadd mysql

useradd -r -g mysql mysql

扩展：

              查看是否存在 mysql 组：more /etc/group | grep mysql

　　　　查看 msyql 属于哪个组：groups mysql

　　　　查看当前活跃的用户列表：w

1.5 改变mysql相关目录的隶属者，并附读写权限

cd /usr/local/mysql

chown -R mysql .

chgrp -R mysql .

chown -R mysql:mysql /data/mysql

chmod -R 775 /data

1.6 配置参数

cd进入/usr/local/mysql目录，执行以下命令：

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

此处需要注意记录生成的临时密码，在上文结尾处，备份好，后面需要用到：
    2020-07-31T05:59:29.953142Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-31T05:59:30.402257Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-07-31T05:59:30.505065Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-07-31T05:59:30.580636Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ffec25bf-d2f2-11ea-98ab-fa163e60db16.
2020-07-31T05:59:30.584965Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-07-31T05:59:30.585659Z 1 [Note] A temporary password is generated for root@localhost: VeWGu3fI_HUg

1.7 生成ssl

cd进入/usr/local/mysql目录，执行以下命令：

bin/mysql_ssl_rsa_setup  --datadir=/data/mysql

1.8修改/etc/init.d/mysql配置文件

cd /usr/local/mysql/support-files/

cp mysql.server /etc/init.d/mysql

修改/etc/init.d/mysql文件中的basedir和datadir路径：

vim /etc/init.d/mysql

basedir=/usr/local/mysql
datadir=/data/mysql

1.9 修改/etc/my.cnf配置文件

配置文件内容如下（主要标红处，两台服务器配置不一样）：

[mysqld]
#设置最大连接数
max_connections=1000
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

#base config
socket=/tmp/mysql.sock
bind-address=0.0.0.0
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql

#开启日志
log-error=/data/mysql/log/mysql-error.log
slow_query_log=1
long_query_time=1
slow_query_log_file=/data/mysql/log/slow-query.log
general_log=1
general_log_file=/data/mysql/log/general-query.log

pid-file=/data/mysql/mysql.pid

character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
lower_case_table_names=1

#master config（以下单机部署可不配）
#要给从机同步的库
binlog-do-db=test_db_dev
binlog-do-db=test_db_test
#不给从机同步的库(多个写多行)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#打开日志(主机需要打开)
log-bin=mysql-bin
binlog_format=mixed
server-id=1 #服务器id (192.168.1.101为1；192.168.1.102为2)
#自动清理10天前的log文件
expire_logs_days=10

relay-log=relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment=2
auto-increment-offset=1 #(192.168.1.101为1；192.168.1.102为2)
thread_cache_size=64

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid

[mysql.server]
user=mysql
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
 

1.10 启动mysql

/etc/init.d/mysql start 或者 service mysql start

拓展：查看mysql状态：service mysql status

          停止mysql：service mysql stop

          重启mysql：service mysql restart

1.11登录mysql，修改密码、远程访问授权

mysql -h localhost -u root -p

输入1.6节产生的随机密码

修改密码

mysql> set password=password('你的新密码');

授权远程访问

mysql>grant all privileges on *.* to 'root'@'%' identified by '你的新密码';
mysql>flush privileges;

测试mysql查询：

接下来使用本地Navicat 测试是否可以远程连接192.168.1.101和192.168.1.102两台服务器数据看库；

注：如何提示不成功，检查服务器3306端口是否开通、防火墙中是否添加了3306端口；或者关闭防火墙。

命令拓展：

防火墙中添加端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

查看防火墙状态
systemctl status firewalld

查看开发的端口
firewall-cmd --zone=public --list-ports

开启防火墙
systemctl start firewalld

重启防火墙
systemctl restart firewalld.service

关闭防火墙
systemctl stop firewalld.service 

1.12添加环境变量

vim /etc/profile

文件最后一行添加：

export PATH=/usr/local/mysql/bin:$PATH

 source /etc/profile

1.13配置mysql开机自启动

  chmod 755 /etc/init.d/mysql
  chkconfig --add mysql
  chkconfig --level 345 mysql on    

至此mysql安装配置结束

---

三、两台MySQL配置主主同步

要实现互为主从，就必须 （192.168.1.101）master1设为（192.168.1.102）master2的主服务器, 同时（192.168.1.102）master2设为（192.168.1.101）mster1 的主服务器。

1.修改前面/etc/my.conf

前面/etc/my.conf中已配置两台mysql同步数据库的配置,此处略。。。

2.在192.168.1.101 master1上创建一个master2复制用户

登录mysql后，一般复制用户名可设置为项目名检测，例如rep

mysql> GRANT replication slave on *.* to 'rep'@'192.168.1.102' IDENTIFIED BY '你的数据库密码';

  mysql> flush privileges;

锁表，待同步配置完成在解锁

mysql> flush tables with read lock;

查看当前的binlog以及数据所在位置

mysql> show master status;

将 master1 设为 master2 的主服务器,在192.168.1.101服务器上操作:

mysql> unlock tables; 

mysql> stop slave；

mysql> change master to master_host='192.168.1.102',master_user='rep',master_password='你的数据库密码',master_log_file='mysql-bin.000009',master_log_pos=154;

mysql> start slave;

说明：

master_host 是另外一台的ip
master_user  是另外一台数据库的复制数据用户名
master_password 是另外一台的数据库的密码
master_log_file 是另外一台数据库的日志文件，对应show master status 输出中 File位置
master_log_pos 是另外一台数据库的日志位置，对应show master status 输出中 Position位置

3.在192.168.1.102 master2上创建一个master1复制用户

登录mysql后，一般复制用户名可设置为项目名检测，例如rep

mysql> GRANT replication slave on *.* to 'rep'@'192.168.1.101' IDENTIFIED BY '你的数据库密码';

  mysql> flush privileges;

锁表，待同步配置完成在解锁

mysql> flush tables with read lock;

查看当前的binlog以及数据所在位置

mysql> show master status;

将 master1 设为 master2 的主服务器,在192.168.1.101服务器上操作:

mysql> unlock tables; 

mysql> stop slave；

mysql> change master to master_host='192.168.1.101',master_user='rep',master_password='你的数据库密码',master_log_file='mysql-bin.000008',master_log_pos=9280894;

mysql> start slave;

4.在两台服务器上分别查看Mysql的线程状态

mysql> show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.102
                  Master_User: prm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000009
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           ..................................

 *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: prm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           ..................................

 注：Slave_IO_Running和Slave_SQL_Running均为Yes，则表示配置成功；若有一个不是，则不成功，重新检查配置是否正确。

5.两台数据库服务器数据同步验证

在192.168.1.101上登录mysql，创建数据库、创建表、并插入数据；然后在192.168.1.102上登录mysql，查看是192.168.1.101是否同步到192.168.1.102上；反之，同理。

四、keepalived安装配置

所需按照配置的机器：192.168.1.101和192.168.1.102

1. 安装openssl-devel popt-devel libnl libnl-devel libnfnetlink-devel gcc

yum install openssl-devel popt-devel libnl libnl-devel libnfnetlink-devel gcc -y

2.上传keepalived置/usr/local/src/下，解压并安装

 cd /usr/local/src/

 tar zxvf keepalived-2.0.0.tar.gz

mkdir -p ../keepalived

cd keepalived-2.0.0

./configure --prefix=/usr/local/keepalived

make && make install

cp /usr/local/src/keepalived-2.0.0/keepalived/etc/init.d/keepalived /etc/init.d/

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

mkdir /etc/keepalived

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

echo "/etc/init.d/keepalived start" >> /etc/rc.local

chmod +x /etc/rc.d/init.d/keepalived

chkconfig keepalived

3.修改/etc/keepalived/keepalived.conf文件配置

3.1 192.168.1.101服务器配置修改

! Configuration File for keepalived

global_defs {
   router_id mysql-1
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
   script_user root
   enable_script_security
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass T&^!4A0q
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

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

    real_server 192.168.1.101 3306 {
        weight 1
        notify_down   /etc/keepalived/mysql.sh
        TCP_CHECK {
            connect_timeout 10    #连接超时时间  
            retry 3      #重连次数
            delay_before_retry 3   #重连间隔时间 
            connect_port 3306
            retry 3
        }
    }
}

3.2 192.168.1.102服务器配置修改

! Configuration File for keepalived

global_defs {
   router_id mysql-2
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
   script_user root
   enable_script_security
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass T&^!4A0q
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

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

    real_server 192.168.1.102 3306 {
        weight 1
        notify_down   /etc/keepalived/mysql.sh
        TCP_CHECK {
            connect_timeout 10    #连接超时时间  
            retry 3      #重连次数
            delay_before_retry 3   #重连间隔时间 
            connect_port 3306
            retry 3
        }
    }
}

4. mysql.sh脚本

4.1 在192.168.1.101服务器/etc/keepalived/下编写mysql.sh脚本

#!/bin/bash
pkill keepalived
/bin/python /etc/keepalived/send_email.py master 192.168.1.101 192.168.1.100
/sbin/ifdown eth0 && /sbin/ifup eth0

4.2 在192.168.1.102服务器/etc/keepalived/下mysql.sh脚本

#!/bin/bash
pkill keepalived
/bin/python /etc/keepalived/send_email.py master 192.168.1.102 192.168.1.100
/sbin/ifdown eth0 && /sbin/ifup eth0

5. send_email.py脚本

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import sys, time, subprocess, random

# 第三方 SMTP 服务
mail_host="mail.gmail.com"  #设置服务器
# 设置发送邮件的用户及密码，可设置多个
userinfo_list = [{'user':'sender_test@gmail.com','pass':'123456'}]

user_inst = userinfo_list[random.randint(0, len(userinfo_list)-1)]
mail_user=user_inst['user']    #用户名
mail_pass=user_inst['pass']   #口令

sender = 'sender_test@gmail.com'    # 邮件发送者
receivers = ['receiver_test@gmail.com']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱

p = subprocess.Popen('hostname', shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
hostname = p.stdout.readline().split('\n')[0]

message_to = ''
for i in receivers:
    message_to += i + ';'

def print_help():
    note = '''python script.py role ip vip
    '''
    print(note)
    exit(1)

time_stamp = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))

if len(sys.argv) != 4:
    print_help()
elif sys.argv[1] == 'master':
     message_content = '  \t您好：\n   \t %s 数据库服务器: %s 出现故障，请您前往服务器排除故障！可能出现的问题为数据库不可用或者服务器宕机！ 虚拟IP: %s' %(time_stamp, sys.argv[2], sys.argv[3])
  #   message_content = '%s server: %s(%s) change to Master, vIP: %s' %(time_stamp, sys.argv[2], hostname, sys.argv[3])
 #   message_content = '%s server: %s(%s) change to Master, vIP: %s' %(time_stamp, sys.argv[2], hostname, sys.argv[3])
#    subject = '%s change to Master -- keepalived notify' %(sys.argv[2])
elif sys.argv[1] == 'backup':
     message_content = '  \t您好：\n   \t %s 数据库服务器: %s 出现故障，请您前往服务器排除故障！可能出现的问题为数据库不可用或者服务器宕机！ 虚拟IP: %s' %(time_stamp, sys.argv[2], sys.argv[3])
 #   message_content = '%s server: %s(%s) change to Backup, vIP: %s' %(time_stamp, sys.argv[2], hostname, sys.argv[3])
#    subject = '%s change to Backup -- keepalived notify' %(sys.argv[2])
else:
    print_help()

message = MIMEText(message_content, 'plain', 'utf-8')
message['From'] = Header(sender, 'utf-8')
message['To'] =  Header(message_to, 'utf-8')
message['Subject'] = Header('PRM服务器系统邮件', 'utf-8')

#message['Subject'] = Header(subject, 'utf-8')

try:
    smtpObj = smtplib.SMTP()
    smtpObj.connect(mail_host, 25)    # 25 为 SMTP 端口号
    smtpObj.login(mail_user,mail_pass)
    smtpObj.sendmail(sender, receivers, message.as_string())
    print("邮件发送成功")
except smtplib.SMTPException as e:
    print("Error: 无法发送邮件")
    print(e)
 

6. 两台服务器上启动keepalived

chmod 775 /etc/keepalived/mysql.sh

/etc/init.d/keepalived start

拓展：

 重启：/etc/init.d/keepalived restart

 停止：/etc/init.d/keepalived stop

7.验证vip是否配置成功

Navicat使用 192.168.1.100 测试是否可建立数据库连接，连接成功说明配置成功；如失败请检查keepalived日志

查看日志方式：

tail -200f /var/log/messages

根据日志提示的错误进行相应调整 

8.我所遇见的错误

8.1 keepalived 报错 Keepalived_healthcheckers[1127] Unknown keyword 'nb_get_retry'

原因：keepalived 高版本。 nb_get_retry无法再使用，nb_get_retry替换为 retry 

解决办法：nb_get_retry替换为 retry ，并重启keepalived

8.2 Keepalived报错 default user does not exist 。。

原因：未知

解决办法：

global_defs {

#添加内容如下：
   script_user root
   enable_script_security 

}

 8.3 Configuration file '/etc/keepalived/keepalived.conf' is not a regular non-executable file。。

解决办法：更改 /etc/keepalived/keepalived.conf 文件权限，命令如下：

chmod 644 /etc/keepalived/keepalived.conf