using innobackupex to backup MySQL & store files to Amazon S3
introduction
the database is the main point in many infrastructures. Engineers design the data structures to serve a particular purpose and store those data in modern databases. We all know how important it is to protect the databases.
at my previous company, I used an application to back up the MySQL database and store it in Amazon S3. I will write it down so you can refer to it yourself.
objectives
- install the backup toolkit
- take a full backup of database
- take an incremental backup
- restore MySQL data using backup file
- store backup files using Amazon S3
- scripting backup job
- troubleshooting
component
- mySQL 5.7
- persona xtrabackup 2.4
- qpress 11-1.xenial
- ubuntu 16.04 LTS
- amazon S3
before You Begin
- you are using Linux
- you are using InnoDB or XtraDB as your default MySQL storage engine.
setup Environment
our current version of MySQL is 5.6, so we must upgrade to 5.7. It helps to avoid an issue with the redo log when running the backup job with Innobackupex.
add Percona MySQL repository
~# wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb
~# dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb
~# apt-get update
then, set the value innodb_fast to 0; innodb will fail to start if we do not change it.
mysql> SET GLOBAL innodb_fast_shutdown = 0;
stop MySQL
~# systemctl stop mysql
and start upgrading
~# apt-get install percona-server-server-5.7
run the command mysql_upgrade to repair if there are any changes.
~# mysql_upgrade
next, we have to restart MySQL, and verify the operation.
mysql> select version ();
also, the variable innodb_fast_shutdown doesn't exist on version 5.7
mysql> show variables like '%innodb_fast';Empty set (0.01 sec)
install qpress as a default compression tool.
~# apt-cache search qpress
perform a full backup
creating a full backup
~# innobackupex --compress --no-timestamp --databases="books sys mysql information_schema performance_schema" /opt/backup/full
~# ls -lrt /opt~backup/full
total 240
-rw-r –– root root 199225 Nov 23 16:05 ibdata1.qp
drwxr-x –– 2 root root 4096 Nov 23 16:05 books
drwxr-x –– 2 root root 4096 Nov 23 16:05 mysql
drwxr-x –– 2 root root 4096 Nov 23 16:05 performance_schema
drwxr-x –– 2 root root 12288 Nov 23 16:05 sys
-rw-r –– 1 root root 552 Nov 23 16:05 xtrabackup_logfile.qp
-rw-r –– 1 root root 113 Nov 23 16:05 xtrabackup_checkpoints
-rw-r –– 1 root root 342 Nov 23 16:05 ib_buffer_pool.qp
-rw-r –– 1 root root 475 Nov 23 16:05 backup-my.cnf.qp
-rw-r –– 1 root root 491 Nov 23 16:05 xtrabackup_info.qp
preparing a full backup
before preparing the backup, there is a step to uncompress all the files with qpress.
~# innobackupex --decompress /opt/backup/full/
you can run the full backup
~# innobackupex --apply-log /opt/backup/full/
perform an incremental backup
creating an incremental backup
first, we must make at least one full backup as the base for subsequent incremental backups.
~# innobackupex --compress --no-timestamp --databases="books sys mysql information_schema performance_schema" /opt/backup/full
~# ls -lrt /opt/backup/full/
total 240
-rw-r –– 1 root root 199225 Nov 23 16:05 ibdata1.qp
drwxr-x –– 2 root root 4096 Nov 23 16:05 books
drwxr-x –– 2 root root 4096 Nov 23 16:05 mysql
drwxr-x –– 2 root root 4096 Nov 23 16:05 performance_schema
drwxr-x –– 2 root root 12288 Nov 23 16:05 sys
-rw-r –– 1 root root 552 Nov 23 16:05 xtrabackup_logfile.qp
-rw-r –– 1 root root 113 Nov 23 16:05 xtrabackup_checkpoints
-rw-r--— 1 root root 342 Nov 23 16:05 ib_buffer_pool.qp
-rw-r –– 1 root root 475 Nov 23 16:05 backup-my.cnf.qp
-rw-r –– 1 root root 491 Nov 23 16:05 xtrabackup_info.qp
then, make an incremental backup.
~# innobackupex --compress --no-timestamp --databases="books" --incremental /opt/backup/incremental --incremental-basedir=/opt/backup/full
assuming there is a new record to book database, the incremental is only the latest record.
~# ls -lrt /opt/backup/incremental/
total 32
-rw-r –– 1 root root 44 Nov 23 16:07 ibdata1.meta
-rw-r –– 1 root root 690 Nov 23 16:07 ibdata1.delta.qp
drwxr-x –– 2 root root 4096 Nov 23 16:07 books
-rw-r –– 1 root root 544 Nov 23 16:07 xtrabackup_logfile.qp
-rw-r –– 1 root root 117 Nov 23 16:07 xtrabackup_checkpoints
-rw-r –– 1 root root 342 Nov 23 16:07 ib_buffer_pool.qp
-rw-r –– 1 root root 475 Nov 23 16:07 backup-my.cnf.qp
-rw-r –– 1 root root 471 Nov 23 16:07 xtrabackup_info.qp
preparing an incremental backup like a full backup, we need to uncompress all the files with qpress
~# innobackupex --decompress /opt/backup/full/
~# innobackupex --decompress /opt/backup/incremental/
preparing incremental backups is different than full. Perhaps, the stage where more attention is needed:
- first, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones.
- then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.
if we replay the committed transactions and roll back the uncommitted ones on the base backup, we will not be able to add the incremental ones. If we do this on an incremental one, we won’t be able to add data from that moment and the remaining increments.
having this in mind, the procedure is very straight-forward using the --redo-only option, starting with the base backup.
~# innobackupex --apply-log --redo-only /opt/backup/full/
then, the first incremental backup can be applied to the full backup by using
~# innobackupex --apply-log /opt/backup/full/ --incremental-dir=/opt/backup/incremental/
note
--redo-only should be used when merging all incrementals except the last one. The previous line doesn’t contain the --redo-only option. Even if the --redo-only were used on the previous step, backup would still be consistent, but in that case, the server would perform the rollback phase as follows
~# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
~# innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
restoring Backup Files
restoring a full backup
use innobackupex command
~# innobackupex --copy-back /opt/backup
now the MySQL directory looks like this one
~# ls -lrt /var/lib/mysql
total 110644
-rw-r –– 1 mysql mysql 50331648 Nov 23 13:22 ib_logfile1
drwxr-x –– 2 mysql mysql 4096 Nov 23 13:22 books
-rw-r –– 1 mysql mysql 451 Nov 23 13:22 xtrabackup_info
-rw-r –– 1 mysql mysql 1 Nov 23 13:22 xtrabackup_master_key_id
-rw-r –– 1 mysql mysql 56 Nov 23 13:22 auto.cnf
-rw-r--r –– 1 mysql mysql 1120 Nov 23 13:22 ca.pem
-rw –– 1 mysql mysql 1676 Nov 23 13:22 ca-key.pem
-rw –– 1 mysql mysql 1676 Nov 23 13:22 server-key.pem
-rw-r--r –– 1 mysql mysql 1120 Nov 23 13:22 server-cert.pem
-rw –– 1 mysql mysql 1676 Nov 23 13:22 client-key.pem
-rw-r--r –– 1 mysql mysql 1120 Nov 23 13:22 client-cert.pem
-rw-r--r –– 1 mysql mysql 452 Nov 23 13:22 public_key.pem
-rw –– 1 mysql mysql 1680 Nov 23 13:22 private_key.pem
-rw-r –– 1 mysql mysql 261 Nov 23 13:23 ib_buffer_pool
-rw-r –– 1 mysql mysql 50331648 Nov 23 13:23 ib_logfile0
-rw-r--— 1 mysql mysql 12582912 Nov 23 13:23 ibdata1
remember to set the file/ directory permission
~# chown -R mysql. /var/lib/mysql
and start MySQL service
~# systemctl start mysql
verify
~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2
Server version: 5.7.23-25 Percona Server (GPL), Release '25', Revision '7e2732e'
go inside MySQL database for checking the data.
restoring an incremental backup
after preparing the incremental backups, the entire directory contains the same information as the full one. To restore it, you can use
~# innobackupex --copy-back /opt/backup/full/
now MySQL directory looks like this one
~# ls -lrt /var/lib/mysql
total 122908
-rw-r –– 1 root root 50331648 Nov 23 16:11 ib_logfile0
-rw-r –– 1 root root 50331648 Nov 23 16:11 ib_logfile1
-rw-r –– 1 root root 12582912 Nov 23 16:11 ibdata1
-rw-r –– 1 root root 12582912 Nov 23 16:11 ibtmp1
drwxr-x –– 2 root root 4096 Nov 23 16:11 books
-rw-r –– 1 root root 522 Nov 23 16:11 xtrabackup_info
drwxr-x –– 2 root root 4096 Nov 23 16:11 mysql
-rw-r –– 1 root root 1 Nov 23 16:11 xtrabackup_master_key_id
drwxr-x –– 2 root root 4096 Nov 23 16:11 performance_schema
-rw-r –– 1 root root 349 Nov 23 16:11 ib_buffer_pool
drwxr-x –– 2 root root 4096 Nov 23 16:11 sys
set the their permission
~# chown -R mysql. /var/lib/mysql
start MySQL service
~# systemctl start mysql
verify
~# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
store Backup Files Using Amazon S3
we decided to use the S3SF - FUSE-based file system backed by Amazon S3 - based solution to mount an Amazon S3 bucket.
install S3FS and its dependencies
~# sudo apt-get install build-essential libcurl4-openssl-dev libxml2-dev mime-support
download and compile FUSE
~# cd /usr/src/
~# wget https://github.com/libfuse/libfuse/releases/download/fuse-3.1.0/fuse-3.1.0.tar.gz
~# tar xzf fuse-3.1.0.tar.gz
~# cd fuse-3.1.0./configure --prefix=/usr/localmake && make installexport PKG_CONFIG_PATH=/usr/local/lib/pkgconfigldconfigmodprobe fuse
download and compile S3FS
~# cd /usr/src/~# git clone https://github.com/s3fs-fuse/s3fs-fuse.git
~# cd s3fs-fuse./autogen.sh./configuremake && make install
set up AWS access key
~# echo AWS_ACCESS_KEY_ID:AWS_SECRET_ACCESS_KEY > ~/.passwd-s3fs
~# chmod 600 ~/.passwd-s3fs
create a mount point and cache directory
~# mkdir /data/s3mysqlbackup /tmp/cache
then mount Amazon S3 bucket
~# s3fs -o use_cache=/tmp/cache skylab-mysql-backup /data/s3mysqlbackup/
make partition mount at startup
~# /opt/mysqlbackup/incremental# vim /etc/fstabskylab-mysql-backup /data/s3mysqlbackup fuse allow_other,use_cache=/tmp/cache 0 0
the mount point looks like
~# /opt/mysqlbackup/incremental# df -h
Filesystem Size Used Avail Use% Mounted on
s3fs 256T 0 256T 0% /data/s3mysqlbackup
automated script
#!/bin/bash
#FILE ./innobackupex.sh
#AUTHOR tien.phan
#DATE 06 of November, 2018
#VERSION 0.1 first draff 20181107
# 0.2 second draff 20181108
# 0.2.1 release version
# 0.2.2 change the backup location to AWS S3 (mounted by s3fs)
# 0.2.3 add feature "delete file older than 30 days
# 0.2.4 add $HOSTNAME & echo timestamp for [start/ finish] job
# 0.2.5 change the schedule of backup. Also change /data/s3mysqlbackup to /s3mysqlbackup
#HOW IT WORKS
#On Saturday, it runs full backup
#On Sunday, it runs incremental as base full backup of Monday
#From Monday to Friday, it runs incremental as base incremental of Tuedays. The same action for Thurday, Friday, Sartuday and Sunday
#VARIABLES
BACKUP_DATE=`date +%Y%m%d`
YESTERDAY=$((BACKUP_DATE -1))
TODAY=$(LC_TIME=C date +%A)
DATABASES=""
LOG="/var/log/xtrabackup.log"
#SCRIPT RUNS
if [ "$TODAY" = "Saturday" ]
then
echo "Start to backup at `date`" >> $LOG
mkdir -p /s3mysqlbackup/$HOSTNAME/full/"$BACKUP_DATE"
innobackupex --compress --no-timestamp --databases=$DATABASES /s3mysqlbackup/$HOSTNAME/full/"$BACKUP_DATE"/
echo "Full backup was completed at `date`" >> $LOG
elif [ "$TODAY" = "Sunday" ]
then
echo "Start to backup at `date`" >> $LOG
mkdir -p
/s3mysqlbackup/$HOSTNAME/incremental/"$BACKUP_DATE"
innobackupex --compress --no-timestamp --databases=$DATABASES --incremental /s3mysqlbackup/$HOSTNAME/incremental/"$BACKUP_DATE" --incremental-basedir=/s3mysqlbackup/$HOSTNAME/full/"$YESTERDAY"/
echo "Incremental backup was completed at `date`" >> $LOG
else
echo "Start to backup at `date`" >> $LOG
mkdir -p /s3mysqlbackup/$HOSTNAME/incremental/"$BACKUP_DATE"
innobackupex --compress --no-timestamp --databases=$DATABASES --incremental /s3mysqlbackup/$HOSTNAME/incremental/"$BACKUP_DATE" --incremental-basedir=/s3mysqlbackup/$HOSTNAME/incremental/"$YESTERDAY"/
echo "Incremental backup was completed at `date`" >> $LOG
fi
#DELETE FILE OLDER THAN 60 DAYS
find /s3mysqlbackup/$HOSTNAME/full/ -mtime +60 -exec rm -rf {} \;
find /s3mysqlbackup/$HOSTNAME/incremental/ -mtime +60 -exec rm -rf {} \;
troubleshooting
xtrabackup could not read metadata
the title speaks for itself. Indeed, the restoring step needs to use the same directory to perform a backup.
xtrabackup --prepare --target-dir=/opt/restore
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/opt/restoreencryption: using gcrypt 1.6.5
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /opt/restore/
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: Error: failed to read metadata from './xtrabackup_checkpoints'
mySQL could not startMySQL could not start after finished restoring.
~# systemctl status mysql
● mysql.service - Percona ServerLoaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: deactivating (final-sigterm) (Result: exit-code) since Fri 2018-11-23 13:21:37 +07; 6min ago
Process: 15740 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited,Process: 15731 ExecStartPre=/usr/bin/ps_mysqld_helper (code=exited, status=0/SUCCESS)
Process: 15633 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 8313 (code=exited, status=0/SUCCESS)
Tasks: 11
Memory: 124.5M
CPU: 522ms
CGroup: /system.slice/mysql.service
└─15744 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Nov 23 13:28:16 puppet systemd[1]: Starting Percona Server...
Nov 23 13:28:16 puppet su[15670]: Successful su for mysql by root
Nov 23 13:28:16 puppet su[15670]: + ??? root:mysql
Nov 23 13:28:16 puppet su[15670]: pam_unix(su:session): session opened for user mysql by (uid=0)
Nov 23 13:28:16 puppet mysql-systemd-start[15633]: 2018-11-23T06:28:16.140731Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_D
Nov 23 13:28:16 puppet mysql-systemd-start[15633]: 2018-11-23T06:28:16.140777Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mod
Nov 23 13:28:16 puppet mysql-systemd-start[15633]: 2018-11-23T06:28:16.142081Z 0 [ERROR] --initialize specified but the
Nov 23 13:28:16 puppet mysql-systemd-start[15633]: 2018-11-23T06:28:16.142653Z 0 [ERROR] Aborting
Nov 23 13:28:19 puppet mysqld[15740]: Initialization of mysqld failed: 0
Nov 23 13:28:19 puppet systemd[1]: mysql.service: Control process exited, code=exited status=1
…
2018-11-23T06:35:38.446574Z 2 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_table_stats.ibd' OS error: 71
2018-11-23T06:35:38.446578Z 2 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-11-23T06:35:38.446581Z 2 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-11-23T06:35:38.446586Z 2 [ERROR] InnoDB: Could not find a valid tablespace file for mysql/innodb_table_stats. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2018-11-23T06:35:38.446612Z 2 [ERROR] InnoDB: Tablespace for table mysql.innodb_table_stats is missing.
2018-11-23T06:35:38.446619Z 2 [ERROR] InnoDB: Fetch of persistent statistics requested for table books.authors but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
it means there is no default database mysql, so MySQL could not start. We can fix it by using the parameter --skip-grant-tables.
~# systemctl set-environment MYSQLD_OPTS=“--skip-grant-tables"
then start MySQL again.
~# systemctl start mysql
it works. However, please remember to add the mysql, performance_schema, sys, information_schema databases when performing backup job.