mysql高可用双主+keepalived

mysql高可用双主+keepalived

#关闭防火墙和selinux

环境介绍：两台mysql数据库 centos7.9 数据库版本5.7.20

master01 192.168.52.10

master02 192.168.52.20

1）安装mysql，保持两台数据库的数据一致性

我这里使用脚本安装。

cat mysql.sh

#!/bin/bash

rpm -qa | grep mariadb &> /dev/null

rpm -e mariadb --nodeps &> /dev/null

tar -zxf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/src

cd /usr/local/src

mv mysql-5.7.38-linux-glibc2.12-x86_64/ /usr/local/mysql

cd /usr/local/mysql

groupadd -r mysql && useradd -r -g mysql -s /bin/false -M mysql

mkdir /usr/local/mysql/data

chown -R mysql:mysql /usr/local/mysql

sed -i '$a export PATH=$PATH:/usr/local/mysql/bin' /etc/profile

source /etc/profile

echo "" > /etc/my.cnf

sed -i '1s/^/[mysqld]/' /etc/my.cnf

sed -i '1a basedir=/usr/local/mysql' /etc/my.cnf

sed -i '2a datadir=/usr/local/mysql/data' /etc/my.cnf

sed -i '3a pid-file=/usr/local/mysql/data/mysqld.pid' /etc/my.cnf

sed -i '4a log-error=/usr/local/mysql/data/mysql.err' /etc/my.cnf

sed -i '5a socket=/tmp/mysql.sock' /etc/my.cnf

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

cd support-files/

cp mysql.server /etc/init.d/

service mysql.server start

ss -anplt | grep 3306

mysqlpasswd=`awk -F 'root@localhost: ' '/temporary password/{print $2}' /usr/local/mysql/data/mysql.err`

alias mysql=/usr/local/mysql/bin/mysql

mysqladmin -uroot -p$mysqlpasswd password 123

source /etc/profile

mysql -uroot -p123

2）创建用户，先做一个主从复制在做另一个。

master01上操作：

mysql -uroot -p123-u：指定用户 -p：密码

grant Replication client, Replication slave on *.* to rep@’192.168.52.%’identified by ‘123’;

3）修改mysql主配置文件/etc/my.cnf

vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

pid-file=/usr/local/mysql/data/mysqld.pid

log-error=/usr/local/mysql/data/mysql.err

socket=/tmp/mysql.sock

log_bin=mysql-bin#开启二进制日志

server_id=1#设置sever_id，两台不能一致

relay-log=relay-bin #开启中继日志

relay_log_index=slave-relay-bin.index #设置中继日志的位置

auto_increment_increment=2#自增长字段每次递增的量,有几台服务器就设为几。

auto_increment_offset=1#用来设定数据库中自动增长的起点。

binlog_format=mixed#二进制日志的类型

#重启mysql服务

/etc/init.d/mysql.server restart

4）查看二进制日志的位置

show master status；

master2上操作：让master2从master1配置主从复制

5）配置change master to参数

Change master to

Master_host=‘192.168.52.20’，

Master_user=‘rep’，

Master_password=’123’,

Master_log_file=‘mysql-bin.000001',

master_log_pos=154；

6）启动slave，并查看状态。

Start slave;

show slave status\G

#查看到SQL线程和IO线程都为yes，主从复制就配置成功了。

#配置第二个主从复制

1）创建用户

master02上操作：

mysql -uroot -p123-u：指定用户 -p：密码

grant Replication client, Replication slave on *.* to rep@’192.168.52.%’identified by ‘123’;

3）修改mysql主配置文件/etc/my.cnf

修改主配置文件中的server_id=2，auto-increment-offset=2 其他配置同上。

#重启mysql服务

/etc/init.d/mysql.server restart

4）查看二进制日志的位置

show master status；

master1上操作：让master1从master2配置主从复制

5）配置change master to参数

Change master to

Master_host=‘192.168.52.20’，

Master_user=‘rep’，

Master_password=’123’,

Master_log_file=‘mysql-bin.000001',

master_log_pos=472；

6）启动slave，并查看状态。

Start slave;

show slave status\G

#查看到SQL线程和IO线程都为yes，主从复制就配置成功了。

#安装keepalived实现高可用，注意两台都要安装配置一样！！！

1）下载keepalived tar包

wget https://www.keepalived.org/software/keepalived-2.2.7.tar.gz

2）安装依赖

yum install kernel-devel openssl-devel popt-devel

3)解包编译安装

tar zxf keepalived-2.0.20.tar.gz

cd keepalived-2.0.20/

./configure --prefix=/ && make && make install

4)修改主配文件/etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id master-1

vrrp_skip_check_adv_addr

vrrp_strict

vrrp_garp_interval 0

vrrp_gna_interval 0

}

vrrp_instance VI_1 {

state BACKUP

interface ens33

virtual_router_id 51

priority 100

nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.52.100

}

}

virtual_server 192.168.52.100 3306 {

delay_loop 6

lb_algo rr

lb_kind DR

persistence_timeout 50

protocol TCP

real_server 192.168.52.20 3306 {

weight 1

notify_down /etc/keepalived/bin/mysql.sh

TCP_CHECK {

connect_timeout 3

retry 3

delay_before_retry 3

}

}

}

参数解释：

global_defs:全局配置

router_id master-1 //表示keepalived的标识不能重复

vrrp_instance VI_1:vrrp实例配置

state BACKUP //两台都是backup根据优先级选择主从。

interface ens33 //指定HA监测网络的接口

priority 100 //优先级高的为master

nopreempt //不抢占，即允许一个priority比较低的节点作为master，即使有priority更高的节点启动

virtual_ipaddress //VIP区域，指定vip地址

192.168.52.100

virtual_server 192.168.52.100 3306 //设置虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开

lb_kind DR //设置LVS实现负载均衡的机制，有NAT、TUN、DR

real_server 192.168.52.10 3306 //配置服务节点1，需要指定real server的真实IP地址和端口

notify_down /etc/keepalived/bin/mysql.sh //检测到realserver的mysql服务down后执行的脚本

#创建一个bin目录，编写keepalived脚本

mkdir /etc/keepalived/bin -p

vim mysql.sh

#!/bin/bash

pkill keepalived

#赋予权限

chmod +x mysql.sh

5）master02：

修改主配文件/etc/keepalived/keepalived.conf

修改router_id为 master-2

修改优先级为50

修改真实ip地址:192.168.52.20 3306

其他配置同上

6)启动keepalived服务

systemctl start keepalived.service

#查看VIP地址为：192.168.52.100

模拟故障：

把mysql服务停掉，查看VIP地址会不会漂移到另一台，即可！

/etc/init.d/mysql.server stop