Resolving MySQL Replication Connection Failure Caused by SSL Requirement

1 Fault Background

During a MySQL homogeneous data migration, the usual master‑slave replication setup failed because the I/O thread could not connect to the master after executing change master . The account credentials were verified as correct, and network connectivity was confirmed.

2 Fault Analysis

The master’s error log showed repeated access‑denied messages for user repl connecting to 10.186.61.27:3310 . Inspection of the mysql.user table revealed that the replication user had ssl_type=ANY , which forces SSL connections.

MySQL clients from version 5.7 enable SSL by default, so normally no explicit SSL option is needed.

2021-06-07T16:56:54.812721+08:00 121 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master '[email protected]:3310' - retry-time: 60 retries: 1 message: Access denied for user 'repl'@'10.186.61.27' (using password: YES), Error_code: MY-001045

Querying mysql.user confirmed the ssl_type=ANY setting for the repl user.

3 Problem Reproduction

Disabling SSL explicitly with mysql --ssl-mode=disable caused the expected access‑denied error, confirming that the SSL requirement was the root cause.

[root@10-186-61-27 ~]# mysql -h10.186.61.27 -urepl -p -P3310 --ssl-mode=disable
ERROR 1045 (28000): Access denied for user 'repl'@'10.186.61.27' (using password: YES)

4 Issue Summary

By default, replication does not use SSL unless SSL parameters are explicitly set.

When troubleshooting connection failures, examine not only privileges and passwords but also user attributes such as ssl_type , max_questions , max_updates , max_connections , plugin , etc.

Common ERROR 1045 scenarios include wrong username, host restrictions, password errors, SSL enforcement, DNS issues, wrong IP, or misconfigured external authentication.

5 Solution

Remove the mandatory SSL requirement from the replication user: ALTER USER repl REQUIRE NONE;

When running CHANGE MASTER , explicitly specify SSL parameters if SSL is desired, e.g.: CHANGE MASTER TO MASTER_HOST='10.186.61.27', MASTER_USER='repl', MASTER_PASSWORD='xxxx', MASTER_PORT=3310, MASTER_AUTO_POSITION=1, MASTER_SSL=1;

Reference

Setting Up Replication to Use Encrypted Connections: https://dev.mysql.com/doc/refman/8.0/en/replication-encrypted-connections.html