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 /data1.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=10relay-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.d1.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 keepalived3.修改/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 eth04.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 eth05. 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
