# Mysql 8 Replication Troubles **URL:** \ **Category:** Percona XtraDB Cluster 8.x\ **Created:** [November 13, 2022, 4:17pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519 "2022-11-13T16:17:25Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 13, 2022, 4:17pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/1 "2022-11-13T16:17:25Z")
I have a 3 node PXC cluster, which was just upgraded to Mysql 8.0.29 from 5.7. I also have an asynchronous slave running separately, also running Mysql 8.0.29. The slave has been running 8.0.29 for a few weeks. After the upgrade of the PXC cluster, I needed to do a resync of the slave, which I did using xtrabackup. After preparing the backup, I start the slave and it starts. But it does not receive any updates from the master. Show slave status shows no errors. Slave\_SQL\_Running\_State: Replica has read all relay log; waiting for more updates Slave\_IO\_State: Waiting for source to send event Slave\_IO\_Running: Yes Slave\_SQL\_Running: Yes I used auto\_position to set the master’s position. I also tried setting the coordinates manually. Neither worked. There are no errors on the master or the slave, and no messages in the mysqld.log file. The firewall ports are open. This exact process has been run before the upgrade with no issue. Any help would be greatly appreciated. ---
**Author:** ![Mauricio\_Cacho](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mauricio_cacho/32/5859_2.png) [@Mauricio\_Cacho](https://forums.percona.com/u/Mauricio_Cacho)\ **Post date:** [November 13, 2022, 4:57pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/2 "2022-11-13T16:57:36Z")
Hi @MeirW2 , from the status shared, it seems that replication IS running, but it’s just not getting anything nre from the primary, maybe you need to confirm if the primary in the replica is pointed to the right IP, also confirm if there are writes to be replicated. If that seems ok, please share the complete output from the SHOW SLAVE STATUS\G command so we have more info to troubleshoot; also are you using GTIDs? Best, Mauricio. ---
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 13, 2022, 5:08pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/3 "2022-11-13T17:08:49Z")
Yes, using GTIDs. IPs are open in both directions between the machines. This is the SLAVE STATUS: ```auto **************************1. row*************************** Replica_IO_State: Waiting for source to send event Source_Host: 209.222.9.148 Source_User: slave_user Source_Port: 3306 Connect_Retry: 60 Source_Log_File: binlog.000062 Read_Source_Log_Pos: 89134941 Relay_Log_File: CentOS-74-64-minimal-relay-bin.000007 Relay_Log_Pos: 8927131 Relay_Source_Log_File: binlog.000062 Replica_IO_Running: Yes Replica_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_Source_Log_Pos: 89134941 Relay_Log_Space: 8927400 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 4 Source_UUID: f07b9880-5ea6-11ed-97a6-3cecef6a3314 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 1bb74cdf-588a-11ed-bbe9-3cecefb9c2c0:1-5, 2ea7d4d5-db8c-11e7-a395-0cc47aa8ab3e:1-96, 4a545cd1-29bc-11ec-9bdc-3cecef46a4b8:1-4415196, 4d154a62-29bc-11ec-b634-3cecef6b0a8a:1-818469, 52bf7592-5a6d-11ed-9f81-001e67c47b04:1, 59ce7551-b19c-11ec-8d08-001e67c47b04:1-13, 772fd0ef-db6b-11e7-8394-de7434f4bcd0:1-91824198, 88d02f10-2494-ee18-7c6b-218bcb0b432f:1-3873719826, b8213878-b0eb-11ec-8bba-3cecef46a4b8:1-19, c7063da6-4f9c-11e9-924d-001e67c47b04:1-4, fc9c4ee0-24cc-ee18-7862-69b0adc2fe43:1-1377216 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ``` This is the show master status (on the master): ```auto mysql> show master status\G; ***************************1. row*************************** File: binlog.000062 Position: 110631339 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1bb74cdf-588a-11ed-bbe9-3cecefb9c2c0:1-5, 2ea7d4d5-db8c-11e7-a395-0cc47aa8ab3e:1-96, 4a545cd1-29bc-11ec-9bdc-3cecef46a4b8:1-4415196, 4d154a62-29bc-11ec-b634-3cecef6b0a8a:1-818469, 772fd0ef-db6b-11e7-8394-de7434f4bcd0:1-62636948, 88d02f10-2494-ee18-7c6b-218bcb0b432f:1-3873719826, b8213878-b0eb-11ec-8bba-3cecef46a4b8:1-19, fc9c4ee0-24cc-ee18-7862-69b0adc2fe43:1-1377216 1 row in set (0.00 sec) ``` ---
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 13, 2022, 5:16pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/4 "2022-11-13T17:16:02Z")
```auto mysql> select * from performance_schema.replication_connection_status\G ***************************1. row*************************** CHANNEL_NAME: GROUP_NAME: SOURCE_UUID: f07b9880-5ea6-11ed-97a6-3cecef6a3314 THREAD_ID: 300 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 1789621 LAST_HEARTBEAT_TIMESTAMP: 2022-11-13 12:15:26.252162 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 1 row in set (0.00 sec) ``` ---
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 13, 2022, 5:28pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/5 "2022-11-13T17:28:01Z")
Here’s the mysqld.cnf file on the master ```auto [mysqld] pid-file=/var/run/mysqld/mysqld.pid gtid_mode=ON log_bin=/var/log/mysql/binlog log_slave_updates=1 binlog_row_image=minimal enforce_gtid_consistency expire_logs_days=4 server_id=4 skip-name-resolve=true binlog_format = ROW open_files_limit=150000 innodb_buffer_pool_size = 55G innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 2G innodb_file_per_table = 1 datadir = /var/lib/mysql max_allowed_packet=1073741824 character_set_server=latin1 collation_server=latin1_swedish_ci wsrep_cluster_address=gcomm://192.168.0.143,192.168.0.148,192.168.0.236 wsrep_provider =/usr/lib/galera4/libgalera_smm.so wsrep_node_address=192.168.0.148 wsrep_node_name=db11.example.com wsrep_slave_threads = 16 innodb_autoinc_lock_mode = 2 transaction-isolation=READ-COMMITTED wsrep_sst_method=xtrabackup-v2 pxc_encrypt_cluster_traffic=off wsrep_cluster_name=centos_cluster wsrep_provider_options="gcache.size=10G" thread_cache_size=50 max_connections = 500 sql_mode=IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO socket=/var/run/mysqld/mysqld.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [sst] compressor='zstd -2 -T6' decompressor='zstd -d -T6' backup_threads=6 [xtrabackup] parallel=6 ``` ---
**Author:** ![Mauricio\_Cacho](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mauricio_cacho/32/5859_2.png) [@Mauricio\_Cacho](https://forums.percona.com/u/Mauricio_Cacho)\ **Post date:** [November 13, 2022, 5:48pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/6 "2022-11-13T17:48:09Z")
Thanks for this. Based on the SHOW MASTER STATUS and Executed\_Gtid\_Set in the replica, you can see there’s nothing “new” in the primary that needs to be replicated. **Brief summary on GTIDs and auto\_position:** The replica retrieves the Executed\_Gtid\_Set from the primary, and compares it with its own Executed\_Gtid\_Set, and if there’s some GTIDs in the primary missing in the replica, it asks for them. You can manually compare the Executed\_Gtid\_Set (from master status in the primary and the show slave status in the replica) and you’ll see there’s nothing missing; actually the replica has more GTIDs executed. Take this for example: **772fd0ef-db6b-11e7-8394-de7434f4bcd0** In the primary, the GTIDs executed are: 1-62636948 In the replica, the GTIDs executed are: 1-91824198. This means the replica is ahead from its primary; also your replica has this GTID sets executed, that aren’t in your primary: ```auto 52bf7592-5a6d-11ed-9f81-001e67c47b04:1, 59ce7551-b19c-11ec-8d08-001e67c47b04:1-13, c7063da6-4f9c-11e9-924d-001e67c47b04:1-4, ``` Bottom line, the replication is working, but your data may not be as you think it is. This may be a backup isseue, or maybe your primary is not replicating properly? ---
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 13, 2022, 5:51pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/7 "2022-11-13T17:51:42Z")
The master is a very busy machine, with very frequent updates. Primary is working properly. I tried the backup via both xtrabackup (several times). I also tried doing a fullstop to the master and rsyncing over the files (took forever…), and got the same result. I think the issue must be on the primary not sending over the events, but why? The config, as you see above, is pretty vanilla. The binlogs are being written to the master server with seemingly no issue. ---
**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\ **Post date:** [November 15, 2022, 12:15pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/8 "2022-11-15T12:15:00Z")
Hi MeirW2, Do you have “log\_replica\_updates” enabled on every node of the PXC? Do you have a different server\_id in every node in the topology? (i.e all pxc nodes and all the replicas must have a different server\_id each). Please check above and confirm ---
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 16, 2022, 5:57am UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/9 "2022-11-16T05:57:16Z")
Yes, each of the servers has a separate id, and has log\_replicaa\_updates enabled. A development - I spun up a brand new server, added it to the cluster, and it was able to serve as a master for replication! So presumably something in the update process going from 5.7-\>8 seems to have broken something - maybe a missing library? It is Ubunutu 20.04, and all the mysql/percona packages between the 2 servers are the same. ---
**Author:** ![MeirW2](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@MeirW2](https://forums.percona.com/u/MeirW2)\ **Post date:** [November 16, 2022, 7:57pm UTC](https://forums.percona.com/t/mysql-8-replication-troubles/18519/10 "2022-11-16T19:57:20Z")
After another round of apt updates and a reboot, it seems that replication is now working as expected. I assume something was stuck in limbo, and needed a reboot to settle.