server 数据库
Xtrabackup 由 percona 提供
percona Xtrabackup 是一个自由、开源的完整的在线备份工具,支持 mysql、perconna server、mariadb
到官网 https://www.percona.com / 下载安装包,并配置好 epel 源安装需要依赖 libev 这个包
- [[email protected]~]# wget
- [[email protected]~]# vim /etc/yum.repos.d/ali-epel.repo
- [epel]
- name=ali-epel
- baseurl=
- gpgcheck=0
- enabled=1
- [[email protected]~]# 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 [email protected] 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 [email protected];
- 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的,可以做热备。
全备:
- [[email protected]~]# mkdir /backupdir
- [[email protected]~]# innobackupex --user='backup' --password='xtrabackup123' /backupdir
- [[email protected]~]# ls /backupdir/
- 2016-07-05_08-42-50
全备恢复:
- [[email protected]~]# mysql -e 'drop database hellodb;' #模拟环境先将要恢复的数据库删除;
- MariaDB [(none)]> show databases;
- +--------------------+| Database |
- +--------------------+| information_schema |
- | mysql |
- | performance_schema || test |
- +--------------------+4 rows in set (0.00 sec)
- [[email protected]~]# innobackupex --apply-log /backupdir/2016-07-05_08-42-50/
- [[email protected]~]# systemctl stop mariadb
- [[email protected]~]# innobackupex --copy-back /backupdir/2016-07-05_08-42-50/
- #验证数据库有没恢复
- [[email protected]~]# 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)
增备:
增备之前要先做全备,因为增备是依据全备的变化来做的
- [[email protected]~]# innobackupex --user='backup' --password='xtrabackup123' /backup/
- [[email protected]~]# 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)
- 做增备
- [[email protected]~]# innobackupex --user='backup' --password='xtrabackup123' --incremental /incbackup/ --incremental-basedir=/backup/2016-07-05_08-28-54/
- [[email protected]~]# 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
全备 + 增备恢复:
- 增备合并到全备,恢复数据的时候只需要恢复合并的全备就可以了
- [[email protected]~]# innobackupex --apply-log --redo-only /backup/2016-07-05_08-28-54/
- [[email protected]~]# innobackupex --apply-log --redo-only /backup/2016-07-05_08-28-54/ --incremental-dir=/incbackup/2016-07-05_08-42-06/
- [[email protected]~]# mysql -e 'use hellodb;drop table courses; '
- [[email protected]~]# mysql -e 'use hellodb;
- MariaDB [(none)]> show tables;
- '+-------------------+| Tables_in_hellodb |
- +-------------------+| classes |
- | coc |
- | scores |
- | students |
- | teachers || toc |
- +-------------------+
- [[email protected]~]# 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: 以流的方式压缩备份
- [[email protected]~]# innobackupex --user='backup' --password='xtrabackup123' --include='hellodb' --stream=tar /backup/ | gzip > /backup/`data +%F_%H_%M%S`.tar.gz
来源: http://www.bubuko.com/infodetail-2017044.html