innobackupex-1.5.1 - Perl日誌

xtrabackup試そうとしたらinnobackupex-1.5.1がなにかをする予感がしたのでやってみる

リファレンス

http://www.percona.com/docs/wiki/percona-xtrabackup:start

インストール

% uname -i
i386

% wget http://www.percona.com/downloads/XtraBackup/XtraBackup-1.3/RPM/rhel5/i386/xtrabackup-1.3-10.rhel5.i386.rpm

% sudo rpm -i xtrabackup-1.3-10.rhel5.i386.rpm
警告: xtrabackup-1.3-10.rhel5.i386.rpm: ヘッダ V3 DSA signature: NOKEY, key ID cd2efd2a

インストールされる場所

% which xtrabackup
/usr/bin/xtrabackup

ヘルプは長いけど読んでおく

% /usr/bin/xtrabackup --help
% /usr/bin/innobackupex-1.5.1 --help

この--slave-infoが良く分かってないのですがとりあえずやってみる。ローカルにibdataをコピー

--slave-info
    This option is useful when backing up a replication
            slave server. It prints the binary log position and
    name of the binary log file of the master server.
    It also writes this information to the 'ibbackup_slave_info'
    file as a 'CHANGE MASTER' command. A new slave for this
    master can be set up by starting a slave server on this
    backup and issuing a 'CHANGE MASTER' command with the binary
    log position saved in the 'ibbackup_slave_info' file.

% mkdir ~/xtrabackup
% sudo /usr/bin/innobackupex-1.5.1 --user=root --password=XXXX --slave-info ~/xtrabackup

ライセンスの説明

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

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

この作業が完了したらcompleted OKと表示される。

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

MySQLは5.1を使っています。

innobackupex-1.5.1: Using mysql  Ver 14.14 Distrib 5.1.48, for redhat-linux-gnu (i686) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

バックアップ先のディレクトリは日付から自動的に定まる

innobackupex-1.5.1: Created backup directory /home/homepage/xtrabackup/2010-07-31_10-15-57

mysql接続。なぜ2回？

100731 10:15:57  innobackupex-1.5.1: Starting mysql with options:  --password=XXXX --user=root --unbuffered --
100731 10:15:57  innobackupex-1.5.1: Connected to database with mysql child process (pid=28592)
100731 10:16:05  innobackupex-1.5.1: Connection to database server closed
100731 10:16:05  innobackupex-1.5.1: Starting mysql with options:  --password=XXXX --user=root --unbuffered --
100731 10:16:05  innobackupex-1.5.1: Connected to database with mysql child process (pid=28603)
100731 10:16:09  innobackupex-1.5.1: Connection to database server closed

xtrabackup_50というコマンドが活躍するらしい。

100731 10:16:09  innobackupex-1.5.1: Starting ibbackup with command: xtrabackup_50 --backup --suspend-at-end --target-dir=/home/homepage/xtrabackup/2010-07-31_10-15-57
innobackupex-1.5.1: Waiting for ibbackup (pid=28610) to suspend
innobackupex-1.5.1: Suspend file '/home/homepage/xtrabackup/2010-07-31_10-15-57/xtrabackup_suspended'

mysqldから設定情報を拾い出す。

xtrabackup_50  Ver 1.3 Rev 148 for 5.0.91 pc-linux-gnu (i686)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = /var/lib/mysql
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = /var/lib/mysql
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (0 39354673)

mysqldの設定情報から使用しているデータファイルが分かるのでシステムテーブルスペースであるibdata1をコピーする。
innodb_file_per_tableを有効にしているので*.ibdが生成されており、それらも一緒にコピーしている。

Copying /var/lib/mysql/ibdata1
     to /home/homepage/xtrabackup/2010-07-31_10-15-57/ibdata1
        ...done
Copying ./sample/customer_mst.ibd
     to /home/homepage/xtrabackup/2010-07-31_10-15-57/sample/customer_mst.ibd
        ...done
Copying ./sample/orders.ibd
     to /home/homepage/xtrabackup/2010-07-31_10-15-57/sample/orders.ibd
        ...done
Copying ./sample/product_mst.ibd
     to /home/homepage/xtrabackup/2010-07-31_10-15-57/sample/product_mst.ibd
        ...done
Copying ./testdb/test.ibd
     to /home/homepage/xtrabackup/2010-07-31_10-15-57/testdb/test.ibd
        ...done

ibbackupが停止してもまだまだ続く。またmysqldに接続している？

100731 10:16:13  innobackupex-1.5.1: Continuing after ibbackup has suspended
100731 10:16:13  innobackupex-1.5.1: Starting mysql with options:  --password=XXXX --user=root --unbuffered --
100731 10:16:13  innobackupex-1.5.1: Connected to database with mysql child process (pid=28617)
>> log scanned up to (0 39354673)

そろそろテーブルをロックするか。ロックしてフラッシュしてまう。

100731 10:16:17  innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (0 39354673)
>> log scanned up to (0 39354673)
100731 10:16:29  innobackupex-1.5.1: All tables locked and flushed to disk

データファイルをコピーする。

100731 10:16:29  innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex-1.5.1: subdirectories of '/var/lib/mysql'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/sample/customer_mst.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/sample/product_mst.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/sample/orders.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/sample/db.opt'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/testdb/test.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/testdb/db.opt'
innobackupex-1.5.1: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (65 files)
>> log scanned up to (0 39354673)
100731 10:16:30  innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt files

停止していたibbackupを再開します。

innobackupex-1.5.1: Resuming ibbackup

直近のチェックポイント。トランザクションログをなんかやってる

xtrabackup: The latest check point (for incremental): '0:39354673'
>> log scanned up to (0 39354673)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (0 39354673) to (0 39354673) was copied.
100731 10:16:32  innobackupex-1.5.1: All tables unlocked
100731 10:16:32  innobackupex-1.5.1: Connection to database server closed

ここにつくったよ

innobackupex-1.5.1: Backup created in directory '/home/homepage/xtrabackup/2010-07-31_10-15-57'

バイナリログの名前とポジションを示して終了

innobackupex-1.5.1: MySQL binlog position: filename 'hostname.000008', position 106
innobackupex-1.5.1: MySQL slave binlog position: master host '', filename '', position
100731 10:16:32  innobackupex-1.5.1: completed OK!