MySQL Forums - Replication

MYSQL 8.4.8 CE - Group Replication - read_only ON (2 replies)

Mircea Ispasoiu — Mon, 23 Mar 2026 16:21:41 +0000

Hello,

I have an issue with a cluster from 3 nodes and Group replication, oracle linux 9.7 and MYSQL CE 8.4.8.

Every time I restart the mysqld service the read_only and super_read_only remain ON.

If I check the cluster all nodes are online and primary and no errors (it`s a multi master).

What`s the problem ?

I`ve tried to set the /etc/my.cnf with setting:
read_only = OFF
super_read_only = OFF

but no success.

Once I restart mysqld service read_only came back to ON.

Thank you.

MySQL Replication - change replica iP (no replies)

Azhar Mat Zin — Fri, 10 Oct 2025 08:21:10 +0000

Hi,

The database is MySQL version 8.4.6

There is source and target database (the replica).

The replica will be relocated to different building and location, and will be using new IP. The question:

a) what is needed to be done before shutting down the replica database.
b) what is needed to be done after the restart of the replica database.

On the source / production database and on the replica database.

I have experience in Oracle/Data Guard but could use some tips. This will be a new database (for me) and a one off thing, so additional queries about getting info/setting would be helpful too.

Is it true that this is the only thing needed to be done?

-- Remove old grant
REVOKE REPLICATION SLAVE ON *.* FROM 'repl_user'@'old_ip';

-- Add new grant
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'new_ip' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

Also, since for NySQL IP for users to access from is stored, is this something I need to take into account in case of future "swing" (from source to replica) so this is also what needed to be updated?

Thanks.

Setting up replication, no errors but no data replicates (no replies)

David Horan — Tue, 09 Sep 2025 19:37:11 +0000

Trying to configure replication.
We used the existing dev/test DB VM server to make a clone and create a DB replica server. I dropped the two target DB's that we want to replicate. I used mysqldump to dump the existing database and recreate them on the replica_server.

If I stop and start replica this is the only message related to replication that I see on the replica_server:
2025-09-09T16:23:39.240573Z 25 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'replication_user@152.x.x.x5:3310' with server_uuid=5f89cf28-6f3c-11ed-83c5-00505683041f, server_id=1. Starting replication from file 'mysql_bin.000002', position '4478572'.

##### relevant information from the master server
mysql> show master status\G
*************************** 1. row ***************************
File: mysql_bin.000002
Position: 4938118
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 5f89cf28-6f3c-11ed-83c5-00505683041f:1-10430
1 row in set (0.00 sec)

mysql>
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.41 |
+---------------+--------+
1 row in set (0.00 sec)

### relevant information from mysql.cnf
# Enable GTID based replication
server-id = 1
log_bin = mysql_bin
gtid_mode=ON
enforce-gtid-consistency=ON

##### relevant information from the replica server. I cut some of the blank
##### variables, they have never come up in any google searches
### The replica
mysql> show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 152.x.x.x5
Source_User: replication_user
Source_Port: 3310
Connect_Retry: 60
Source_Log_File: mysql_bin.000002
Read_Source_Log_Pos: 4960447
Relay_Log_File: replicaserver-relay-bin.000011
Relay_Log_Pos: 482241
Relay_Source_Log_File: mysql_bin.000002
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB: app1_devdb,app2_devdb
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 4960447
Relay_Log_Space: 4961088
Until_Condition: None
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: 1
Source_UUID: 5f89cf28-6f3c-11ed-83c5-00505683041f
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
Retrieved_Gtid_Set: 5f89cf28-6f3c-11ed-83c5-00505683041f:1688-10459
Executed_Gtid_Set: 01bddbdf-8a85-11f0-9d64-005056909086:1-8,
5f89cf28-6f3c-11ed-83c5-00505683041f:1-3539156,
b390a816-acc1-11ea-9f97-005056aca952:1-301238
Auto_Position: 0
Get_Source_public_key: 0
1 row in set (0.01 sec)

#### NOTES from above I don't know where these two came from
#### 01bddbdf-8a85-11f0-9d64-005056909086
#### b390a816-acc1-11ea-9f97-005056aca952

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.41 |
+---------------+--------+
1 row in set (0.00 sec)

mysql>

### the relevant section mysql.cnf
# relay log location
relay_log = replicaserver-relay-bin
relay_log_index = /mysql01/data/replicaserver-relay-bin.index

# Enable GTID based replication
server-id = 2
gtid_mode=ON
enforce-gtid-consistency=ON
read-only=1

# Only Replicate the following DBs.
replicate_do_db = app1_devdb
replicate_do_db = app2_devdb

Additional info, after the initial attempt I have reset the master and the replica server. I have reset just the replica.
After making changes on the master and them not showing up on the replica, I have stopped replica, did mysqldump to copy over the updated DB and used mysql change replication source updating the source logfile and source log position with the new information.

I saw this as a basic test of replication: create table test_replication (id INT); But the table does not replicate to the replica server.

From linux CLI I tested the replication user from replica server to master using:
mysql -H 152.x.x.x5 -P 3310 -u replica_user -p and I can successfully connect.

Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' (no replies)

Sohail Jafferi — Thu, 26 Jun 2025 05:50:39 +0000

Hi Expert,

Appreciate if some one can assist me to fix this issue, Mysql Replication Master database, one table have huge deletion every time , which creates the replication inconsistency on slave during configuration ,how to fix this issue

025-06-23T10:50:21.550781Z 7 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000142, end_log_pos 557157769; Could not execute Delete_rows event on table salesforce.customer_details_log; Can't find record in 'customer_details_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 557157769, Error_code: MY-001032

Thanks

MYSQL 5.7.44 HA cluster active/active problems (no replies)

Mircea Ispasoiu — Wed, 11 Jun 2025 10:25:58 +0000

Hello,

I have an issue and I hope to find a solution.

I need to setup an active/active DB cluster, with 2 linux servers nodes that must write at the same time into mysql 5.7.44 database (aprox 1 TB size).

The filesystem on my databse are MyISAM and InnoDB.
Database doesnt have Primary Keys and NO posibility to add it.

Is there any posibility to set up a functional active/active HA system with these specifications?

Everywhere I look it`s says that is impossible cause of primary keys missing.

Thank you.

question about loose-group_replication_start_on_boot when all members reboot simultaneously (no replies)

Fabian Santiago — Sat, 19 Apr 2025 21:20:13 +0000

hello. i did some searching and couldn't find mention of this. apologies if there is somewhere else...

all of this has taken place on 2 servers with matching system specs:
Ubuntu 24.04.2 LTS
kernel: 6.8.0-58-generic
mysql Ver 8.0.41-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

i setup a multi-primary replication group with 2 members. basic, mostly default boiler plate config options used:

[mysqld]
## binlog_format = MIXED

# General replication settings
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = ON
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
loose-group_replication_group_name = "xxx"
loose-group_replication_ip_whitelist = "xxx"
loose-group_replication_group_seeds = "xxx"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = x
bind-address = "xxx"
report_host = "xxx"
loose-group_replication_local_address = "xxx"

the config parameters i'm concerned with is "loose-group_replication_start_on_boot = ON".

if both servers reboot in too close proximity, and thus boot without another master being online, they come up with replication stopped. i believe this is to be expected. when rebooted one at a time, and with enough time in between each reboot that the whole repl group doesn't go down at once, it seems to come back up ok.

is there any way anyone has accomplished getting the repl group to start on its own in this scenario without logging into the cli and issuing the commands to re-bootstrap, start it? i'm curious. thanks.

Any workaround for MYSQL 8.0 MGR rolling upgrade (no replies)

Jason Chen — Tue, 31 Dec 2024 03:43:57 +0000

The Problem like:

I perform the rolling upgrade for mysql minor version 8.0.35 to 8.0.36.

The steps like:
1) upgrade the standby B to to 8.0.36.
2) upgrade the standby C to to 8.0.36.
3) Now am trying switchover from primary A to standby B by using function group_replication_set_as_primary .
But got error:
ERROR 3910 (HY000): The function 'group_replication_set_as_primary' failed. Error processing configuration start message: The appointed primary member has a version that is greater than the one of some of the members in the group.

So is there any workaround for the rolling upgrade for MGR?

Thanks
Jason

Last_SQL_Errno: 1062, Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s) (no replies)

Jon Adams — Tue, 03 Dec 2024 22:51:33 +0000

My mysql replication (master - slave) is failing with following error:

Last_SQL_Errno: 1062

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000115, end_log_pos 468117420. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

I checked the bin log file and I see following

2024-12-03T21:48:34.088063Z 7 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000123, end_log_pos 515; Could not execute Write_rows event on table mydb.wp_options; Duplicate entry '_transient_jetpack_update_remote_package_last_query' for key 'wp_options.option_name', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's source log mysql-bin.000123, end_log_pos 515, Error_code: MY-001062

2024-12-03T21:48:34.088873Z 6 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'mysql-bin.000123' position 157

The problematic database is a WordPress database.

I did reset replica and restarted but still getting same error.

How do I fix it and also avoid this happening in the future as I've many wordPress databases and I'll have more in the future.

Plugin instructed the server to rollback the current transaction (1 reply)

Krishnadas K P — Thu, 29 May 2025 10:16:39 +0000

I have a 3 node MySQL cluster with group replication enabled. I am using MySQL version 8.0.39-0ubuntu0.24.04.2. I have setup the replication in multi-primary mode. However, my application is only connecting to one node and reading and writing from/to that single node. The rest of the 2 nodes are just replicating data and ready to serve writes anytime I point my application to it. This cluster works most of the time but when I apply large load to it, a number of transactions fail with the message "plugin instructed the server to rollback the current transaction". On reading about this, I could understand the issue can arise due to conflicting writes in multiple nodes. However, in my current setup, write is ONLY going to one node. There should not be a conflicting write from another node. When I change the setup to single primary, this error disappears. How do I solve this issue ?

Resync mysql replica in a GTID setup gives error HA_ERR_FOUND_DUPP_KEY with AUTOPOSITION (no replies)

Baptiste Mille-Mathias — Wed, 25 Sep 2024 10:28:40 +0000

Hello

I use a procedure based on the page http://web.archive.org/web/20230323121432/https://blog.pythian.com/mysql-streaming-xtrabackup-slave-recovery/ for years to sync a new replica or resync a broken replica for year without issue. This uses xtrabackup to save and send the mysql data to the slave, apply the redo log, and restart the slave with MASTER_AUTO_POSITION. Simple and effective.
xtrabackup is convenient for us, due to the size of the databases we have to transfer. we use this way "xtrabackup --backup --stream=xbstream --parallel=$(NB_PROC/2)"

mysql: 5.7.42
xtrabackup: 2.4.29

lately when I start the slave I get such error
==============================================
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table db_2.webhook_message_status; Duplicate entry '3304591' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001615, end_log_pos 5021
Skip_Counter: 0
Exec_Master_Log_Pos: 4692
Relay_Log_Space: 143514642
...
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table db_2.webhook_message_status; Duplicate entry '3304591' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001615, end_log_pos 5021
Replicate_Ignore_Server_Ids:
Master_Server_Id: 189087
Master_UUID: 144c0164-3223-11ef-8319-74563c5c838d
Master_Info_File: mysql.slave_master_info
==============================================

Looking on slave I confirm the entry with id 3304591 is already there

==============================================
+---------+---------------------+---------+--------+-----------------------------+---------------------+
| id | message_external_id | site_id | status | context | created_at |
+---------+---------------------+---------+--------+-----------------------------+---------------------+
| 3304591 | xxxxxxxxxxxxxxxxxxx | 483 | read | {"code":200,"title":"read"} | 2024-09-19 07:52:00 |
+---------+---------------------+---------+--------+-----------------------------+---------------------+
==============================================

so it seems the slave does not know anymore to properly position itself.

As the workaround I get the content of xtrabackup_binlog_info

==============================================
mysql-bin.001615 73610932 144c0164-3223-11ef-8319-74563c5c838d:1-14071690
==============================================

and I did this

==============================================
mysql> reset master
mysql> set global GTID_PURGED="144c0164-3223-11ef-8319-74563c5c838d:1-14071690"
mysql> start slave
==============================================

and now it works
==============================================
Master_Server_Id: 189087
Master_UUID: 144c0164-3223-11ef-8319-74563c5c838d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
...
Retrieved_Gtid_Set: 144c0164-3223-11ef-8319-74563c5c838d:14047201-14248907
Executed_Gtid_Set: 144c0164-3223-11ef-8319-74563c5c838d:1-14248907
Auto_Position: 1
==============================================

as an alternative solution I use pt-slave-restart to bypass all error id 1062, so after a while, the slave eventually is in sync, but I don't feel confident about the replica data integrity.

Do you have an idea about what could be the cause of this problem? It used to work fine, during the last 3 years we use without an issue. We did not changed major version of MySQL, or anytool involved.
is the "workaround" I'm doing is fine ? Do I have all master data on slave ?

best

MySQL Cluster won't start on docker (3 replies)

Thierry LESIRE — Fri, 06 Sep 2024 06:39:21 +0000

Hello,
I try to create a MySql InnoDB Cluster with containers.
I have 2 VM with Ubuntu 22.04, In each I have a container with MySql 8.0.39
I want to create a cluster with the 2 containers. But I failed to create.

dba.createCluster(clusterName, {localAddress:'mysqlClstr01:3306'})
A new InnoDB Cluster will be created on instance 'mysqlClstr01:3306'.

Disabling super_read_only mode on instance 'mysqlClstr01:3306'.
Validating instance configuration at mysqlClstr01:3306...

This instance reports its own address as mysqlClstr01:3306

Instance configuration is suitable.
* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'mysqlCluster' on 'mysqlClstr01:3306'...

Adding Seed Instance...
ERROR: Unable to start Group Replication for instance 'mysqlClstr01:3306'.
The MySQL error_log contains the following messages:
2024-08-23 14:40:57.147907 [System] [MY-013587] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2024-08-23 14:40:57.148434 [System] [MY-011565] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-08-23 14:40:57.149175 [Error] [MY-011735] Plugin group_replication reported: '[GCS] There is no local IP address matching the one configured for the local node (mysqlClstr01:3306).'
2024-08-23 14:40:57.149375 [Error] [MY-011674] Plugin group_replication reported: 'Unable to initialize the group communication engine'
2024-08-23 14:40:57.149392 [Error] [MY-011637] Plugin group_replication reported: 'Error on group communication engine initialization'
Dba.createCluster: Group Replication failed to start: MySQL Error 3096 (HY000): mysqlClstr01:3306: The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer. (RuntimeError)

I tried to pass an argument in my docker-compose to set the local address group-replication-local-address without success.

My docker compose:
volumes:
mysqlData:
name: mysqlData
external: true
mysqlLog:
name: mysqlLog
external: true
services:
mysql-server-1:
env_file:
- mysql-server.env
image: mysql:8.0
container_name: mysql-server-1
volumes:
- mysqlData:/var/lib/mysql
- mysqlLog:/var/log/mysql
network_mode: host
ports:
- "3301:3306"
- "3306:3306"
- "33060:33060"
- "33061:33061"
- "33062:33062"
command: ["mysqld","--server_id=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_replica_updates=ON","--user=mysql","--host_cache_size=0", "--authentication_policy=mysql_native_password","--group-replication-local-address='mysqlClstr01:33061'"]
restart: always
mysql-shell:
env_file:
- mysql-shell.env
image: mysql-shell
container_name: mysql-shell-1
volumes:
- ./scripts/:/scripts/
network_mode: host

Could you please help me?

Replication ignore table issue (1 reply)

Stefan Kuhn — Sun, 14 Jul 2024 22:53:30 +0000

Hi all,
I am having trouble with replication after a hardware upgrade. I have to say that I initially used mysql (years ago), all worked fine, I then used Mariadb for a while without issues, but now returned to mysql, mainly because it is default in the linux distribution I am provided with on the new hardware. Replication worked there for a few weeks, but now it is stuck and I have no idea what to do.
The setup it one master, one slave. The master is running fine and logging as required.
The slave server runs fine as well, no issues with the mysql instance other than the replication. The Slave_IO_Running says yes running and the relay log files are up to date.
Problem is the Slave_SQL_Running says no, and there is an error. If I do:
select * from performance_schema.replication_applier_status_by_worker;
which seems to be the best way to get a meaningful error message, I get:
Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000021, end_log_pos 36111459; Could not execute Update_rows event on table nmrshiftdb.SESSIONS; Can't find record in 'SESSIONS', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log binlog.000021, end_log_pos 36111459
There is other stuff, but this is the core message, I believe. SESSIONS is a table name in my database.
If I take the binlog from master and look at it, the command at that position is a commit. The command before is an "update SESSIONS", and before that there is begin.
From this, I take that the update is the problem. This statement works on the server, and the tables are identical on master and slave.
The first issue is: This table should be ignored. If I do a "show slave status", I get "x.y,x.SESSIONS" in "Replicate_Ignore_Table", where x is my database name and y is another table. It seems the ignore table is ignored (haha). Is there any explanation for this? Something I can do? I am lost here. I tried leaving out the database name from ignore table and played with it, but nothing seems to work, sessions is still executed, or attempted to be executed. Plus it seems it worked initially, since the table is heavily used, I can't imagine that it was not used when the replication worked initially.
Secondly (and this is more an observation) looking at the binlog, the update statement is not the original one, but it has all columns in the update and the where statement. Since the table is different on master and slave (that's why I use ignore), it makes sense the replication "can't find the record". I wasn't aware replication works like this, I thought it logs the original statement (which is "update SESSIONS set x=... where y=...", whereas the logged statement has where x=...,y=...,z=...). I can see that if the table would be the same, the replicated statement would work, so this is not really the issue here.

So in short, are there any ideas why my ignore table doesn't work? If you need more information, please ask.
Thanks

Replication Startup and related performance parameters (no replies)

John Maag — Wed, 10 Jul 2024 13:33:51 +0000

We have been running on Mysql 5.1 for a long time. Recently with an OS update are running on 8.0.32.

It appears 5.1 was a little quicker not much but a second or two to send data from the active to the standby database.

I would like to find a way to check settings to either confirm or deny this. I found SOURCE_HEARTBEAT_PERIOD and it is unset in the V8 I am guessing I need to set this to get polling set up to check a little quicker for new data.

I would appreciate any guidance anyone can give.

Troubleshoorting replication slowness (no replies)

John Maag — Wed, 26 Jun 2024 20:33:06 +0000

I am new to mysql but experienced oracle dba.

We have ported from mysql 5 to mysql 8 and am seeing lags in replication meaning when we check status, it is reporting as broken briefly.

I would appreciate anyone who can point me in some basic checks. It is not hardware performance. disk, network, cpu, memory all show basically nothing happening on both nodes.

FYI, the process of replication worked seamlessly on v5..

Master-Master MySQL DB Replication (no replies)

Rudresh SN — Tue, 04 Jun 2024 06:43:37 +0000

Hello, I am having two nodes of Zabbix which are in pacemaker cluster having resources for virtual IP address, Zabbix server and for front end. In both the nodes, we have MySQL server installed. And we have master-master DB replication established between the MySQL server components present in the two nodes of Zabbix. The approach which I followed to establish the replication is, did some configuration in the mysql server configuration file, ran the sql query "change master to" and provided the master_host, master_user, master_password, master_log_file and master_log_pos values. and started the slaves in both the zabbix nodes.

The issue which I am facing is once after failover switch occurs, at that time I am seeing some deleted data's are appearing as a fresh data with new timestamp in the Zabbix MySQL DB tables (problem, history_text and events table etc,.) of Zabbix nodes.

So any inputs to resolve this kind of issue will be more appreciable. Thanks.

Mysql 8.0 point in time recovery error - sequence number is inconsistent (no replies)

Ron Tai — Wed, 27 Mar 2024 15:15:03 +0000

Using relay logs to speed up the process of point in time recovery I run into an error.
I’m testing this method to recover binlogs.
https://lefred.be/content/howto-make-mysql-point-in-time-recovery-faster/

But for each relay binlog being recovered I get one error.
ERROR] [MY-010411] [Repl] Transaction’s sequence number is inconsistent with that of a preceding one: sequence_number (1) <= previous sequence_number (1929667)
The previous sequence number is different each time but the preceding sequence of (1) is always the same.
I thought it might be that slave_preserve_commit_order was not enabled, but even with it enabled I still encounter that error.
Note: binlog_order_commits set is not set to ON for the Master if that also needs to be set to prevent this issue?

How do I tell what sequence number in the binlog is causing the issue given the replica and master status info below to calculate the offset? Also why is there a sequence number error?

Here is information from replica status and master status.
Master_Log_File: bin-log.006410
Read_Master_Log_Pos: 708184140
Relay_Log_File: bin-log.000002
Relay_Log_Pos: 1073742162
Exec_Master_Log_Pos: 1073742162

Master status
File: bin-log.006410
Position: 708184140

The start of the master binlog has this position info.
at 4
#240326 10:46:13 server id 12345678 end_log_pos 126 CRC32 0x7e689634 Start: binlog v 4, server v 8.0.32-24 created 240326 10:46:13 at startup
Warning: this binlog is either in use or was not closed properly.
ROLLBACK/!/;
at 126

The end has this info.
at 708184051
#240323 21:34:38 server id 12345678 end_log_pos 708184140 CRC32 0x72090bb8 Query thread_id=2369892 exec_time=221900 error_code=0
SET TIMESTAMP=1711254878/!/;
COMMIT
Thanks for the help.

mysql 8.0.32 mysqlbinlog point in time recovery very slow after upgrade (no replies)

Ron Tai — Wed, 27 Mar 2024 15:01:35 +0000

After upgrading from mysql 8.0.21 to mysql 8.0.32 and using mysqlbinlog to test point in time recovery it takes over 24 hours with 8.0.32, but with 8.0.21 it took less than 6 hours. I am recovering the same amount of binlogs and each binlog is 1 GB large. What might be causing such a large difference in time?
I am using this method.
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Thanks!

Difficult to debug replication failure between MySQL 5.7 primary and MySQL 8 replica (no replies)

Rahul Agrawal — Sat, 16 Mar 2024 03:33:42 +0000

We have a MySQL primary running on 5.7.40. We are in process of testing the upgrade to 8.0.36 (this is on AWS RDS). We had 2 MySQL-8 replicas and 2 MySQL-5.7 replicas replicating from the primary. At almost the same time, both the MySQL 8.0 replicas stopped replicating and complained on `HA_ERR_FOUND_DUPP_KEY`

```
Replica SQL for channel '': Worker 2 failed executing transaction '87953f5d-7595-11ed-830d-02f4790d85ab:57805008598' at source log mysql-bin-changelog.676514, end_log_pos 52858646; Could not execute Write_rows event on table ebdb.bike_issues; Duplicate entry '177235118' for key 'bike_issues.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's source log mysql-bin-changelog.676514, end_log_pos 52858646, Error_code: MY-001062
```

Weirdly though the replicas complained on different key values, but the timestamp was nearly the same (few ms apart). The MySQL 5.7 replicas were fine, so probably there was no hiccups on the primary side. Nothing shows up in the logs of primary around this time either.

The table, it complained on, is very commonly written table and we had this MySQL 8 replicas running for over a week now, without any replication issues. We do Row-based, GTID based replication (gtid_mode ON, enforce_gtid_consistency ON)

I was able to resume replication by setting `slave_exec_mode` to `IDEMPOTENT` temporarily. When I check the error logs after the replication was in sync, I didn't see errors for the first replica's key in the second replica's error logs & vice versa i.e. they both failed on different keys and did not overlap. Could this be some issue on the replication receiver part? Or possibly some bug due to version mismatch? Or some MySQL variable mismatch?

How can I debug this further? What could have possibly caused this blip?

fail to run clone command on mysql8.0.31 (no replies)

Liang Cheng — Thu, 14 Mar 2024 11:49:06 +0000

Hi,
I'm trying to make clone work on my local test environment.
I have two mysql servers, one run as primary and another run as slave.
1. First, I create the one account named 'clone_user' for clone
mysql> show grants for clone_user;
+-----------------------------------------------------------+
| Grants for clone_user@% |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `clone_user`@`%` |
| GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO `clone_user`@`%` |
+-----------------------------------------------------------+
2. While group_replication is running on slave, I run clone command. It failed
mysql> clone instance from clone_user@10.50.10.249:3308 identified by '***';
ERROR 3875 (HY000): The clone operation cannot be executed when Group Replication is running.

3. So I stop group_replication and reset slave
mysql> stop group_replication;
Query OK, 0 rows affected (4.08 sec)

mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
4. Run clone again, it fails again because the slave is on super-read-only mode

mysql> clone instance from clone_user@10.50.10.249:3308 identified by '***';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Please help if you have any idea. Thank you very much

Any workaround to execute one transaction on slave (no replies)

Liang Cheng — Sun, 10 Mar 2024 06:21:08 +0000

Hi, All
We have one MySQL8 innodb cluster environment. For some unclear reason, the cluster hit some problems. We tried to restart all servers, however the slaves could not be restarted. We checked all 3 servers, the clone is installed on slave2, but not intalled on both slave1 and primary db. We can install clone plugin on primary server, however since the group_replication is failed to start on slave1 and it's in read-only mode, we could not install clone plugin.
I think sometimes we may have other similar cases, that is, slave is missing one record and we know that if we can insert such record we can bring this broken slave back to the cluster, but it's not easy to do this, at least on MySQL8.
Please share your ideas if you know how to do(workaround) it. Thanks a lot.

2024-03-07T03:34:55.348541Z 2 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address m5128:3308.'
2024-03-07T03:34:56.349399Z 0 [Warning] [MY-013470] [Repl] Plugin group_replication reported: 'This member will start distributed recovery using clone. It is due to no ONLINE member has the missing data for recovering in its binary logs.'
2024-03-07T03:34:57.353749Z 0 [System] [MY-013471] [Repl] Plugin group_replication reported: 'Distributed recovery will transfer data using: Cloning from a remote group donor.'
2024-03-07T03:34:57.354259Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to m3127:3308, m5128:3308 on view 17097824372187244:2.'
2024-03-07T03:34:57.354703Z 32 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2024-03-07T03:34:57.381436Z 32 [ERROR] [MY-011569] [Repl] Plugin group_replication reported: 'Internal query: CLONE INSTANCE FROM 'mysql_innodb_cluster_1'@'m5128':3308 IDENTIFIED BY '*****' REQUIRE NO SSL; result in error. Error number: 3862'

MySQL Master-Replica incremental backups (1 reply)

Bob Stoneman — Wed, 28 Feb 2024 07:32:09 +0000

I have a client who had me create a Mysql 8.0.35 row-based (not gtid) replication setup. They want the following:

MySQL -
15 minute transaction logs
Nightly incrementals
Weekly fulls

Should I "cp" replica binary logs or use mysqlbinlog (syntax ?) ?

Currently, I am running mysqldump backups every hour on the Replica.

date=$(date +%Y-%m-%d-%H.%M.%S)
/usr/bin/mysql --vertical -e "SHOW REPLICA STATUS" \
| egrep 'Relay_Source_Log_File|Exec_Source_Log_Pos' > /mnt/sql_backups/all-databases_dumpall_02_replica_status_logfile_pos_$(date +%Y-%m-%d-%H.%M.%S).txt
/usr/bin/mysqladmin stop-replica
/usr/bin/mysqldump --all-databases > /mnt/sql_backups/all-databases_dumpall_02_$(date +%Y-%m-%d-%H.%M.%S).sql
/usr/bin/mysqladmin start-replica

Failover/Switchover steps for Single Source Single Replica configuration (5 replies)

Bob Stoneman — Sun, 28 Jan 2024 19:33:41 +0000

I am looking for Failover/Switchover steps for a Single Source Single Replica configuration. Everything I have found is at minimum Single Source Two Replicas.

I need to know how to Failover/Switchover when a Single Source fails/crashes and there is only a Single Replica to Failover/Switchover to :)

Configure replication on the slave/replica server using the filename and position (1 reply)

Bob Stoneman — Sat, 20 Jan 2024 19:22:00 +0000

I'm running the following while setting up replication and I get the following error every time ?

CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.50.11.11',
SOURCE_USER='cloud-repl', SOURCE_PASSWORD='*****************',
SOURCE_LOG_FILE='mysql-bin.000051', SOURCE_LOG_POS=40474608;

Replica Server error log:
2024-01-20T18:48:36.203277Z 75 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-01-20T18:49:29.833413Z 76 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-01-20T18:50:49.893578Z 76 [ERROR] [MY-010717] [Repl] Error reading replica worker configuration
2024-01-20T18:50:49.893647Z 76 [ERROR] [MY-010418] [Repl] Error creating applier metadata: Failed to initialize the worker info structure.

I can't find anything on this anywhere. Any guidance would be appreciated :)

Removing master status (no replies)

Robert Fitzpatrick — Fri, 01 Dec 2023 19:08:39 +0000

Not sure how it happened, I copyied a KVM VPS while shut down where there was a MySQL 5.7 database I want to test upgrade to 8.0 on FreeBSD 13.2. While it also has a jailed mysql process running on port 3307 setup as a slave to another master, the database running on the default port 3306 is a standalone database with no replication set up at all.

After getting the copied QEMU disk booted with its own IP address and the jail disabled on boot, I find in phpmyadmin where the Replication is set as a master and slave configurations. I went back and checked the source VPS to confirm it has no Replication configuration at all. I did the following to stop and reset the slave...

STOP SLAVE;
RESET SLAVE;
RESET SLAVE ALL;

That removed the slave configuration, but the master remains...

root@localhost [(none)]> show master status;
+---------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000003 | 157 | | | ca3f72cd-03be-11ed-97ff-5254006b63d5:1-9387 |
+---------------+----------+--------------+------------------+---------------------------------------------+

I found this should work? But not...

root@localhost [(none)]> CHANGE MASTER TO MASTER_HOST='';
ERROR 1210 (HY000): Incorrect arguments to SOURCE_HOST
root@localhost [(none)]> show master status;

Not sure how this Replication got configured with this mysqld config? I tried to comment out the relay-log, sync_binlog and sync_relay_log.

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind-address = *
basedir = /usr/local
datadir = /www/db/mysql
tmpdir = /www/db/mysql_tmpdir
secure-file-priv = /www/db/mysql_secure
relay-log = /www/db/log/mysql-relay-bin.log
log-output = FILE
general-log = 1
general-log-file = /www/db/log/general.log
relay-log-recovery = 1
slow-query-log = 1
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
expire_logs_days = 30
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = OFF
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
open_files_limit = 32768
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M
query_cache_type = 0
query_cache_size = 0
long_query_time = 0.5
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /www/db/mysql
innodb_log_group_home_dir = /www/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 2
skip-symbolic-links
tls_version = TLSv1.2

Any other pointers regarding the upgrade are much appreciated.

Replication fails with "Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs" (no replies)

Shobhana Sriram — Fri, 24 Nov 2023 17:00:18 +0000

I have 2 master instances (M1 and M2) with 1 replica each (R1 and R2) - all running MySQL 8.0.34 version with GTID enabled in all. All schema names are unique in M1 and M2 and there is no overlap. Also, R1 is configured to ignore replicating one schema (say myExcludedSchemaName).

I wanted to copy a few tables from M2 to M1. So I executed the following mysqldump command in R2:

sudo mysqldump -umyUserName -p myExcludedSchemaName myTableName1 myTableName2 myTableName3 myTableName4 --lock-tables=false > partialDump.sql

There was a warning when this command was executed:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

However, the dump file got generated. So I tried to restore these tables in M1 using the following command:

mysql -umyUserName -p myExcludedSchemaName < partialDump.sql

This failed with the following error:

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

So I regenerated the dump again by passing another flag: --set-gtid-purged=OFF

There were no warnings when the dump was generated or restored in M1. However, the replica has stopped after this. The I/O thread has stopped with the following error:

Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the source's error log or the manual for GTID_SUBTRACT'

How can I resolve this error?

Transaction inconsistency in Group Replication with AFTER mode (no replies)

Ankur Shukla — Thu, 16 Nov 2023 09:54:42 +0000

Replication Error (no replies)

Piseth Ben — Sat, 30 Sep 2023 09:28:35 +0000

Hello All,

I got an issue while configure replication on mysql 8.0.33 community version.
I see the error log in replica server as below.

2023-09-30T08:57:41.613111Z 23 [System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL '' executed'. Previous state source_host='10.11.21.114', source_port= 17567, source_log_file='mysql-bin.000003', source_log_pos= 157, source_bind=''. New state source_host='10.11.21.114', source_port= 17567, source_log_file='mysql-bin.000004', source_log_pos= 157, source_bind=''.
2023-09-30T08:58:04.915579Z 24 [Warning] [MY-010584] [Repl] Replica I/O for channel '': Get source SERVER_ID failed with error: , Error_code: MY-001159
2023-09-30T08:58:04.916362Z 24 [Warning] [MY-013120] [Repl] Replica I/O for channel '': Source command COM_REGISTER_REPLICA failed: failed registering on source, reconnecting to try again, log 'mysql-bin.000004' at position 157, Error_code: MY-013120
2023-09-30T08:58:23.899753Z 24 [Warning] [MY-010584] [Repl] Replica I/O for channel '': Get source clock failed with error: , Error_code: MY-001159

Can you help to check.

Thanks,
Piseth

Replication replay errors? (no replies)

Jason K — Fri, 29 Sep 2023 19:47:45 +0000

I have some weirdness I am struggling with.

I have one slave database (8.0.32) replicating multiple remotes (different sites) databases.

I have a specific remote master database (8.0.34) where the slave has stopped and thrown errors twice in the last week.

However, these errors are really weird. They include 'duplicate entries' for tables with auto_increment ids. Entries that where NEVER in the slave database to begin with.

Its almost as if the slave database added a bunch of entries and then started to replay the logs trying to re-enter those rows?

If I delete those rows on the slave database then I can restart the slave.

Thoughts?

ps. Three times :-(

[REPLICATION] : replica hangs on Applying batch of row changes (update) (1 reply)

christophe offroy — Mon, 20 Nov 2023 11:43:57 +0000

MySQL Replication Observability (no replies)

Edwin Desouza — Thu, 24 Aug 2023 15:55:21 +0000

MySQL Replication Observability
- https://blogs.oracle.com/mysql/post/mysql-8-and-replication-observability