【Mysql】xtrabackup 备份和恢复测试

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> 

时间: 2025-01-29 15:01:03

【Mysql】xtrabackup 备份和恢复测试的相关文章

MySQL的备份和恢复

前奏: 建议在Linux中使用RPM包来安装MySQL.MySQL RPM目前已经嵌入到SuSE Linux 7.3系统中,但是应当能在大多数支持rpm和使用glibc的Linux版本中工作. MySQL AB不提供与具体平台相关的RPM:具体平台相关的RPM和通用RPM之间的区别是具体平台相关RPM为目标平台而构建,为动态连接.而通用RPM与Linux线程之间是静态连接. 注释:通常由其它供应商提供MySQL的RPM分发版.其特征和功能与MySQL AB所构建的不同,该手册中的指令不一定适合安

MySQL数据库备份和恢复详解

本文讨论 MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb,文中设计的 MySQL 版本为 5.0.22.      目前 MySQL 支持的免费备份工具有:mysqldump.mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件.MyISAM 表是保存成文件的形式,因此相对比较容易备份

mysql dba系统学习(17)mysql的备份和恢复的完整实践

mysql的备份和恢复的完整实践 一,备份数据库之间的环境设置 1,创建数据库test1,创建表tt插入如下数据 mysql> create database test1; Query OK, 1 row affected (0.04 sec) mysql> use test1 Database changed mysql> create table tt(id int,name varchar(100),msg varchar(200)) engine=myisam; Query OK

深入分析MySQL 的备份和恢复机制_Mysql

本文讨论 MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型: MyISAM 和 Innodb ,文中设计的 MySQL 版本为 5.0.22. 目前 MySQL 支持的免费备份工具有: mysqldump.mysqlhotcopy ,还可以用 SQL 语法进行备份: BACKUP TABLE 或者 SELECT INTO OUTFILE ,又或者备份 二进制日志(binlog) ,还可以是 直接拷贝数据文件和相关的配置文件 .MyISAM 表是保存成文件的形式,因此相对比

mysql xtrabackup 备份恢复实现分享_Mysql

简介 Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具.特点: (1)备份过程快速.可靠: (2)备份过程不会打断正在执行的事务: (3)能够基于压缩等功能节约磁盘空间和流量: (4)自动实现备份检验: (5)还原速度快: Xtrabackup中包含两个工具: * xtrabackup - 用于热备份innodb, xtradb表的工具,不能备份其他表. * innobackupex

Linux下实现MySQL数据备份和恢复的命令使用全攻略_Mysql

为了保障数据的安全,需要定期对数据进行备份.备份的方式有很多种,效果也不一样.一旦数据库中的数据出现了错误,就需要使用备份好的数据进行还原恢复.从而将损失降到最低.下面我们来了解一下MySQL常见的有三种备份恢复方式: 1.利用Mysqldump+二进制日志实现备份 2.利用LVM快照+二进制日志实现备份 3.使用Xtrabackup备份 一:实验环境介绍: 系统介绍:CentOS6.4_X64 数据库版本:mysql-5.5.33 二:基于Mysqldump命令实现备份恢复 2.1.思路概念

mysql数据库备份及恢复命令 mysqldump,source的用法_Mysql

还原一个数据库:mysql -h localhost -u root -p123456 www<c:\www.sql 备份一个数据库:mysqldump -h localhost -u root -p123456 www > d:\www2008-2-26.sql //以下是在程序中进行测试 //$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile&qu

java Mysql 数据库备份和恢复

package cn.com.git.demo; import java.io.BufferedReader; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.InputStreamReader; import java.io.OutputStream; import java.io.OutputStreamWriter; pub

利用Xtrabackup工具备份及恢复(MySQL DBA的必备工具)_Mysql

Xtrabackup------MySQL DBA的必备工具 注意: 1)文档参照http://www.percona.com/docs/wiki/percona-xtrabackup:start 2)mysql要使用5.1.50版本或以上. 一.Xtrabackup简介及安装 1.Xtrabackup  是percona的一个开源项目,可以热备份innodb ,XtraDB,和MyISAM(会锁表),可以看做是InnoDB Hotbackup的免费替代品.