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;
  1. 啓動從庫複製線程
    START SLAVE;

. 查看從庫複製狀態

SHOW SLAVE STATUS\G

MySQL 常用高可用方案_ipad

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

完畢