文章目录
一、分析
- 本文所说的高可用
本文所说的MySQL高可用主要是通过Keepalived来实现多个节点的服务的可用性检测和故障转移,对外提供VIP地址供客户端连接,由keepalived根据服务的状态,负责将vip漂移到真实可用的机器上,从而实现服务的高可用。
- 架构
两台MySQL或者多台做主主互备,VIP对外提供连接
机器 | IP/VIP | 组件 | 版本 |
---|---|---|---|
mysql1 | 10.10.0.1 | MySQL/Keepalived | 5.7.25/2.0.20 |
mysql2 | 10.10.0.2 | MySQL/Keepalived | 5.7.25/2.0.20 |
10.10.0.3 |
二、基础环境
1、修改主机名及hosts
hostnamectl set-hostname mysql1
hostnamectl set-hostname mysql2
...
cat >> /etc/hosts <<EOF
10.10.0.1 mysql1
10.10.0.2 mysql2
EOF
2、修改系统进程打开最大文件数
echo "* soft nofile 65535" >> /etc/security/limits.conf
echo "* hard nofile 65535" >> /etc/security/limits.conf
3、关闭防火墙及selinux
systemctl stop firewalld && systemctl disable firewalld
setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
三、MySQL
分别在mysql1及mysql2节点安装同一版本的mysql,本文以5.7.25为例说明
1、卸载mariadb数据库
rpm -qa | grep mariadb
yum -y remove mariadb-libs-5.5.52-1.el7.x86_64
2、依赖检查安装
- 安装MySQL需要libaio组件,默认的操作系统自带,可检查一下,如果没有需自行安装
rpm -qa | grep libaio
- 安装net-tools组件,默认系统没有需自行安装
rpm -ivh net-tools-2.0-0.24.20131004git.el7.x86_64.rpm
3、解压安装MySQL
仅安装必须的几个包即可
tar -xvf mysql-5.7.25-1.el7.x86_64.rpm-bundle.tar
rpm -ivh mysql-community-common-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.25-1.el7.x86_64.rpm
4、修改配置文件
vim /etc/my.cnf
default-storage-engine = innodb
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
max_allowed_packet = 500M
innodb_buffer_pool_size = 512M
max_connections = 20000
expire_logs_days = 10
max_binlog_size = 512M
5、初始化并启动数据库
mysqld --initialize
chown mysql:mysql /var/lib/mysql -R
systemctl enable mysqld && systemctl start mysqld
6、修改密码及权限添加
默认MySQL安装完有一个临时密码,可通过这个临时密码修改我们数据库的密码
cat /var/log/mysqld.log | grep password
通过临时密码登陆MySQL,修改密码
mysql -uroot -pSlg.ieA+j8Dn
set password for root@localhost = password('Test1234!');
添加远程登录权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Test1234\!';
FLUSH PRIVILEGES;
四、主主互备
1、主机mysql1配置
创建用于存放bin日志文件的目录
mkdir -p /usr/local/mysql
创建bin日志文件并修改权限及用户属组
touch /usr/local/mysql/mysql-bin.log
chmod 777 /usr/local/mysql/mysql-bin.log && chown -R mysql:mysql /usr/local/mysql/
修改配置文件my.cnf
vim /etc/my.cnf
log_bin = /usr/local/mysql/mysql-bin.log
server_id = 1
binlog_ignore_db=mysql
auto_increment_increment = 2
auto_increment_offset = 1
重启MySQL
service mysqld stop && service mysqld start
登陆MySQL配置mysql2的同步账号
mysql -uroot -p
grant replication slave on *.* to'slave'@'10.10.0.2' identified by 'Test1234!';
FLUSH PRIVILEGES;
2、主机mysql2配置
创建用于存放bin日志文件的目录
mkdir -p /usr/local/mysql
创建bin日志文件并修改权限及用户属组
touch /usr/local/mysql/mysql-bin.log
chmod 777 /usr/local/mysql/mysql-bin.log && chown -R mysql:mysql /usr/local/mysql/
修改配置文件my.cnf
vim /etc/my.cnf
log_bin = /usr/local/mysql/mysql-bin.log
server_id = 2
binlog_ignore_db=mysql
auto_increment_increment = 2
auto_increment_offset = 1
重启MySQL
service mysqld stop && service mysqld start
登陆MySQL配置mysql1的同步账号
mysql -uroot -p
grant replication slave on *.* to'slave'@'10.10.0.1' identified by 'Test1234!';
FLUSH PRIVILEGES;
3、mysql1配置
重启并登陆MySQL
service mysqld stop && service mysqld start
mysql -uroot -p
配置salve
stop slave;
change master to master_host='10.10.0.2',master_user='slave',master_password='Test1234!';
start slave;
4、mysql2配置
重启并登陆MySQL
service mysqld stop && service mysqld start
mysql -uroot -p
配置salve
stop slave;
change master to master_host='10.10.0.1',master_user='slave',master_password='Test1234!';
start slave;
5、查看主主互备状态
分别登陆mysql1及mysql2,查看互备状态
show slave status \G;
重点关注项为:
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
五、Keepalived
在mysql1和mysql2分别安装keepalived
1、安装依赖
yum -y install openssl-devel
2、获取Keepalived
https://www.keepalived.org/download.html
3、安装
tar -xvf keepalived-2.0.20.tar.gz && rm -rf keepalived-2.0.20.tar.gz && cd keepalived-2.0.20
./configure --prefix=/usr/local/keepalived
make && make install
4、版本查看
/usr/local/keepalived/sbin/keepalived -v
5、配置文件
- 配置文件参数详解
https://www.cnblogs.com/arjenlee/p/9258188.html
- 创建配置文件目录及配置文件
mkdir -p /etc/keepalived
自带配置文件:/usr/local/keepalived/etc/keepalived/keepalived.conf,可作参考,这里直接创建新的配置文件即可
- mysql1服务器
vim /etc/keepalived/keepalived.conf
global_defs {
router_id mysql1
}
vrrp_script mysql_check {
script "/usr/local/keepalived/check.sh"
interval 2
weight -20
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 80
advert_int 1
track_script {
mysql_check
}
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
10.10.0.3
}
}
- mysql2服务器
vim /etc/keepalived/keepalived.conf
global_defs {
router_id mysql2
}
vrrp_script mysql_check {
script "/usr/local/keepalived/check.sh"
interval 2
weight:-20
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 70
advert_int 1
track_script {
mysql_check
}
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
10.10.0.3
}
}
- vrrp_script:检查脚本,检查haproxy状态,如果挂掉,VIP漂移
- script:脚本命令
- interval:检查间隔
- weight:权重
- state:标记该节点是master还是backup
- interface:配置VIP绑定的网卡,这里使用和外网通信的网卡
- virtual_router_id:取1-255之间的值,主备需要相同,这样才能成为一个组
- priority:权重,数值高的主机是master,所以主节点要比从节点大,这是影响主备的关键
- advert_int:主备之间通讯的间隔秒数,用于判断主节点是否存活
- auth_type:进行安全认证的方式,PASS或者AH方式,推荐PASS
- auth_pass:PASS的密码
- virtual_ipaddress:VIP地址,最多可以写20个,keepalive启动后会自动配置该VIP
6、检测脚本
放置到配置文件所指定目录并添加权限
vim /usr/local/keepalived/check.sh
#!/bin/bash
count=`ps aux | grep -v grep | grep mysqld | wc -l`
if [ $count -eq 0 ]; then
exit 1
else
exit 0
fi
chmod +x /usr/local/keepalived/check.sh
7、启动
- 创建service文件
cat > /etc/systemd/system/keepalived.service <<EOF
[Unit]
Description=LVS and VRRP High Availability Monitor
After=syslog.target network-online.target
[Service]
Type=forking
KillMode=process
ExecStart=/usr/local/keepalived/sbin/keepalived
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable keepalived && systemctl start keepalived && systemctl status keepalived
8、主从查看
在主节点查看网络信息,可以发现我们设置的VIP,当主节点或者MySQL任意一个挂掉时,VIP都会自动漂移到从节点实现mysql的高可用