您好,登录后才能下订单哦!
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
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。