0

This one got my head scratching...

I was going to set up replication (the server is running 8.0.26), so I fire up a was EC2 VM and installed mysql-server (8.0.30) from the official MySQL repo. After some minor configurations, I import the whole dump, and restart the service, I tried to set up the replication, but it failed. Then I tried to connect to the remote database using mysql -h my_server -u my_user -p and it failed again. I checked if the port was open by running a netcat on 3306. So I log into another random VM and tried to connect using the exact same command, and succeded.

I checked the grants, and I looks alright (again, I did connect using that user on another server).

show grants for my_user \G
*************************** 1. row ***************************
Grants for my_user@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `my_user`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for my_user@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `my_user`@`%` WITH GRANT OPTION
2 rows in set (0.00 sec)

Slave status error:

show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: my_server
                  Master_User: my_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my_server-bin.000004
          Read_Master_Log_Pos: 6273997
               Relay_Log_File: db-replication-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: my_server-bin.000004
             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: 6273997
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'my_user@my_server:3306' - retry-time: 60 retries: 9 message: Access denied for user 'my_user'@'15.228.**.***' (using password: YES)
               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: 220728 09:14:44
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         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.00 sec)

Is this a version thing? The VM is brand new, I am really struggling to understand why it would work in another VM, but not in that one.

UPDATE

I set up another server, in another provider, and before importing the dump, I tried to connect to my master, and it worked. But after finishing importing the dump, and setting up the master, I no longer can connect to the master. What the hell?

I literally just add a single line to my MySQL configuration file (server-id = 2), absolutely nothing else.

2
  • Did you check the remote error log after the connection failure?
    – mustaccio
    Jul 28 at 13:02
  • Yes, I was tailing /var/log/mysql/error.log but nothing showed up. Is there another log? Jul 28 at 13:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.