Not the answer you need?
Register and ask your own question!

testing xtrabackup and have an odd issue

tksintenntksintenn EntrantCurrent User Role Beginner
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


cd /data/backups/new_backup/$today

tar -cvzf ../../$filename .

#rm -rf /data/backups/new_backup/$today

scp -Cr /data/backups/$filename root@

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]


  • tksintenntksintenn Entrant Current User Role Beginner
    First problem was I used a how to from percona on innobackupex and xtrabackup, not realizing they had different commands. Read the first is depreciated.
    So i've switched my scripts over to xtrabackup. xtrabackup seems to not have some features of innobackupex, such as not creating a directory for backup if it doesn't exist?
  • tksintenntksintenn Entrant Current User Role Beginner
    Switched to xtrabackup and still restoring a fresh full backup as if it's 8/23 17:06 (7PM). I'm doing something wrong...
  • tksintenntksintenn Entrant Current User Role Beginner
    helps if you stop mysql before and then restart. issues resolved.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Thanks for letting us know - and it might help another poster in the future so ... appreciated.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.