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
备份需自行创建备份用户，赋予备份用户相应的一些权限（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
分享文章：Xtrabackup备份mysql数据库
转载源于：http://azwzsj.com/article/geoihp.html
