MySQL Study之--MySQL innodb引擎备份工具XtraBackup之二（数据库全备）

Xtrabackup备份原理：

     在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

     xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。

      上面就是xtrabackup的备份过程。接下来是准备（prepare）过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像MySQL刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了。

以上的过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件后，然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上，之后复制MyISAM数据文件，最后释放锁。

       备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表数据与InnoDB表数据是同步的。类似Oracle的，InnoDB的prepare过程可以称为recover（恢复），MyISAM的数据复制过程可以称为restore（还原）。

        xtrabackup和innobackupex这两个工具都提供了许多前文没有提到的功能特点。手册上有对各个功能都有详细的介绍。简单介绍下，这些工具提供了如流（streaming）备份，增量（incremental）备份等，通过复制数据文件，复制日志文件和提交日志到数据文件（前滚）实现了各种复合备份方式。

如下图所示：

案例分析：

一、数据库全备

1、创建配置文件

[root@rh64 ~]# cat /tmp/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648

2、创建备份目录
[root@rh64 ~]# ls -ld /data/mysql/backup/
drwxrwxrwx. 3 mysql mysql 4096 Oct 15 12:13 /data/mysql/backup/

3、测试
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| prod               |
| test               |
+--------------------+
5 rows in set (0.06 sec)

mysql> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    49152 |
+----------+
1 row in set (0.13 sec)

插入数据：
mysql> insert into t1 select * from t1;
Query OK, 49152 rows affected (0.69 sec)
Records: 49152  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    98304 |
+----------+
1 row in set (0.03 sec)

4、创建备份用户并授权
mysql> create user 'bkusr'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)
mysql> grant reload,create tablespace,lock tables ,replication client,super on *.* to 'bakusr'@'%';
Query OK, 0 rows affected (0.00 sec
mysql> create user 'bakusr'@localhost identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)

5、进行数据库全备
[root@rh64 ~]# innobackupex --user=bakusr --password='oracle' --socket=/var/lib/mysql/mysql.sock --defaults-file=/tmp/my.cnf /data/mysql/backup/full
xtrabackup: Error: --defaults-file must be specified first on the command line
----提示配置文件参数必须放在第一位

以root用户备份：
[root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full

查看备份：
[root@rh64 backup]# ls
bak.sh  full  prod  t.txt
[root@rh64 backup]# cd full
[root@rh64 full]# ls
2015-10-28_14-23-23
[root@rh64 full]# cd 2015-10-28_14-23-23/
[root@rh64 2015-10-28_14-23-23]# ls -lt
total 22560
-rw-r----- 1 root root      507 Oct 28 14:23 xtrabackup_info
-rw-r----- 1 root root      398 Oct 28 14:23 backup-my.cnf
-rw-r----- 1 root root      115 Oct 28 14:23 xtrabackup_checkpoints
-rw-r----- 1 root root     2560 Oct 28 14:23 xtrabackup_logfile
drwx------ 2 root root     4096 Oct 28 14:23 performance_schema
drwx------ 2 root root     4096 Oct 28 14:23 mysql
drwx------ 2 root root     4096 Oct 28 14:23 test
drwx------ 2 root root     4096 Oct 28 14:23 prod
-rw-r----- 1 root root 10485760 Oct 28 14:23 ibdata2
-rw-r----- 1 root root 12582912 Oct 28 14:23 ibdata1
[root@rh64 2015-10-28_14-23-23]# 

使用参数：--no-timestamp
[root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp

则不建立时间相关的目录：
[root@rh64 backup]# ls
bak.sh  full  prod  t.txt
[root@rh64 backup]# cd full
[root@rh64 full]# ls 
backup-my.cnf  ibdata2  performance_schema  test                    xtrabackup_info
ibdata1        mysql    prod                xtrabackup_checkpoints  xtrabackup_logfile
[root@rh64 full]# ls -l
total 22560
-rw-r----- 1 root root      398 Oct 28 14:25 backup-my.cnf
-rw-r----- 1 root root 12582912 Oct 28 14:25 ibdata1
-rw-r----- 1 root root 10485760 Oct 28 14:25 ibdata2
drwx------ 2 root root     4096 Oct 28 14:25 mysql
drwx------ 2 root root     4096 Oct 28 14:25 performance_schema
drwx------ 2 root root     4096 Oct 28 14:25 prod
drwx------ 2 root root     4096 Oct 28 14:25 test
-rw-r----- 1 root root      115 Oct 28 14:25 xtrabackup_checkpoints
-rw-r----- 1 root root      522 Oct 28 14:25 xtrabackup_info
-rw-r----- 1 root root     2560 Oct 28 14:25 xtrabackup_logfile

使用普通用户备份：
[root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full
Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

----提示：缺少reload权限

查看用户权限：
mysql> select user,host,Reload_priv from user where user='bakusr';
+--------+-----------+-------------+
| user   | host      | Reload_priv |
+--------+-----------+-------------+
| bakusr | %         | Y           |
| bakusr | localhost | N           |
+--------+-----------+-------------+
2 rows in set (0.04 sec)

授权：
mysql> grant reload,create tablespace,lock tables ,replication client,super on *.* to 'bakusr'@localhost;
Query OK, 0 rows affected (0.08 sec)

mysql> select user,host,Reload_priv from user where user='bakusr';
+--------+-----------+-------------+
| user   | host      | Reload_priv |
+--------+-----------+-------------+
| bakusr | %         | Y           |
| bakusr | localhost | Y           |
+--------+-----------+-------------+
2 rows in set (0.15 sec)

备份：

[root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full  --no-timestamp

[root@rh64 backup]# ls -l full
total 22560
-rw-r----- 1 root root      398 Oct 28 14:35 backup-my.cnf
-rw-r----- 1 root root 12582912 Oct 28 14:35 ibdata1
-rw-r----- 1 root root 10485760 Oct 28 14:35 ibdata2
drwx------ 2 root root     4096 Oct 28 14:35 mysql
drwx------ 2 root root     4096 Oct 28 14:35 performance_schema
drwx------ 2 root root     4096 Oct 28 14:35 prod
drwx------ 2 root root     4096 Oct 28 14:35 test
-rw-r----- 1 root root      115 Oct 28 14:35 xtrabackup_checkpoints
-rw-r----- 1 root root      524 Oct 28 14:35 xtrabackup_info
-rw-r----- 1 root root     2560 Oct 28 14:35 xtrabackup_logfile

二、数据库恢复

1、测试

关闭数据库，更改datadir目录

[mysql@rh64 ~]$ service mysql stop
Shutting down MySQL (Percona Server)...[  OK  ]
rm: cannot remove `/var/lock/subsys/mysql': Permission denied

[root@rh64 ~]# mv /var/lib/mysql /var/lib/mysql.bak
[root@rh64 ~]# cd /var/lib/mysql.bak
[root@rh64 mysql.bak]# ls
auto.cnf  ibdata2      ib_logfile1    mysql               prod      RPM_UPGRADE_HISTORY      test
ibdata1   ib_logfile0  ib_logfile101  performance_schema  rh64.pid  RPM_UPGRADE_MARKER-LAST

创建新的datadir:
[root@rh64 mysql.bak]# mkdir /var/lib/mysql


数据库恢复：
[root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf  --copy-back --rsync /data/mysql/backup/full
......
151028 14:35:22 [01] Copying ./performance_schema/events_waits_history.frm to /data/mysql/backup/full/performance_schema/events_waits_history.frm
151028 14:35:22 [01]        ...done
151028 14:35:22 [01] Copying ./performance_schema/host_cache.frm to /data/mysql/backup/full/performance_schema/host_cache.frm
151028 14:35:22 [01]        ...done
151028 14:35:22 [01] Copying ./performance
......
151028 14:41:53 [01]        ...done
151028 14:41:53 [01] Copying ./performance_schema/events_stages_history_long.frm to /var/lib/mysql/performance_schema/events_stages_history_long.frm
151028 14:41:53 [01]        ...done
151028 14:41:53 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
151028 14:41:53 [01]        ...done
151028 14:41:54 completed OK!

重新授权datadir：

[root@rh64 lib]# ls -ld mysql.bak/
drwxr-xr-x. 6 mysql mysql 4096 Oct 28 14:38 mysql.bak/
[root@rh64 lib]# ls -ld mysql
drwxr-xr-x 6 root root 4096 Oct 28 14:45 mysql
[root@rh64 lib]# ls -l mysql.bak/
total 170020
-rw-rw----. 1 mysql mysql       56 Sep  6 18:08 auto.cnf
-rw-rw----. 1 mysql mysql 12582912 Oct 28 14:38 ibdata1
-rw-rw----. 1 mysql mysql 10485760 Oct 28 14:38 ibdata2
-rw-rw----. 1 mysql mysql 50331648 Oct 28 14:38 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Sep  6 18:06 ib_logfile1
-rw-rw----. 1 mysql mysql 50331648 Sep 11 11:50 ib_logfile101
drwx------. 2 mysql mysql     4096 Sep  6 18:06 mysql
drwx------. 2 mysql mysql     4096 Sep  6 18:06 performance_schema
drwx------. 2 mysql mysql     4096 Oct 13 16:41 prod
-rw-rw----. 1 mysql mysql        5 Oct 15 11:36 rh64.pid
-rw-r--r--. 1 root  root       293 Sep  6 18:07 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql      293 Sep  6 18:07 RPM_UPGRADE_MARKER-LAST
drwx------. 2 mysql mysql     4096 Sep  6 18:06 test
[root@rh64 lib]# chown -R mysql.mysql mysql

验证数据恢复：
[root@rh64 lib]# service mysql start
Starting MySQL (Percona Server)....[  OK  ]
[root@rh64 lib]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

mysql> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    98304 |
+----------+
1 row in set (0.04 sec)

---至此，数据恢复成功！！！