1 创建测试环境
mysql> create table t1 as select * from sbtest;
Query OK, 1000000 rows affected (33.37 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 1000000 rows affected (1 min 4.02 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql>
mysql> exit
2 执行备份
-bash-3.2$ xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/opt/mysql/backup/test
xtrabackup version 1.6.3 for Percona Server 5.1.55 unknown-linux-gnu (x86_64) (revision id: 292)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /opt/mysql/data
xtrabackup: Target instance is assumed 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 = 268435456
>> log scanned up to (4972495139)
[01] Copying ./ibdata1
to /opt/mysql/backup/test/ibdata1
>> log scanned up to (4972495139)
>> log scanned up to (4972495139)
>> log scanned up to (4972495139)
>> log scanned up to (4972495139)
>> log scanned up to (4972495139)
>> log scanned up to (4976241217)
>> log scanned up to (5050531464)
>> log scanned up to (5116904909)
>> log scanned up to (5144668918)
>> log scanned up to (5167804122)
>> log scanned up to (5228139500)
>> log scanned up to (5230910238)
>> log scanned up to (5230910238)
>> log scanned up to (5230910238)
>> log scanned up to (5230910238)
>> log scanned up to (5230910238)
[01] ...done
xtrabackup: The latest check point (for incremental): '5178591272'
>> log scanned up to (5230910238)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (4972495139) to (5230910238) was copied.
3 关闭mysql 服务：
[root@rac3 tmp]# service mysql stop
Shutting down MySQL.. [确定]
4 删除数据文件和innodb log
-bash-3.2$ pwd
/opt/mysql/data
-bash-3.2$ ls ib*
ibdata1 ib_logfile0 ib_logfile1 ib_logfile2
-bash-3.2$ rm ib*
-bash-3.2$
5 使用xtrabackup 恢复数据
-bash-3.2$ xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/opt/mysql/backup/test
xtrabackup version 1.6.3 for Percona Server 5.1.55 unknown-linux-gnu (x86_64) (revision id: 292)
xtrabackup: cd to /opt/mysql/backup/test
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=290717696, start_lsn=(4972495139)
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 = 290717696
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
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
111211 16:25:36 InnoDB: Initializing buffer pool, size = 100.0M
111211 16:25:36 InnoDB: Completed initialization of buffer pool
111211 16:25:36 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 4972495139
111211 16:25:36 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 4977737728 (2 %)
InnoDB: Doing recovery: scanned up to log sequence number 4982980608 (4 %)
InnoDB: Doing recovery: scanned up to log sequence number 4988223488 (6 %)
InnoDB: Doing recovery: scanned up to log sequence number 4993466368 (8 %)
InnoDB: Doing recovery: scanned up to log sequence number 4998709248 (10 %)
InnoDB: Doing recovery: scanned up to log sequence number 5003952128 (12 %)
InnoDB: Doing recovery: scanned up to log sequence number 5009195008 (14 %)
InnoDB: Doing recovery: scanned up to log sequence number 5014437888 (16 %)
InnoDB: Doing recovery: scanned up to log sequence number 5019680768 (18 %)
InnoDB: Doing recovery: scanned up to log sequence number 5024923648 (20 %)
InnoDB: Doing recovery: scanned up to log sequence number 5030166528 (22 %)
111211 16:25:38 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: Doing recovery: scanned up to log sequence number 5035409408 (24 %)
InnoDB: Doing recovery: scanned up to log sequence number 5040652288 (26 %)
InnoDB: Doing recovery: scanned up to log sequence number 5045895168 (28 %)
InnoDB: Doing recovery: scanned up to log sequence number 5051138048 (30 %)
InnoDB: Doing recovery: scanned up to log sequence number 5056380928 (32 %)
InnoDB: Doing recovery: scanned up to log sequence number 5061623808 (34 %)
InnoDB: Doing recovery: scanned up to log sequence number 5066866688 (36 %)
InnoDB: Doing recovery: scanned up to log sequence number 5072109568 (38 %)
InnoDB: Doing recovery: scanned up to log sequence number 5077352448 (40 %)
InnoDB: Doing recovery: scanned up to log sequence number 5082595328 (42 %)
InnoDB: Doing recovery: scanned up to log sequence number 5087838208 (44 %)
111211 16:25:42 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: Doing recovery: scanned up to log sequence number 5093081088 (46 %)
InnoDB: Doing recovery: scanned up to log sequence number 5098323968 (48 %)
InnoDB: Doing recovery: scanned up to log sequence number 5103566848 (50 %)
InnoDB: Doing recovery: scanned up to log sequence number 5108809728 (52 %)
InnoDB: Doing recovery: scanned up to log sequence number 5114052608 (54 %)
InnoDB: Doing recovery: scanned up to log sequence number 5119295488 (56 %)
InnoDB: Doing recovery: scanned up to log sequence number 5124538368 (58 %)
InnoDB: Doing recovery: scanned up to log sequence number 5129781248 (60 %)
InnoDB: Doing recovery: scanned up to log sequence number 5135024128 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 5140267008 (64 %)
InnoDB: Doing recovery: scanned up to log sequence number 5145509888 (66 %)
111211 16:25:45 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: Doing recovery: scanned up to log sequence number 5150752768 (68 %)
InnoDB: Doing recovery: scanned up to log sequence number 5155995648 (71 %)
InnoDB: Doing recovery: scanned up to log sequence number 5161238528 (73 %)
InnoDB: Doing recovery: scanned up to log sequence number 5166481408 (75 %)
InnoDB: Doing recovery: scanned up to log sequence number 5171724288 (77 %)
InnoDB: Doing recovery: scanned up to log sequence number 5176967168 (79 %)
InnoDB: Doing recovery: scanned up to log sequence number 5182210048 (81 %)
InnoDB: Doing recovery: scanned up to log sequence number 5187452928 (83 %)
InnoDB: Doing recovery: scanned up to log sequence number 5192695808 (85 %)
InnoDB: Doing recovery: scanned up to log sequence number 5197938688 (87 %)
InnoDB: Doing recovery: scanned up to log sequence number 5203181568 (89 %)
111211 16:25:48 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: Doing recovery: scanned up to log sequence number 5208424448 (91 %)
InnoDB: Doing recovery: scanned up to log sequence number 5213667328 (93 %)
InnoDB: Doing recovery: scanned up to log sequence number 5218910208 (95 %)
InnoDB: Doing recovery: scanned up to log sequence number 5224153088 (97 %)
InnoDB: Doing recovery: scanned up to log sequence number 5229395968 (99 %)
InnoDB: Doing recovery: scanned up to log sequence number 5230910238 (100 %)
111211 16:25:51 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 1249, file name ./mysql-bin.000018
111211 16:25:52 Percona XtraDB (http://www.percona.com) 1.0.15-12.5 started; log sequence number 5230910238
[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 1249, file name ./mysql-bin.000018
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
111211 16:25:52 InnoDB: Starting shutdown...
111211 16:25:53 InnoDB: Shutdown completed; log sequence number 5230910238
6 将生成的文件拷贝备份到/opt/mysql/data
-bash-3.2$ cp ibdata1 /opt/mysql/data/
7 启动mysql服务
[root@rac3 tmp]# service mysql start
Starting MySQL.. [确定]
8 测试
-bash-3.2$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.18-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 2000000 |
+----------+
1 row in set (7.52 sec)
mysql>
