前期安装准备:
https://blog.csdn.net/lizy928/article/details/82531749
https://blog.csdn.net/zsg88/article/details/76652958
https://www.cnblogs.com/kingsonfu/p/11392470.html
卸载:
https://blog.csdn.net/typa01_kk/article/details/49057073
1、双主配置
(1) 准备两台服务器
master1 :172.16.1.111
master2 :172.16.1.112
(2) 修改mysql配置文件,配置主从复制
vi /etc/my.cnf
master1的配置
server-id = 2
auto_increment_offset = 2 自增长字段从那个数开始
auto_increment_increment = 2 自增长字段每次递增的量
log-bin = mysql-bin 日志备份,数据库操作日志
max_binlog_size=1024M
binlog-ignore-db =sys,mysql, information_schema,performance_schema 对这些数据库不进行操作
binlog-do-db = ztest 对这些数据库进行操作
slave-skip-errors=all
gtid_mode = on 开启全局事务id操作
enforce_gtid_consistency = on
master2的配置
server-id = 1
auto_increment_offset = 1 自增长字段从那个数开始
auto_increment_increment = 2 自增长字段每次递增的量
log-bin = mysql-bin 日志备份,数据库操作日志
max_binlog_size=1024M
binlog-ignore-db =sys,mysql, information_schema,performance_schema 对这些数据库不进行操作
binlog-do-db = ztest 对这些数据库进行操作
slave-skip-errors=all
gtid_mode = on 开启全局事务id操作
enforce_gtid_consistency = on
(3)重启master1和master2的MySQL
systemctl restart mysqld
(4)在master1中添加能访问master2数据库的用户,同时赋予相权限
grant replication slave on *.* to 'test2'@'172.16.1.112' identified by '1234567890';
grant file on *.* to 'test2'@'172.16.1.112' identified by '1234567890';
允许主机master2 账号 test1 密码 1234567890 连接我取日志
mysql -utest1 -p -h172.16.1.112
(5)在master2中添加能访问master1数据库的用户,同时赋予相权限
grant replication slave on *.* to 'test1'@'172.16.1.111' identified by '1234567890';
grant file on *.* to 'test1'@'172.16.1.111' identified by '1234567890';
允许主机master1 账号 test2 密码 1234567890 连接我取日志
mysql -utest2 -p -h172.16.1.111
(6)查看master1数据库状态
mysql> show master status;
(7)查看master2数据库状态
mysql> show master status;
(8)在master1中配置同步数据
mysql> change master to master_host='172.16.1.112', master_user='test2', master_password='1234567890',master_log_file='mysql-bin.000038',master_log_pos=194;
master2日志文件及master2日志位置。
(9)执行如下命令
mysql> start slave;
mysql> show slave status\G;
(10)下面两个状态都是YES表明正确
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(11)在master2中配置同步数据
mysql> change master to master_host='172.16.1.111', master_user='test1', master_password='1234567890',master_log_file='mysql-bin.000028',master_log_pos=3020;
master1日志文件及master1日志位置。
(12)执行(9)的命令查看配置是否成功。
2、 keepalived的安装
(1)下载keepalived
wget http://www.keepalived.org/software/keepalived-1.4.2.tar.gz
(2)解压
tar -zxvf keepalived-1.2.18.tar.gz
(3)安装
yum install -y openssl openssl-devel(下载所需依赖)
cd keepalived-1.2.18(进入解压的文件目录)
./configure --prefix=/usr/local/keepalived (安装)
make && make install (安装)
(4)文件拷贝
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
在复制最后一个文件时,出现了没有rc.d文件夹也没有init.d文件夹及keepalived可执行文件。这些都是我自己创建的。
mkdir /usr/local/keepalived/etc/rc.d/ (创建文件夹)
mkdir /usr/local/keepalived/etc/rc.d/init.d/ (创建文件夹)
touch /usr/local/keepalived/etc/rc.d/init.d/keepalived (创建文件)
vi /usr/local/keepalived/etc/rc.d/init.d/keepalived (修改文件)
在/usr/local/keepalived/etc/rc.d/init.d/keepalived文件中添加如下内容
#!/bin/sh
#
# Startup script for the Keepalived daemon
#
# processname: keepalived
# pidfile: /var/run/keepalived.pid
# config: /etc/keepalived/keepalived.conf
# chkconfig: - 21 79
# description: Start and stop Keepalived
# Source function library
. /etc/rc.d/init.d/functions
# Source configuration file (we set KEEPALIVED_OPTIONS there)
. /etc/sysconfig/keepalived
RETVAL=0
prog="keepalived"
start() {
echo -n $"Starting $prog: "
daemon keepalived ${KEEPALIVED_OPTIONS}
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog
}
stop() {
echo -n $"Stopping $prog: "
killproc keepalived
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog
}
reload() {
echo -n $"Reloading $prog: "
killproc keepalived -1
RETVAL=$?
echo
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
reload)
reload
;;
restart)
stop
start
;;
condrestart)
if [ -f /var/lock/subsys/$prog ]; then
stop
start
fi
;;
status)
status keepalived
RETVAL=$?
;;
*)
echo "Usage: $0 {start|stop|reload|restart|condrestart|status}"
RETVAL=1
esac
exit $RETVAL
将/usr/local/keepalived/etc/rc.d/init.d/keepalived改为可执行文件,然后再进行复制操作。
chmod +x /usr/local/keepalived/etc/rc.d/init.d/keepalived
3、keepalived高可用配置
(1)修改master1的keepalived的配置
vi /etc/keepalived/keepalived.conf
具体配置如下
! Configuration File for keepalived
global_defs {
router_id LVS_DEVEL1 /表示运行 keepalived 服务器的一个标识
}
vrrp_instance VI_1 {
state BACKUP
interface ens192 /指定 HA 监测网络的接口 通过 lshw -class network 查询
virtual_router_id 51 /虚拟路由标识,这个标识是一个数字(取值在 0-255 之间,用来区分多个instance 的 VRRP 组播),同一个 vrrp 实例使用唯一的标识,确保和 master2 相同,同网内不同集群此项必须不同,否则发生冲突。
priority 140 /用来选举 master 的,要成为 master,该项取值范围是 1-255(在此范围之外会被识别成默认值 100),此处 master2 上设置为 50
advert_int 1 /发 VRRP 包的时间间隔,即多久进行一次 master 选举(可以认为是健康查检时间间隔)
authentication { /认证区域
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { /VIP 区域,指定 vip 地址
172.16.1.100
}
}
virtual_server 172.16.1.100 3306 { /设置虚拟服务器,需要指定虚拟 IP 地址和服务端口
delay_loop 6 /设置运行情况检查时间,单位是秒
lb_algo wrr /设置后端调度算法
lb_kind DR /设置 LVS 实现负载均衡的机制
persistence_timeout 60 /会话保持时间,单位是秒。
protocol TCP /指定转发协议类型,有 TCP 和 UDP 两种
real_server 172.16.1.111 3306 { /配置服务节点 1,需要指定 real server 的真实 IP 地址和端口,IP 与端口之间用空格隔开
weight 100 /配置服务节点的权值,权值大小用数字表示,数字越大,权值越高,设置权值大小为了区分不同性能的服务器
notify_down /data/sh/mysql.sh /检测到 realserver 的 mysql 服务 down 后执行的脚本
TCP_CHECK {
connect_timeout 10 /连接超时时间
nb_get_retry 3 /重连次数
delay_before_retry 3 /重连间隔时间
connect_port 3306 /健康检查端口
}
}
}
(2)master2配置与master1相似,修改master2的配置
vi /etc/keepalived/keepalived.conf
具体配置如下
! Configuration File for keepalived
global_defs {
router_id LVS_DEVEL1 /表示运行 keepalived 服务器的一个标识
}
vrrp_instance VI_1 {
state BACKUP
interface ens192 /指定 HA 监测网络的接口 通过 lshw -class network 查询
virtual_router_id 51 /虚拟路由标识,这个标识是一个数字(取值在 0-255 之间,用来区分多个instance 的 VRRP 组播),同一个 vrrp 实例使用唯一的标识,确保和 master2 相同,同网内不同集群此项必须不同,否则发生冲突。
priority 40 /用来选举 master 的,要成为 master,该项取值范围是 1-255(在此范围之外会被识别成默认值 100),此处 master2 上设置为 50
advert_int 1 /发 VRRP 包的时间间隔,即多久进行一次 master 选举(可以认为是健康查检时间间隔)
authentication { /认证区域
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { /VIP 区域,指定 vip 地址
172.16.1.100
}
}
virtual_server 172.16.1.100 3306 { /设置虚拟服务器,需要指定虚拟 IP 地址和服务端口
delay_loop 6 /设置运行情况检查时间,单位是秒
lb_algo wrr /设置后端调度算法
lb_kind DR /设置 LVS 实现负载均衡的机制
persistence_timeout 60 /会话保持时间,单位是秒。
protocol TCP /指定转发协议类型,有 TCP 和 UDP 两种
real_server 172.16.1.112 3306 { /配置服务节点 1,需要指定 real server 的真实 IP 地址和端口,IP 与端口之间用空格隔开
weight 100 /配置服务节点的权值,权值大小用数字表示,数字越大,权值越高,设置权值大小为了区分不同性能的服务器
notify_down /data/sh/mysql.sh /检测到 realserver 的 mysql 服务 down 后执行的脚本
TCP_CHECK {
connect_timeout 10 /连接超时时间
nb_get_retry 3 /重连次数
delay_before_retry 3 /重连间隔时间
connect_port 3306 /健康检查端口
}
}
}
(3)添加notify_down中的脚本问
mkdir /data 创建文件夹
mkdir /data/sh/ 创建文件夹
touch /data/sh/mysql.sh 创建文件
vi /data/sh/mysql.sh 修改文件
(4)在/data/sh/mysql.sh中添加如下内容
/etc/init.d/keepalived stop
(5)将/data/sh/mysql.sh 变为可执行文件
chmod +x /data/sh/mysql.sh
4、MySQL宕机并自动重启MySQL及keepalived服务
(1)添加定时检测脚本
mkdir /data 创建文件夹
mkdir /data/sh 创建文件夹
touch /data/sh/checkmysqlkeepalived.sh 创建文件
(2)在定时脚本添加如下内容
pgrep -x mysqld &> /dev/null
if [ $? -ne 0 ]
then
echo "At time: `date` :MySQL is stop .">> /var/log1/mysql_log.log
systemctl start mysqld
systemctl start keepalived
else
echo "At time: `date` :MySQL is running .">> /var/log1/mysql_log.log
fi
pgrep -x keepalived &> /dev/null
if [ $? -ne 0 ]
then
echo "At time: `date` :keepalived is stop .">> /var/log1/mysql_log.log
systemctl start mysqld
systemctl start keepalived
else
echo "At time: `date` :keepalived is running .">> /var/log1/mysql_log.log
fi
(3)将文件更改为可执行文件
chmod +x /data/sh/checkmysqlkeepalived.sh
(4)添加定时检测文件
*/1* * * * root /bin/bash /data/sh/checkmysqlkeepalived.sh (每分钟检测一次)
参数详解如下
# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed