ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Mysql】xtrabackup 备份和恢复测试

【Mysql】xtrabackup 备份和恢复测试

原创 Linux操作系统 作者:杨奇龙 时间:2011-12-11 19:43:57 0 删除 编辑
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> 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-713124/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7531302