MySQL8安装和主从配置

不建议编译安装MySQL，要么使用打包好的二进制（deb、rpm包），要么使用docker容器。

使用Docker容器

mkdir -p /opt/quickon/docker/q-7-205-mysql-replica/{config,data}
touch /opt/quickon/docker/q-7-205-mysql-replica/config/slave.cnf
tee /opt/quickon/docker/q-7-205-mysql-replica/config/slave.cnf <<'eof'
[mysqld]
server_id = 72051
slave-skip-errors = 1032,1062,1677,1805
eof

docker run --name q-7-205-mysql-replica --hostname q-7-205-mysql-replica --restart always \
 -l maintainer='dingguodong@20250715' \
 -e MYSQL_ALLOW_EMPTY_PASSWORD=true \
 -v /etc/localtime:/etc/localtime \
 -v /data/docker/q-7-205-mysql-replica/data:/var/lib/mysql \
 -v /data/docker/q-7-205-mysql-replica/config/slave.cnf:/etc/mysql/conf.d/slave.cnf \
 -p 23305:3306 \
 -d \
mysql:8.0.36

默认MySQL8版本的密码不是mysql_native_password，而是caching_sha2_password，因此在设置主从时MySQL可能会提示：Error connecting to source 'replication_user@10.0.7.205:3306'. This was attempt 2/86400, with a delay of 10 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

这种密码插件需要满足以下任意一个因素：

1. A secure connection (SSL/TLS)
2. RSA key pair-based password exchange

ALTER USER 'replication_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'EfoK07D8zOyBi1QLazY3';
FLUSH PRIVILEGES;

正常应该是：

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'EfoK07D8zOyBi1QLazY3';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

导出 MySQL SQL文件

注意：在导出 MySQL文件时建议停止任何数据库连接，或使用FLUSH TABLES WITH READ LOCK; 和 UNLOCK TABLES; 组合。

FLUSH TABLES WITH READ LOCK;
mysqldump -uroot -p --all-databases --master-data > /tmp/mysql.sql;
UNLOCK TABLES;

或者

mysqldump --single-transaction --master-data=2 --flush-logs --routines --triggers --events --all-databases > /tmp/mysql.sql

这样可以在 /tmp/mysql.sql 文件的前25行找到MASTER_LOG_FILE和MASTER_LOG_POS。

CHANGE MASTER TO MASTER_LOG_FILE='binlog.002416', MASTER_LOG_POS=618366457,
  MASTER_HOST='10.0.7.205',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='EfoK07D8zOyBi1QLazY3',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=10;

启动同步线程

START SLAVE;
SHOW SLAVE STATUS\G

--