Last Updated on 2021年7月4日 by かんりにん
percona xtrabackupを使って作成したバックアップファイルをリストアする方法。
バックアップを取ったらリストアを検証しなくちゃね、ということで早速インスタンスをコピーして試してみることに。検証につきmy.cnfは特にいじくってないので、ご了承ください。
検証に使った環境はAWS EC2にてCentOS6＋MySQL5.5です。前日のバックアップの検証はこちら。
■参考：お世話になっております！
Preparing a Full Backup with innobackupex
Restoring a Full Backup with innobackupex
■手順
１：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
２：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コンソールやアプリケーションからアクセスして諸々テスト、問題なければリストア完了。
MySQL徹底入門 第4版 MySQL 8.0対応 Kindle版
posted with カエレバ
