利用keepalived部署mysql双主高可用
1.服务器配置mysql1 192.168.47.131
mysql2 192.168.47.132
vip 192.168.47.130
2.初始化系统
2.1 操作系统 centos7
2.2 关闭selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce 0
2.3 卸载防火墙firewalld
yum -y remove firewalld
2.4 安装基础工具包
yum install -y net-tools vim iptables-services
3.安装mariadb
yum install -y mariadb mariadb-server
mysql_secure_installation #初始化mariadb数据库
systemctl start mariadb #启动mariadb数据库
systemctl enable mariadb #开机启动mariadb数据库
4.配置两台数据库互为主从
4.1 mysql1下数据库配置
修改mysql配置文件/etc/my.conf,在mysqld下增加以下内容
server-id =131
log-bin = mysql-bin
auto-increment-offset = 1
auto-increment-increment = 2
log-slave-updates = true
systemctl restart mariadb#修改完配置文件后重启mariadb
mysql -uroot -p #登陆数据库
show master status;#查看binlog日志文件和pos点
**mysql-bin.000001 | 399**
grant replication slave on *.* to repl@'192.168.47.%' identified by 'repl'; #授权同步用户
CHANGE MASTER TO
MASTER_USER='repl',
MASTER_HOST='192.168.47.132',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=399; #设置主服务器为mysql2
start slave;#启动主从
show slave status\G
**Slave_IO_Running: Yes**
**Slave_SQL_Running: Yes**
4.2 mysql2下数据库配
修改mysql配置文件/etc/my.conf,在mysqld下增加以下内容
server-id =132
log-bin = mysql-bin
auto-increment-offset = 2
auto-increment-increment = 2
log-slave-updates = true
systemctl restart mariadb#修改完配置文件后重启mariadb
mysql -uroot -p #登陆数据库
show master status;#查看binlog日志文件和pos点
**mysql-bin.000001 | 399**
grant replication slave on *.* to repl@'192.168.47.%' identified by 'repl'; #授权同步用户
CHANGE MASTER TO
MASTER_USER='repl',
MASTER_HOST='192.168.47.131',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=399; #设置主服务器为mysql1
start slave;#启动主从
show slave status\G
**Slave_IO_Running: Yes**
**Slave_SQL_Running: Yes**
5.安装keepalived
yum -y install keepalived
systemctl enable keepalived#开机启动keepalived
6.配置keepalive高可用
6.1 配置mysql为MASTER节点
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
} #配置文件默认部分，不用修改
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
weight2
}
vrrp_instance MYSQL {
state MASTER
interface ens33
virtual_router_id 130
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 666666
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.47.130
}
}
6.2 配置mysql为BACKUP节点
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
} #配置文件默认部分，不用修改
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
weight2
}
vrrp_instance MYSQL {
state BACKUP #与mysql1不同
interface ens33
virtual_router_id 130
priority 90 #与mysql1不同
advert_int 1
authentication {
auth_type PASS
auth_pass 666666
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.47.130
}
}
6.3 启动keepalived
systemctl start keepalived
7.修改防火墙配置文件
vim /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -s 192.168.47.0/24 -p vrrp -j ACCEPT
systemctl restart iptables #重启防火墙
systemctl enable iptables #开机启动防火墙
8.查看vip是否生效
mysql1：
ip addr
inet 192.168.47.131/24 brd 192.168.47.255 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.47.130/32 scope global ens33
valid_lft forever preferred_lft forever
mysql2：
ip addr
inet 192.168.47.132/24 brd 192.168.47.255 scope global ens33
valid_lft forever preferred_lft forever
9.mysql_check脚本
vim /etc/keepalived/check_mysql.sh
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=mysql
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
if [ $? == 0 ]
then
echo " $host mysql login successfully "
exit 0
else
#echo " $host mysql login faild"
systemctl stop keepalived
exit 2
fi
chmod +x check_mysql.sh#给脚本添加可执行权限
10.高可用测试
在mysql1上关闭mysql
systemctl stop mariadb
查看vip是否漂移到mysql2
在mysql1上重新启动mysql，并启动keepalived
systemctl start mariadb
systemctl start keepalived
查看vip是否回到mysql1
不是很明白楼主为啥把firewalld卸载了，改为iptables。难道他两功能不一样嘛。楼主写的很用心，我会按照楼主的步骤做一遍 180311 18:31:34 Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; thesesame-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code:
180311 18:31:34 Slave I/O thread exiting, read up to log 'FIRST', position 4
# cat /etc/my.cnf
datadir=/var/lib/mysql
server-id=131
log-bin=mysql-bin
auto-increment-offset=1
auto-increment-increment=2
log-slave-updates=true
socket=/var/lib/mysql/mysql.sock
# 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
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
datadir=/var/lib/mysql
server-id=132
log-bin=mysql-bin
auto-increment-offset=2
auto-increment-increment=2
log-slave-updates=true
socket=/var/lib/mysql/mysql.sock
# 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
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
mayiwen123456 发表于 2018-3-11 09:06
不是很明白楼主为啥把firewalld卸载了，改为iptables。难道他两功能不一样嘛。楼主写的很用心，我会按照楼 ...
很多人还不习惯用firewalld，所以就用iptables了。另外，我试过卸载firewalld，不安装iptables，启动keepalived的时候还是默认会启动防火墙规则，导致vip无法ping通。
mayiwen123456 发表于 2018-3-11 11:48
180311 18:31:34 Slave I/O: Fatal error: The slave I/O thread stops because master and slave...
你是不是修改完配置文件没有重启mysql，报错说的是主从有一样的server id
10.高可用测试
在mysql1上关闭mysql
systemctl stop mariadb
查看vip是否漂移到mysql2
怎么测试 vip是否漂移到mysql2 ??? 10.高可用测试
在mysql1上关闭mysql
systemctl stop mariadb
怎么测试 查看vip是否漂移到mysql2 ？
页:
[1]