Percona XtraBackup下载地址

https://www.percona.com/downloads/XtraBackup/LATEST/

Percona XtraBackup提供了xtrabackup和innobackupex两个工具

1，xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表

2，innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢 复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

全数据库备份：

[root@ms01 /backup ] # innobackupex --user=aolens --password=*** /backup/
 
Error: option 'datadir' has different values:
'.' in defaults file
'/usr/local/mysql/data/' in SHOW VARIABLES

报错datadir默认是当前路径，show variables是/usr/local/mysql/data

查看数据库datadir的值：show global variables like '%datadir%'；

查看配置文件发现配置文件里没有datadir参数的定义，为配置文件添加上datadir=/usr/local/mysql/data

[root@ms01 ~] # innobackupex --defaults-file=/etc/my.cnf --user=aolens --password=aolens /backup/
 
160113 11:08:55 innobackupex: Starting the backup operation
 
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!" .
 
160113 11:08:55 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'aolens' (using password: YES).
160113 11:08:55 version_check Connected to MySQL server
160113 11:08:55 version_check Executing a version check against the server...
160113 11:08:55 version_check Done.
160113 11:08:55 Connecting to MySQL server host: localhost, user: aolens, password: set , port: 3306, socket: /tmp/mysql .sock
Using server version 5.5.29-log
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id : 306a2e0)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
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
160113 11:08:55 >> log scanned up to (23706694302)
xtrabackup: Generating a list of tablespaces
160113 11:08:55 [01] Copying . /ibdata1 to /backup//2016-01-13_11-08-55/ibdata1
160113 11:08:56 >> log scanned up to (23706694560)
160113 11:08:56 [01] ... done
160113 11:08:57 >> log scanned up to (23706694560)
Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
160113 11:08:57 Executing FLUSH TABLES WITH READ LOCK...
160113 11:08:57 Starting to backup non-InnoDB tables and files
 
...
 
160113 11:09:13 [00] ... done
160113 11:09:13 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point ( for incremental): '23706694560'
xtrabackup: Stopping log copying thread.
.160113 11:09:13 >> log scanned up to (23706694560)
 
160113 11:09:13 Executing UNLOCK TABLES
160113 11:09:13 All tables unlocked
160113 11:09:13 Backup created in directory '/backup//2016-01-13_11-08-55'
MySQL binlog position: filename 'mysql-bin.000032' , position '286360161'
160113 11:09:13 [00] Writing backup-my.cnf
160113 11:09:13 [00] ... done
160113 11:09:13 [00] Writing xtrabackup_info
160113 11:09:13 [00] ... done
xtrabackup: Transaction log of lsn (23706693241) to (23706694560) was copied.
160113 11:09:13 completed OK!

备份成功

所以在使用xtrabackup时一定要注意，在配置文件里写上datadir的路径，不然数据还原也会出错。

单库备份

1
[root@ms01 /backup ] # innobackupex --defaults-file=/etc/my.cnf --user=aolens --password=*** --database=recon /backup/

数据还原

提交未提交的数据

innobackupex --apply-log /backup/2016-01-13_11-08-55/

innobackup不支持离线备份，但是恢复无需启动MySQL。

删除数据目录下的一个库目录。执行恢复。

[root@node2 data] # innobackupex --copy-back /backup/2016-01-13_11-08-55/
innobackupex: Error: Original data directory '/mydata/data' is not empty! at /usr/bin/innobackupex line 2163. #报错数据目录不为空！
[root@node2 data] # rm -rf * #删除数据目录下的所有文件。
[root@node2 data] # innobackupex --copy-back /backup/2016-01-13_11-08-55/
innobackupex: Finished copying back files.
140909 02:58:36 innobackupex: completed OK!

修改恢复回来的数据的权限

chown -R mysql.mysql /usr/local/mysql/data

启动mysql即可

http://www.aolens.cn/?p=862