Error
Error Code:
2024
MySQL Error 2024: Replica Connection Failure
Description
MySQL Error 2024, identified by the symbol CR_PROBE_REPLICA_CONNECT, indicates that a MySQL client or a replica server is unable to establish a connection to another MySQL instance, typically a source server in a replication setup. This error commonly occurs when the replica cannot reach, authenticate with, or communicate effectively with its designated source server.
Error Message
Error connecting to replica:
Known Causes
4 known causesNetwork Connectivity Problems
The replica server cannot establish a network connection to the source server due to firewall restrictions, incorrect IP addresses, DNS issues, or general network outages.
Invalid Replication Credentials
The username or password configured for the replication user on the replica server is incorrect, or the user lacks the necessary replication privileges on the source server.
Source Server Unreachable
The MySQL source server is either not running, is overloaded, or is otherwise unresponsive to connection attempts from the replica server.
Incorrect Replication Configuration
The `CHANGE MASTER TO` command on the replica contains incorrect host, port, or other connection parameters for the source server.
Solutions
4 solutions available1. Verify Replica Hostname and Port Accessibility easy
Ensures the replica server is reachable from the primary.
1
On the primary server, attempt to connect to the replica using the `mysql` client.
mysql -h <replica_hostname_or_ip> -P <replica_port> -u <replication_user> -p2
If the connection fails, check network connectivity. Use `ping` to verify the replica host is reachable.
ping <replica_hostname_or_ip>3
If `ping` works, use `telnet` or `nc` (netcat) to check if the replica's MySQL port is open and listening.
telnet <replica_hostname_or_ip> <replica_port>
# or
nc -vz <replica_hostname_or_ip> <replica_port>4
If the port is not accessible, investigate firewall rules on both the primary and replica servers, as well as any network devices in between.
2. Check Replication User Credentials and Permissions medium
Confirms the replication user has the necessary privileges and correct credentials.
1
On the replica server, connect as a user with sufficient privileges (e.g., root).
mysql -u root -p2
Verify the replication user exists and has the correct password by checking the `mysql.user` table.
SELECT user, host FROM mysql.user WHERE user = '<replication_user>';
-- If password needs reset or user doesn't exist, use:
-- CREATE USER '<replication_user>'@'<primary_host>' IDENTIFIED BY '<new_password>';
-- GRANT REPLICATION SLAVE ON *.* TO '<replication_user>'@'<primary_host>';
-- FLUSH PRIVILEGES;3
Ensure the replication user has the `REPLICATION SLAVE` privilege granted.
SHOW GRANTS FOR '<replication_user>'@'<primary_host>';4
On the primary server, update the replication connection information with the correct username and password if they have changed.
CHANGE REPLICATION SOURCE TO
SOURCE_USER='<replication_user>',
SOURCE_PASSWORD='<correct_password>';5
Restart replication on the replica.
START REPLICA;3. Review Replica Server Status and Error Logs medium
Identifies specific reasons for connection failure from the replica's perspective.
1
On the replica server, check the status of the replication threads.
SHOW REPLICA STATUS\G2
Look for errors in the `Last_IO_Error` and `Last_SQL_Error` fields. These will often provide a more specific reason for the failure.
3
Examine the MySQL error log on the replica server for any related messages around the time of the connection failure. The log file location can be found in the `my.cnf` or `my.ini` configuration file (e.g., `log_error` directive).
tail -f /var/log/mysql/error.log # Example path, adjust as needed4
Based on the error messages, take corrective actions. For example, if it's a network timeout, investigate network latency or firewalls. If it's an authentication error, re-verify credentials.
4. Ensure Primary Server is Accessible and Configured for Replication medium
Verifies the primary is properly set up to accept replication connections.
1
On the primary server, ensure the `server_id` is unique and set in the MySQL configuration file (`my.cnf` or `my.ini`).
[mysqld]
server_id = 12
Verify the `bind-address` in the primary's configuration file allows connections from the replica's IP address. If it's `127.0.0.1`, it will only accept local connections. Set it to `0.0.0.0` to allow all interfaces, or a specific IP address.
[mysqld]
bind-address = 0.0.0.03
Ensure binary logging is enabled on the primary.
[mysqld]
log_bin = mysql-bin
binlog_format = ROW4
Restart the primary MySQL server after making configuration changes.
sudo systemctl restart mysql # or equivalent command for your OS5
On the primary, ensure the replication user has the correct host specified in its grant. For example, if the replica connects from `192.168.1.100`, the grant should be `'<replication_user>'@'192.168.1.100'`.
GRANT REPLICATION SLAVE ON *.* TO '<replication_user>'@'<replica_host_or_ip>';
FLUSH PRIVILEGES;