xtrabackup 备份

-----xtrabackup备份简介
xtrabackup是一个percona开源数据库备份软件，innobackupex则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁，对InnoDB存储引擎实现热备；备份原理：    开始备份前，innobackupex会连接到数据库获取当前LSN(日志序列号)，在拷贝数据文件同时，监控InnoDB状态，当发现LSN变化，innobackupex会将变化的LSN附加到xtrabackup_logfile，如果有大量的增删改操作xtrabackup_logfile会很庞大(建议在业务低峰时备份)    还原数据前，innobackupex需要将xtrabackup_logfile文件中已经提交的事务合并到数据文件中，对未提交的事务进行回滚

mysql> SHOW ENGINE INNODB STATUS\G---LOG---Log sequence number 90710866Log flushed up to   90710866Last checkpoint at  907108660 pending log writes, 0 pending chkp writes
-----

安装

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.3/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.3-1.el7.x86_64.rpm
xtrabackupperl-Digest-MD5.x86_64-----

数据库关键参数设置

log_bin = 1server-id = 1innodb_file_per_table = 1　＃是否能进行单表恢复关键参数#　skip_name_resolve = 1innodb_buffer_pool_size = 1G----备份所需权限mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES
----

脚本

cat mysql_backup_restore_script.sｈ

全备

------单库备份

整合
------------------------------单表备份例子1流程： 备份 --> 备份数据prepare --> 删除表空间 --> 拷贝数据文件 --> 导入表空间 -->完成   

恢复时必须导出单独表

ALTER TABLE tbl_name IMPORT TABLESPACE;

例子2

------

单表恢复

操作前，建议先进行单表备份，　CREATE TABLE backup LIKE database.oldtable;INSERT INTO backup SELECT * FROM database.oldtable;
例子：恢复ids表

1、错删除操作

2、删除表空间mysql> ALTER TABLE zrd.ids DISCARD TABLESPACE;Query OK, 0 rows affected (0.02 sec)

3、解压缩for bf in `find /tmp/backup/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm -f $bf; done
4、导出单表innobackupex --apply-log --read-only --export /tmp/backup/FULL/
5、拷贝文件.frm 不拷贝cp ids.cfg ids.exp ids.ibd /var/lib/mysql/zrdchown -R mysql:mysql　/var/lib/mysql/zrd
6、导入表空间

7、数据恢复完成

------

备份目录数据概览

-----表空间解释By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and associated indexes in its own data file. This feature is called “file-per-table tablespaces” because each table has its own tablespace, and each tablespace has its own .ibd data file. This feature is controlled by the innodb_file_per_table configuration option.
来源： http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Portability Considerations for .ibd Files

You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

ALTER TABLE statement to delete the current .ibd file:

ALTER TABLE tbl_name DISCARD TABLESPACE;

Copy the backup .ibd file to the proper database directory.

Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:

ALTER TABLE tbl_name IMPORT TABLESPACE;