mysql xtrabackup在windows平臺的備份測試

Xtrabackup有2款主要工具：xtrabackup和innobackupex
xtrabackup只能備份和恢復innodb，而且只備份.ibd檔案，.frm檔案它不管，恢復時DBA要自己提供
innobackupex能備份和恢復MyISAM檔案及.frm檔案，並且對xtrabackup作了友好的封裝，所以可用它進行全庫備份

windows 64位平臺下載地址:



下載完解壓縮到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平臺版本。