# 檢查 MySQL 版本
mysql -V

[mysqld]
server-id = 1                 # 唯一標識，建議使用IP地址的最后一個數(shù)
log-bin = mysql-bin           # 啟用二進制日志
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_format = ROW

sudo systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'gtid_mode';"  # 應顯示 ON
mysql -e "SHOW VARIABLES LIKE 'enforce_gtid_consistency';"  # 應顯示 ON

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

[mysqld]
server-id = 2                 # 唯一標識，不能與主庫重復
log-bin = mysql-bin           # 啟用二進制日志
read-only = 1                 # 從庫設置為只讀（可選）
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_format = ROW

sudo systemctl restart mysql

在從服務器上執(zhí)行以下命令，配置主從關系：
CHANGE MASTER TO
MASTER_HOST='主服務器IP',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION = 1;  # 使用 GTID 自動定位

START SLAVE;

SHOW SLAVE STATUS\G;    #檢查 IO 和 SQL 線程狀態(tài)，確認從服務器是否落后于主服務器
檢查以下兩個狀態(tài)是否均為 Yes：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

# step 1 在主庫和從庫生成 SSL 證書
# 在主庫和從庫分別執(zhí)行（使用相同的 CA）
mkdir /etc/mysql/ssl && cd /etc/mysql/ssl
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-csr.pem
openssl x509 -req -in server-csr.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# step 2 配置主庫 my.cnf
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# step 3 配置從庫 my.cnf
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# step 4 重啟主從庫服務
systemctl restart mysql

# step 5 創(chuàng)建用戶
# 創(chuàng)建使用舊認證方式的復制用戶
-- 在主庫執(zhí)行
CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

# 修改 MySQL 用戶認證方式
ALTER USER 'yang'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

[mysqld]
require_secure_transport = OFF  # 關閉強制 SSL

-- 從庫執(zhí)行
CHANGE MASTER TO
  MASTER_HOST='主庫IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='主庫binlog文件',
  MASTER_LOG_POS=主庫binlog位置;
START SLAVE;

stop slave;        #停止復制從庫
reset slave;        #刪除從庫配置
reset master;        #刪除主庫配置
SHOW MASTER STATUS;    #查詢當前主庫的 binlog 文件和位置