学习主从复制,之前使用 root 和 repl ,也遇到过 root 可以, repl 不行,后来莫名其妙的可以了。今天重新来过的时候,再次遇到这个问题!
问题
mysql主从复制只能root,使用 repl 报错:
mysql> show replica status\G;
......
Read_Source_Log_Pos: 157
Relay_Log_File: ubuntu2004-103-relay-bin.000001
Relay_Log_Pos: 4
Relay_Source_Log_File: binlog.000001
Replica_IO_Running: Connecting
Replica_SQL_Running: Yes
......
Last_IO_Error: Error connecting to source 'repl@192.168.0.101: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.
......
如果按照网上建议,改成 mysql_native_password ,那是一种退步!
一定是别的原因造成的!
我的环境
主服务器
Ubuntu 20.04 :
192.1568.0.101
$ mysql -V
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
从服务器
Ubuntu 20.04 :
192.168.0.103
$ mysql -V
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
解决过程
-
在主从服务器上分别确认 repl 权限
mysql> show grants for repl; +----------------------------------------------+ | Grants for repl@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` | +----------------------------------------------+ 1 row in set (0.00 sec)
权限没有问题!
-
在从服务器上,远程登录试试
$ mysql -urepl -h192.168.0.101 -pPassword@123
OK!
-
从服务器上再来
$ mysql -uroot -pPassword@123mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.101',SOURCE_USER='repl123',SOURCE_PASSWORD='Password@123',SOURCE_LOG_FILE='binlog.000001',SOURCE_LOG_POS= 157; Query OK, 0 rows affected, 2 warnings (0.26 sec) mysql> start replica; Query OK, 0 rows affected (0.10 sec) mysql> show replica status\G; ...... Source_Log_File: binlog.000001 Read_Source_Log_Pos: 1087 Relay_Log_File: ubuntu2004-103-relay-bin.000002 Relay_Log_Pos: 323 Relay_Source_Log_File: binlog.000001 Replica_IO_Running: Yes Replica_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1396 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000001, end_log_pos 478. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Source_Log_Pos: 157 ......
这次报错已经不一样了!
难道远程登录也会改变主服务器上的 binlog ?
-
上主服务器上重新查看
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000001 | 1087 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
确实 Position 改变了!
-
从服务器上换成新的 Position 再来
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.101',SOURCE_USER='repl123',SOURCE_PASSWORD='Password@123',SOURCE_LOG_FILE='binlog.000001',SOURCE_LOG_POS= 1087; Query OK, 0 rows affected, 2 warnings (0.21 sec) mysql> start replica; Query OK, 0 rows affected (0.12 sec) mysql> show replica status\G; *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 192.168.0.101 Source_User: repl123 Source_Port: 3306 Connect_Retry: 60 Source_Log_File: binlog.000001 Read_Source_Log_Pos: 1087 Relay_Log_File: ubuntu2004-103-relay-bin.000002 Relay_Log_Pos: 323 Relay_Source_Log_File: binlog.000001 Replica_IO_Running: Yes Replica_SQL_Running: Yes ......
这次 OK 了!
后记
1… 这之后,再次远程使用 repl 登录, 也不会改变 master 的 binlog 和 position 了!
- 难道真的是这么简单?只是远程登录一次即可?