Use innobackupex of Xtrabackup and create MySQL backup

Xtrabackupのinnobackupexというコマンドを利用してバックアップを作成したいと思います。
（前回はこちら） 

かなりシンプルな内容です。

やることは、以下の通りです。

①ローカルのMySQLのバックアップを作成する。
②ローカルのデータベースを削除する。
③バックアップを元に復元する。

①ローカルのMySQLのバックアップを作成する。
 

#innobackpex --user=root --password=pass /data/backup/ 

オプション説明

--user
・・・MySQLで利用するユーザ名

--password
・・・userで指定したパスワード

/data/backup
・・・バックアップデータの出力先


作成されるデータは /data/backup/2013-11-11_21-04-42　と日付のフォルダが作成される。

ログは長いですが、以下の通りです。

#innobackpex --user=root --password=pass /data/backup
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.

131111 21:04:42  innobackupex: Connecting to MySQL server with DSN
'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password:
YES).
131111 21:04:42  innobackupex: Connected to MySQL server
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.5.32

innobackupex: Created backup directory /data/backup/2013-11-11_21-04-42

131111 21:04:42  innobackupex: Starting ibbackup with command: xtrabackup_55
--defaults-group="mysqld" --backup --suspend-at-end
--target-dir=/data/backup/2013-11-11_21-04-42 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=22602) to suspend
innobackupex: Suspend file
'/data/backup/2013-11-11_21-04-42/xtrabackup_suspended_2'

xtrabackup_55 version 2.1.5 for Percona Server 5.5.31 Linux (x86_64) (revision
id: 680)
xtrabackup: uses posix_fadvise(.
xtrabackup: cd to /var/lib/mysql
xtrabackup: using the following InnoDB configuration:
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 = 5242880
>> log scanned up to (24059404)
[01] Copying ./ibdata1 to /data/backup/2013-11-11_21-04-42/ibdata1
[01]        ...done
>> log scanned up to (24059404)
xtrabackup: Creating suspend file
'/data/backup/2013-11-11_21-04-42/xtrabackup_suspended_2' with pid '22602'

131111 21:04:44  innobackupex: Continuing after ibbackup has suspended
131111 21:04:44  innobackupex: Starting to lock all tables...
131111 21:04:44  innobackupex: All tables locked and flushed to disk

131111 21:04:44  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql'
innobackupex: Backing up files
'/var/lib/mysql/test/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'
(10 files)
131111 21:04:45  innobackupex: Finished backing up non-InnoDB tables and files

131111 21:04:45  innobackupex: Waiting for log copying to finish

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

xtrabackup: Creating suspend file
'/data/backup/2013-11-11_21-04-42/xtrabackup_log_copied' with pid '22602'
xtrabackup: Transaction log of lsn (24059404) to (24059404) was copied.
131111 21:04:46  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/data/backup/2013-11-11_21-04-42'
131111 21:04:46  innobackupex: Connection to database server closed
131111 21:04:46  innobackupex: completed OK!)

②ローカルのデータベースを削除する。

#mysql -u root -p
< drop database test; 

データベースを削除する

③バックアップを元に復元する。

#service mysql stop
#mv /var/lib/mysql /var/lib/mysql_def
#mkdir /var/lib/mysql
#chown mysql:mysql  -R /var/lib/mysql
#innobackupex --copy-back /data/backup/2013-11-11_21-04-42

--copy-back
バックアップしたファイルからデータベースを構築しなおす

/data/backup/2013-11-11_21-04-42
バックアップが存在するフォルダ

/var/lib/mysqlへバックアップのデータが作成されます。
mysqlでアクセスすると削除したはずのtestのデータベースが存在していると思います

以下はログです。

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.

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

innobackupex: Starting to copy files in
'/data/backup/mysql/2013-11-11_21-04-42'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Creating directory '/var/lib/mysql/test'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/T_Attribute.MYI' to
'/var/lib/mysql/test/T_Attribute.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/M_Attribute.MYI' to
'/var/lib/mysql/test/M_Attribute.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/AddressTable.frm' to
'/var/lib/mysql/test/AddressTable.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/T_Attribute.MYD' to
'/var/lib/mysql/test/T_Attribute.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/M_Attribute.frm' to
'/var/lib/mysql/test/M_Attribute.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/M_Attribute.MYD' to
'/var/lib/mysql/test/M_Attribute.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/AddressTable.MYI' to
'/var/lib/mysql/test/AddressTable.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/db.opt' to
'/var/lib/mysql/test/db.opt'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/AddressTable.MYD' to
'/var/lib/mysql/test/AddressTable.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/test/T_Attribute.frm' to
'/var/lib/mysql/test/T_Attribute.frm'
innobackupex: Creating directory '/var/lib/mysql/mysql'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_topic.MYI' to
'/var/lib/mysql/mysql/help_topic.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/procs_priv.MYD' to
'/var/lib/mysql/mysql/procs_priv.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/procs_priv.MYI' to
'/var/lib/mysql/mysql/procs_priv.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/host.MYI' to
'/var/lib/mysql/mysql/host.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/tables_priv.MYD' to
'/var/lib/mysql/mysql/tables_priv.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_transition_type.MYD'
to '/var/lib/mysql/mysql/time_zone_transition_type.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_transition.frm'
to '/var/lib/mysql/mysql/time_zone_transition.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_leap_second.MYD'
to '/var/lib/mysql/mysql/time_zone_leap_second.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/func.MYD' to
'/var/lib/mysql/mysql/func.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/columns_priv.MYI' to
'/var/lib/mysql/mysql/columns_priv.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/plugin.frm' to
'/var/lib/mysql/mysql/plugin.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_name.MYI' to
'/var/lib/mysql/mysql/time_zone_name.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/slow_log.frm' to
'/var/lib/mysql/mysql/slow_log.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/tables_priv.MYI' to
'/var/lib/mysql/mysql/tables_priv.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_name.frm' to
'/var/lib/mysql/mysql/time_zone_name.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/user.frm' to
'/var/lib/mysql/mysql/user.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/servers.MYD' to
'/var/lib/mysql/mysql/servers.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_transition.MYI'
to '/var/lib/mysql/mysql/time_zone_transition.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_category.MYD' to
'/var/lib/mysql/mysql/help_category.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/event.MYD' to
'/var/lib/mysql/mysql/event.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_keyword.MYD' to
'/var/lib/mysql/mysql/help_keyword.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/proc.frm' to
'/var/lib/mysql/mysql/proc.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/ndb_binlog_index.MYI' to
'/var/lib/mysql/mysql/ndb_binlog_index.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/servers.MYI' to
'/var/lib/mysql/mysql/servers.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_relation.MYI' to
'/var/lib/mysql/mysql/help_relation.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_category.MYI' to
'/var/lib/mysql/mysql/help_category.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/func.frm' to
'/var/lib/mysql/mysql/func.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/db.MYD' to
'/var/lib/mysql/mysql/db.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone.MYD' to
'/var/lib/mysql/mysql/time_zone.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_transition_type.frm'
to '/var/lib/mysql/mysql/time_zone_transition_type.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_leap_second.MYI'
to '/var/lib/mysql/mysql/time_zone_leap_second.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/db.MYI' to
'/var/lib/mysql/mysql/db.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_transition.MYD'
to '/var/lib/mysql/mysql/time_zone_transition.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_name.MYD' to
'/var/lib/mysql/mysql/time_zone_name.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/db.frm' to
'/var/lib/mysql/mysql/db.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/event.frm' to
'/var/lib/mysql/mysql/event.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_relation.MYD' to
'/var/lib/mysql/mysql/help_relation.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/columns_priv.MYD' to
'/var/lib/mysql/mysql/columns_priv.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/plugin.MYI' to
'/var/lib/mysql/mysql/plugin.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/event.MYI' to
'/var/lib/mysql/mysql/event.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/host.MYD' to
'/var/lib/mysql/mysql/host.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/proc.MYI' to
'/var/lib/mysql/mysql/proc.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/user.MYD' to
'/var/lib/mysql/mysql/user.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/general_log.frm' to
'/var/lib/mysql/mysql/general_log.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_transition_type.MYI'
to '/var/lib/mysql/mysql/time_zone_transition_type.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/servers.frm' to
'/var/lib/mysql/mysql/servers.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/user.MYI' to
'/var/lib/mysql/mysql/user.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_keyword.frm' to
'/var/lib/mysql/mysql/help_keyword.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/host.frm' to
'/var/lib/mysql/mysql/host.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/plugin.MYD' to
'/var/lib/mysql/mysql/plugin.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/ndb_binlog_index.frm' to
'/var/lib/mysql/mysql/ndb_binlog_index.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone_leap_second.frm'
to '/var/lib/mysql/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/columns_priv.frm' to
'/var/lib/mysql/mysql/columns_priv.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/tables_priv.frm' to
'/var/lib/mysql/mysql/tables_priv.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/ndb_binlog_index.MYD' to
'/var/lib/mysql/mysql/ndb_binlog_index.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/general_log.CSV' to
'/var/lib/mysql/mysql/general_log.CSV'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone.frm' to
'/var/lib/mysql/mysql/time_zone.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_topic.MYD' to
'/var/lib/mysql/mysql/help_topic.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_keyword.MYI' to
'/var/lib/mysql/mysql/help_keyword.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/general_log.CSM' to
'/var/lib/mysql/mysql/general_log.CSM'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/slow_log.CSV' to
'/var/lib/mysql/mysql/slow_log.CSV'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/time_zone.MYI' to
'/var/lib/mysql/mysql/time_zone.MYI'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/slow_log.CSM' to
'/var/lib/mysql/mysql/slow_log.CSM'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_relation.frm' to
'/var/lib/mysql/mysql/help_relation.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/proc.MYD' to
'/var/lib/mysql/mysql/proc.MYD'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/procs_priv.frm' to
'/var/lib/mysql/mysql/procs_priv.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_category.frm' to
'/var/lib/mysql/mysql/help_category.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/help_topic.frm' to
'/var/lib/mysql/mysql/help_topic.frm'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/mysql/func.MYI' to
'/var/lib/mysql/mysql/func.MYI'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/data/backup/mysql/2013-11-11_21-04-42'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying '/data/backup/mysql/2013-11-11_21-04-42/ibdata1'
to '/var/lib/mysql/ibdata1'

innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/data/backup/mysql/2013-11-11_21-04-42'
innobackupex: back to '/var/lib/mysql'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/data/backup/mysql/2013-11-11_21-04-42'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Finished copying back files.

131111 20:30:00  innobackupex: completed OK!

感想
楽だった。コマンドのオプションなど面白そうなものがたくさんあるので色々と試してみたい。