MySQL 主从+keepalive高可用

1.MySQL 主库执行
容器化安装：
mkdir -p /data/mysql/{conf,log,data} #创建数据和配置目录，下面适用于mysql 8.0.25
生成配置文件/data/mysql/my.cnf #调整容器中my.cnf中参数[client]和!includedir的位置

cat > /data/mysql/my.cnf <<'EOF'
[client]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1

skip-host-cache
skip-name-resolve
bind-address=0.0.0.0
secure-file-priv=/var/lib/mysql-files

# ----------------- 主从复制 -----------------
server-id=1
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
sync_binlog=1

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE

# ----------------- 性能 -----------------
max_connections=2000
innodb_buffer_pool_size=3G        # 机器 7GB 内存可分配 ~3G 给 InnoDB
innodb_log_file_size=512M
sort_buffer_size=2M
open_files_limit=65535
max_allowed_packet=128M

# ----------------- 日志 -----------------
log-error=/var/log/error.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-query.log
long_query_time=3

!includedir /etc/mysql/conf.d/
EOF

扩展-后续修改mysql容器配置如下案例(新增*.conf写入配置参数即可)：

vim /data/mysql/conf/extra.cnf
 [mysqld]
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

启动：

docker run -p 3306:3306 --name mysql8 --restart always -v /etc/localtime:/etc/localtime \
  -v /data/mysql/conf:/etc/mysql/conf.d \
  -v /data/mysql/log:/var/log/ \
  -v /data/mysql/data:/var/lib/mysql \
  -v /data/mysql/my.cnf:/etc/my.cnf \
  -e MYSQL_ROOT_PASSWORD=tcbnYaGLGkz8dnxx -d mysql:8.0.25

开启远程权限

    docker exec -it mysql8 bash
    mysql -uroot -ptcbnYaGLGkz8dnxx
    ALTER USER 'root'@'%' IDENTIFIED BY 'tcbnYaGLGkz8dnxx';
    FLUSH PRIVILEGES;

主库执行同步账号

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'VjFSJhuDLTVRwx3Q';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec

从库执行一下，其他的步骤和主库一样

root@ansible:/data/mysql# cat my.cnf 
[client]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1

skip-host-cache
skip-name-resolve
bind-address=0.0.0.0
secure-file-priv=/var/lib/mysql-files

# ----------------- 主从复制 -----------------
server-id=2
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
sync_binlog=1

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
read_only=1     #只读
super_read_only=1   #super用户也只能读

# ----------------- 性能 -----------------
max_connections=2000
innodb_buffer_pool_size=3G        # 机器 7GB 内存可分配 ~3G 给 InnoDB
innodb_log_file_size=512M
sort_buffer_size=2M
open_files_limit=65535
max_allowed_packet=128M

# ----------------- 日志 -----------------
log-error=/var/log/error.log
slow-query-log=1
slow-query-log-file=/var/log/slow-query.log
long_query_time=3

从库执行GTID模式

如果是 GTID 模式，改成：

CHANGE MASTER TO
  MASTER_HOST='10.0.8.25',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='VjFSJhuDLTVRwx3Q',
  MASTER_AUTO_POSITION=1;

6. 启动从库复制线程
   START SLAVE;

. 查看从库复制状态

SHOW SLAVE STATUS\G

io线程和sql线程都是yes说明同步成功

部署keepalive

yum install -y keepalived

主库

root@web:/etc/keepalived# cat keepalived.conf 
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 150
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.0.8.100/24
    }

}

root@web:/etc/keepalived# cat /etc/keepalived/keepalived.conf 
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 150
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.0.8.100/24
    }

}

从库

root@web:~# cat /etc/keepalived/keepalived.conf 
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    preempt

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.0.8.100/24
    }
}

启动keepalive

systemctl enable keepalived
systemctl start keepalived

数据库手动主从切换

Step 1️⃣：冻结所有从库

STOP SLAVE;
SHOW SLAVE STATUS\G

确认：

Slave_IO_Running: No
Slave_SQL_Running: No

---

Step 2️⃣：选最新的从库

查看：

SHOW SLAVE STATUS\G

关注：

Executed_Gtid_Set
Retrieved_Gtid_Set

👉 Executed_Gtid_Set 最大的那个，从理论上数据最全

---

Step 3️⃣：提升从库为新主（核心）

在新主上执行：

STOP SLAVE;
RESET SLAVEALL;

关闭只读：

SETGLOBAL read_only= OFF;
SETGLOBAL super_read_only= OFF;

验证：

SHOW MASTER STATUS;

你会看到：

Executed_Gtid_Set: xxxxx

---

Step 4️⃣：其他从库指向新主（重点）

在其他从库执行：

STOP SLAVE;
RESET SLAVEALL;

CHANGE MASTERTO
MASTER_HOST='新主IP',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl_pass',
MASTER_AUTO_POSITION=1;

START SLAVE;

检查：

SHOW SLAVE STATUS\G

必须是：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主库关闭keepalive

完毕