mysql xtrabackup在windows平台的备份测试是怎么样的,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
Xtrabackup有2款主要工具:xtrabackup和innobackupex
xtrabackup只能备份和恢复innodb,而且只备份.ibd文件,.frm文件它不管,恢复时DBA要自己提供
innobackupex能备份和恢复MyISAM文件及.frm文件,并且对xtrabackup作了友好的封装,所以可用它进行全库备份
下载完解压缩到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平台版本。
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。
原创文章,作者:奋斗,如若转载,请注明出处:https://blog.ytso.com/tech/database/201817.html