mysql 8.0.35 搭建主从

参考文档：
Replication Not Working in MySQL 8.0. The I/O Thread Cannot Connect, Fails With Error 2061 (Doc ID 2423671.1)

-- 安装mysql8.0.35 ，解压tar包后，直接初始化（略）

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.00 sec)
 
mysql>

-- 创建复制账号，所有的库上。可以使用两种方式创建账号，

--create user 'rep1'@'192.168.2.%' identified by 'mysql';         <<<<< 这种方式创建的账号，会报Error_code: MY-002061
CREATE USER 'rep1'@'192.168.2.%' IDENTIFIED WITH 'mysql_native_password' BY 'mysql';    <<<<< 这种方式创建的账号，不会报Error_code: MY-002061
grant replication slave on *.* to 'rep1'@'192.168.2.%';
flush privileges;

-- conf文件 这里的conf文件，仅仅为搭建主从的最小最少参数

[mysqld]
server_id=3306 
admin_port=33062
mysqlx_port=33060
socket=/mysql/mysql3306/mysql3306.sock  
mysqlx_socket=/mysql/mysql3306/mysql3306x.sock  
log-error=/mysql/mysql3306/data/error.log 
port=3306
character_set_server=utf8mb4
basedir=/mysql/mysql3306
datadir=/mysql/mysql3306/data
log_bin=binlog
log_bin_index=/mysql/mysql3306/data/binlog.index 
gtid_mode=on
enforce_gtid_consistency=true
 
 
binlog-ignore-db = mysql 
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

[mysqld]
server_id=3307
admin_port=33072
mysqlx_port=33070
socket=/mysql/mysql3307/mysql3307.sock
mysqlx_socket=/mysql/mysql3307/mysql3307x.sock  
log-error=/mysql/mysql3307/data/error.log
log_bin=binlog
log_bin_index=/mysql/mysql3307/data/binlog.index 
port=3307
character_set_server=utf8mb4
basedir=/mysql/mysql3307
datadir=/mysql/mysql3307/data
socket=/tmp/mysql.sock
gtid_mode=on
enforce-gtid-consistency=true
 
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
 
 
binlog-ignore-db = mysql 
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

-- 开启半同步 (8.0和5.7的半同步插件，不一样。安装5.7的插件也可以，log中有提示，即将被淘汰)

--INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
--SET GLOBAL rpl_semi_sync_master_enabled=ON;
install plugin rpl_semi_sync_source soname 'semisync_source.so';
set global rpl_semi_sync_source_enabled=1;

-- 备份主库

mysqldump --source-data=2 --single-transaction -uroot -h127.0.0.1 -p -P3306 --databases test test_db  >mysqldump_`date +%Y%m%d`.sql

--开启主从同步
-- CHANGE MASTER TO MASTER_LOG_FILE='on.000008', MASTER_LOG_POS=1268;

change master to master_host='192.168.2.154',
master_port=3306,
master_user='rep1',
master_password='mysql',
master_log_file='on.000008',
master_log_pos=1268;
 
 
start slave;
show slave status \G;

-- 出现的错误

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.2.154
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: on.000008
          Read_Master_Log_Pos: 1268
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: on.000008
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:

-- log中的错误 

2023-11-07T02:51:59.855154Z 8 [System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL '' executed'. Previous state source_host='', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''. New state source_host='192.168.2.154', source_port= 3306, source_log_file='on.000008', source_log_pos= 1268, source_bind=''.
2023-11-07T02:52:30.166311Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2023-11-07T02:52:45.230203Z 9 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'rep1@192.168.2.154:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2023-11-07T02:53:45.231503Z 9 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'rep1@192.168.2.154:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

-- 查看当前的默认认证插件，为caching_sha2_password

+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.03 sec)
 
mysql>

--参数中修改为mysql_native_password，不行      

default_authentication_plugin=mysql_native_password

方法1 
--drop 掉用户，重新创建，加上WITH 'mysql_native_password'即可

drop user 'rep1'@'192.168.2.%'
CREATE USER 'rep1'@'192.168.2.%' IDENTIFIED WITH 'mysql_native_password' BY 'mysql';
 
mysql> select host,user,plugin from mysql.user;
+-------------+------------------+-----------------------+
| host        | user             | plugin                |
+-------------+------------------+-----------------------+
| 192.168.2.% | rep1             | mysql_native_password |
| 192.168.2.% | root             | caching_sha2_password |
| localhost   | mysql.infoschema | caching_sha2_password |
| localhost   | mysql.session    | caching_sha2_password |
| localhost   | mysql.sys        | caching_sha2_password |
| localhost   | root             | caching_sha2_password |
+-------------+------------------+-----------------------+
6 rows in set (0.00 sec)
 
mysql>

-- 方法2 
创建用户，需要修改一些SSL及public key

CREATE USER 'rep1'@'192.168.2.%' IDENTIFIED BY 'mysql';
 
mysql> select host,user,plugin from mysql.user;
+-------------+------------------+-----------------------+
| host        | user             | plugin                |
+-------------+------------------+-----------------------+
| 192.168.2.% | rep1             | caching_sha2_password |
| 192.168.2.% | root             | caching_sha2_password |
| localhost   | mysql.infoschema | caching_sha2_password |
| localhost   | mysql.session    | caching_sha2_password |
| localhost   | mysql.sys        | caching_sha2_password |
| localhost   | root             | caching_sha2_password |
+-------------+------------------+-----------------------+
6 rows in set (0.00 sec)
 
mysql>

启用SSL

stop replica;
CHANGE REPLICATION SOURCE TO SOURCE_SSL=1;
START REPLICA;

将public_key.pem从主端复制到从端

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_PUBLIC_KEY_PATH='/path/public_key.pem';
START REPLICA;
SHOW REPLICA STATUS\G

-- 查看从库复制状态，可以看到Source_SSL_Allowed、Source_public_key_path等

mysql> show replica status \G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.2.154
                  Source_User: rep1
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000023
          Read_Source_Log_Pos: 197
               Relay_Log_File: slave-relay-bin.000006
                Relay_Log_Pos: 367
        Relay_Source_Log_File: binlog.000023
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: Yes
           Source_SSL_CA_File:
           Source_TLS_Version:
       Source_public_key_path: /mysql/mysql3307/data/public_key.pem
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

END