I have percona xtrabackup installed in my machines. My local machine IP address is 172.17.130.55. The database that i need to backup is in 172.17.130.58. I use the following command in machine 172.17.130.58 .
xtrabackup --host=127.0.0.1 --port=3306 --user=root --backup --no-server-version-check --datadir=/home/liugang/data/sqlnode1/ --stream=xbstream --target-dir=./ | ssh root@172.17.130.55 " cat - > /home/liugang/data/xbackup1/$(date +%Y-%m-%d_%H:%m:%S).xbstream"
I found that when the xtrabackup complete,
.....
2023-05-22T14:47:03.967670+08:00 0 [Note] [MY-011825] [Xtrabackup] Streaming <STDOUT>
2023-05-22T14:47:03.967681+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Streaming file <STDOUT>
2023-05-22T14:47:04.968227+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (81754722832) to (81754722852) was copied.
2023-05-22T14:47:05.175738+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
the remote machine(172.17.130.55 ) still write disk. which means if the remote machine was break down due to a power failure, the remote machine data received from source machine(172.17.130.55 ) was not complete.
if dba use the data to restore , maybe it cannot restore backup data normally. even though the dba get message: [Note] [MY-011825] [Xtrabackup] completed OK! It does not make sense.
Could anyone please help me with this one.
Thank you for the response.
Hello gangliu and welcome to the Percona Community,
Thank you for posting an interesting question here which has even more interesting answer 
So, I encourage you to go through the documentation that explain “how xtrabackup works”.
Specifically the 4th para where it reads
At the same time, Percona XtraBackup runs a background process that watches the transaction log files, and copies changes from it.
Coming to your question:
the remote machine(172.17.130.55 ) still write disk. which means if the remote machine was break down due to a power failure, the remote machine data received from source machine(172.17.130.55 ) was not complete.
In case of power failure on backup server, your backup would fail as well and you will not really see “completed OK!”. Such backups are neither complete nor useful and you’re right that it will not be a useful backup. But note that DBA will not get the message “completed OK!”. (You might want to test shuttingdown mysql while your test backup is running)
In case of power failure on Primary, ofcourse the backup will not fail. There is no reason for it to fail. You will still be able to restore the backup but it might not be “up-to-date”.
As you noted that this is your architecture (as I understand it):
172.17.130.55 -> 172.17.130.58
If your backup is on 172.17.130.58 and your primary crashes, ofcourse your backup will not be sufficient to make a complete restore. You will also need point in time restore. I encourage you to read an awesome blog by Matthew about Backup Best Practices. The scenario you’re talking about needs point in time configuration and you might want to think about binary log backups.
Hope this helps.
Thanks,
K
Thank you for the response.
Maybe I didn’t explain it clearly.
my architecture :
172.17.130.58 -> 172.17.130.55
I use the following command in machine 172.17.130.58:
xtrabackup --host=127.0.0.1 --port=3306 --user=root --backup --no-server-version-check --datadir=/home/liugang/data/sqlnode1/
--stream=xbstream --target-dir=./ | ssh root@172.17.130.55 " cat - > /home/liugang/data/xbackup1/$(date +%Y-%m-%d_%H:%m:%S).xbstream"
when the command finish and print the message “completed OK!”. It will not grant data in machine 172.17.130.55 sync to disk.
if the machine 172.17.130.55 was break down due to a power failure, the data in machine 172.17.130.55 was not complete.
Hey gangliu,
“when the command finish and print the message “completed OK!”. It will not grant data in machine 172.17.130.55 sync to disk.”
Yes, so if the backup source crashes, it will fail. I did not understand what you meant by “sync-to-disk”!
When I said 172.17.130.58 -> 172.17.130.55, I mean a replication setup. I wonder when you say " You have architecure 172.17.130.58 -> 172.17.130.55" and then you are streaming backup from 172.17.130.58 to 172.17.130.55!! Why?
If you want to constantly replicate data from source to replica, you should configure MySQL replication.
Thanks,
K
I do not want to constantly replicate data from source to replica.
I start mysql instance in machine 172.17.130.58 and I want to backup the data int this instance to data directory (/home/liugang/data/xbackup1/) in remote machine 172.17.130.55.
I use the following command in machine 172.17.130.58:
xtrabackup --host=127.0.0.1 --port=3306 --user=root --backup --no-server-version-check --datadir=/home/liugang/data/sqlnode1/
--stream=xbstream --target-dir=./ | ssh root@172.17.130.55 " cat - > /home/liugang/data/xbackup1/$(date +%Y-%m-%d_%H:%m:%S).xbstream"
when the command finish and print the message “completed OK!”. It will not grant data in machine 172.17.130.55 sync to disk.
if the machine 172.17.130.55 was break down due to a power failure, the data in machine 172.17.130.55 was not complete.
It is not recommended to backup data from primary server. Best is:
- Setup replication
- Setup backup on replica
- Configure binary log backups
Update:
Ofcourse this goes without saying that we have to ensure that the replica that we use for backup is up-to-date and fully consistent with the primary. We can always verify this with two of the Percona Tools, checksum with pt-table-checksum and sync with pt-table-sync.
Hi @gangliu
In addition to what @kedarpercona said, the last part of xtrabackup, once it has copied all the database files, it generates metadata files required to execute the --prepare operation to restore the backup, if you had a crash on destination server in between the time the source server displayed Compled OK and sync to disk on destination, you will not be able to --prepare your backup.
Another possibility is to stream the backup to a cloud provider and retrieve it when you need to provision a new server.
Hi,
this would be exactly the same if the backup was saved locally. The “cat” operation only writes data to the OS write cache on 172.17.130.55 as it would locally. The “completed OK!” message is delivered on stderr. By default the OS can take up to 30s to flush its dirty buffers. This can be improved by lowering the value of dirty_expire_centisecs in proc or, maybe simpler but untested, changing the command you use to:
xtrabackup --host=127.0.0.1 --port=3306 --user=root --backup --no-server-version-check --datadir=/home/liugang/data/sqlnode1/
--stream=xbstream --target-dir=./ | ssh root@172.17.130.55 " (cat - ; sync) > /home/liugang/data/xbackup1/$(date +%Y-%m-%d_%H:%m:%S).xbstream"
when the command returns, data has been flushed to storage.