OurSQL Episode 159: Free Hot Backup

This week, we talk about Percona's Xtrabackup. Ear Candy talks about .frm files and how many fields an InnoDB table can have, and At the Movies is a talk about indexes.

Xtrabackup
Episode 38 where we previously talked about Xtrabackup

Installing Percona's Xtrabackup

Operating system permissions and database privileges needed to use xtrabackup
How innobackupex works

different xtrabackup binaries and when they should be used - alternatively, use the innobackupex wrapper script to detect and use the proper binary

Bug: xtrabackup_slave_info does not have GTIDs

Simple backup example:
innobackupex /var/lib/mysql

--copy-back and --move-back
--apply-log for innobackupex, --prepare for xtrabackup, and how they are different
--no-timestamp for innobackupex, --target-dir for xtrabackup
--use-memory

Streaming
tar man page and options
Example of streaming the output to tar:
innobackupex --stream=tar /var/lib/mysql > backup.tar

Ideas of where to pipe output to:
gzip for compression
bzip for compression
openssl for encryption
netcat, or nc for remote copy
ssh for encrypted remote copy

Making a compressed backup:
innobackupex --stream=tar /var/lib/mysql | gzip - > backup.tar.gz

Encrypting the backup:
innobackupex --stream=tar . | gzip - | openssl des3 -salt -k "password" > backup.tar.gz.des3

Streaming the backup to a remote host:

## On the host receiving the backup files:
$ nc -l 9999 | cat - > /data/backups/backup.tar
## On the host where you're taking the backup:
$ innobackupex --stream=tar ./ | nc desthost 9999

pv to control data rates through a pipe

Make a throttled backup at a rate of 10 megabytes per second, is streamed to a remote host using ssh:
innobackupex --stream=tar ./ | pv -q -L10m | ssh user@desthost "cat - > /data/backups/backup.tar"

xbstream as an alternative to --stream. Allows for support of --parallel in innobackupex

Using xbstream:
innobackupex --stream=xbstream ./ > backup.xbstream

Using xbstream with compression:
innobackupex --stream=xbstream --compress ./ > backup.xbstream

Using xbstream and parallel streaming:
innobackupex --compress --compress-threads=8 --stream=xbstream --parallel=4 ./ > backup.xbstream

To extract a backup with xbstream:
xbstream -x < backup.xbstream

xbcrypt
--input, -i
--output, -o

Point in Time Recovery (PITR)
A simple full backup, done on October 21st at 9:34 am and 47 seconds, puts backup into /var/lib/mysql/2013-10-21_09-34-47:
innobackupex /var/lib/mysql/

Incremental backup based on the full backup, done on October 22nd at 9:30 am and 2 seconds, puts the incremental backup into /var/lib/mysql/2013-10-22_09-30-02:
innobackupex --incremental /var/lib/mysql --incremental-basedir=/var/lib/mysql/2013-10-21_09-34-47/

Incremental backup based on the first incremental backup, done on October 23rd at 9:31 am and 4 seconds, puts the incremental backup into /var/lib/mysql/2013-10-23_09-31-04:
innobackupex --incremental /var/lib/mysql --incremental-basedir=/var/lib/mysql/2013-10-22_09-30-02/

Example xtrabackup_checkpoints file from the full backup:

backup_type = full-backuped
from_lsn = 0
to_lsn = 19017399325
last_lsn = 19017399325
compact = 0

Example xtrabackup_checkpoints file from the first incremental backup, notice that the "from_lsn" matches the "to_lsn" of the full backup:

backup_type = incremental
from_lsn = 19017399325
to_lsn = 19017461755
last_lsn = 19017461755
compact = 0

Example xtrabackup_checkpoints file from the second incremental backup, that uses the first

incremental backup as the base -
backup_type = incremental
from_lsn = 19017461755
to_lsn = 19017461755
last_lsn = 19017461755
compact = 0

Restoring to Tuesday's backup - first restore only the committed transactions from Sunday's full backup:
innobackupex --apply-log --redo-only /var/lib/mysql/2013-10-21_09-34-47

Then apply Monday's incremental backup:
iinnobackupex --apply-log --redo-only /var/lib/mysql/2013-10-21_09-34-47 --incremental-dir=/var/lib/mysql/2013-10-22_09-30-02/

Then apply Tuesday's incremental backup. Since this is the last incremental backup, it is OK to rollback the uncommitted transactions, so we do not need the --redo-only flag:
iinnobackupex --apply-log var/lib/mysql/2013-10-21_09-34-47 --incremental-dir=/var/lib/mysql/2013-10-23_09-31-04/

Ear Candy
In this weeks ear candy we talk about how many fields an InnoDB table can have, and why .frm files are so important.
Stewart Smith's article about .FRM files and maximum number of fields

This week in At the Movies, we present an indexing talk Sheeri presented earlier this year at both Confoo and Scale11x, called Are you Getting the Best Out of Your MySQL Indexes? That article contains links to both the video and a PDF of the slides.

Where you can see us

On to where you can see us.
Sheeri will be at the 2013 Usenix LISA conference on Sunday November 3rd, doing a 3-hour workshop with Leslie Hawthron about Recognizing and Overcoming Bias—Ways to Make Your Workplace More Successful and Welcoming

Sheeri will be at the November Boston User Group meeting on Monday, November 11th at 7 pm. The topic will be using the new GTIDs, by Jacob Nikom.

Gerry will be at the December Seattle MySQL Meetup on Monday Dec 9th at 6:30pm at the new Twitter offices in Seattle.

Sheeri will be at the December Boston User Group meeting on Monday, December 9th at 7 pm.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1265