Mysql之xbackup-全量备份+增量备份
介绍
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点： (1)备份过程快速、可靠； (2)备份过程不会打断正在执行的事务； (3)能够基于压缩等功能节约磁盘空间和流量； (4)自动实现备份检验； (5)还原速度快；
下载
[root@wxl opt]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm
安装
[root@wxl opt]# yum install -y perl-DBD-MySQL perl-DBI perl-Time-HiRes libaio* libev-devel libev root@wxl opt]# rpm -ivh percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm warning: percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ########################################### [100%] 1:percona-xtrabackup-24 ########################################### [100%]
[root@wxl opt]# rpm -pql percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm warning: percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY /usr/bin/innobackupex /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-24-2.4.8 /usr/share/doc/percona-xtrabackup-24-2.4.8/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
xtrabackup 主要是三个工具
xbsteam：支持流式备份
xtrbackup：用于热备innodb、xtradb表中数据的工具，不能备份其它类型的表，也不能备份数据表结构
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份MyISAM表的能力
xbcrypt：用于加密或解密备份的数据库
xbcloud：用于从云上上传和下载全部或部分xbstream档案
全量备份+全量恢复
[root@wxl opt]# mkdir /opt/backupDB [root@wxl opt]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cachecloud | | cachecould | | mysql | | performance_schema | | test2017 | +--------------------+ 6 rows in set (0.00 sec) mysql> use cachecloud 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 mysql> show tables; +---------------------------------------+ | Tables_in_cachecloud | +---------------------------------------+ | QRTZ_BLOB_TRIGGERS | | QRTZ_CALENDARS | | QRTZ_CRON_TRIGGERS | | QRTZ_FIRED_TRIGGERS | | QRTZ_JOB_DETAILS | | QRTZ_LOCKS | | QRTZ_PAUSED_TRIGGER_GRPS | | QRTZ_SCHEDULER_STATE | | QRTZ_SIMPLE_TRIGGERS | | QRTZ_SIMPROP_TRIGGERS | | QRTZ_TRIGGERS | | app_audit | | app_audit_log | | app_client_costtime_minute_stat | | app_client_costtime_minute_stat_total | | app_client_datasize_minute_stat | | app_client_exception_minute_stat | | app_client_instance | | app_client_value_distri_minute_stat | | app_client_value_minute_stats | | app_client_version_statistic | | app_daily | | app_data_migrate_status | | app_desc | | app_hour_command_statistics | | app_hour_statistics | | app_minute_command_statistics | | app_minute_statistics | | app_to_user | | app_user | | instance_alert | | instance_alert_configs | | instance_config | | instance_fault | | instance_host | | instance_info | | instance_reshard_process | | instance_slow_log | | instance_statistics | | machine_info | | machine_statistics | | server | | server_stat | | standard_statistics | | system_config | +---------------------------------------+ 45 rows in set (0.00 sec)
[root@wxl opt]# mysqladmin -uroot password "test" //设置密码 [root@wxl opt]# innobackupex -uroot -ptest /opt/backupDB/ 171108 15:40:12 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!".。。。。省略 1108 15:40:20 [00] Writing /opt/backupDB/2017-11-08_15-40-12/backup-my.cnf 171108 15:40:20 [00] ...done 171108 15:40:20 [00] Writing /opt/backupDB/2017-11-08_15-40-12/xtrabackup_info 171108 15:40:20 [00] ...done xtrabackup: Transaction log of lsn (52123353) to (52123353) was copied. 171108 15:40:20 completed OK! //备份完成
模拟删除数据库
[root@wxl opt]# mysql -ptest Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.6.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cachecloud | | cachecould | | mysql | | performance_schema | | test2017 | +--------------------+ 6 rows in set (0.02 sec) mysql> drop database cachecloud; Query OK, 45 rows affected (0.29 sec) mysql> quit Bye
然后随便搞数据库
恢复
关闭数据库--清理日志和目录-恢复日志-恢复数据
[root@wxl mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=test --use-memory=4G --apply-log /opt/backupDB/2017-11-08_15-40-12/ [root@wxl mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=test --copy-back /opt/backupDB/2017-11-08_15-40-12/
[root@wxl mysql]# chown -R mysql.mysql /var/lib/mysql
[root@wxl mysql]# /etc/init.d/mysqld start
--apply-log
在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--copy-back
该选项用于执行恢复(还原)操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息
检查恢复的数据
完全备份+增量备份+完全恢复
[root@wxl opt]# innobackupex -uroot -ptest /opt/backupDB/ //全量备份 [root@wxl opt]# mysql -ptest mysql> create table test01 (name varchar(50) ); Query OK, 0 rows affected (0.04 sec) mysql> insert into test01 values("wuxinglai"); Query OK, 1 row affected (0.02 sec)
[root@wxl opt]# innobackupex --user=root --password=test --incremental /opt/backupDB/ --incremental-basedir=/opt/backupDB/2017-11-08_17-40-41/ //第一次增量备份
mysql> use test;
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
mysql> insert into test01 values("tt");
Query OK, 1 row affected (0.02 sec)
mysql> select * from test01;
+-----------+
| name |
+-----------+
| wuxinglai |
| tt |
+-----------+
2 rows in set (0.00 sec)
[root@wxl opt]# innobackupex --user=root --password=test --incremental /opt/backupDB/ --incremental-basedir=/opt/backupDB/2017-11-08_17-42-53/ //第二次增量备份
删除数据库并关闭
[root@wxl mysql]# rm -rf * [root@wxl mysql]# /etc/init.d/mysqld stop Stopping mysqld: [ OK ]
恢复
[root@wxl mysql]# ll /opt/backupDB/ total 12 drwxr-x--- 8 root root 4096 Nov 8 17:40 2017-11-08_17-40-41 //全量备份 drwxr-x--- 8 root root 4096 Nov 8 17:42 2017-11-08_17-42-53 //第一次增量备份 drwxr-x--- 8 root root 4096 Nov 8 17:46 2017-11-08_17-46-22 //第二次增量备份 [root@wxl mysql]# innobackupex --apply-log --redo-only /opt/backupDB/2017-11-08_17-40-41/ //首先执行完全备份redo-log 然后按照顺序执行增量备份redo-log [root@wxl mysql]# innobackupex --user=root --password=test --apply-log --redo-only /opt/backupDB/2017-11-08_17-40-41/ --incremental-dir=/opt/backupDB/2017-11-08_17-42-53/ [root@wxl mysql]# innobackupex --user=root --password=test --apply-log --redo-only /opt/backupDB/2017-11-08_17-40-41/ --incremental-dir=/opt/backupDB/2017-11-08_17-46-22/ [root@wxl mysql]# innobackupex -uroot -ptest --copy-back /opt/backupDB/2017-11-08_17-40-41/ //执行恢复数据 [root@wxl mysql]# chown -R mysql.mysql /var/lib/mysql //更改数据权限 [root@wxl mysql]# /etc/init.d/mysqld start //启动并查看数据 [root@wxl mysql]# mysql -ptest -e "select * from test.test01;" Warning: Using a password on the command line interface can be insecure. +-----------+ | name | +-----------+ | wuxinglai | | tt | +-----------+
闲杂人等
浙公网安备 33010602011771号