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-epelbaseurl=gpgcheck=0enabled=1[root@localhost ~]# yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm -y

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

备份需自行创建备份用户，赋予备份用户相应的一些权限（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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [hellodb]> show table status\G*************************** 1. row ***************************           Name: classes         Engine: InnoDB        Version: 10     Row_format: Compact           Rows: 8 Avg_row_length: 2048    Data_length: 16384Max_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: 16384Max_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: 16384Max_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: 16384Max_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: 16384Max_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: 16384Max_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: 16384Max_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_infoMariaDB [(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: 0MariaDB [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 = incrementalfrom_lsn = 1628321to_lsn = 1629233last_lsn = 1629233compact = 0recover_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 hellodbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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