Power!
标签 文章
Power!

Mysql 双主部署

Chris Su  included in  categories Linux Mysql Keepalived
     1080 words  6 minutes 

记录一下如何搭建Mysql 双主集群，这是老的笔记中整理出来的部分内容没有再次验证。

操作系统： CentOS7.x

VM数量2台

关闭selinux和防火墙

systemctl disable firewalld 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

设置主机名：

# 节点1
hostnamectl set-hostname mysql-master1
# 节点2
hostnamectl set-hostname mysql-master2

下载mysql：

yum install wget -y
wget -c https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

安装依赖：

yum install libaio numactl -y

用户和组：

groupadd mysql
useradd -m -r -g mysql mysql

设置系统ulimit

vi  /etc/security/limits.conf

内容如下：

mysql     soft    nproc    65536
mysql     hard    nproc    65536
mysql     soft    nofile   65536
mysql     hard    nofile   65536

打开 /etc/security/limits.d/90-nproc.conf 把参数调整为

*          soft    nproc     65536
root       soft    nproc     unlimited
*          soft    nofile    65536
root       soft    nofile    unlimited

添加hosts：

host_ip=$(ifconfig | grep inet | grep cast | awk '{print $2}' | awk -F: '{print $NF}' | head -1)
echo "$host_ip `hostname`" >> /etc/hosts

时间同步

yum install chrony -y
systemctl enable chronyd --now

安装mysql：

tar -xvzf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql57

创建软链接：

ln -s  /usr/local/mysql57/bin/mysql /usr/bin/mysql
ln -s  /usr/local/mysql57/bin/mysqlbinlog /usr/bin/mysqlbinlog

创建mysql的日志和数据目录：

mkdir -p /data/mysql_db/mysql_test
mkdir -p /data/mysql_log/mysql_test

初始化：

/usr/local/mysql57/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql57 --datadir=/data/mysql_db/mysql_test --user=mysql
/usr/local/mysql57/bin/mysql_ssl_rsa_setup --basedir=/usr/local/mysql57 --datadir=/data/mysql_db/mysql_test

创建对应的启动脚本：

cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld-test

修改/etc/init.d/mysqld-test内容如下：

basedir=/usr/local/mysql57
datadir=/data/mysql_db/mysql_test
default_file=/data/mysql_db/mysql_test/my.cnf
lock_file_path="$lockdir/mysql-test"
$bindir/mysqld_safe  --defaults-file="$default_file" --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

修改权限：

chown mysql.mysql /etc/init.d/mysqld-test 
chown mysql.mysql -R /data/mysql*

启动mysql：


su - mysql
[mysql@mysql-master1 ~]$ /etc/init.d/mysqld-test start
Starting MySQL.. SUCCESS!

测试链接：

mysql -S /data/mysql_db/mysql_test/mysql.sock

修改mysql配置：

vim /data/mysql_db/mysql_test/my.cnf

mysql-master1

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql/mysql.sock

[mysqld]
datadir = /data/mysql
# basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

auto_increment_offset = 1
auto_increment_increment = 2

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
log_slave_updates = 1
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

mysql-master2

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

auto_increment_offset = 2
auto_increment_increment = 2

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
log_slave_updates = 1
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

slave

[client]
port = 3307
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3307/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3307
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3307/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3307/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3307
server_id = 833307
read_only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3307/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3307/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3307/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3307/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3307/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

安装：

yum install -y keepalived

编辑/etc/keepalived/keepalived.conf文件, 按照实际情况加上下面的配置

global_defs {
   router_id MYSQL_MM  # 标识
   vrrp_skip_check_adv_addr
   vrrp_strict        # 严格执行 VRRP 协议规范
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script check_mysql {
    script "/bin/bash /etc/keepalived/keepalived_mysql_check.sh"  # 检查脚本
    interval 10  # 检查周期
}

vrrp_instance MYSQL_MM {
    state BACKUP            # 都设为 BACKUP，避免起来后抢占
    interface eth0          # 网卡名称，根据实际情况填写
    virtual_router_id 243   # 用来区分 VRRP 组播的标记，取值 0-255
    priority 100
    advert_int 1
    nopreempt               # 设为非抢占
    authentication {
        auth_type PASS
        auth_pass 1111
    }

    # Master 节点可以注释掉下面语句，防止启动 keepalived 的时候执行脚本
    notify_master "/bin/bash /etc/keepalived/keepalived_mysql_start.sh"  # 变为 MASTER 时执行

    virtual_ipaddress {
        10.0.0.237
    }

    # Slave 节点可以注释下面检查脚本，Slave 没有必要一直检查
    track_script {
        check_mysql
    }
}

创建并编辑/etc/keepalived/keepalived_mysql_check.sh文件， 写入检测脚本

#!/bin/sh
# MySQL 检测脚本
MyPath=$(cd $(dirname $0); pwd)
cd $MyPath

ThisTime=`date '+%F %T'`
log_file='/var/log/keepalived_mysql.log'

# MySQL 连接方式，根据实际情况调整
export MYSQL_PWD='monitor'
MYSQL_USER='monitor'
MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock"
mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} "

# 美化输出
function techo() {
    message=$1
    message_level=$2
    if [ -e $message_level ];then
        message_level='info'
    fi
    echo "`date '+%F %T'` - [${message_level}] $message" >> $log_file
}

# 检查函数, 正常返回 0
function check {
    ret=`$mysql_connect -N -e 'select 1 as value'`
    if [ $? -ne 0 ] || [ $ret -ne '1' ];then
        return 1
    else
        return 0
    fi
}

function read_only {
    param=$1
    $mysql_connect -e "set global read_only = ${param}"
    techo "设置是否只读 read_only ${param}"
}

# 失效转移
function failover {
    techo "开始执行失效转移"
    # 1. 停止 keepalived
    killall keepalived

    # 2. 如果还能执行的话，设为 read_only
    read_only 1

    if [ $? -eq 0 ];then
        # 3. 如果还能执行，kill 所有的连接
        $mysql_connect -e "select concat('KILL ',id,';') from information_schema.processlist where user!='root' AND db is not null into outfile '/tmp/kill.txt.${ThisTime}';"
        if [ $? -eq 0 ];then
            $mysql_connect -e "source /tmp/kill.txt.${ThisTime};"
        fi
    fi

    # 4. 其他操作，比如说自动关机

    techo "失效转移执行成功，当前数据库关闭访问"
}

# 有问题检查 4 次
for ((i=1; i<=4; i ++))  
do  
    check
    if [ $? -eq 0 ];then
        techo "MySQL is ok"
        # 正常退出脚本
        exit 0
    else
        techo "Connection failed $i time(s)"
        sleep 1
    fi
done

techo '无法连接当前数据库'

# 失效转移
failover

打开 /etc/keepalived/keepalived_mysql_start.sh, 写入脚本内容

#!/bin/sh
# keepalived 变为 Master 时执行
MyPath=$(cd $(dirname $0); pwd)
cd $MyPath

ThisTime=`date '+%F %T'`
log_file='/var/log/keepalived_mysql.log'

# MySQL 连接方式，根据实际情况调整
export MYSQL_PWD='monitor'
MYSQL_USER='monitor'
MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock"
mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} "

# 美化输出
function techo() {
    message=$1
    message_level=$2
    if [ -e $message_level ];then
        message_level='info'
    fi
    echo "`date '+%F %T'` - [${message_level}] $message" >> $log_file
}

# 检查函数, 正常返回 0
function check {
    ret=`$mysql_connect -N -e 'select 1 as value'`
    if [ $? -ne 0 ] || [ $ret -ne '1' ];then
        return 1
    else
        return 0
    fi
}

# 获取 slave status 的信息
function slave_info() {
    tmp_file=/tmp/slave_info.tmp
    $mysql_connect -e 'show slave status\G' > /tmp/slave_info.tmp
    slave_sql=`grep 'Slave_SQL_Running:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z"  | awk -F":" '{print $2}'`
    seconds_behind_master=`grep 'Seconds_Behind_Master:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z"  | awk -F":" '{print $2}'`

    master_log_file=`grep 'Master_Log_File:' $tmp_file | head -1 | sed 's/\s*//g' | tr "A-Z" "a-z"  | awk -F":" '{print $2}'`
    master_log_pos=`grep 'Read_Master_Log_Pos:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z"  | awk -F":" '{print $2}'`

    relay_master_log_file=`grep 'Relay_Master_Log_File:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z"  | awk -F":" '{print $2}'`
    exec_master_log_pos=`grep 'Exec_Master_Log_Pos:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z"  | awk -F":" '{print $2}'`

}

# 设置是否可读
function read_only {
    param=$1
    $mysql_connect -e "set global read_only = ${param}"
    techo "设置是否只读 read_only ${param}"
}

# 处理数据同步
function sync_master_log() {
    # 如果是数据一致性优先，等待同步完毕。如果是服务可用性优先，可以注销下面的代码
    slave_info
    if [ $slave_sql == "yes" ];then
        techo "当前同步位置 Master ${master_log_file} ${master_log_pos}"
        techo "等待同步到 Master ${master_log_file} ${master_log_pos}"
        $mysql_connect -e "select master_pos_wait('$master_log_file', $master_log_pos);" > /dev/null
        techo "同步完毕"
    fi
}

techo "当前数据库提升为主库"

check
if [ $? -ne 0 ];then
    techo "无法连接当前数据库"
    exit 1
fi

# 等待同步
sync_master_log

# 设为可写
read_only 0

进入mysql修创建对应的同步用户

grant replication slave on *.* to 'sync'@'ip' identify by 'password';
show master status;

停止slave

stop slave;

修改对应的master

change master to 
master_host='', 
master_user='sync', 
master_password='',
master_log_file='mysql-bin.000003', 
master_log_pos=154, 
master_port=3306;

查看slave的状态

show slave status\G;
Updated on 2024-03-28
 Linux, Mysql, Keepalived
Back | Home