How SSL Configuration Caused MySQL Replication Failure

Background

During a MySQL homogenous migration, an IO thread repeatedly failed to connect to the master despite verified credentials and network connectivity. The error log indicated "Access denied" due to SSL restrictions, even though SSL was not explicitly configured.

Root Cause Analysis

Key Observations

1. ​SSL Configuration Conflict:
   • The replication user repl had ssl_type=ANY in mysql.user, enforcing SSL for connections.
   • MySQL 5.7+ clients default to SSL-enabled connections, but explicit --ssl-mode=DISABLE failed to bypass this restriction.
2. ​Error Log Insights:

[ERROR] Slave I/O: error connecting to master 'repl@10.186.61.27:3310' - Access denied (using password: YES)

‍

The error masked the SSL requirement, mimicking a permission issue.

3. ​Test Case Validation:

# Explicit SSL disable attempt still fails
mysql -h10.186.61.27 -urepl -p --ssl-mode=DISABLE
ERROR 1045 (28000): Access denied...

‍

Solution Steps

1. Disable SSL Requirement for the Replication User:

ALTER USER 'repl'@'%' REQUIRE NONE;
FLUSH PRIVILEGES;

2. Explicitly Configure SSL Parameters During Replication Setup:

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;  -- Enable SSL if required

3. Verify Connectivity:

# Confirm replication resumes without SSL errors
SHOW SLAVE STATUS\G

‍

Best Practices for SSL in MySQL Replication

1. ​Audit User SSL Requirements:
   Regularly check mysql.user.ssl_type to avoid unintended connection blocks.

SELECT user, host, ssl_type FROM mysql.user WHERE ssl_type <> '';

2. ​Align Client and Server Configurations:

1. Ensure clients match the server’s SSL enforcement policy.
2. Use CHANGE MASTER TO MASTER_SSL=0 for non-SSL replication.
3. ​Secure Defaults:
   • For internal clusters, consider ssl_type=DISABLED to simplify setups.
   • Enable SSL only when encryption is mandatory.

Common SSL-Related Pitfalls

• ​Mismatched Certificates: Invalid CA certificates or key mismatches.
• ​Version Incompatibilities: Older clients failing to negotiate TLS versions.
• ​Firewall Rules: Blocking SSL/TLS ports (default: 3306).

Conclusion

This case highlights how SSL misconfigurations can masquerade as permission issues in MySQL replication. By aligning user SSL requirements with replication workflows and leveraging explicit configuration flags, teams can avoid downtime and ensure secure, efficient data synchronization.

‍