mysql集群双活+高可用

84 阅读 0 评论 56 点赞
我是靠谱客的博主勤劳裙子，最近开发中收集的这篇文章主要介绍mysql集群双活+高可用，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述


mysql双主部署+keepalived
  安装mysql（此处使用二进制，还可以用 rpm包安装 或 mysql编译安装）
https://blog.csdn.net/u012562411/article/details/85220783
 
0.卸载原来版本mysql或mariadb
rpm -e mariadb-libs  --nodeps
1.进入/usr/local，下载tar包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
2.解压并重命名
tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
mv /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
3.在/usr/local/mysql新建data目录
mkdir data
4.新建用户组
groupadd mysql
5.为用户组添加新用户
useradd mysql -g mysql
6.将mysql文件夹下所有文件/文件夹的所有者及所属组改为mysql
chown -R mysql.mysql /usr/local/mysql
7.配置
/usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data 
 
8.继续安装
/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize
 
9.编辑/etc/my.cnf文件，将一下代码添加到[mysqld]下。
vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
#取消密码验证
skip-grant-tables
10.将mysql加入服务，并设置开机自启
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chkconfig mysql on
11.开启服务
service mysql start
12.登录mysql（由于/etc/my.cnf中设置了取消密码验证，所以此处密码任意，或者直接回车）
/usr/local/mysql/bin/mysql -u root -p
13.使用mysql数据库
>>use mysql;
14.修改密码
>>update user set authentication_string=password('Cxyjy!Z1zqsc') where user='root';
>>flush privileges;
>>exit;
15.将/etc/my.cnf中的skip-grant-tables删除或注释掉
vim /etc/my.cnf
16.登录再次设置密码（可以和刚刚的相同）
/usr/local/mysql/bin/mysql -u root -p
>>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
>>exit;
17.设置允许远程连接，设置了这条，就可以在自己的window电脑上连接并操作数据库了
/usr/local/mysql/bin/mysql -u root -p
>>use mysql;
>>update user set host='%' where user = 'root';
>>flush privileges;
>>eixt;
18.开放端口
firewall-cmd --zone=public --add-port=3600/tcp --permanent
firewall-cmd --reload 
 
  双主配置
https://blog.csdn.net/yuefei169/article/details/81297668
https://www.cnblogs.com/kevingrace/p/6710136.html
1.主机添加从机账户
-- masterA配置masterB信息
grant replication slave on *.* to 'replicate'@'172.23.7.117' identified by '123456';
flush privileges;
-- masterB配置masterA信息
grant replication slave on *.* to 'replicate'@'172.23.7.116' identified by '123456';
flush privileges;
2.修改配置文件
-- masterA
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
# 主从配置
server-id=1         
log-bin=mysql-bin     
# 跳过复制错误    
slave-skip-errors=all  
# 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
# 自增
auto-increment-increment=1     
auto-increment-offset=2
# 忽略的数据库
binlog-ignore-db=mysql  
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
 
-- masterB
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
# 主从配置
server-id=2         
log-bin=mysql-bin 
# 跳过复制错误   
slave-skip-errors=all 
# 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 
log-slave-updates=true 
# 自增
auto-increment-increment=2     
auto-increment-offset=2    
# 忽略的数据库
binlog-ignore-db=mysql  
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
 
3.查看主从状态
-- masterA
> show master status;
-- masterB
> show master status;
-- 查询结果
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
4.配置同步信息
-- masterA配置masterB信息
> stop slave;
> change master to master_host='172.23.7.117',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=150;
> start slave;
> show slave statusG;
显示有如下状态则正常：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- masterB配置masterA信息
> stop slave;
> change master to master_host='172.23.7.116',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=437;
> start slave;
> show slave statusG;
显示有如下状态则正常：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
 
  配置Mysql+Keepalived
https://www.cnblogs.com/kevingrace/p/6710136.html
https://www.cnblogs.com/Steward-Xu/p/7275273.html
1.安装keepalived并将其配置成系统服务。master1和master2两台机器上同样进行如下操作
[root@master1 ~]# yum install -y openssl-devel
[root@master1 ~]# yum install gcc 
[root@master1 ~]# cd /usr/local/src/
[root@master1 src]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[root@master1 src]# tar -zvxf keepalived-1.3.5.tar.gz
[root@master1 src]# cd keepalived-1.3.5
[root@master1 keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived
[root@master1 keepalived-1.3.5]# make && make install
 
[root@master1 keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@master1 keepalived-1.3.5]# mkdir /etc/keepalived/
[root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@master1 keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local
2-1.master1机器上的keepalived.conf配置。（下面配置中没有使用lvs的负载均衡功能，所以不需要配置虚拟服务器virtual server）
[root@master1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@master1 ~]# vim /etc/keepalived/keepalived.conf       ＃清空默认内容，直接采用下面配置：
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
       
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}     
vrrp_script chk_mysql_port {     #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/opt/chk_mysql.sh"   #这里通过脚本监测
    interval 2                   #脚本执行间隔，每2s检测一次
    weight -5                    #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
    fall 2                       #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    rise 1                       #检测1次成功就算成功。但不修改优先级
}  
vrrp_instance VI_1 {
    state BACKUP
    interface ens33              #指定虚拟ip的网卡接口 ip addr查询
    virtual_router_id 51         #路由器标识，MASTER和BACKUP必须是一致的
    priority 101                 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 
    advert_int 1   
nopreempt                    #非抢占模式      
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        172.23.7.115
    }
      
track_script {               
   chk_mysql_port             
}
}
2-2.编写切换脚本。KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管
[root@master1 ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi
 
[root@master1 ~]# chmod 755 /opt/chk_mysql.sh
     
2-3.启动keepalived服务
[root@master1 ~]# vi /lib/systemd/system/keepalived.service
PIDFile=/var/run/keepalived.pid  
[root@master1 ~]# systemctl daemon-reload
[root@master1 ~]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]
 
3-1.
master1机器上的keepalived.conf配置。（下面配置中没有使用lvs的负载均衡功能，所以不需要配置虚拟服务器virtual server）
[root@master1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@master1 ~]# vim /etc/keepalived/keepalived.conf       ＃清空默认内容，直接采用下面配置：
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
       
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}     
vrrp_script chk_mysql_port {     #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/opt/chk_mysql.sh"   #这里通过脚本监测
    interval 2                   #脚本执行间隔，每2s检测一次
    weight -5                    #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
    fall 2                       #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    rise 1                       #检测1次成功就算成功。但不修改优先级
}  
vrrp_instance VI_1 {
    state BACKUP
    interface ens33              #指定虚拟ip的网卡接口 ip addr查询
    virtual_router_id 51         #路由器标识，MASTER和BACKUP必须是一致的
    priority 99                  #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 
    advert_int 1
nopreempt         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        172.23.7.115
    }
      
track_script {               
   chk_mysql_port             
}
}
3-2.编写切换脚本。KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管
[root@master1 ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi
 
[root@master1 ~]# chmod 755 /opt/chk_mysql.sh
     
3-3.启动keepalived服务
[root@master1 ~]# vi /lib/systemd/system/keepalived.service
PIDFile=/var/run/keepalived.pid  
[root@master1 ~]# systemctl daemon-reload
[root@master1 ~]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]
4.检测keepalive是否起作用



mysql双主部署-配置文件

  执行sql
-- 117
show master status;
stop slave;
change master to master_host='172.23.7.116',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=851;
start slave;
show slave statusG;
-- 116
show master status;
stop slave;
change master to master_host='172.23.7.117',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=851;
start slave;
show slave statusG;
 
 
  配置文件
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
 
# 主从配置
server-id=1         
log-bin=mysql-bin     
# 跳过复制错误    
slave-skip-errors=all  
# 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
# 自增
auto-increment-increment=1     
auto-increment-offset=2
# 忽略的数据库
binlog-ignore-db=mysql  
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
 
 
 
 
 
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
 
# 主从配置
server-id = 2         
log-bin = mysql-bin 
# 跳过复制错误   
slave-skip-errors = all 
# 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 
log-slave-updates = true 
# 自增
auto-increment-increment = 2     
auto-increment-offset = 2    
# 忽略的数据库
binlog-ignore-db = mysql  
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
 
 
  keepalive配置文件
global_defs {
 notification_email {
   acassen@firewall.loc
   failover@firewall.loc
   sysadmin@firewall.loc
 }      
 notification_email_from ops@wangshibo.cn
 smtp_server 127.0.0.1 
 smtp_connect_timeout 30
 router_id MASTER-HA
}     
vrrp_script chk_mysql_port {     #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/opt/chk_mysql.sh"   #这里通过脚本监测
    interval 2                   #脚本执行间隔，每2s检测一次
    weight -5                    #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
    fall 2                       #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    rise 1                       #检测1次成功就算成功。但不修改优先级
}  
vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface ens32              #指定虚拟ip的网卡接口 ip addr查询
    virtual_router_id 151         #路由器标识，MASTER和BACKUP必须是一致的
    priority 90                #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 
    advert_int 2   
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        10.9.13.61
    }
      
track_script {               
   chk_mysql_port             
} 
}

 
 
  keepalive检测mysql脚本

#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=10.9.13.62
MYSQL_USER=root
MYSQL_PASSWORD=1qaz@WSX3edc
CHECK_TIME=3

#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -P33060  -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ] ;then
    MYSQL_OK=1
    else
    MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
   pkill keepalived
    exit 1
fi
done
l