Xtrabackup备份mysql数据库
短信预约 -IT技能 免费直播动态提醒
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 -yXtrabackup的备份是通过日志序列号（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免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
