mysql innobackupex windows_mysql xtrabackup在windows平台的备份测试

Xtrabackup有2款主要工具：xtrabackup和innobackupex

xtrabackup只能备份和恢复innodb，而且只备份.ibd文件，.frm文件它不管，恢复时DBA要自己提供

innobackupex能备份和恢复MyISAM文件及.frm文件，并且对xtrabackup作了友好的封装，所以可用它进行全库备份

windows 64位平台下载地址:

https://www.percona.com/downloads/XtraBackup/XtraBackup-1.6/Windows-alpha/

下载完解压缩到C盘xtrabackup目录下,发现共有4个文件,xtrabackup.exe是可执行文件:

C:\xtrabackup>dir

驱动器 C 中的卷是 Windows8_OS

卷的序列号是 0E54-579B

C:\xtrabackup 的目录

2016/06/03  09:42

.

2016/06/03  09:42

..

2009/12/08  14:37             6,656 cat.exe

2003/10/23  17:35           135,680 grep.exe

2011/04/21  15:23            90,876 innobackupex.pl

2011/04/20  11:39         4,863,488 xtrabackup.exe

这里我们用xtrabackup命令进行innodb全库备份测试:

xtrabackup命令的用法:

Usage: [C:\xtrabackup\xtrabackup [--defaults-file=#] --backup | C:\xtrabackup\xtrabackup [--defaults-file=#] --prepare] [OPTIONS]

建立innodb测试库:

mysql> create database duansf;

Query OK, 1 row affected (0.17 sec)

mysql> use duansf

Database changed

mysql>

mysql>

mysql>

mysql>

mysql> create table dsf(id int);

Query OK, 0 rows affected (0.37 sec)

mysql> insert into dsf values(1);

Query OK, 1 row affected (0.11 sec)

mysql> insert into dsf values(2);

Query OK, 1 row affected (0.14 sec)

mysql> insert into dsf values(3);

Query OK, 1 row affected (0.06 sec)

mysql> insert into dsf values(4);

Query OK, 1 row affected (0.17 sec)

mysql> insert into dsf values(5);

Query OK, 1 row affected (0.09 sec)

mysql> select * from dsf;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

+------+

5 rows in set (0.03 sec)

mysql>

查看数据文件所在目录:

mysql> show variables like 'datadir';

+---------------+---------------------------------------------+

| Variable_name | Value                                       |

+---------------+---------------------------------------------+

| datadir       | C:\ProgramData\MySQL\MySQL Server 5.5\Data\ |

+---------------+---------------------------------------------+

1 row in set (0.00 sec)

mysql>

完全备份:

C:\xtrabackup\xtrabackup --target-dir=C:\xtrabackup\full --backup --datadir="C:\ProgramData\MySQL\MySQL Server 5.5\Data\"

c:\xtrabackup>C:\xtrabackup\xtrabackup --target-dir=C:\xtrabackup\full --backup

--datadir="C:\ProgramData\MySQL\MySQL Server 5.5\Data\duansf"

C:\xtrabackup\xtrabackup  Ver undefined Rev undefined for 5.5.9 Win64 (x86)

xtrabackup: cd to C:\ProgramData\MySQL\MySQL Server 5.5\Data\duansf

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 = 5242880

InnoDB: The first specified data file .\ibdata1 did not exist:

InnoDB: a new database to be created!

160603 10:32:56  InnoDB: Setting file .\ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait...

xtrabackup: Something wrong with source files...

发现有报错，后来研究了一下，把C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini拷贝到c:\xtrabackup目录下再执行以下命令后测试通过:

c:\xtrabackup>xtrabackup.exe --defaults-file=C:\xtrabackup\my.ini --backup --target-dir=C:\xtrabackup\full

xtrabackup.exe  Ver undefined Rev undefined for 5.5.9 Win64 (x86)

xtrabackup: cd to C:/ProgramData/MySQL/MySQL Server 5.5/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 = 118489088

>> log scanned up to (1625547)

[01] Copying .\ibdata1

to C:\xtrabackup\full/ibdata1

[01]        ...done

xtrabackup: The latest check point (for incremental): '1625547'

xtrabackup: Stopping log copying thread..>> log scanned up to (1625547)

xtrabackup: Transaction log of lsn (1625547) to (1625547) was copied.

注意:如果重复执行xtrabackup命令执行备份就会报错，我猜测这可能是由于xtrabackup在windows平台只是个测试版，没有发布正式版，因此

生产环境还是慎用windows平台版本。

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