两台数据库服务器搭建成主从,通过keepalived 实现高可用。如果master mysql 出现故障,虚拟ip 就会漂移到从服务器上,slave mysql 会升级成 master 。继续提供数据服务。
1.服务器分配
Mysql1 设置为主 master mysql ip:ip1
Mysql2 设置为从 slave mysql ip:ip2
虚拟ip 设置在主服务器上 192.168.230.90
2. 部署前准备
设置/etc/selinux/config中selinux=disabled 重启系统
关闭防火墙
安装常用的依赖包
yum -y install zlib zlib-devel pcre pcre-devel gcc gcc-c++ openssl openssl-devel libevent libevent-devel perl unzip net-tools wge
系统软件安装
一、环境准备
- yum 移除mysql的libs
yum remove mysql-libs
2. Rpm 移除mysql
2.1.列出已安装的mysql包:rpm -qa | grep mariadb
2.2.下载:rpm –e
2.3.如果报有依赖,强制卸载 : rpm -e --nodeps
二、安装
- 准备好安装包,解压后执行下列命令:
rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm
mysql-community-client-5.7.21-1.el7.x86_64.rpm
mysql-community-common-5.7.21-1.el7.x86_64.rpm
mysql-community-libs-5.7.21-1.el7.x86_64.rpm
2. 启动MYSQL
systemctl start mysqld.service
3. 默认密码
新安装的默认密码在 mysqld.log里,搜索password 找到
修改密码
登录mysql,修改默认密码
SET PASSWORD = PASSWORD('ASqw12*1s');
Flush privileges;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '6Fcv%98AS ' WITH GRANT OPTION;
flush privileges
GRANT ALL PRIVILEGES ON *.* TO 'sxyall'@'%' IDENTIFIED BY '6Fcv%98AS ' WITH GRANT OPTION;
flush privileges
4. 验证
账号是否可用,native 远程连接,输入账号密码,连接看权限是否正确。
三、配置主从数据库
1. mysql util 安装
tar xzf mysql-utilities-1.5.3.tar.gz
cd mysql-utilities-1.5.3
python setup.py build
python setup.py install
2. my.cnf 配置
Master mysql 的配置:
[mysql]
protocol = tcp
default-character-set=utf8mb4
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8mb4
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
binlog-format=MIXED
log-slave-updates=true
#gtid-mode=on
enforce-gtid-consistency =true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
server_id=1
report-host=server1
port=3507
lower_case_table_names=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
relay_log_recovery=ON
slave_preserve_commit_order=1
#slow_query_log=ON
#slow_query_log_file = /var/lib/mysql/localhost-slow.log
#long_query_time=0.1
#log_queries_not_using_indexes=ON
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
group_concat_max_len = 102400
default_storage_engine=InnoDB
innodb_buffer_pool_size=10737418240 #10G
innodb_log_file_size=209715200 #200M
tmp_table_size=33554432
max_heap_table_size=33554432 #32M
max_connections=4000
thread_cache_size=2000
主要是设置了binlog和启用gtid-mode,并且需要设置不同的server-id和report-host
3. 创建账号 两台数据库都要创建
从库:
Grant all privileges *.* to ‘rpl’@’ip1’ identified by ‘密码’;
主库:
Grant all privileges *.* to ‘rpl’@’ip2’ identified by ‘密码’;
4. 设置主从同步
主数据库服务器上执行下列操作:
mysqlreplicate --master=root:password@server1:3507 --slave=root:password@server2:3507 --rpl-user=rpl:password
mysqlrplshow --master=root:password@server1:3507 --discover-slaves-login=root:password
mysqlrplcheck --master=root:password@server1:3507 --slave=root:password@server2:3507
5. 主从切换脚本
在从服务器上建立主从切换脚本
/data/sh/mysqlfailover.sh
#! /bin/bash
mysqlrpladmin --slave=root:password@server2:3307 failover
四、keepalived安装配置
tar –zvxf keepalived-1.3.5.tar.gz
cd keepalived-1.3.5
./configure --prefix=/opt/keepalived
make && make install
mkdir /etc/keepalived
cp /opt/keepalived/etc/keepalived.conf /etc/keepalived/keepalived.conf
cp /opt/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/keepalived
cp /opt/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
#设置keepalived服务开机启动:
chkconfig keepalived on
主:
! Configuration File for keepalived
vrrp_sync_group VG_1 {
group {
inside_network
}
}
vrrp_instance inside_network {
state BACKUP
interface ens192
virtual_router_id 41
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 3499
}
virtual_ipaddress {
192.168.230.90/24
}
nopreempt
}
virtual_server 192.168.230.90 3507 {
delay_loop 2
#lb_algo wrr
#lb_kind DR
persistence_timeout 60
protocol TCP
real_server ip1 3507 {
weight 3
notify_down /root/mysqld_down.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3507
}
}
}
从:
! Configuration File for keepalived
vrrp_sync_group VG_1 {
group {
inside_network
}
}
vrrp_instance inside_network {
state BACKUP
interface ens192
virtual_router_id 41
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 3499
}
virtual_ipaddress {
192.168.230.90/24
}
notify_master /data/sh/mysqlfailover.sh
}
virtual_server 192.168.230.90 3507 {
delay_loop 2
#lb_algo wrr
#lb_kind DR
persistence_timeout 60
protocol TCP
real_server ip2 3507 {
weight 3
notify_down /root/mysqld_down.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3507
}
}
}
杀掉keepalived 脚本
/root/mysqld_down.sh
#! /bin/sh
pkill keepalived
此keepalived配置需要注意的是:
两台server的state都设置为backup,server1增加nopreempt配置,并且server1 priority比server2高,这样用来实现当server1从宕机恢复时,不抢占VIP;
server2设置notify_master /data/sh/mysqlfailover.sh,意味着server2接管server1后,执行这个脚本,以把server2的mysql提升为主。
五、验证测试
1. 主宕机测试
模拟server1宕机。在server1上执行shutdown关机命令。此时我们登录server2,执行ip addr命令。 Server1的ip是 192.168.230.90
是否自动切换了主从,登录server2的mysql服务器,执行show status;命令,结果:
mysql> show slave status \G;
Empty set (0.00 sec)
证明从库状态已经为空,证明已经切换为主了
测试server1是否抢占VIP ,先来启动server1,之后执行ip addr 如果不是192.168.230.90
server1并没有抢占VIP,测试正常