同一台linux安装2个mysql数据库,Linux实战之MySQL数据库——keepalived实现Mysql双主高可用...

Mysql双主高可用介绍

架构介绍

Mysql的双主高可用架构，主要设计思想是两台Mysql服务器通过Mysql复制技术互相将对方作为自己的主服务器，自己又同时作为对方的从服务器来进行复制，实现高可用架构中的数据同步功能，同时采用keepalived来实现mysql的自动故障切换

在这个架构中，两台Mysq|服务器互为主从，同一时刻只有一个Mysql服务器可读写，另一个Mysql服务器只能进行读操作，保证数据的一致性

Keepalived的作用

(1)Keepalived维护一个VIP，此IP用来对外部提供链接服务

(2)keepalived负责监控DB1和DB2上Mysql数据库的运行状态，DB1故障时自动将VIP地址和Mysql服务切换到DB2上

总结： 通过Keepalived保持数据库服务的连续性，整个切换过程非常快，并且对前端Web服务器主机透明

主主互备模式不足

从库可能会一直处于空闲状态 可以用它当从库，负责部分查询

从库要先与主库同步数据才能提供服务，造成一定程度的同步延时

Mysql双主高可用实现

建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP(非抢占模式)，尽可能减少主库宕机对业务造成的影响，减少主从同步给线上主库带来的压力

主主互备模式配置

初始化环境

同步时间

关闭防火墙

环境规划

主机

IP

Server_ID

server1：mysql

192.168.213.124

1

server2：mysql-1

192.168.213.127

2

配置server2

[root@mysql-1 ~]# vim /etc/my.cnf

[mysqld]

server_id=2

#master必备

log-bin=mysql-bin

log-bin-index=master-bin.index

#slave必备

relay-log=relay-log

relay-log-index=relay-log.index

重启服务

[root@localhost ~]# systemctl restart mysqld

在server1和server2上分别创建一个用于让从数据库连接的用户

mysql> create user 'copy'@'%' identified with mysql_native_password by 'Cloudbu@123';

mysql> grant replication slave on *.* to 'copy'@'%';

mysql> flush privileges; 刷新授权表信息

在server1上获取当前主节点当前binary log文件名和位置

[root@mysql ~]# mysql -uroot -pZhao123@com

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 155 | | | |

+------------------+----------+--------------+------------------+-------------------+

设置主节点参数

[root@mysql-1 ~]# mysql -uroot -pZhao123@com

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.213.124',

MASTER_USER='copy',

MASTER_PASSWORD='Cloudbu@123',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=155;

查看主从同步状态

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#其中的IO线程和SQL线程均为YES，则配置成功

配置server1

修改配置文件，必须指定中继日志的名称

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

server_id=1

#master必备

log-bin=mysql-bin

log-bin-index=master-bin.index

#slave必备

relay-log=relay-log

relay-log-index=relay-log.index

重启服务

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# systemctl start mysqld

在server1上获取当前主节点当前binary log文件名和位置

[root@mysql-1 ~]# mysql -uroot -pZhao123@com

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 825 | | | |

+------------------+----------+--------------+------------------+-------------------+

设置主节点参数

[root@mysql-1 ~]# mysql -uroot -pZhao123@com

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.213.127',

MASTER_USER='copy',

MASTER_PASSWORD='Cloudbu@123',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=825;

查看主从同步状态

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#其中的IO线程和SQL线程均为YES，则配置成功

keepalived配置

安装keepalived

两台服务器上都需要安装

(1)安装软件

[root@mysql ~]# wget https://www.keepalived.org/software/keepalived-2.0.1.tar.gz

[root@mysql ~]# mkdir -p /data/keepalived

[root@mysql ~]# tar -xf keepalived-2.0.1.tar.gz -C /usr/local/src

[root@mysql ~]# yum install openssl-devel gcc gcc-c++ make -y

[root@mysql ~]# cd /usr/local/src/keepalived-2.0.1

[root@mysql keepalived-2.0.1]# ./configure --prefix=/data/keepalived/

[root@mysql keepalived-2.0.1]# echo $?

0

[root@mysql keepalived-2.0.1]# make

[root@mysql keepalived-2.0.1]# echo $?

0

[root@mysql keepalived-2.0.1]# make install

[root@mysql keepalived-2.0.1]# echo $?

0

(2)配置规范启动

[root@bogon etc]# pwd

/usr/local/src/keepalived-2.0.1/keepalived/etc

[root@mysql etc]# pwd

/usr/local/src/keepalived-2.0.1/keepalived/etc

[root@mysql etc]# cp -R init /data/keepalived/

[root@mysql etc]# cp -R init /data/keepalived/etc/

[root@mysql etc]# cp -R init.d /data/keepalived/etc/

[root@mysql etc]# ll /data/keepalived/etc/

total 0

drwxr-xr-x 2 root root 86 Mar 3 20:25 init

drwxr-xr-x 2 root root 135 Mar 3 20:25 init.d

drwxr-xr-x 3 root root 44 Mar 3 20:23 keepalived

drwxr-xr-x 2 root root 24 Mar 3 20:23 sysconfig

[root@mysql etc]# cp /data/keepalived/etc/init.d/keepalived /etc/init.d/

[root@mysql etc]# cp /data/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

[root@mysql etc]# mkdir /etc/keepalived

[root@mysql etc]# cp /data/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

[root@mysql etc]# cp /data/keepalived/sbin/keepalived /usr/sbin/

(3)启动

[root@mysql ~]# /etc/init.d/keepalived start

Starting keepalived (via systemctl): [ OK ]

[root@mysql ~]# ps -ef |grep keepalived

root 7935 1 9 20:28 ? 00:00:01 /data/keepalived/sbin/keepalived -D

root 7936 7935 0 20:28 ? 00:00:00 /data/keepalived/sbin/keepalived -D

root 20466 3788 0 20:28 pts/1 00:00:00 grep --color=auto keepalived

root 20490 7935 0 20:28 ? 00:00:00 [keepalived]

keepalived配置

keepalived中的两种模式

(1)master->backup模式

一旦主库宕机，虚拟ip会自动漂移到从库，当主库修复后，keepalived启动后，还会把虚拟ip抢占过来，即使设置了非抢占模式(nopreempt)抢占ip的动作也会发生。

(2)backup->backup模式

当主库宕机后虚拟ip会自动漂移到从库上，当原主库恢复和keepalived服务启动后，并不会抢占新主的虚拟ip，即使是优先级高于从库的优先级别，也不会发生抢占。为了减少ip漂移次数，通常是把修复好的主库当做新的备库。

在数据库的应用场景中，不建议数据库的频繁切换，因此，将其配置为keepalived的VIP不抢占模式，具体配置如下：

在mysql上

[root@mysql ~]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id HA_MYSQL01

}

vrrp_script check_mysqld {

script "/etc/keepalived/check_mysqld.sh"

interval 2

}

vrrp_instance HA_1 {

state BACKUP

interface ens33

virtual_router_id 51

priority 150

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.213.233

}

track_script {

check_mysqld

}

}

state BACKUP #在ser01和ser02上均配置为backup

nopreempt #不抢占模式，只在优先级高的机器上设置即可，优先级低的机器上不设置

在mysql-1上

[root@mysql-1 ~]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id HA_MYSQL01

}

vrrp_script check_mysqld {

script "/etc/keepalived/check_mysqld.sh"

interval 2

}

vrrp_instance HA_1 {

state BACKUP

interface ens33

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.213.233

}

track_script {

check_mysqld

}

}

监控脚本

[root@mysql ~]# cat /etc/keepalived/check_mysqld.sh

#!/bin/bash

user=root

pass=Zhao123@com

/usr/bin/mysql -u$user -p$pass -e "show status;" > /dev/null 2>&1

if [ $? -eq 0 ];

then

MYSQL_STATUS=0

else

MYSQL_STATUS=1

fi

exit $MYSQL_STATUS

[root@mysql ~]# chmod +x /etc/keepalived/check_mysqld.sh

Mysql配置账号测试

(1)授权账号

mysql> create user 'keepalived'@'%' identified by 'Cloudbu@123';

mysql> ALTER USER 'keepalived'@'%' IDENTIFIED WITH mysql_native_password BY

'Cloudbu@123';

mysql> flush privileges;

(2)结果测试

在测试节点上用192.168.213.233，即浮动ip测试

[root@configure ~]# mysql -h192.168.213.233 -ukeepalived -pCloudbu@123

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

关闭优先级别较高的mysql，VIP会漂移到mysql-1上，客户端连接会显示如下信息，但可重新登录上

[root@configure ~]# mysql -h192.168.213.233 -ukeepalived -pCloudbu@123

mysql> show databases;

ERROR 2013 (HY000): Lost connection to MySQL server during query

[root@configure ~]# mysql -h192.168.213.233 -ukeepalived -pCloudbu@123

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

当mysql恢复后，能够实现数据同步，vip并没有漂移回mysql上，而是保持在mysql-1上；当mysql-1故障后，VIP自然漂移到了mysql上