Xtrabackup备份mysql数据库

Xtrabackup由percona提供

percona Xtrabackup是一个自由、开源的完整的在线备份工具，支持Mysql、perconna server、mariadb

到官网https://www.percona.com/下载安装包，并配置好epel源安装需要依赖libev这个包

[root@localhost ~]# wget 
 [root@localhost ~]# vim /etc/yum.repos.d/ali-epel.repo
 [epel]
name=ali-epel
baseurl=
gpGCheck=0
enabled=1
[root@localhost ~]# yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm -y

Xtrabackup的备份是通过日志序列号（log sequence number <LSN>）来实现的

备份需自行创建备份用户，赋予备份用户相应的一些权限（reload;lock tables;replication client;create tablespace;process;super;create;insert;select）

创建备份恢复用户：

MariaDB [(none)]> create user 'backup'@'localhost' identified by 'xtrabackup123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant reload,lock tables,replication client,insert,select,process,super,create,create tablespace on *.* to 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Xtrabackup仅对InnoDB支持热备； 查看数据库信息：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| infORMation_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> show table status\G*************************** 1. row ***************************
           Name: classes
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 9
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: coc
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 14
 Avg_row_length: 1170
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 15
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: courses
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 8
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 4. row ***************************
           Name: scores
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 15
 Avg_row_length: 1092
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 16
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 5. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 25
 Avg_row_length: 655
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 26
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 6. row ***************************
           Name: teachers
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 5
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 7. row ***************************
           Name: toc
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 1
    Create_time: 2016-07-05 08:16:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
7 rows in set (0.00 sec)#全部都是InnoDB的，可以做热备。

全备：

[root@localhost ~]# mkdir /backupdir
[root@localhost ~]# innobackupex --user='backup' --passWord='xtrabackup123' /backupdir
[root@localhost ~]# ls /backupdir/
2016-07-05_08-42-50

全备恢复：

[root@localhost ~]# mysql -e 'drop database hellodb;'       #模拟环境先将要恢复的数据库删除；
MariaDB [(none)]> show databases;
+--------------------+| Database           |
+--------------------+| information_schema |
| mysql              |
| performance_schema || test               |
+--------------------+4 rows in set (0.00 sec)


[root@localhost ~]# innobackupex --apply-log /backupdir/2016-07-05_08-42-50/
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# innobackupex --copy-back /backupdir/2016-07-05_08-42-50/

#验证数据库有没恢复
[root@localhost ~]# ls /var/lib/mysql/
hellodb  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  test  xtrabackup_info

MariaDB [(none)]> show databases;+--------------------+| Database           |
+--------------------+| information_schema |
| hellodb            |
| mysql              |
| performance_schema || test               |
+--------------------+5 rows in set (0.00 sec)

增备：

 增备之前要先做全备，因为增备是依据全备的变化来做的

[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123'  /backup/
[root@localhost ~]# ls /backup/2016-07-05_08-28-54

修改数据库
MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> insert into courses(Course) values('zhangsan'),('lisi');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | zhangsan       |
|        9 | lisi           |
+----------+----------------+
9 rows in set (0.00 sec)

做增备
[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' --incremental /incbackup/ --incremental-basedir=/backup/2016-07-05_08-28-54/
[root@localhost ~]# cat /incbackup/2016-07-05_08-42-06/
xtrabackup_checkpoints backup_type = incremental
from_lsn = 1628321
to_lsn = 1629233
last_lsn = 1629233
compact = 0
recover_binlog_info = 0

全备+增备恢复：

增备合并到全备，恢复数据的时候只需要恢复合并的全备就可以了
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2016-07-05_08-28-54/
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2016-07-05_08-28-54/ --incremental-dir=/incbackup/2016-07-05_08-42-06/
[root@localhost ~]# mysql -e 'use hellodb;drop table courses; '
[root@localhost ~]# mysql -e 'use hellodb;
MariaDB [(none)]> show tables; 
'+-------------------+| Tables_in_hellodb |
+-------------------+| classes           |
| coc               |
| scores            |
| students          |
| teachers          || toc               |
+-------------------+
[root@localhost ~]# innobackupex --copy-back /backup/2016-07-05_08-28-54/
MariaDB [(none)]> show databases;
+--------------------+| Database           |
+--------------------+| information_schema |
| hellodb            |
| mysql              |
| performance_schema || test               |
+--------------------+5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb
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
MariaDB [hellodb]> show tables;
+-------------------+| Tables_in_hellodb |
+-------------------+| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          || toc               |
+-------------------+7 rows in set (0.00 sec)

MariaDB [hellodb]> select * from courses;
+----------+----------------+| CourseID | Course         |
+----------+----------------+|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | zhangsan       ||        9 | lisi           |
+----------+----------------+9 rows in set (0.00 sec)

innobackupex一些参数说明：

--include:可选定备份的库或表，支持正则表达式

--tables-file:指定一个文件中所列出的所有表名

--databasea：以上两种的合并

--stream=tar:以流的方式压缩备份

[root@localhost ~]# innobackupex --user='backup' --password='xtrabackup123' --include='hellodb' --stream=tar /backup/ | gzip >  /backup/`data +%F_%H_%M%S`.tar.gz