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 STATUSG

---

LOG

---

Log sequence number 90710866

Log flushed up to   90710866

Last checkpoint at  90710866

0 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

xtrabackup

perl-Digest-MD5.x86_64

-----

数据库关键参数设置

log_bin = 1

server-id = 1

innodb_file_per_table = 1　＃是否能进行单表恢复关键参数

#　skip_name_resolve = 1

innodb_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/zrd

chown -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;

If you have a “clean” backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

1. The table must not have been dropped or truncated since you copied the .ibd file, because doing so changes the table ID stored inside the tablespace.

2. Issue this ALTER TABLE statement to delete the current .ibd file:

   ALTER TABLE tbl_name DISCARD TABLESPACE;
   

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

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

   ALTER TABLE tbl_name IMPORT TABLESPACE;