harumaki.net

インフラ屋の覚書や、ラーメン食べある記とか。

backup MySQL 運用

[MySQL] Percona xtrabackupを試す(Ver2.0.8)リストア編

投稿日:2013年9月11日

percona xtrabackupを使って作成したバックアップファイルをリストアする方法。
バックアップを取ったらリストアを検証しなくちゃね、ということで早速インスタンスをコピーして試してみることに。検証につきmy.cnfは特にいじくってないので、ご了承ください。
検証に使った環境はAWS EC2にてCentOS6+MySQL5.5です。前日のバックアップの検証はこちら

■参考:お世話になっております!
Preparing a Full Backup with innobackupex
Restoring a Full Backup with innobackupex

■手順

1:prepare backup

バックアップ準備。
バックアップ実行中に保存したトランザクションをバックアップファイルに適用する。この処理によって、起動時に必要となるib_logfile*なども作成される。

参考:https://www.percona.com/doc/percona-xtrabackup/2.0/innobackupex/preparing_a_backup_ibk.html

– 書式

# innobackupex --user root --password="" --apply-log /path/to/backup

 

2:restore

prepare 実施後にリストアを開始。リストアというよりはファイルコピーとなる。

– 書式

# innobackupex --user root --password="" --copy-back /path/to/BACKUP-DIR

 

■作業ログ

▼1.mysqldが停止していることを確認。

# ps -aef | grep mysql
root 1307 1289 0 10:13 pts/0 00:00:00 grep mysql

※起動している場合は停止する。
 

▼2.作業前の状態を確認(前日の分です)

# ls -al /tmp/xtrabackup/2013-09-10_17-33-57/
合計 18504
drwxr-xr-x 7 root root 4096 9月 10 17:34 2013 .
drwxr-xr-x 3 root root 4096 9月 10 17:33 2013 ..
-rw-r--r-- 1 root root 260 9月 10 17:33 2013 backup-my.cnf
-rw-r----- 1 root root 18874368 9月 10 17:33 2013 ibdata1
drwxr-xr-x 2 root root 4096 9月 10 17:34 2013 mysql
drwxr-xr-x 2 root root 4096 9月 10 17:34 2013 performance_schema
drwxr-xr-x 2 root root 4096 9月 10 17:34 2013 wordpress
drwxr-xr-x 2 root root 4096 9月 10 17:34 2013 wordpress_test
drwxr-xr-x 2 root root 4096 9月 10 17:34 2013 wordpress_test2
-rw-r--r-- 1 root root 13 9月 10 17:34 2013 xtrabackup_binary
-rw-r--r-- 1 root root 23 9月 10 17:34 2013 xtrabackup_binlog_info
-rw-r----- 1 root root 77 9月 10 17:34 2013 xtrabackup_checkpoints
-rw-r----- 1 root root 2560 9月 10 17:34 2013 xtrabackup_logfile

 

▼3.prepare backupを実行

# innobackupex --user root --password=rabbirabbi --apply-log /tmp/xtrabackup/2013-09-10_17-33-57

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 apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".

130911 10:17:38 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/tmp/xtrabackup/2013-09-10_17-33-57/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/tmp/xtrabackup/2013-09-10_17-33-57 --tmpdir=/tmp

xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: cd to /tmp/xtrabackup/2013-09-10_17-33-57
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(3377014)
xtrabackup: Temporary instance for recovery is set 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 = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set 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 = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
130911 10:17:38 InnoDB: Initializing buffer pool, size = 100.0M
130911 10:17:38 InnoDB: Completed initialization of buffer pool
130911 10:17:38 InnoDB: highest supported file format is Barracuda.
130911 10:17:38 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 3377014

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 4503608, file name ./mysql-bin.000009

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130911 10:17:38 InnoDB: Starting shutdown...
130911 10:17:38 InnoDB: Shutdown completed; log sequence number 3378244

130911 10:17:38 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file="/tmp/xtrabackup/2013-09-10_17-33-57/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/tmp/xtrabackup/2013-09-10_17-33-57 --tmpdir=/tmp
for creating ib_logfile*

xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: cd to /tmp/xtrabackup/2013-09-10_17-33-57
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set 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 = 5242880
xtrabackup: Temporary instance for recovery is set 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 = 5242880
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
130911 10:17:38 InnoDB: Initializing buffer pool, size = 100.0M
130911 10:17:38 InnoDB: Completed initialization of buffer pool
130911 10:17:38 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
130911 10:17:39 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
130911 10:17:39 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!
130911 10:17:39 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 4503608, file name ./mysql-bin.000009
130911 10:17:39 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 3378700

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 4503608, file name ./mysql-bin.000009

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130911 10:17:39 InnoDB: Starting shutdown...
130911 10:17:39 InnoDB: Shutdown completed; log sequence number 3378700
130911 10:17:39 innobackupex: completed OK!
#

 

– 実行後のファイルを確認

# ls -al /tmp/xtrabackup/2013-09-10_17-33-57/
合計 30820
drwxr-xr-x 7 root root 4096 9月 10 17:34 .
drwxr-xr-x 3 root root 4096 9月 10 17:33 ..
-rw-r--r-- 1 root root 260 9月 10 17:33 backup-my.cnf
-rw-r--r-- 1 root root 5242880 9月 11 10:17 ib_logfile0
-rw-r--r-- 1 root root 5242880 9月 11 10:17 ib_logfile1
-rw-r----- 1 root root 18874368 9月 11 10:17 ibdata1
drwxr-xr-x 2 root root 4096 9月 10 17:34 mysql
drwxr-xr-x 2 root root 4096 9月 10 17:34 performance_schema
drwxr-xr-x 2 root root 4096 9月 10 17:34 wordpress
drwxr-xr-x 2 root root 4096 9月 10 17:34 wordpress_test
drwxr-xr-x 2 root root 4096 9月 10 17:34 wordpress_test2
-rw-r--r-- 1 root root 13 9月 10 17:34 xtrabackup_binary
-rw-r--r-- 1 root root 23 9月 10 17:34 xtrabackup_binlog_info
-rw-r----- 1 root root 77 9月 10 17:34 xtrabackup_checkpoints
-rw-r----- 1 root root 2560 9月 10 17:34 xtrabackup_logfile

起動時に必要となるib_logfile*なども作成された状態となる。とても便利。
 

▼4.restoreを実行

# innobackupex --user root --password= --copy-back /tmp/xtrabackup/2013-09-10_17-33-57

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 '/tmp/xtrabackup/2013-09-10_17-33-57'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/xtrabackup_binlog_pos_innodb' to '/var/lib/mysql/xtrabackup_binlog_pos_innodb'
innobackupex: Creating directory '/var/lib/mysql/wordpress-test'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/wordpress-test/db.opt' to '/var/lib/mysql/wordpress-test/db.opt'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/wordpress-test/wp_postmeta.frm' to '/var/lib/mysql/wordpress-test/wp_postmeta.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/wordpress-test/wp_term_taxonomy.frm' to '/var/lib/mysql/wordpress-test/wp_term_taxonomy.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/wordpress-test/wp_options.frm' to '/var/lib/mysql/wordpress-test/wp_options.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/wordpress-test/wp_comments.frm' to '/var/lib/mysql/wordpress-test/wp_comments.frm'

--- 長いのでsnip ---

innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/performance_timers.frm' to '/var/lib/mysql/performance_schema/performance_timers.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/events_waits_summary_global_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/threads.frm' to '/var/lib/mysql/performance_schema/threads.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/setup_timers.frm' to '/var/lib/mysql/performance_schema/setup_timers.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/events_waits_current.frm' to '/var/lib/mysql/performance_schema/events_waits_current.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/rwlock_instances.frm' to '/var/lib/mysql/performance_schema/rwlock_instances.frm'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/performance_schema/cond_instances.frm' to '/var/lib/mysql/performance_schema/cond_instances.frm'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/tmp/xtrabackup/2013-09-10_17-33-57'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/ibdata1' to '/var/lib/mysql/ibdata1'

innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/tmp/xtrabackup/2013-09-10_17-33-57'
innobackupex: back to '/var/lib/mysql'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/tmp/xtrabackup/2013-09-10_17-33-57'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/ib_logfile0' to '/var/lib/mysql'
innobackupex: Copying '/tmp/xtrabackup/2013-09-10_17-33-57/ib_logfile1' to '/var/lib/mysql'
innobackupex: Finished copying back files.

130911 10:19:26 innobackupex: completed OK!

 

– バックアップファイルが指定のディレクトリにコピーされたことを確認。

# ls -al /var/lib/mysql
合計 28752
drwxr-xr-x 7 root root 4096 9月 10 17:34 .
drwxr-xr-x 3 root root 4096 9月 10 17:33 ..
-rw-r--r-- 1 root root 5242880 9月 11 10:17 ib_logfile0
-rw-r--r-- 1 root root 5242880 9月 11 10:17 ib_logfile1
-rw-r----- 1 root root 18874368 9月 11 10:17 ibdata1
drwxr-xr-x 2 root root 4096 9月 10 17:34 mysql
drwxr-xr-x 2 root root 4096 9月 10 17:34 performance_schema
drwxr-xr-x 2 root root 4096 9月 10 17:34 wordpress
drwxr-xr-x 2 root root 4096 9月 10 17:34 wordpress_test
drwxr-xr-x 2 root root 4096 9月 10 17:34 wordpress_test2
-rw-r----- 1 root root 2560 9月 11 10:19 xtrabackup_binlog_pos_innodb

前評判通り高速でのリストアができたので非常に有用。
強いて言うと、データディレクトリのオーナー権限をmysql実行ユーザーにchownしてあげる手間が必要、ここはシステムによって実行ユーザーが変わることもあるので、手作業で修正する前提でrootのままでも差し支えは無いかな。あまり健全ではないけど…
 

▼5.mysqldを起動。

– まずオーナー権限を変更

# chown -R mysql.mysql /var/lib/mysql

– mysqld起動

# /etc/init.d/mysqld start
Starting mysqld: [ OK ]

– ログを確認

130911 10:25:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130911 10:25:19 [Note] Plugin 'FEDERATED' is disabled.
130911 10:25:19 InnoDB: The InnoDB memory heap is disabled
130911 10:25:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130911 10:25:19 InnoDB: Compressed tables use zlib 1.2.3
130911 10:25:19 InnoDB: Using Linux native AIO
130911 10:25:19 InnoDB: Initializing buffer pool, size = 128.0M
130911 10:25:19 InnoDB: Completed initialization of buffer pool
130911 10:25:19 InnoDB: highest supported file format is Barracuda.
130911 10:25:19 InnoDB: Waiting for the background threads to start
130911 10:25:20 InnoDB: 5.5.30 started; log sequence number 3378700
130911 10:25:20 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130911 10:25:20 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130911 10:25:20 [Note] Server socket created on IP: '0.0.0.0'.
130911 10:25:20 [Note] Event Scheduler: Loaded 0 events
130911 10:25:20 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.30-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Distributed by The IUS Community Project

 
InnoDBの設定やIP周りはテスト環境につき未設定のためスルー(汗
起動後、mysqlコンソールやアプリケーションからアクセスして諸々テスト、問題なければリストア完了。
 

Effective MySQL Replication Techniques in Depth
McGraw-Hill Education (2012-09-22)

 

-backup, MySQL, 運用
-, , ,

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


関連記事

no image

[MySQL] ZRM(Zmanda Recovery Manager) を試してみる

お客さんのWebサイトで運用しているMySQLにて、ここんとこmysqldump実行時にエラーが出るように。 レプリケーションにてスレーブ2ホストで同期しているので、ダンプのエラー自体は緊急性は高くな …

no image

メールサーバー構築ログ[postfix+mysql+postfixadmin]

約2年ぶりくらいにpostfix+mysql+postfixadminでメールサーバーをセットアップしたので 備忘録として。 割とスタンダードな内容なので、参考になるかわかりませんが・・・ 概要 &d …

nagios​/plugin​/SNMP​/mem監視

&topicpath; snmpでのメモリ監視(物理メモリ、仮想メモリ) 1)物理メモリ監視/check_snmp_mem スクリプト ログ コンフィグ カスタマイズ カスタマイズ後の動作テス …

nagios​/tipやメモ

&topicpath; 1)var/以下のファイル ファイル一覧 nagiosのイベントログの格納場所 2)インシデント、あるいはチケットオプションはあるか? 3)nagiosのフロントエンド …

no image

[MySQL] Percona xtrabackupを試す(Ver2.3)

PerconaのMySQL用バックアップツール。今回は2.3の最新版2.3.3を入れて検証してみる。リリースは2015年12月。 バックアップ対象のMySQLは、Percona謹製のMySQLだけでな …