Sample Header Ad - 728x90

New MySQL 8.0 slave added to a 5.7 Percona Mysql cluster is stuck Waiting for dependent transaction to commit

0 votes
0 answers
56 views
I've tried following several guides to add a new Percona cluster as a slave to another cluster. The new cluster will run MySQL 8.0 while the old cluster needs to continue running 5.7 for a few more weeks at least. I have added a MySQL 8.0 server as a replica to a 5.7 Percona/Galera MySQL cluster. (The new server is the first node of a new 8.0 cluster, started in bootstrap mod. The other nodes in the PCX cluster has been shut down and the /var/lib/mysql data directory wiped on all of them) The data was seeded using using a mysqldump, using the command: mysqldump -h 172.16.75.3 -u repl --all-databases --master-data --ssl-mode=disabled | mysql It took three days to import the db. (Previous attempts to get the slave seeded using xtrabackup initially looked promising but the DB refuse to start.) On the selected master node I can see the slave connected using SHOW SLAVE HOSTS.
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        11 |      | 3306 |         1 | ede7d279-0867-11ef-a45d-566fd2de0048 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
On the new replica the slave status looks like this:
[root@galera-dbnode-03 etc]# mysql -e 'show slave status \G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.75.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: core-cloud-mysql-server-02-bin.000341
          Read_Master_Log_Pos: 453928562
               Relay_Log_File: galera-dbnode-03-relay-bin.000002
                Relay_Log_Pos: 162610326
        Relay_Master_Log_File: core-cloud-mysql-server-02-bin.000325
             Slave_IO_Running: Yes
            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: 1047889015
              Relay_Log_Space: 16772376805
              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: 62789
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6c7d7361-e68b-11ee-bf51-566fd2de005e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     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:
I've tried stopping and starting the slave. Could this be a TLS related issue? What else did I miss? Edit: After restarting MySQL server on the replica (again in bootstrap mode, since it is the only valid node right now) it showed an error. I was able to skip the transaction, and then it got back to the same error as shown above. Restarting the service again does not help.
[root@galera-dbnode-03 ~]# mysql -e "show slave status \G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.75.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: core-cloud-mysql-server-02-bin.000341
          Read_Master_Log_Pos: 722663436
               Relay_Log_File: galera-dbnode-03-relay-bin.000004
                Relay_Log_Pos: 712735716
        Relay_Master_Log_File: core-cloud-mysql-server-02-bin.000326
             Slave_IO_Running: Yes
            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: 712735458
              Relay_Log_Space: 16852646253
              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: 65252
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6c7d7361-e68b-11ee-bf51-566fd2de005e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     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:
Edit: The /etc/my.cnf file and the output of pt-mysql-summary here: https://pastebin.com/kB597zEW
Asked by VagrantPaladin (1 rep)
May 5, 2024, 08:50 AM
Last activity: May 6, 2024, 08:25 AM