1. How to Backup MySQL 5.7

How to Backup MySQL 5.7

If you ever tried to backup MySQL 5.7 with Percona Xtrabackup that comes in percona-xtrabackup package most likely you got error message like this:

# innobackupex ./
170623 05:58:43 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

170623 05:58:44 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'dba' (using password: YES).
170623 05:58:44 version_check Connected to MySQL server
170623 05:58:44 version_check Executing a version check against the server...
170623 05:58:44 version_check Done.
170623 05:58:44 Connecting to MySQL server host: localhost, user: dba, password: set, port: not set, socket: not set
Error: Unsupported server version: '5.7.18-15'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup

This happens because percona-xtrabackup actually installs version 2.3 that doesn’t support MySQL 5.7.

To take copies from MySQL 5.7 you need to use Percona XtraBackup version 2.4 and higher.

Starting from version 2.13.0 TwinDB Backup uses Percona XtraBackup 2.4 to backup MySQL 5.7, 5.6 and earlier versions.

Configuring Repositories

For Debian based systems run following script:

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
curl -s https://packagecloud.io/install/repositories/twindb/main/script.deb.sh | sudo bash

For RedHat based systems run this:

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
curl -s https://packagecloud.io/install/repositories/twindb/main/script.rpm.sh | sudo bash

Installing TwinDB Backup

For TwinDB Backup just install package twindb-backup.

# On Debian/Ubuntu

apt-get install twindb-backup

# On CentOS/RedHat

yum install twindb-backup

Package percona-xtrabackup-24 conflicts with percona-xtrabackup. So if you get the error just uninstall percona-xtrabackup.

Configuring TwinDB Backup

There are many ways to configure backups. See documentation for more detailed description of options in the configuration file.

Along with MySQL database I prefer to take copies of /etc/ as well. As the destination S3 is probably the obvious choice. So, the configuration file /etc/twindb/twindb-backup.cfg should look something like this:

[source]
backup_dirs="/etc"
backup_mysql=yes

[destination]
backup_destination=s3
keep_local_path=/var/log/mysql/DBBACKUP/

[s3]
AWS_ACCESS_KEY_ID=**
AWS_SECRET_ACCESS_KEY=***
AWS_DEFAULT_REGION=us-west-2
BUCKET=my_bucket_for_backups

[mysql]
mysql_defaults_file=/root/.my.cnf
full_backup=daily

[retention]
hourly_copies=24
daily_copies=7
weekly_copies=4
monthly_copies=12
yearly_copies=3

[retention_local]
hourly_copies=1
daily_copies=1
weekly_copies=0
monthly_copies=0
yearly_copies=0

[intervals]
run_hourly=yes
run_daily=yes
run_weekly=yes
run_monthly=yes
run_yearly=yes

Test run

TwinDB Backup package installs a cron job, so you could let it run and check the backups an hour later. Or you can run it manually of course to make sure everything is configured correctly.

# Let's do a daily run. Cron will run exactly this command every day.
twindb-backup backup daily

If success the tool should not produce any output. If any errors drop me a message on support@twindb.com.

Backups verification

You don’t have backups if you don’t verify them. Unfortunately too many users learned this lesson hard way. So don’t be that user and do verify your backups.

TwinDB Backup is super friendly when it comes to restoring backup copies. It will take care of full or incremental backups, local or remote copies.

First step in the verification process is to check what backups we actually have:

twindb-backup ls
2017-06-23 07:23:27,830: INFO: ls.list_available_backups():22: Local copies:
/var/backup/master1/daily/files/_etc-2017-06-23_06_58_11.tar.gz
/var/backup/master1/daily/mysql/mysql-2017-06-23_06_58_22.xbstream.gz
/var/backup/master1/hourly/files/_etc-2017-06-23_07_00_03.tar.gz
/var/backup/master1/hourly/files/_home-2017-06-23_07_00_12.tar.gz
/var/backup/master1/hourly/mysql/mysql-2017-06-23_07_00_14.xbstream.gz
2017-06-23 07:23:27,835: INFO: ls.list_available_backups():33: hourly copies:
/path/to/twindb-server-backups/master1/hourly/files/_etc-2017-06-23_07_00_03.tar.gz
/path/to/twindb-server-backups/master1/hourly/mysql/mysql-2017-06-23_07_00_14.xbstream.gz
2017-06-23 07:23:28,451: INFO: ls.list_available_backups():33: daily copies:
/path/to/twindb-server-backups/master1/daily/files/_etc-2017-06-23_06_58_11.tar.gz
/path/to/twindb-server-backups/master1/daily/mysql/mysql-2017-06-23_06_58_22.xbstream.gz
2017-06-23 07:23:29,073: INFO: ls.list_available_backups():33: weekly copies:
2017-06-23 07:23:29,704: INFO: ls.list_available_backups():33: monthly copies:
2017-06-23 07:23:30,337: INFO: ls.list_available_backups():33: yearly copies:

Ok, we have some copies on the local and remote storage. Let’s grab and restore the last hourly copy:

twindb-backup restore mysql /path/to/twindb-server-backups/master1/hourly/mysql/mysql-2017-06-23_07_00_14.xbstream.gz --dst /var/lib/mysql_restored
2017-06-23 07:28:59,242: INFO: restore.restore_from_mysql():308: Restoring /path/to/twindb-server-backups/master1/hourly/mysql/mysql-2017-06-23_07_00_14.xbstream.gz in /var/lib/mysql_restored

170623 07:29:06 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
...
InnoDB: Shutdown completed; log sequence number 2542148
170623 07:29:13 completed OK!
2017-06-23 07:29:13,279: INFO: restore.restore_from_mysql():380: Successfully restored /path/to/twindb-server-backups/master1/hourly/mysql/mysql-2017-06-23_07_00_14.xbstream.gz in /var/lib/mysql_restored.
2017-06-23 07:29:13,279: INFO: restore.restore_from_mysql():382: Now copy content of /var/lib/mysql_restored to MySQL datadir: cp -R /var/lib/mysql_restored/* /var/lib/mysql/
2017-06-23 07:29:13,279: INFO: restore.restore_from_mysql():383: Fix permissions: chown -R mysql:mysql /var/lib/mysql/
2017-06-23 07:29:13,279: INFO: restore.restore_from_mysql():385: Make sure innodb_log_file_size and innodb_log_files_in_group in /var/lib/mysql_restored/backup-my.cnf and in /etc/my.cnf are same.
2017-06-23 07:29:13,279: INFO: restore.restore_from_mysql():388: Original my.cnf is restored in /var/lib/mysql_restored/_config.
2017-06-23 07:29:13,279: INFO: restore.restore_from_mysql():390: Then you can start MySQL normally.

So the backup copy is restored successfully.
To be absolutely sure the backup copy is usable I’d recommend to run a test instance on /var/lib/mysql_restored and run some queries to make sure the data is there.

Previous Post Next Post