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
Last activity: May 6, 2024, 08:25 AM