MySQL replication fails with permission error for unknown reason
Posted by:
Lars Hagen
Date: November 29, 2022 09:46AM
Since 36h I'm struggling to setup a simple Master <-> Master replication with MySQL 8. None of the found solutions on the Internet works here and meanwhile I'm a bit desperate. Even the first Master -> Slave replication wont work. So I hope for your help.
**How does the environment looks like:**
- Master and Slave are running on separated machines
- MySQL runs in a docker (the latest official MySQL image) on port 33060
- hosts are managed by/with Plesk Obsidian
**MySQL configuration on both machines**
[mysqld]
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
sql-mode=""
server-id = 1 // 1 = master host, 2 = slave host
binlog_do_db = dbsscm
gtid_mode = ON
enforce-gtid-consistency = ON
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
**Master Setup**
CREATE USER 'replicator'@'%' IDENTIFIED BY 'REPLIC-PW';
GRANT ALL PRIVILEGES ON *.* TO replicator@'%';
FLUSH PRIVILEGES;
**Test master access via:**
mysql --host=MASTER-IP --port=33060 -u replicator --password='REPLIC-PW'
**Slave Setup**
CREATE USER 'replicator'@'%' IDENTIFIED BY 'REPLIC-PW';
GRANT ALL PRIVILEGES ON *.* TO replicator@'%';
FLUSH PRIVILEGES;
**Test slave access via:**
mysql --host=SLAVE-IP --port=33060 -u replicator --password='REPLIC-PW'
The master sql-db is accessible via `mysql --host ...` or telnet from the slave host.
The slave sql-db is accessible via `mysql --host ...` or telnet from the master host.
**Setup Master -> Slave**
On the slave host:
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST = 'MASTER-IP',
MASTER_PORT = 33060,
MASTER_USER = 'replicator',
MASTER_PASSWORD = 'REPLIC-PW',
MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G;
**Problem - Last_IO_Errno: 2003**
MySQL [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to source
Master_Host: MASTER-IP
Master_User: replicator
Master_Port: 33060
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: 8f18893b5155-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'replicator@MASTER-IP:33060' - retry-time: 60 retries: 10 message: Can't connect to MySQL server on 'MASTER-IP:33060' (110)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 221129 07:22:21
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 50e65cd5-6ccf-11ed-9fbf-0242ac110003:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.000 sec)
**What I have tried to solve?**
- search Google and forums for more than 24h now
- remove/reset everything several times
- tried different user names and passwords for the "replicator"
- switched of the firewalls on Master & Slave hosts
Whatever I do - I always get the same `Last_IO_Errno: 2003` error and I have no idea what else I can further do?
BTW: The databases on the Master are running and are accessible for client applications via the MASTER-IP.
P.S.: I'm aware that I've yesterday posted a similar post. But the post contains far more details.
Subject
Views
Written By
Posted
MySQL replication fails with permission error for unknown reason
1153
November 29, 2022 09:46AM
238
November 06, 2023 01:00AM
Sorry, only registered users may post in this forum.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.