OurSQL Episode 160: More Free Hot Backup

This week we finish our 2-part series on xtrabackup. Ear Candy is changing the sorting behavior of GROUP BY and At the Movies is a talk on performance schema.

DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

Percona Live London 2013 is happening Monday November 11th and Tuesday November 12th, 2013 at the Millenium Gloucester Conference Center, with a community dinner on the 11th.

SkySQL Trainings

Tungsten University trainings

Oracle's MySQL Trainings

Percona Trainings

Xtrabackup part 2
Part 1 of the xtrabackup series
--defaults-file and --defaults-extra-file
--defaults-group option to read a particular group from a configuration file - very useful for mysqld_multi

An example of --version-check:

# innobackupex --version-check /var/lib/mysql
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
131022 06:23:15  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
131022 06:23:15  innobackupex: Connected to MySQL server
131022 06:23:15  innobackupex: Executing a version check against the server...
131022 06:23:15  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
innobackupex: Using mysql server version 5.6.12-log
innobackupex: Created backup directory /var/lib/mysql/2013-10-22_06-23-15

Note the 2nd-to-last line, this is the version of the server:
innobackupex: Using mysql server version 5.6.12-log

--galera-info to create a file called xtrabackup_galera_info



--kill-long-query-type and --kill-long-queries-timeout in seconds
--sshopt and --scpopt when using --remote-host

Partial backups with innobackupex, including examples
--include to specify a regexp of what databases and tables to include in a partial backup, or use --tables-file to use a file that has all the database/table names to backup inside it, or use the --databases option.

exporting and importing tables from a full backup with innobackupex

Prepare all the tables of a full backup for export:
innobackupex --apply-log --export /path/to/backup

3-step process to importing a table:

  1. Create a table with the same schema as the table you want to import

  2. Throw away the existing tablespace:
    mysql> ALTER TABLE db.tablename DISCARD TABLESPACE;

  3. move the .exp file to the database directory and then run:
    mysql> ALTER TABLE db.tablename IMPORT TABLESPACE;
  4. backing up partitions, with examples

    compact backups

    Preparing a compact backup:
    # innobackupex --apply-log --rebuild-indexes /var/lib/mysql/2013-11-01_02-19-48


    scripting backups


    Ear Candy
    Implicit sorting by GROUP BY is deprecated in MySQL 5.6, may be actually changed in future versions. So if you are using GROUP BY NULL to tell MySQL not to sort a GROUP BY, in a few years you may not need to do that. This is another reason to use pt-upgrade, as we discussed in Episode 158's Ear Candy.

    At the Movies
    This week in At the Movies, we present An Introduction to Performance Schema. Sheeri gave this talk less than 3 months ago at the Tokyo MySQL Users Group, and then again at the Boston MySQL User Group 2 months ago. It was very well-received.

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