mysql

MySQL主主从+maxscale

admin · 7月30日 · 2020年

1.准备

  • A服务器：172.16.1.131 masterA
  • B服务器：172.16.1.132 masterB
  • C服务器：172.16.1.133 slave
  • D服务器：172.16.1.134 maxscale

AB互为主从，C为B的slave,AB有一个VIP172.16.1.130,D上部署maxscale

2.主服务器A

#配置/etc/my.cnf
[mysqld]
log-bin=mysql-bin 
#使Ａ既能为主服务器，也能为从服务器
log-slave-updates
relay-log=mysql-relay
server-id=181
#奇数ID
auto_increment_offset=1
# 自增长为2
auto_increment_increment=2

cat >> /etc/my.cnf <<EOF
log-bin=mysql-bin
log-slave-updates
relay-log=mysql-relay
auto_increment_offset=1
auto_increment_increment=2
server-id=131
EOF

systemctl restart mysqld.service

3.主服务器B

[mysqld]
log-bin=mysql-bin 
#使Ｂ既能为主服务器，也能为从服务器
log-slave-updates＝１
relay-log=mysql-relay
server-id=182
#偶数ID
auto_increment_offset=2
# 自增长为2
auto_increment_increment=2

cat >> /etc/my.cnf <<EOF
log-bin=mysql-bin
log-slave-updates=1
relay-log=mysql-relay
auto_increment_offset=2
auto_increment_increment=2
server-id=132
EOF

systemctl restart mysqld.service

4.在AB主机上分辨建立账户并授权

grant replication slave,replication client on *.* to slave@'%' identified by '12345678';
mysql>flush privileges;

#锁表
mysql>flush tables with read lock;

#同步完成后，解除锁表
mysql>unlock tables;

#查询master的状态
mysql>show master status\G

masterA
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1603                
Binlog_Do_DB: 
Binlog_Ignore_DB: 
记录上面结果中File和Position的值

masterB
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1603                
Binlog_Do_DB: 
Binlog_Ignore_DB: 
记录上面结果中File和Position的值

5.在AB主机上分别配置

mysql> change master to master_host = '',              #master的ip
    ->master_user = '',                                #在master中创建的slave账户
    ->master_password = '',                            #在master中创建的slave密码
    ->master_log_file = 'mysql-bin.000001',                    #填写master中的数据
    ->master_log_pos = ;                                   #填写master中的数据

#开启slave模式
mysql>start slave;
#查看主从复制是否配置成功
mysql>show slave status\G

*********************以下为正常状态*************************
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

6.C配置slave

具体参考主从配置

7.在AB服务器上安装keepalived

wget https://download.luckinserver.cn:90/myfiles/centos7/keepalived_install.sh

8.maxscale的部署

wget https://download.luckinserver.cn:90/myfiles/centos7/maxscale_install.sh

9.masterA上部署脚本

#由于masterA和masterB互为主从，maxscale无法识别谁为master
#经实际操作后发现先停掉masterA,此时masterB成为maxscale路由表中的master
#再将masterA启动，此时masterA成为maxscale路由表中的master
#具体原因未知，学习中，以后回来填坑
#先AD做密钥登陆,然后在masterA上授权一个mysqlha账户，然后使用crontab运行以下脚本
#maxscale配置文件中server1要和A对应
#--------------------------------------------------------------------------------------------
#create database mysqlha;
#grant replication client on mysqlha.* to mysqlha@'localhost' identified by '12345678'; 
#--------------------------------------------------------------------------------------------
#!/bin/bash

KEYWORD=`ssh 172.16.1.134 "maxadmin list servers" | grep server1 | grep -o Master`
CHECK_KEEPALIVED=`ps -ef | grep -v grep | grep -o "/usr/sbin/keepalived" | wc -l`
CHECK_MYSQL=`mysql -u mysqlha -p12345678 -e 'select @@hostname' | sed -n '2p'`
TIME=`date +"%Y-%m-%d %T"`

mkdir -p /var/log/MySQL-HA
LOG=/var/log/MySQL-HA/MySQL-HA.log
LOG_NUM=`cat $LOG | wc -l `

if [[ "$KEYWORD" != Master ]] || [[ "$CHECK_MYSQL" != $HOSTNAME ]];then

  systemctl stop keepalived.service

else

    if [[ "$CHECK_KEEPALIVED" == 0 ]] && [[ "$CHECK_MYSQL" == $HOSTNAME ]];then
        systemctl restart keepalived.service
        echo "MySQL-HA is down,try to fix it. $TIME" >> $LOG
    else
        echo "MySQL-HA is up $TIME" >> $LOG
    fi

fi

if [[ "$LOG_NUM" -gt "300" ]];then

    DEL_NUM=$(($LOG_NUM-300))
    sed -i "1,${DEL_NUM}d" $LOG >/dev/null 2>&1
fi
@2020版权所有