MYSQLオンラインバックアップ (xtrabackup)

MYSQLオンラインバックアップ

基本的にWeb系のサービスではオンラインバックアップが基本だと思うので、
改めてMYSQLのオンラインバックアップの選択肢を確認。

ハードウエアやOSの機能を利用
　LVMやストレージが提供しているSnapShot機能を利用する。
　自分で簡単なスクリプトを作成すれば瞬時にオンラインで
　MYSQLバックアップをする事が可能。

ソフトウエアの機能を利用したバックアップ
mysqldump
mMySQL Enterprise Backup
InnoDB Hot Backup
Xtra Backup

Xtra Backupの動作確認
32bit版はi686 64bit版はx86_64
http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/
http://www.percona.com/software/percona-xtrabackup

OS確認

[root@HOME001 mysql]# uname -m
i686
[root@HOME001 mysql]#

ダウンロード

[root@HOME001 mysql]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
--2013-01-24 13:31:20--  http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
www.percona.com をDNSに問いあわせています... 74.121.199.234
www.percona.com|74.121.199.234|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: /downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz [続く]
--2013-01-24 13:31:21--  http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
www.percona.com:80 への接続を再利用します。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 22328480 (21M) [application/x-gzip]
`percona-xtrabackup-2.0.5-499.tar.gz' に保存中

100%[==========================================================>] 22,328,480  1.40M/s 時間 15s

2013-01-24 13:31:36 (1.45 MB/s) - `percona-xtrabackup-2.0.5-499.tar.gz' へ保存完了 [22328480/22328480]

[root@HOME001 mysql]#

展開
MYSQLのホームディレクトリーのbinに展開したファイルをコピーしないとエラーが出たのでコピーしてます。

[root@HOME001 mysql]# tar zxvf percona-xtrabackup-2.0.5-499.tar.gz
percona-xtrabackup-2.0.5/
percona-xtrabackup-2.0.5/bin/
percona-xtrabackup-2.0.5/bin/xtrabackup_55
percona-xtrabackup-2.0.5/bin/xtrabackup_51
percona-xtrabackup-2.0.5/bin/innobackupex
percona-xtrabackup-2.0.5/bin/xtrabackup
省略…
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/bug723097.sql
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/ib_stream_common.sh
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/incremental_sample-schema.sql
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/common.sh
[root@HOME001 mysql]#


[root@HOME001 percona-xtrabackup-2.0.5]# pwd
/home/mysql/percona-xtrabackup-2.0.5
[root@HOME001 percona-xtrabackup-2.0.5]# ls -l
合計 8
drwxr-xr-x. 2 root root 4096  1月 18 05:37 2013 bin
drwxr-xr-x. 4 root root 4096  1月 18 05:37 2013 share
[root@HOME001 percona-xtrabackup-2.0.5]# ls -l bin/
合計 32272
-rwxr-xr-x. 1 root root   106390  1月 18 05:37 2013 innobackupex
lrwxrwxrwx. 1 root root       12  1月 24 13:34 2013 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x. 1 root root  2031819  1月 18 05:37 2013 xbstream
-rwxr-xr-x. 1 root root  9809963  1月 18 05:34 2013 xtrabackup
-rwxr-xr-x. 1 root root  8375751  1月 18 05:37 2013 xtrabackup_51
-rwxr-xr-x. 1 root root 12713916  1月 18 05:29 2013 xtrabackup_55
[root@HOME001 percona-xtrabackup-2.0.5]#cd bin
[root@HOME001 bin]# cp -rp * /usr/local/mysql/bin/
[root@HOME001 bin]# cd /usr/local/mysql/bin/
[root@HOME001 bin]# mkdir /home/mysql/backup

InnoDBのオンラインバックアップ実行
パスワードは変数を入れてます。

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass /home/mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130124 14:44:51  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:44:51  innobackupex-1.5.1: Connected to database with mysql child process (pid=4736)
130124 14:44:57  innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex-1.5.1
           prints "completed OK!".

innobackupex-1.5.1: Using mysql  Ver 14.14 Distrib 5.5.29, for linux2.6 (i686) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

innobackupex-1.5.1: Created backup directory /home/mysql/backup/2013-01-24_14-44-57
130124 14:44:57  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:44:57  innobackupex-1.5.1: Connected to database with mysql child process (pid=4763)
130124 14:44:59  innobackupex-1.5.1: Connection to database server closed

130124 14:44:59  innobackupex-1.5.1: Starting ibbackup with command: xtrabackup_55  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/backup/2013-01-24_14-44-57
innobackupex-1.5.1: Waiting for ibbackup (pid=4771) to suspend
innobackupex-1.5.1: Suspend file '/home/mysql/backup/2013-01-24_14-44-57/xtrabackup_suspended'

xtrabackup_55 version 2.0.5 for Percona Server 5.5.16 Linux (i686) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 8388608
>> log scanned up to (1656933)
[01] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_14-44-57/ibdata1
[01]        ...done

130124 14:45:03  innobackupex-1.5.1: Continuing after ibbackup has suspended
130124 14:45:03  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:45:03  innobackupex-1.5.1: Connected to database with mysql child process (pid=4785)
>> log scanned up to (1656933)
130124 14:45:05  innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (1656933)
>> log scanned up to (1656933)
130124 14:45:15  innobackupex-1.5.1: All tables locked and flushed to disk

130124 14:45:15  innobackupex-1.5.1: Starting to backup non-InnoDB tables and files
innobackupex-1.5.1: in subdirectories of '/usr/local/mysql/data'
innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/performance_schema/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/DB_REPLICATION/db.opt'
innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/test/TABLE001.frm'
innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/mysql/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
130124 14:45:15  innobackupex-1.5.1: Finished backing up non-InnoDB tables and files

130124 14:45:15  innobackupex-1.5.1: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1656933'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1656933)

xtrabackup: Transaction log of lsn (1656933) to (1656933) was copied.
130124 14:45:18  innobackupex-1.5.1: All tables unlocked
130124 14:45:18  innobackupex-1.5.1: Connection to database server closed

innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_14-44-57'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107
130124 14:45:18  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#


[root@HOME001 bin]# ls -l /home/mysql/backup/
合計 4
drwxr-xr-x. 6 root root 4096  1月 24 14:45 2013 2013-01-24_14-44-57
[root@HOME001 bin]#

並列処理を指定する事も可能

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --parallel=4 /home/mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130124 15:23:25  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 15:23:25  innobackupex-1.5.1: Connected to database with mysql child process (pid=5010)
130124 15:23:31  innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex-1.5.1
           prints "completed OK!".
省略・・・
xtrabackup: Starting 4 threads for parallel data files transfer
[03] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_15-23-31/ibdata1
[03]        ...done
省略・・・
innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107
130124 15:23:52  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#

リストアを試してみる
リストア時にフルバックアップで取得したデータをリストアすると、
データフォルダーが空では無いとエラーになる。
バイナリーログは差分リカバリーで必要になるので残しておく。
Oracleでいうテーブルスペースのオンライバックアップをリストアして、
ArchiveログやRedoを適用してロールフォワードするような感じ。

[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|       28 |
+----------+
[root@HOME001 bin]# mysql -u root -p$b_pass -e "truncate table test.TABLE001;"
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|        0 |
+----------+
[root@HOME001 bin]#
[root@HOME001 bin]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@HOME001 bin]# mv /usr/local/mysql/data  /home/mysql/backup/
[root@HOME001 bin]# mkdir /usr/local/mysql/data
[root@HOME001 bin]# chown -R mysql:mysql /usr/local/mysql/data

コピーバックしてリストア
注意：レプリケーション環境ではきちんと必要なファイル等を理解した上でリカバリーして下さい。

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --copy-back /home/mysql/backup/2013-01-24_15-23-31

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

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

innobackupex-1.5.1: Starting to copy files in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/performance_schema'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/file_summary_by_event_name.frm' to '/usr/l         ocal/mysql/data/performance_schema/file_summary_by_event_name.frm'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/rwlock_instances.frm' to '/usr/local/mysql         /data/performance_schema/rwlock_instances.frm'
....省略
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition.MYI' to '/usr/local/mysql/data/mys         ql/time_zone_transition.MYI'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition_type.frm' to '/usr/local/mysql/dat         a/mysql/time_zone_transition_type.frm'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/DB_REPLICATION'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/DB_REPLICATION/db.opt' to '/usr/local/mysql/data/DB_REPLICATI         ON/db.opt'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/test'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/test/TABLE001.frm' to '/usr/local/mysql/data/test/TABLE001.frm'

innobackupex-1.5.1: Starting to copy InnoDB system tablespace
innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original InnoDB data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/ibdata1' to '/usr/local/mysql/data/ibdata1'

innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex-1.5.1: Finished copying back files.
130124 15:38:03  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#

サービス再起動とデータの確認

[root@HOME001 bin]# /etc/init.d/mysql start
Starting MySQL....                                         [  OK  ]
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|       28 |
+----------+
[root@HOME001 bin]#

リカバリーログの内容（ログファイル再作成、ロールフォワードの履歴）

130124 15:40:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
130124 15:40:42 [Note] Plugin ‘FEDERATED’ is disabled.
130124 15:40:42 InnoDB: The InnoDB memory heap is disabled
130124 15:40:42 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
130124 15:40:42 InnoDB: Compressed tables use zlib 1.2.3
130124 15:40:42 InnoDB: Using Linux native AIO
130124 15:40:42 InnoDB: Initializing buffer pool, size = 32.0M
130124 15:40:42 InnoDB: Completed initialization of buffer pool
130124 15:40:42 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:42 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:43 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130124 15:40:43 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Last MySQL binlog file position 0 3917, file name ./mysql-bin.000035
130124 15:40:43 InnoDB: Waiting for the background threads to start
130124 15:40:44 InnoDB: 1.1.8 started; log sequence number 1657356
130124 15:40:44 [Note] Semi-sync replication initialized for transactions.
130124 15:40:44 [Note] Semi-sync replication enabled on the master.
130124 15:40:44 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
130124 15:40:44 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
130124 15:40:44 [Note] Server socket created on IP: ‘0.0.0.0’.
130124 15:40:44 [Note] Event Scheduler: Loaded 0 events
130124 15:40:44 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.29-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
130124 15:40:46 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000050, 194)
130124 15:40:46 [Note] Stop semi-sync binlog_dump to slave (server_id: 2)

その他LVM のスナップショット機能を利用したバックアップに関しては後日検証
LVMの現状確認

[root@HOME001 ~]# fdisk -l /dev/sda
ディスク /dev/sda: 60.0 GB, 60011642880 バイト
ヘッド 255, セクタ 63, シリンダ 7296
Units = シリンダ数 of 16065 * 512 = 8225280 バイト
セクタサイズ (論理 / 物理): 512 バイト / 512 バイト
I/O size (minimum/optimal): 512 bytes / 512 bytes
ディスク識別子: 0x1ecfb89b

デバイス ブート      始点        終点     ブロック   Id  システム
/dev/sda1   *           1          64      512000   83  Linux
パーティション 1 は、シリンダ境界で終わっていません。
/dev/sda2              64        7296    58091520   8e  Linux LVM
[root@HOME001 ~]#


[root@HOME001 ~]# pvscan
  PV /dev/sda2   VG vg_home001   lvm2 [55.40 GiB / 0    free]
  Total: 1 [55.40 GiB] / in use: 1 [55.40 GiB] / in no VG: 0 [0   ]
[root@HOME001 ~]# 

[root@HOME001 ~]# vgscan
  Reading all physical volumes.  This may take a while...
  Found volume group "vg_home001" using metadata type lvm2
[root@HOME001 ~]# 

[root@HOME001 ~]# lvscan
  ACTIVE            '/dev/vg_home001/lv_root' [46.86 GiB] inherit
  ACTIVE            '/dev/vg_home001/lv_home' [4.57 GiB] inherit
  ACTIVE            '/dev/vg_home001/lv_swap' [3.97 GiB] inherit
[root@HOME001 ~]# 

[root@HOME001 ~]# vgs
  VG         #PV #LV #SN Attr   VSize  VFree
  vg_home001   1   3   0 wz--n- 55.40g    0
[root@HOME001 ~]# 

[root@HOME001 ~]# lvs
  LV      VG         Attr     LSize  Pool Origin Data%  Move Log Copy%  Convert
  lv_home vg_home001 -wi-ao--  4.57g
  lv_root vg_home001 -wi-ao-- 46.86g
  lv_swap vg_home001 -wi-ao--  3.97g
[root@HOME001 ~]# 

[root@HOME001 ~]# df
Filesystem           1K-ブロック    使用   使用可 使用% マウント位置
/dev/mapper/vg_home001-lv_root      48360312   2618204  43285532   6% /
tmpfs                                 969472         0    969472   0% /dev/shm
/dev/sda1                             495844     36136    434108   8% /boot
/dev/mapper/vg_home001-lv_home       4721020    441796   4039404  10% /home
tmpfs                                  65536         0     65536   0% /usr/local/tmp
[root@HOME001 ~]# 

[root@HOME001 ~]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#


もしボリューム名を変更したら起動、fstabの変更も忘れずに
[root@HOME001 ~]# cat /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/mapper/vg_home001-lv_root
#          initrd /initrd-[generic-]version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title CentOS (2.6.32-279.el6.i686)
        root (hd0,0)
        kernel /vmlinuz-2.6.32-279.el6.i686 ro root=/dev/mapper/vg_home001-lv_root rd_NO_LUKS rd_LVM_LV=vg_home001/lv_swap rd_NO_MD rd_LVM_LV=vg_home001/lv_root crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=jp106 LANG=ja_JP.UTF-8 rd_NO_DM rhgb quiet
        initrd /initramfs-2.6.32-279.el6.i686.img
[root@HOME001 ~]#

参考

MySQLバックアップ頂上決戦！！ LVMスナップショット vs InnoDB Hot Backup
Percona XtraBackup
Percona XtraBackupの基本的な使い方
XtraBackupを使ってMySQLをバックアップしよう
MySQLバックアップツール比較 XtraBackup / mysqldump / Mydumper
MySQL – XtraBackupを使ったバックアップ＆リストアについて
レプリケーション作成を簡単にする mysql40dump という mysqldump の wrapper を作った話
keepalived + MySQL-MHA + xtrabackupで自動フェイルオーバーと手動フェイルバック(その１ keepalived編)
Percona XtraBackupの抽出と圧縮の並列処理
LVM を導入する
LVM のパーティションを管理する
LVM のスナップショット機能を使ってみる
How to setup a slave for replication in 6 simple steps with Xtrabackup