红豆泥运维库

mysql主主同步+keepalived+故障自动切换

一、MySQL安装

数据库服务器1 master1
数据库服务器2 master2
先配置selinux为宽容模式/disabled

setenforce 0
getenforce
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
reboot

1.上传mysql.tar包并解压

tar xf mysql-8.0.33-el7-x86_64.tar
tar -xf mysql-8.0.33-el7-x86_64.tar.gz
mv mysql-8.0.33-el7-x86_64 /usr/local/mysql
cd /usr/local/mysql
mkdir data

2.创建mysql用户组和用户并赋予目录权限

groupadd mysql
useradd -g mysql mysql
chown -R mysql:mysql /usr/local/mysql/

3.初始化数据库并修改my.cnf配置

初始化后会出现临时密码，请一定要记录下来！！！

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize
vi /etc/my.cnf
[mysqld]
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 [client]
   socket = /usr/local/mysql/mysql.sock
   default-character-set=utf8

4.配置全局环境变量

vi /etc/profile
**在最后添加**
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib
export PATH

**加载配置**
source /etc/profile

5.使用systemctl来管理mysql

vi /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=notify
TimeoutSec=0
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS
# EnvironmentFile=/etc/sysconfig/mysql
LimitNOFILE=65535
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false

**加载配置**
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld

6.使用临时密码登录并修改密码

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password';

7.设置mysql远程登录

use mysql;
update user set host='%' where user='root' limit 1;
flush privileges;
SELECT user,host FROM mysql.user;

二、配置MySQL主主同步

1.修改my.cnf

**master1数据库**
vi /etc/my.cnf
[mysqld]
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin=mysql-bin
binlog-format=ROW
server-id=1  # 第一台服务器
auto_increment_increment=2  #控制主键自增的步长，几台服务器就设置几
auto_increment_offset=1  #设置自增起始值。这个是第1台，那么为1，下一台则为2。
#binlog_do_db = DBNAME　　#设置要同步的数据库
#replicate-ignore-db = test　　#设置不同步的数据库
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 [client]
   socket = /usr/local/mysql/mysql.sock
   default-character-set=utf8

**master2数据库**
vi /etc/my.cnf
[mysqld]
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin=mysql-bin
binlog-format=ROW
server-id=2  # 第二台服务器
auto_increment_increment=2  #控制主键自增的步长，几台服务器就设置几
auto_increment_offset=2
#binlog_do_db = DBNAME　　#设置要同步的数据库
#replicate-ignore-db = test　　#设置不同步的数据库
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 [client]
   socket = /usr/local/mysql/mysql.sock
   default-character-set=utf8

**两台数据库均重启加载配置**
systemctl restart mysqld.service

2.在两台数据库服务器上均创建用于复制的用户并授权

CREATE USER 'repl'@'%' IDENTIFIED BY 'your password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3.配置主主关系

执行SHOW MASTER STATUS;查看对应信息
C6094B59-04D6-4591-B6ED-B1DCF5C28C93.png

在master1服务器上执行以下命令

CHANGE MASTER TO
MASTER_HOST='master2_ip',
MASTER_USER='repl',
MASTER_PASSWORD='your password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157;
START SLAVE;

在master2服务器上执行以下命令

CHANGE MASTER TO
MASTER_HOST='master1_ip',
MASTER_USER='repl',
MASTER_PASSWORD='your password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157;
START SLAVE;

执行完后输入SHOW SLAVE STATUS\G查看状态
F4CEBA40-3001-43a0-BF54-396BBCEA2ECD.png
这里发现Slave_IO_Running: Connecting，出现问题报错如下
9CBD0737-9330-419b-9FCE-82F30117EE8A.png

use mysql;
select plugin from user;
select user,host,plugin from user;

原来是repl的plugin是caching_sha2_password 导致连接不上，修改为mysql_native_password即可解决。
82F39845-6EB2-4cdf-B475-414E83A9E30A.png

alter user repl@'%' identified with mysql_native_password by 'your password';
select user,host,plugin from user;

F1ACEB06-C1E7-479e-9083-AC806B369451.png
再次执行SHOW MASTER STATUS;查看对应信息
E18A34FC-82F4-4154-98E5-1C82B2C7F8EE.png
在master1服务器上执行以下命令

stop slave;
CHANGE MASTER TO
MASTER_HOST='master2_ip',
MASTER_USER='repl',
MASTER_PASSWORD='your password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=838;
START SLAVE;

在master2服务器上执行以下命令

stop slave;
CHANGE MASTER TO
MASTER_HOST='master1_ip',
MASTER_USER='repl',
MASTER_PASSWORD='your password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=838;
START SLAVE;

在两台服务器上执行SHOW SLAVE STATUS\G，确保Slave_IO_Running和Slave_SQL_Running都为Yes
352F3D45-47E9-47a7-9FEA-C52764E3B297.png

三、安装配置Keepalived

1.下载Keepalived并配置vip地址和抢占模式

yum -y install keepalived
**master1**
vi /etc/keepalived/keepalived.conf
vrrp_script chk_mysql_port {
    script "/etc/keepalived/chk_mysql.sh"
    interval 10
    weight –5
    fall 2
    rise 2
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.100.50    #vip虚拟地址，与服务器地址应在同一网关下
    }
track_script {
   chk_mysql_port
   }

}


yum -y install keepalived
**master2**
vi /etc/keepalived/keepalived.conf
vrrp_script chk_mysql_port {
    script "/etc/keepalived/chk_mysql.sh"
    interval 10
    weight –5
    fall 2
    rise 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.100.50     #vip虚拟地址，与服务器地址应在同一网关下
    }
track_script {
   chk_mysql_port
   }

}

2.两台数据库服务器均配置mysql存活检测脚本

vi /etc/keepalived/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi



chmod +x chk_mysql.sh

3.firewalld防火墙放行VRRP使得keepalived传播心跳包

firewall-cmd --add-rich-rule='rule protocol value="vrrp" accept' --permanent
firewall-cmd --reload
firewall-cmd --list-all
**启动keepalived并通过ip a,ping,telnet测试vip地址是否生效**
systemctl start keepalived.service
systemctl enable keepalived.service
ip a
site logo

© 2025 红豆泥运维库