Sample Header Ad - 728x90

MySQL replication fails with permission error for unknown reason

-1 votes
1 answer
327 views
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.
Asked by Lars (109 rep)
Nov 29, 2022, 07:44 AM
Last activity: Apr 27, 2025, 07:01 PM