xtrabackup restore mysql database example
时间：2010-08-06 来源：rainbow
要求：
1. my.cnf，xtrabackup会读取my.cnf来确定mysql的配置
2. 相同的目录结构
xtrabackup的恢复十分简单，2板斧搞定（apply-log,copy-back）,深入的备份恢复设置持续更新。。。
假设备份如下：
/usr/local/xtrabackup/bin/innobackupex-1.5.1 --user=root /usr/local/mysql/bk/.
恢复步骤如下：
1. [mysql@mawebtest2 bk]$ innobackupex-1.5.1 --apply-log 2010-08-06_09-03-08/
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.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex-1.5.1
prints "completed OK!".
100806 09:48:45 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --prepare --target-dir=/usr/local/mysql/bk/2010-08-06_09-03-08
xtrabackup Ver 1.2 Rev undefined for 5.1.45 pc-linux-gnu (i686)
xtrabackup: cd to /usr/local/mysql/bk/2010-08-06_09-03-08
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(600990168)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:256M: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 InnoDB's own implementation
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
100806 9:48:45 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!
100806 9:48:45 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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
100806 9:48:46 InnoDB Plugin 1.0.6-unknown started; log sequence number 600990168
[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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100806 9:48:46 InnoDB: Starting shutdown...
100806 9:48:47 InnoDB: Shutdown completed; log sequence number 600990178
100806 09:48:47 innobackupex-1.5.1: Restarting xtrabackup with command: xtrabackup --prepare --target-dir=/usr/local/mysql/bk/2010-08-06_09-03-08
for creating ib_logfile*
xtrabackup Ver 1.2 Rev undefined for 5.1.45 pc-linux-gnu (i686)
xtrabackup: cd to /usr/local/mysql/bk/2010-08-06_09-03-08
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:256M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 134217728
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 InnoDB's own implementation
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
100806 9:48:47 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
100806 9:48:49 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
100806 9:48:51 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
100806 9:48:54 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!
100806 9:48:54 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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
100806 9:48:54 InnoDB Plugin 1.0.6-unknown started; log sequence number 600990220
[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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100806 9:48:54 InnoDB: Starting shutdown...
100806 9:48:55 InnoDB: Shutdown completed; log sequence number 600990230
100806 09:48:55 innobackupex-1.5.1: completed OK!
Note: 该步骤2次调用xtrabackup --prepare --target-dir=/usr/local/mysql/bk/2010-08-06_09-03-08；当然你手工执行2次该命令结果相同
2. [mysql@mawebtest2 ~]$ innobackupex-1.5.1 --copy-back bk/2010-08-06_09-03-08/
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.
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 MyISAM tables, indexes,
innobackupex-1.5.1: .MRG, .TRG, .TRN, .ARM, .ARZ, .opt, and .frm files
innobackupex-1.5.1: in '/usr/local/mysql/bk/2010-08-06_09-03-08'
innobackupex-1.5.1: back to original data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/mysql'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/test'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/erp'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/malabs'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/xtrabackup_binlog_info'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/xtrabackup_checkpoints'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/prodlib'
innobackupex-1.5.1: Starting to copy InnoDB tables and indexes
innobackupex-1.5.1: in '/usr/local/mysql/bk/2010-08-06_09-03-08'
innobackupex-1.5.1: back to original InnoDB data directory '/usr/local/mysql/data/'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ibdata1'
innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/usr/local/mysql/bk/2010-08-06_09-03-08'
innobackupex-1.5.1: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ib_logfile0'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ib_logfile1'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ib_logfile2'
innobackupex-1.5.1: Finished copying back files.
100806 09:42:09 innobackupex-1.5.1: completed OK!
Note：该步骤将prepare的文件cp到my.cnf中配置的目录中。
3. start mysql : service mysql start.
1. my.cnf，xtrabackup会读取my.cnf来确定mysql的配置
2. 相同的目录结构
xtrabackup的恢复十分简单，2板斧搞定（apply-log,copy-back）,深入的备份恢复设置持续更新。。。
假设备份如下：
/usr/local/xtrabackup/bin/innobackupex-1.5.1 --user=root /usr/local/mysql/bk/.
恢复步骤如下：
1. [mysql@mawebtest2 bk]$ innobackupex-1.5.1 --apply-log 2010-08-06_09-03-08/
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.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex-1.5.1
prints "completed OK!".
100806 09:48:45 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --prepare --target-dir=/usr/local/mysql/bk/2010-08-06_09-03-08
xtrabackup Ver 1.2 Rev undefined for 5.1.45 pc-linux-gnu (i686)
xtrabackup: cd to /usr/local/mysql/bk/2010-08-06_09-03-08
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(600990168)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:256M: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 InnoDB's own implementation
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
100806 9:48:45 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!
100806 9:48:45 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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
100806 9:48:46 InnoDB Plugin 1.0.6-unknown started; log sequence number 600990168
[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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100806 9:48:46 InnoDB: Starting shutdown...
100806 9:48:47 InnoDB: Shutdown completed; log sequence number 600990178
100806 09:48:47 innobackupex-1.5.1: Restarting xtrabackup with command: xtrabackup --prepare --target-dir=/usr/local/mysql/bk/2010-08-06_09-03-08
for creating ib_logfile*
xtrabackup Ver 1.2 Rev undefined for 5.1.45 pc-linux-gnu (i686)
xtrabackup: cd to /usr/local/mysql/bk/2010-08-06_09-03-08
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:256M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 134217728
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 InnoDB's own implementation
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
100806 9:48:47 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
100806 9:48:49 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
100806 9:48:51 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
100806 9:48:54 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!
100806 9:48:54 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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
100806 9:48:54 InnoDB Plugin 1.0.6-unknown started; log sequence number 600990220
[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 525208598, file name /usr/local/mysql/binlog/mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100806 9:48:54 InnoDB: Starting shutdown...
100806 9:48:55 InnoDB: Shutdown completed; log sequence number 600990230
100806 09:48:55 innobackupex-1.5.1: completed OK!
Note: 该步骤2次调用xtrabackup --prepare --target-dir=/usr/local/mysql/bk/2010-08-06_09-03-08；当然你手工执行2次该命令结果相同
2. [mysql@mawebtest2 ~]$ innobackupex-1.5.1 --copy-back bk/2010-08-06_09-03-08/
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.
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 MyISAM tables, indexes,
innobackupex-1.5.1: .MRG, .TRG, .TRN, .ARM, .ARZ, .opt, and .frm files
innobackupex-1.5.1: in '/usr/local/mysql/bk/2010-08-06_09-03-08'
innobackupex-1.5.1: back to original data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/mysql'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/test'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/erp'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/malabs'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/xtrabackup_binlog_info'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/xtrabackup_checkpoints'
innobackupex-1.5.1: Copying directory '/usr/local/mysql/bk/2010-08-06_09-03-08/prodlib'
innobackupex-1.5.1: Starting to copy InnoDB tables and indexes
innobackupex-1.5.1: in '/usr/local/mysql/bk/2010-08-06_09-03-08'
innobackupex-1.5.1: back to original InnoDB data directory '/usr/local/mysql/data/'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ibdata1'
innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/usr/local/mysql/bk/2010-08-06_09-03-08'
innobackupex-1.5.1: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ib_logfile0'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ib_logfile1'
innobackupex-1.5.1: Copying file '/usr/local/mysql/bk/2010-08-06_09-03-08/ib_logfile2'
innobackupex-1.5.1: Finished copying back files.
100806 09:42:09 innobackupex-1.5.1: completed OK!
Note：该步骤将prepare的文件cp到my.cnf中配置的目录中。
3. start mysql : service mysql start.
相关阅读 更多 +
