Weve been testing xtrabackup and have an odd issue. We’re using the latest version in the OpenSuse repo’s for xtrabackup
On the production server we have a bash script that cleans up our backup directory (to ensure no old data), runs a backup and specifies the target directory with the date and time as the folder name, prepares the backup, then tars the folder, THEN finally uses scp to copy it to an offsite backup server.
The off-site server then stops mysql, moves the current data to the tmp folder, gets the latest tar file sent over, and untar’s it, applies the log, and then does a copy back.
the main point of this is to have an offisite backup of the mysql server’s (there’s more than one), not to create a hot site. I’m doing the copy back to verify the backup is good.
The problem is it seems to be hanging on to old data??? How I know is there are tables that log actions in the application, like log_page_views. The log i’m using to verify the data stops at 8/23 1700 EST. The production system has log entries going up until close of business yesterday. The backup i ran yesterday, restored and the last time stamps in it are 8/23 1700… so so odd.
production server backup script
today=$(date "+%m_%d_%Y-%H_%M_%S")
##remove previous backup j- 7/27 turned off - TS
rm -rf /data/backups/*
innobackupex --user=mysqlbackup --password=xxxxxxxx /data/backups/new_backup/$today --socket=/var/run/mysql/mysql.sock --no-timestamp
#innobackupex --user=mysqlbackup --password=tidalwave --no-timestamp --backup --target-dir=/data/backups/new_backup/$today
innobackupex --prepare --target-dir=/data/backups/new_backup/$today/
#innobackupex --prepare --target-dir=/data/backups/new_backup/$today/
#innobackupex --apply-log /data/backups/restore
filename="$today.tar.gz"
cd /data/backups/new_backup/$today
tar -cvzf ../../$filename .
#rm -rf /data/backups/new_backup/$today
scp -Cr /data/backups/$filename root@10.7.0.26:/data/backups/
find /data/backups/new_backup/ -mmin +180 -type f -exec rm -fv {} \;
off-site server copy back script
set -x
date >> /root/date.txt
rm -rf /tmp/mysql/*
mv /var/lib/mysql/* /tmp/mysql/
cd /data/backups/
newest=`ls -t *.tar.gz | head -1`
echo "FILE:"$newest
mkdir restore
echo $newest
tar -xvf "/data/backups/$newest" -C /data/backups/restore
innobackupex --apply-log /data/backups/restore
innobackupex --copy-back /data/backups/restore
rm -rf /data/backups/restore
chown -R mysql: /var/lib/mysql
systemctl start mysql.service
date >> /root/date.txt[U][B]
[/B][/U]