Xtrabackup增量备份和PTTR

                                                    Xtrabackup增量备份和PTTR

      xtrabackup的增量备份的基本思想和其他的备份技术并无太大的区别，本身是第一次全备份，在执行增量备份的时候，
使用第一次备份作为一个基础的备份,然后增量备份会去备份自从上次全备过后更改的数据页(page).

      在innodb中，每个page都有一个lsn(log sequence number.innodb当前的lsn，可以通过show engine innodb status\G;查看),
在增量备份的时候，扫描所page,当page的lsn大于基础备份的时候，表明改page被修改过，需要备份.

例:创建一个全备份

[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --no-timestamp /root/bak/20140330

--no-timestamp，定义不需要xtrbackup创建目录，由用户自定义

140330 13:24:25  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
140330 13:24:25  innobackupex: Connected to MySQL server
140330 13:24:25  innobackupex: Executing a version check against the server...
140330 13:24:25  innobackupex: Done.
………………………………………..
innobackupex: Backup created in directory '/root/bak/20140330'
innobackupex: MySQL binlog position: filename 'mysqlbin145.000019', position 107
140330 13:24:28  innobackupex: Connection to database server closed
140330 13:24:28  innobackupex: completed OK!
[root@localhost ~]#

增量备份使用以前的全备作为基础备份.
[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --no-timestamp --incremental /root/bak/20140330i --incremental-basedir=/root/bak/20140330

140330 13:29:02  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
140330 13:29:02  innobackupex: Connected to MySQL server
140330 13:29:02  innobackupex: Executing a version check against the server...
140330 13:29:02  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
innobackupex: Using mysql server version 5.5.33-rel31.1-log
innobackupex: Created backup directory /root/bak/20140330i
………………….
xtrabackup: Creating suspend file '/root/bak/20140330i/xtrabackup_log_copied' with pid '27221'
xtrabackup: Transaction log of lsn (2944130080) to (2944130080) was copied.
140330 13:29:06  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/root/bak/20140330i'
innobackupex: MySQL binlog position: filename 'mysqlbin145.000019', position 107
140330 13:29:06  innobackupex: Connection to database server closed
140330 13:29:06  innobackupex: completed OK!

以/root/bak/20140330作为基础备份，执行增量份备，增量备份目录存放的位置为
/root/bak/20140330i

增量备份的prepare需要应用多次

第一次以redo only方式应用

[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --apply-log --redo-only /root/bak/20140330

第二次，应用增量备份

[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --apply-log --redo-only /root/bak/20140330 --incremental-dir=/root/bak/20140330i

xtrabackup_55 version 2.1.8 for Percona Server 5.5.35 Linux (x86_64) (revision id: 733)
incremental backup from 2944130080 is enabled.
xtrabackup: cd to /root/bak/20140330
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2944130080)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = /root/bak/20140330i
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: page size for /root/bak/20140330i/ibdata1.delta is 16384 bytes
Applying /root/bak/20140330i/ibdata1.delta to ././ibdata1...
xtrabackup: page size for /root/bak/20140330i/frank/t1.ibd.delta is 16384 bytes
Applying /root/bak/20140330i/frank/t1.ibd.delta to ././frank/t1.ibd...
xtrabackup: using the following InnoDB configuration fo
………………
innobackupex: Copying '/root/bak/20140330i/mysql/func.MYI' to '/root/bak/20140330/mysql/func.MYI'
innobackupex: Copying '/root/bak/20140330i/frank/db.opt' to '/root/bak/20140330/frank/db.opt'
innobackupex: Copying '/root/bak/20140330i/frank/t1.frm' to '/root/bak/20140330/frank/t1.frm'
innobackupex: Copying '/root/bak/20140330i/test/db.opt' to '/root/bak/20140330/test/db.opt'
140330 13:36:05  innobackupex: completed OK!

对于myisam的表无法做增量备份，所以在apply-log期间可以看出是直接拷贝文件的方式去做恢复的.

再次应用
[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --apply-log /root/bak/20140330

xtrabackup_55 version 2.1.8 for Percona Server 5.5.35 Linux (x86_64) (revision id: 733)
xtrabackup: cd to /root/bak/20140330
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
………………..
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140330 13:38:42  InnoDB: Starting shutdown...
140330 13:38:46  InnoDB: Shutdown completed; log sequence number 2944130572
140330 13:38:46  innobackupex: completed OK!

至此，basedir已经达到了一致性的状态，可以使用该备份去做恢复了(恢复参考第一季).
基于xtrabackup的PTTR
服务器需要开binlog.通过xtrabackup和重新执行binlog中挖掘的SQL来达到PTTR的目标.

例:数据库中有表
mysql> select * from frank.t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

DBA在2014-03-30 13:45:00备份数据库
[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --no-timestamp /root/bak/20140330

……………..
innobackupex: Backup created in directory '/root/bak/20140330'
innobackupex: MySQL binlog position: filename 'mysqlbin145.000019', position 107
140330 13:45:37  innobackupex: Connection to database server closed
140330 13:45:37  innobackupex: completed OK!

Prepare
[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --apply-log  /root/bak/20140330
………………
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1153, file name ./mysqlbin145.000076

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140330 13:46:23  InnoDB: Starting shutdown...
140330 13:46:27  InnoDB: Shutdown completed; log sequence number 2944130572
140330 13:46:27  innobackupex: completed OK!

13:48分用户误删除数据
mysql> delete from frank.t1 where a=3;
Query OK, 1 row affected (0.00 sec)

13:49分用户insert一条数据5,

13:50接到用户反映.用户需要恢复被删除数据，并且需要保存新数据

关闭当前的mysql数据库实例
[root@localhost ~]# mysqladmin -u root -p --socket=/tmp/mysql.sock shutdown

先恢复数据库
[root@localhost ~]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --copy-back  /root/bak/20140330

挖掘binlog

[root@localhost binlog]# mysqlbinlog --start-position 107 mysqlbin145.000019
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 107
#140330 13:48:28 server id 145  end_log_pos 171         Query   thread_id=10    exec_time=0     error_code=0
…………………….

delete from frank.t1 where a=3
/*!*/;
# at 260
#140330 13:48:28 server id 145  end_log_pos 287         Xid = 134
COMMIT/*!*/;
# at 287
#140330 13:49:11 server id 145  end_log_pos 351         Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1396187351/*!*/;
BEGIN
/*!*/;
# at 351
#140330 13:49:11 server id 145  end_log_pos 440         Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1396187351/*!*/;
insert into frank.t1 values(5)
/*!*/;
# at 440
#140330 13:49:11 server id 145  end_log_pos 467         Xid = 148
……………….

Start-position和文件信息，可以通过xtrabackup备份中的xtracbackup_binlog_info文件得到

启动mysql实例

[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data

mysql> select * from frank.t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

可以看到当前已经恢复到我们执行备份的那个时间点,通过挖出来的sql文件，把delete
语句去掉，保留insert语句，这样就恢复了误删的数据

[root@localhost ~]#  mysql -u root -p --socket=/tmp/mysql.sock  frank <1.sql
Enter password:

mysql> select * from frank.t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
+------+
检查一下,3已经成功恢复，而新增的5也被保留下来了,实现了PTTR的目标 。