My backup was made on the MySQL slave database. When I was preparing the backup file, it prompted “start shutdown”, and then it has been showing “Waiting for change buffer merge to complete number of bytes of change buffer”, but this number keeps increasing and decreasing. I suspect whether it is still synchronizing with the master database during the prepare process.
Here are note info:
2024-07-30T17:43:28.535402+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown…
2024-07-30T17:44:28.536153+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26059
2024-07-30T17:45:02.912677+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:45:28.536472+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 25672
2024-07-30T17:46:28.536851+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 25046
2024-07-30T17:46:37.242152+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:47:28.537042+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 24800
2024-07-30T17:48:11.466264+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:48:28.537710+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26506
2024-07-30T17:49:28.537857+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26222
2024-07-30T17:49:45.659260+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:50:28.538629+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26147
2024-07-30T17:51:19.890650+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:51:28.539107+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 24990
2024-07-30T17:52:28.539204+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26118
2024-07-30T17:52:54.088733+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:53:28.539744+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26763
2024-07-30T17:54:28.304980+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:54:28.540255+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 25321
2024-07-30T17:55:28.540414+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26119
2024-07-30T17:56:02.526305+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:56:28.541092+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 26168
2024-07-30T17:57:28.541436+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 24441
2024-07-30T17:57:36.744417+08:00 0 [Note] [MY-013073] [InnoDB] Waiting for master to exit.
2024-07-30T17:58:28.541860+08:00 0 [Note] [MY-012874] [InnoDB] Waiting for change buffer merge to complete number of bytes of change buffer just merged: 25650
You do not need to shut down MySQL to take a backup. What tool are you using?
When the prepare operation is executed, master-slave data synchronization will be performed with the MySQL master node. Is there a command to prohibit master-slave synchronization during the prepare process?
That’s not correct. When you run xtrabackup --prepare, MySQL should not be running at all because you are about to restore this backup to this server, so MySQL must be stopped. You cannot restore a backup to a running MySQL server.
You can take a backup with running MySQL, you cannot restore a backup with running MySQL.
When I execute xtrabackup --prepare --export --apply-log-only, it keeps showing as follows
The data volume behind “this change buffer just merged:” will decrease and increase from time to time.
If I reset the master-slave synchronization information of the current MySQL when performing --backup again, the problem of “Waiting for change buffer merge to complete number of bytes of change buffer just merged” will not occur during the subsequent --prepare.
- Why are you using
--export? Are you trying to restore a single table? If not, remove that flag. - Why are you using
--apply-log-only? Is this an incremental backup you are merging into a full? If not, remove this flag. - I would not use 8.3.0-Innovation. This version is a “public beta”. You should be using 8.0.* for anything production until Percona 8.4 is released.
yes,i want to get cfg file and ibd file to restore a table
yes,incremental backup
my mysql version is 8.3.0
I tried to wait for the merge to end, but as described in my problem log, the change buffer kept growing repeatedly. When I cleared the master-slave information before executing the backup, this problem did not occur. Therefore, I am not sure if the key point is that during the preparation process, the xtrabackup tool synchronized data with my master database, which caused the change buffer to remain unable to merge successfully
Xtrabackup does not make any connections to any MySQL databases during the prepare phase. It only operates on the local data.
Can you try without --export and --apply-log-only just as a test?
I tried to reproduce this issue in the testing environment, but I was not successful. The testing environment can successfully prepare using these three parameters
Is it possible that this problem occurred because there were too many data changes during the backup period in the official environment, but why does the quantity after the merge repeatedly increase and decrease? Shouldn’t it be decreasing
When I use --apply-log-only, this problem occurs. Is this “Waiting for change buffer merge to complete number of bytes of change buffer just merged” a normal phenomenon? What I don’t understand is that if this merge capacity shows the already merged capacity, then it should keep increasing until the merge is completed. If it shows the unmerged capacity, then it should keep decreasing until the merge is completed. Why does it increase and decrease sometimes?
--apply-log-only should only be used when you have multiple intermediate incrementals. If you take Full/Sunday, Incr/Mon, Incr/Tues, Incr/Wed, then you would need to use --apply-log-only when preparing Mon, and Tues, but not on Wed. If you do not have this situation of multiple incrementals, you should not be using this flag.
i have multiple intermediate incrementals
so, how to resolve my problem
I tried to wait for it to end, but after half an hour, the number of this merge kept changing repeatedly. Is this a normal phenomenon?
Sir, have you found a solution to this problem?