xtrabackup (settings)
# cat /opt/mysql-5.0.75/etc/my.cnf ..... [xtrabackup] target_dir = /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2
# ./xtrabackup
./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64)
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: [./xtrabackup --backup | ./xtrabackup --prepare] [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /opt/mysql-5.0.75/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld xtrabackup
.....
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
target-dir /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2
backup FALSE
prepare FALSE
print-param FALSE
use-memory 104857600
suspend-at-end FALSE
throttle 0
stream FALSE
create-ib-logfile FALSE
datadir /opt/mysql-5.0.75/data
tmpdir (No default value)
innodb_adaptive_hash_index TRUE
innodb_additional_mem_pool_size 16777216
innodb_autoextend_increment 8
innodb_buffer_pool_size 134217728
innodb_checksums TRUE
innodb_data_file_path (No default value)
innodb_data_home_dir (No default value)
innodb_doublewrite FALSE
innodb_file_io_threads 4
innodb_file_per_table TRUE
innodb_flush_log_at_trx_commit 0
innodb_flush_method (No default value)
innodb_force_recovery 0
innodb_lock_wait_timeout 120
innodb_log_buffer_size 16777216
innodb_log_file_size 67108864
innodb_log_files_in_group 3
innodb_log_group_home_dir (No default value)
innodb_max_dirty_pages_pct 90
innodb_open_files 300
# ./xtrabackup --backup
./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64)
>> log scanned up to (0 1009910580)
Copying ./ibdata1
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/ibdata1
...done
Copying ./tpcc/stock.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/stock.ibd
...done
Copying ./tpcc/new_orders.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/new_orders.ibd
...done
Copying ./tpcc/history.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/history.ibd
...done
Copying ./tpcc/customer.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/customer.ibd
>> log scanned up to (0 1010561109)
...done
Copying ./tpcc/district.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/district.ibd
...done
Copying ./tpcc/item.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/item.ibd
...done
Copying ./tpcc/order_line.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/order_line.ibd
>> log scanned up to (0 1012047066)
...done
Copying ./tpcc/orders.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/orders.ibd
...done
Copying ./tpcc/warehouse.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp2/tpcc/warehouse.ibd
...done
>> log scanned up to (0 1014592707)
Stopping log copying thread..
Transaction log of lsn (0 1009910580) to (0 1014592707) was copied.
# ./xtrabackup --prepare ./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64) xtrabackup_logfile detected: size=4685824, start_lsn=(0 1009910580) InnoDB: Log scan progressed past the checkpoint lsn 0 1009910580 090309 11:42:18 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 1014592707 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 21 row operations to undo InnoDB: Trx id counter is 0 687360 090309 11:42:18 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 28670163, file name ./mysql-bin.000001 InnoDB: Starting in background the rollback of uncommitted transactions 090309 11:42:30 InnoDB: Rolling back trx with id 0 686935, 21 rows to undo 090309 11:42:30 InnoDB: Started; log sequence number 0 1014592707 [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 28670163, file name ./mysql-bin.000001 090309 11:42:30 InnoDB: Starting shutdown... InnoDB: Rolling back of trx id 0 686935 completed 090309 11:42:33 InnoDB: Rollback of non-prepared transactions completed 090309 11:42:58 InnoDB: Shutdown completed; log sequence number 0 1015046389
* second execution in same options. (if create ib_logfile*)
# ./xtrabackup --prepare ./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64) notice: xtrabackup_logfile was already used to '--prepare'. 090309 11:44:16 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 64 MB InnoDB: Database physically writes the file full: wait... 090309 11:44:19 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 64 MB InnoDB: Database physically writes the file full: wait... 090309 11:44:21 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 64 MB InnoDB: Database physically writes the file full: wait... InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090309 11:44:24 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Last MySQL binlog file position 0 28670163, file name ./mysql-bin.000001 090309 11:44:24 InnoDB: Started; log sequence number 0 1015046668 [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 28670163, file name ./mysql-bin.000001 090309 11:44:24 InnoDB: Starting shutdown... 090309 11:44:25 InnoDB: Shutdown completed; log sequence number 0 1015046668
# ./innobackupex-1.5.1 ./tmp3
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 backup run completes successfully.
At the end of a successful backup run innobackup
prints "innobackup completed OK!".
innobackup: Using mysql Ver 14.12 Distrib 5.0.75, for unknown-linux-gnu (x86_64) using readline 5.1
innobackup: Using mysql server version 5.0.75
innobackup: Created backup directory /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21
090309 11:46:21 innobackup: Starting mysql with options: --unbuffered
090309 11:46:21 innobackup: Connected to database with mysql child process (pid=5665)
090309 11:46:25 innobackup: Connection to database server closed
090309 11:46:25 innobackup: Starting ibbackup with command: ./xtrabackup --backup --suspend-at-end --target-dir=/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21
innobackup: Waiting for ibbackup (pid=5671) to suspend
innobackup: Suspend file '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/xtrabackup_suspended'
./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64)
>> log scanned up to (0 1019429185)
Copying ./ibdata1
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/ibdata1
...done
Copying ./tpcc/stock.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/stock.ibd
...done
Copying ./tpcc/new_orders.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/new_orders.ibd
...done
Copying ./tpcc/history.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/history.ibd
...done
Copying ./tpcc/customer.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/customer.ibd
...done
Copying ./tpcc/district.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/district.ibd
...done
Copying ./tpcc/item.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/item.ibd
>> log scanned up to (0 1019429185)
...done
Copying ./tpcc/order_line.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/order_line.ibd
>> log scanned up to (0 1019429185)
...done
Copying ./tpcc/orders.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/orders.ibd
...done
Copying ./tpcc/warehouse.ibd
to /home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc/warehouse.ibd
...done
>> log scanned up to (0 1019429185)
090309 11:46:46 innobackup: Continuing after ibbackup has suspended
090309 11:46:46 innobackup: Starting mysql with options: --unbuffered
090309 11:46:46 innobackup: Connected to database with mysql child process (pid=5678)
090309 11:46:50 innobackup: Starting to lock all tables...
>> log scanned up to (0 1019429185)
>> log scanned up to (0 1019432135)
>> log scanned up to (0 1019432208)
>> log scanned up to (0 1019432214)
090309 11:47:06 innobackup: All tables locked and flushed to disk
090309 11:47:06 innobackup: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackup: .TRG, .TRN, and .opt files in
innobackup: subdirectories of '/opt/mysql-5.0.75/data'
innobackup: Backing up files '/opt/mysql-5.0.75/data/tpcc/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (10 files)
innobackup: Backing up files '/opt/mysql-5.0.75/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52 files)
innobackup: Backing up file '/opt/mysql-5.0.75/data/test/db.opt'
090309 11:47:07 innobackup: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt files
innobackup: Resuming ibbackup
>> log scanned up to (0 1019432214)
Stopping log copying thread.
Transaction log of lsn (0 1019429185) to (0 1019432214) was copied.
090309 11:47:11 innobackup: All tables unlocked
090309 11:47:11 innobackup: Connection to database server closed
innobackup: Backup created in directory '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21'
innobackup: MySQL binlog position: filename '', position
090309 11:47:11 innobackup: innobackup completed OK!
# ./innobackupex-1.5.1 --apply-log tmp3/2009-03-09_11-46-21/
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 innobackup
prints "innobackup completed OK!".
090309 11:48:20 innobackup: Starting ibbackup with command: ./xtrabackup --prepare --target-dir=/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21
./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64)
xtrabackup_logfile detected: size=2097152, start_lsn=(0 1019429185)
InnoDB: Log scan progressed past the checkpoint lsn 0 1019429185
090309 11:48:20 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 1019432214
090309 11:48:21 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 28670163, file name ./mysql-bin.000001
090309 11:48:21 InnoDB: Started; log sequence number 0 1019432214
[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 28670163, file name ./mysql-bin.000001
090309 11:48:21 InnoDB: Starting shutdown...
090309 11:48:23 InnoDB: Shutdown completed; log sequence number 0 1019432214
090309 11:48:23 innobackup: Restarting xtrabackup with command: ./xtrabackup --prepare --target-dir=/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21
for creating ib_logfile*
./xtrabackup Ver alpha-0.2 for 5.0.75 unknown-linux-gnu (x86_64)
notice: xtrabackup_logfile was already used to '--prepare'.
090309 11:48:23 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
090309 11:48:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
090309 11:48:28 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 64 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090309 11:48:30 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 28670163, file name ./mysql-bin.000001
090309 11:48:30 InnoDB: Started; log sequence number 0 1019432460
[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 28670163, file name ./mysql-bin.000001
090309 11:48:30 InnoDB: Starting shutdown...
090309 11:48:32 InnoDB: Shutdown completed; log sequence number 0 1019432460
090309 11:48:32 innobackup: innobackup completed OK!
# ./innobackupex-1.5.1 --copy-back tmp3/2009-03-09_11-46-21
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 innobackup
prints "innobackup completed OK!".
innobackup: Starting to copy MyISAM tables, indexes,
innobackup: .MRG, .TRG, .TRN, .opt, and .frm files
innobackup: in '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21'
innobackup: back to original data directory '/opt/mysql-5.0.75/data'
innobackup: Copying directory '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/tpcc'
innobackup: Copying directory '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/mysql'
innobackup: Copying file '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/xtrabackup_binlog_info'
innobackup: Copying directory '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/test'
innobackup: Starting to copy InnoDB tables and indexes
innobackup: in '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21'
innobackup: back to original InnoDB data directory '/opt/mysql-5.0.75/data'
innobackup: Copying file '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/ibdata1'
innobackup: Starting to copy InnoDB log files
innobackup: in '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21'
innobackup: back to original InnoDB log directory '/opt/mysql-5.0.75/data'
innobackup: Copying file '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/ib_logfile0'
innobackup: Copying file '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/ib_logfile2'
innobackup: Copying file '/home/kinoyasu/xtrabackup_work/mysql-5.0.75/innobase/xtrabackup/tmp3/2009-03-09_11-46-21/ib_logfile1'
innobackup: Finished copying back files.
090310 15:08:43 innobackup: innobackup completed OK!