网页制作工具有什么,北京优化词网站,沈阳建设工程质量安全,wordpress 搜索本站Xtrabackup是一个对InnoDB做数据备份的工具#xff0c;支持在线热备份#xff08;备份时不影响数据读写#xff09;#xff0c;是商业备份工具InnoDB Hotbackup的一个很好的替代品。 xtrabackup官方网址#xff1a;https://www.percona.com/downloads/XtraBackup/MySQL Ba… Xtrabackup是一个对InnoDB做数据备份的工具支持在线热备份备份时不影响数据读写是商业备份工具InnoDB Hotbackup的一个很好的替代品。
xtrabackup官方网址https://www.percona.com/downloads/XtraBackup/MySQL Backup Tool Feature Comparison Features Percona XtraBackup MySQL Enterprise backup License GPL Proprietary Price Free Included in subscription at $5000 per Server Streaming and encryption formats Open source Proprietary Supported MySQL flavors MySQL, Percona Server, MariaDB,Percona XtraDB Cluster, MariaDB Galera Cluster MySQL Supported operating systems Linux Linux, Solaris, Windows, OSX, FreeBSD. Non-blocking InnoDB backups [1] Yes Yes Blocking MyISAM backups Yes Yes Incremental backups Yes Yes Full compressed backups Yes Yes Incremental compressed backups Yes Fast incremental backups [2] Yes Incremental backups with archived logs feature in Percona Server Yes Incremental backups with REDO log only Yes Backup locks [8] Yes Encrypted backups Yes Yes [3] Streaming backups Yes Yes Parallel local backups Yes Yes Parallel compression Yes Yes Parallel encryption Yes Yes Parallel apply-log Yes Parallel copy-back Yes Partial backups Yes Yes Partial backups of individual partitions Yes Throttling [4] Yes Yes Backup image validation Yes Point-in-time recovery support Yes Yes Safe slave backups Yes Compact backups [5] Yes Buffer pool state backups Yes Individual tables export Yes Yes [6] Individual partitions export Yes Restoring tables to a different server [7] Yes Yes Data index file statistics Yes InnoDB secondary indexes defragmentation Yes rsync support to minimize lock time Yes Improved FTWRL handling Yes Backup history table Yes Yes Backup progress table Yes Offline backups Yes Backup to tape media managers Yes Cloud backups support Amazon S3 External graphical user interfaces to backup/recovery Zmanda Recovery Manager for MySQL MySQL Workbench, MySQL Enterprise Monitor 来源 https://www.percona.com/doc/percona-xtrabackup/2.4/intro.html xtrabackup安装方法 可以下载源码编译安装也可以下载适合的RPM包或者yum进行安装。1、Installing Percona XtraBackup from Percona yum repository 安装Percona XtraBackup Percona yum资源库yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm 查看软件包是否存在[rootdb01 ~]# yum list|grep percona测试是否存在软件包 ...
percona-xtrabackup-20.x86_64 2.0.8-587.rhel5 percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64 2.0.8-587.rhel5 percona-release-x86_64
percona-xtrabackup-20-test.x86_64 2.0.8-587.rhel5 percona-release-x86_64
percona-xtrabackup-test-22.x86_64 2.2.13-1.el5 percona-release-x86_64
... 安装软件包 yum install percona-xtrabackup-222、Installing Percona XtraBackup using downloaded rpm packages 使用rpm包安装Installing Percona Server using downloaded rpm packages下载需要的版本wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.10-3/binary/redhat/7/x86_64/Percona-Server-5.7.10-3-r63dafaf-el7-x86_64-bundle.tar需要解压tar xvf Percona-Server-5.7.10-3-r63dafaf-el7-x86_64-bundle.tarls *.rpm Percona-Server-57-debuginfo-5.7.10-3.1.el7.x86_64.rpmPercona-Server-client-57-5.7.10-3.1.el7.x86_64.rpmPercona-Server-devel-57-5.7.10-3.1.el7.x86_64.rpmPercona-Server-server-57-5.7.10-3.1.el7.x86_64.rpmPercona-Server-shared-57-5.7.10-3.1.el7.x86_64.rpmPercona-Server-shared-compat-57-5.7.10-3.1.el7.x86_64.rpmPercona-Server-test-57-5.7.10-3.1.el7.x86_64.rpmPercona-Server-tokudb-57-5.7.10-3.1.el7.x86_64.rpm运行方式rpm -ivh Percona-Server-server-57-5.7.10-3.1.el7.x86_64.rpm \Percona-Server-client-57-5.7.10-3.1.el7.x86_64.rpm \Percona-Server-shared-57-5.7.10-3.1.el7.x86_64.rpm ...Uninstalling Percona XtraBackup卸载软件包yum remove percona-xtrabackup官方文档https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html#uninstalling-percona-xtrabackup Download Percona Server 5.7 软件包说明Download All Packages TogetherPercona-Server-5.7.12-5-ra2f663a-el6-x86_64-bundle.tar Download Packages SeparatelyPercona-Server-57-debuginfo-5.7.12-5.1.el6.x86_64.rpm Percona-Server-client-57-5.7.12-5.1.el6.x86_64.rpm Percona-Server-devel-57-5.7.12-5.1.el6.x86_64.rpmPercona-Server-server-57-5.7.12-5.1.el6.x86_64.rpmPercona-Server-shared-57-5.7.12-5.1.el6.x86_64.rpmPercona-Server-test-57-5.7.12-5.1.el6.x86_64.rpmPercona-Server-tokudb-57-5.7.12-5.1.el6.x86_64.rpm官方解释每个软件包的作用What’s in each RPM package?¶ 每个Percona服务器的RPM包有一个特定的目的。 Percona-Server-server-57包包含了服务器本身(mysqld二进制)。 为服务器Percona-Server-57-debuginfo包包含调试符号。 Percona-Server-client-57包包含命令行客户端。Percona-Server-devel-57包包含所需的头文件编译软件使用客户端库。Percona-Server-shared-57包包含客户端共享库。Percona-Server-shared-compat包包含共享库的编译软件旧版本的客户端库。库包含在这个包:libmysqlclient.so。12,libmysqlclient.so。14日,libmysqlclient.so。15日,libmysqlclient.so。16、libmysqlclient.so.18。Percona-Server-test-57包包括Percona服务器的测试套件检查安装结果 [rootdb01 ~]# rpm -qa|grep xtrabackuppercona-xtrabackup-22-2.2.13-1.el6.x86_64 Xtrabackup中主要包含两个工具 xtrabackup是用于热备innodbxtradb表中数据的工具不能备份其他类型的表也不能备份数据表结构innobackupex是将xtrabackup进行封装的perl脚本提供了备份myisam表的能力。使用xtrabackup实现对MySQL的备份 1.完全备份
基本语法多实例备份[rootdb02 3306]# innobackupex --userroot --password123456 --socket/data/3306/mysql.sock --defaults-file/data/3306/my.cnf /tmp/ 语法解释–user数据库用户 –password数据库密码 –socket指定socket –default-file指定配置文件 最后面是存放位置 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p160703 20:00:13 innobackupex: Executing a version check against the server...160703 20:00:13 innobackupex: Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_file/data/3306/my.cnf;mysql_read_default_groupxtrabackup;mysql_socket/data/3306/mysql.sock as root (using password: YES).160703 20:00:13 innobackupex: Connected to MySQL server160703 20:00:13 innobackupex: Done.160703 20:00:13 innobackupex: Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_file/data/3306/my.cnf;mysql_read_default_groupxtrabackup;mysql_socket/data/3306/mysql.sock as root (using password: YES).160703 20:00:13 innobackupex: Connected to MySQL server160703 20:00:13 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints completed OK!.innobackupex: Using server version 5.5.49-loginnobackupex: Created backup directory /tmp/2016-07-03_20-00-13160703 20:00:13 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file/data/3306/my.cnf --defaults-groupmysqld --backup --suspend-at-end --target-dir/tmp/2016-07-03_20-00-13 --tmpdir/tmp --extra-lsndir/tmpinnobackupex: Waiting for ibbackup (pid2586) to suspendinnobackupex: Suspend file /tmp/2016-07-03_20-00-13/xtrabackup_suspended_2xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/3306/dataxtrabackup: open files limit requested 1024, set to 65535xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir ./xtrabackup: innodb_data_file_path ibdata1:128M:autoextendxtrabackup: innodb_log_group_home_dir ./xtrabackup: innodb_log_files_in_group 3xtrabackup: innodb_log_file_size 4194304 log scanned up to (2097320)xtrabackup: Generating a list of tablespaces[01] Copying ./ibdata1 to /tmp/2016-07-03_20-00-13/ibdata1 log scanned up to (2097320) log scanned up to (2097320) log scanned up to (2097320) log scanned up to (2097320) log scanned up to (2097320)[01] ...done log scanned up to (2097320)xtrabackup: Creating suspend file /tmp/2016-07-03_20-00-13/xtrabackup_suspended_2 with pid 2586160703 20:00:20 innobackupex: Continuing after ibbackup has suspended160703 20:00:20 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...160703 20:00:20 innobackupex: Executing FLUSH TABLES WITH READ LOCK...160703 20:00:20 innobackupex: All tables locked and flushed to disk160703 20:00:20 innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of /data/3306/data/innobackupex: Backing up file /data/3306/data//qqqqqqq41233/db.opt log scanned up to (2097320)innobackupex: Backing up file /data/3306/data//dadadadadadad/db.optinnobackupex: Backing up file /data/3306/data//qqqqqqqq3/db.optinnobackupex: Backing up file /data/3306/data//qqqqqqqq/db.optinnobackupex: Backing up file /data/3306/data//qqqqqqq43/db.optinnobackupex: Backing up file /data/3306/data//oldboy_gbk/db.optinnobackupex: Backing up file /data/3306/data//oldboy/student.frminnobackupex: Backing up file /data/3306/data//oldboy/db.optinnobackupex: Backing up file /data/3306/data//ooooooooooooooooo/db.optinnobackupex: Backing up files /data/3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par} (18 files)innobackupex: Backing up file /data/3306/data//qqqqqqqq1/db.optinnobackupex: Backing up file /data/3306/data//wordpress/db.optinnobackupex: Backing up file /data/3306/data//aaa/db.optinnobackupex: Backing up files /data/3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par} (72 files)innobackupex: Backing up file /data/3306/data//cyh/db.optinnobackupex: Backing up file /data/3306/data//wuyi/db.opt160703 20:00:21 innobackupex: Finished backing up non-InnoDB tables and files160703 20:00:21 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...160703 20:00:21 innobackupex: Waiting for log copying to finish log scanned up to (2097320)xtrabackup: The latest check point (for incremental): 2097320xtrabackup: Stopping log copying thread.. log scanned up to (2097320)xtrabackup: Creating suspend file /tmp/2016-07-03_20-00-13/xtrabackup_log_copied with pid 2586xtrabackup: Transaction log of lsn (2097320) to (2097320) was copied.160703 20:00:22 innobackupex: All tables unlockedinnobackupex: Backup created in directory /tmp/2016-07-03_20-00-13innobackupex: MySQL binlog position: filename mysql-bin.000029, position 107160703 20:00:22 innobackupex: Connection to database server closed160703 20:00:22 innobackupex: completed OK! 错误提示如果执行该命令出现如下错误 Cant load /usr/local/lib64/perl5/auto/DBD/mysql/mysql.so for module DBD::mysql: libmysqlclient.so.18: 无法打开共享对象 at /usr/bin/innobackupex line 18 需要拷贝libmysqlclient.so.18至/usr/lib64: [rootlocalhost ~]# cp /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib64/ 备份后的文件 在备份的同时备份数据会在备份目录下创建一个以当前时间为名字的目录存放备份文件 各文件说明 1xtrabackup_checkpoints —-备份类型如完全或增量、备份状态如是否已经没prepare状态和LSN日志序列号范围信息 每个InnoDB页通常大小为16K都会包含一个日志序列号即LSNLSN是整个数据库系统的系统版本号每个页面相关的LSN能够表名此页面最近是如何发生改变的。 backup_type full-backuped from_lsn 0 to_lsn 2097320 last_lsn 2097320 compact 0 2xtrabackup_binlog_info —-mysql服务器当前正在使用的二进制日志及备份这一刻为二进制日志事件的位置 3xtrabackup_pos_innodb —-二进制日志及用于InnoDB或XtraDB表的二进制日志的当前posistion。 4xtrabackup_binary —-备份中用到的xtrabackup的可执行文件 5backup-my.cnf —- 备份命令用到的配置选项信息 在使用innobackupex命令备份时吗还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录 innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据 还需要注意备份数据库的用户需要具有相对应的权限如果要使用一个最小权限的用户进行备份可以使用以下参数 mysql GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘bkpuser’’localhost’; mysql FLUSH PRIVILEGES; 2. 准备prepare一个完整备份 一般情况下在备份完成后数据尚且不能用于恢复操作因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务因此此时数据文件仍处理不一致状态。“准备”的主要作用是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。 innobackupex命令的–apply-log选项可用于实现上述功能。 [rootdb02 /]# innobackupex –apply-log /tmp/2016-07-03_21-07-38/ 如果执行正确会输出以下内容不提示错误即可 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p160703 21:43:08 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints completed OK!.160703 21:43:08 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file/tmp/2016-07-03_21-07-38/backup-my.cnf --defaults-groupmysqld --prepare --target-dir/tmp/2016-07-03_21-07-38xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)xtrabackup: cd to /tmp/2016-07-03_21-07-38xtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size2097152, start_lsn(2097320)xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir ./xtrabackup: innodb_data_file_path ibdata1:128M:autoextendxtrabackup: innodb_log_group_home_dir ./xtrabackup: innodb_log_files_in_group 1xtrabackup: innodb_log_file_size 2097152xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir ./xtrabackup: innodb_data_file_path ibdata1:128M:autoextendxtrabackup: innodb_log_group_home_dir ./xtrabackup: innodb_log_files_in_group 1xtrabackup: innodb_log_file_size 2097152xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: Using atomics to ref count buffer pool pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, size 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Highest supported file format is Barracuda.InnoDB: The log sequence numbers 2085708 and 2085708 in ibdata files do not match the log sequence number 2097320 in the ib_logfiles!InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pages InnoDB: from the doublewrite buffer...InnoDB: Last MySQL binlog file position 0 9706, file name /data/3306/mysql-bin.000025InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.6.24 started; log sequence number 2097320[notice (again)] If you use binary log and dont use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 9706, file name /data/3306/mysql-bin.000025xtrabackup: starting shutdown with innodb_fast_shutdown 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2098397160703 21:43:11 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file/tmp/2016-07-03_21-07-38/backup-my.cnf --defaults-groupmysqld --prepare --target-dir/tmp/2016-07-03_21-07-38for creating ib_logfile*xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)xtrabackup: cd to /tmp/2016-07-03_21-07-38xtrabackup: This target seems to be already prepared.xtrabackup: notice: xtrabackup_logfile was already used to --prepare.xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir ./xtrabackup: innodb_data_file_path ibdata1:128M:autoextendxtrabackup: innodb_log_group_home_dir ./xtrabackup: innodb_log_files_in_group 3xtrabackup: innodb_log_file_size 4194304xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir ./xtrabackup: innodb_data_file_path ibdata1:128M:autoextendxtrabackup: innodb_log_group_home_dir ./xtrabackup: innodb_log_files_in_group 3xtrabackup: innodb_log_file_size 4194304xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: Using atomics to ref count buffer pool pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, size 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Setting log file ./ib_logfile101 size to 4 MBInnoDB: Setting log file ./ib_logfile1 size to 4 MBInnoDB: Setting log file ./ib_logfile2 size to 4 MBInnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN2098397InnoDB: Highest supported file format is Barracuda.InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.6.24 started; log sequence number 2098700[notice (again)] If you use binary log and dont use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 9706, file name /data/3306/mysql-bin.000025xtrabackup: starting shutdown with innodb_fast_shutdown 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2099425160703 21:43:13 innobackupex: completed OK! 查看完全备份时日志位置 [rootdb02 2016-07-03_21-07-38]# cat xtrabackup_binlog_info mysql-bin.000029 107 模式数据库修改 create table student(id int(4) not null,name char(20) not null,age tinyint(2) NOT NULL default 0,dept varchar(16) default NULL);insert into student(id,name,age) values(1,good,15);insert into student(id,name,age) values(2,hehe,18); 模拟数据库损坏 进入数据库的data目录删除所有 [rootdb02 data]# rm -rf *mysqlshow databases; 现在已经造成了数据库文件被删除里面的数据消失--------------------| Database |--------------------| information_schema |--------------------1 row in set (0.00 sec) 还原完全备份 innobackupex命令的–copy-back选项用于执行恢复操作其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息 还原数据库语法 [rootdb02 data]# innobackupex –userroot –password123456 –socket/data/3306/mysql.sock –defaults-file/data/3306/my.cnf –copy-back /tmp/2016-07-03_21-07-38/ 只是在恢复的时候加入了–copy-back 如果执行正确会输出以下内容 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p160704 00:03:33 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints completed OK!.innobackupex: Starting to copy files in /tmp/2016-07-03_21-07-38innobackupex: back to original data directory /data/3306/datainnobackupex: Copying /tmp/2016-07-03_21-07-38/xtrabackup_info to /data/3306/data/xtrabackup_infoinnobackupex: Copying /tmp/2016-07-03_21-07-38/xtrabackup_binlog_pos_innodb to /data/3306/data/xtrabackup_binlog_pos_innodbinnobackupex: Creating directory /data/3306/data/qqqqqqq41233innobackupex: Copying /tmp/2016-07-03_21-07-38/qqqqqqq41233/db.opt to /data/3306/data/qqqqqqq41233/db.optinnobackupex: Creating directory /data/3306/data/dadadadadadadinnobackupex: Copying /tmp/2016-07-03_21-07-38/dadadadadadad/db.opt to /data/3306/data/dadadadadadad/db.optinnobackupex: Creating directory /data/3306/data/qqqqqqqq3innobackupex: Copying /tmp/2016-07-03_21-07-38/qqqqqqqq3/db.opt to /data/3306/data/qqqqqqqq3/db.optinnobackupex: Creating directory /data/3306/data/qqqqqqqqinnobackupex: Copying /tmp/2016-07-03_21-07-38/qqqqqqqq/db.opt to /data/3306/data/qqqqqqqq/db.optinnobackupex: Creating directory /data/3306/data/qqqqqqq43innobackupex: Copying /tmp/2016-07-03_21-07-38/qqqqqqq43/db.opt to /data/3306/data/qqqqqqq43/db.optinnobackupex: Creating directory /data/3306/data/oldboy_gbkinnobackupex: Copying /tmp/2016-07-03_21-07-38/oldboy_gbk/db.opt to /data/3306/data/oldboy_gbk/db.optinnobackupex: Creating directory /data/3306/data/oldboyinnobackupex: Copying /tmp/2016-07-03_21-07-38/oldboy/student.frm to /data/3306/data/oldboy/student.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/oldboy/db.opt to /data/3306/data/oldboy/db.optinnobackupex: Creating directory /data/3306/data/oooooooooooooooooinnobackupex: Copying /tmp/2016-07-03_21-07-38/ooooooooooooooooo/db.opt to /data/3306/data/ooooooooooooooooo/db.optinnobackupex: Creating directory /data/3306/data/performance_schemainnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/file_instances.frm to /data/3306/data/performance_schema/file_instances.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/setup_instruments.frm to /data/3306/data/performance_schema/setup_instruments.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/setup_consumers.frm to /data/3306/data/performance_schema/setup_consumers.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/file_summary_by_instance.frm to /data/3306/data/performance_schema/file_summary_by_instance.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/events_waits_summary_by_instance.frm to /data/3306/data/performance_schema/events_waits_summary_by_instance.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/mutex_instances.frm to /data/3306/data/performance_schema/mutex_instances.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/db.opt to /data/3306/data/performance_schema/db.optinnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/performance_timers.frm to /data/3306/data/performance_schema/performance_timers.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/cond_instances.frm to /data/3306/data/performance_schema/cond_instances.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/setup_timers.frm to /data/3306/data/performance_schema/setup_timers.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/events_waits_current.frm to /data/3306/data/performance_schema/events_waits_current.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/file_summary_by_event_name.frm to /data/3306/data/performance_schema/file_summary_by_event_name.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/events_waits_history.frm to /data/3306/data/performance_schema/events_waits_history.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/events_waits_summary_by_thread_by_event_name.frm to /data/3306/data/performance_schema/events_waits_summary_by_thread_by_event_name.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/events_waits_history_long.frm to /data/3306/data/performance_schema/events_waits_history_long.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/events_waits_summary_global_by_event_name.frm to /data/3306/data/performance_schema/events_waits_summary_global_by_event_name.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/rwlock_instances.frm to /data/3306/data/performance_schema/rwlock_instances.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/performance_schema/threads.frm to /data/3306/data/performance_schema/threads.frminnobackupex: Creating directory /data/3306/data/qqqqqqqq1innobackupex: Copying /tmp/2016-07-03_21-07-38/qqqqqqqq1/db.opt to /data/3306/data/qqqqqqqq1/db.optinnobackupex: Creating directory /data/3306/data/wordpressinnobackupex: Copying /tmp/2016-07-03_21-07-38/wordpress/db.opt to /data/3306/data/wordpress/db.optinnobackupex: Creating directory /data/3306/data/aaainnobackupex: Copying /tmp/2016-07-03_21-07-38/aaa/db.opt to /data/3306/data/aaa/db.optinnobackupex: Creating directory /data/3306/data/mysqlinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_keyword.MYD to /data/3306/data/mysql/help_keyword.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/general_log.CSV to /data/3306/data/mysql/general_log.CSVinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_keyword.MYI to /data/3306/data/mysql/help_keyword.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_topic.frm to /data/3306/data/mysql/help_topic.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/procs_priv.MYD to /data/3306/data/mysql/procs_priv.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/servers.frm to /data/3306/data/mysql/servers.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_transition_type.MYI to /data/3306/data/mysql/time_zone_transition_type.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone.frm to /data/3306/data/mysql/time_zone.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/event.MYI to /data/3306/data/mysql/event.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/db.frm to /data/3306/data/mysql/db.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/ndb_binlog_index.MYD to /data/3306/data/mysql/ndb_binlog_index.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/proc.MYD to /data/3306/data/mysql/proc.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/tables_priv.MYD to /data/3306/data/mysql/tables_priv.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/user.MYD to /data/3306/data/mysql/user.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_name.MYI to /data/3306/data/mysql/time_zone_name.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/general_log.CSM to /data/3306/data/mysql/general_log.CSMinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/event.frm to /data/3306/data/mysql/event.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_topic.MYD to /data/3306/data/mysql/help_topic.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/procs_priv.frm to /data/3306/data/mysql/procs_priv.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_name.MYD to /data/3306/data/mysql/time_zone_name.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/slow_log.CSM to /data/3306/data/mysql/slow_log.CSMinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_leap_second.MYI to /data/3306/data/mysql/time_zone_leap_second.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_topic.MYI to /data/3306/data/mysql/help_topic.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/proc.MYI to /data/3306/data/mysql/proc.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/columns_priv.MYD to /data/3306/data/mysql/columns_priv.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/slow_log.CSV to /data/3306/data/mysql/slow_log.CSVinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/func.frm to /data/3306/data/mysql/func.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_leap_second.MYD to /data/3306/data/mysql/time_zone_leap_second.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/ndb_binlog_index.frm to /data/3306/data/mysql/ndb_binlog_index.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_category.frm to /data/3306/data/mysql/help_category.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/db.MYI to /data/3306/data/mysql/db.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/func.MYI to /data/3306/data/mysql/func.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/columns_priv.frm to /data/3306/data/mysql/columns_priv.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/user.MYI to /data/3306/data/mysql/user.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/general_log.frm to /data/3306/data/mysql/general_log.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_keyword.frm to /data/3306/data/mysql/help_keyword.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/event.MYD to /data/3306/data/mysql/event.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_transition.frm to /data/3306/data/mysql/time_zone_transition.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_relation.frm to /data/3306/data/mysql/help_relation.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_transition_type.MYD to /data/3306/data/mysql/time_zone_transition_type.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/host.MYD to /data/3306/data/mysql/host.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_relation.MYI to /data/3306/data/mysql/help_relation.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/servers.MYD to /data/3306/data/mysql/servers.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/host.frm to /data/3306/data/mysql/host.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_relation.MYD to /data/3306/data/mysql/help_relation.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone.MYI to /data/3306/data/mysql/time_zone.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/plugin.MYI to /data/3306/data/mysql/plugin.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/servers.MYI to /data/3306/data/mysql/servers.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/user.frm to /data/3306/data/mysql/user.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/ndb_binlog_index.MYI to /data/3306/data/mysql/ndb_binlog_index.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/tables_priv.MYI to /data/3306/data/mysql/tables_priv.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/plugin.MYD to /data/3306/data/mysql/plugin.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/db.MYD to /data/3306/data/mysql/db.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/proxies_priv.frm to /data/3306/data/mysql/proxies_priv.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_transition.MYI to /data/3306/data/mysql/time_zone_transition.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/plugin.frm to /data/3306/data/mysql/plugin.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/host.MYI to /data/3306/data/mysql/host.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/columns_priv.MYI to /data/3306/data/mysql/columns_priv.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_transition_type.frm to /data/3306/data/mysql/time_zone_transition_type.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_category.MYD to /data/3306/data/mysql/help_category.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/procs_priv.MYI to /data/3306/data/mysql/procs_priv.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_name.frm to /data/3306/data/mysql/time_zone_name.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone.MYD to /data/3306/data/mysql/time_zone.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/proc.frm to /data/3306/data/mysql/proc.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/func.MYD to /data/3306/data/mysql/func.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/proxies_priv.MYD to /data/3306/data/mysql/proxies_priv.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_leap_second.frm to /data/3306/data/mysql/time_zone_leap_second.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/help_category.MYI to /data/3306/data/mysql/help_category.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/proxies_priv.MYI to /data/3306/data/mysql/proxies_priv.MYIinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/time_zone_transition.MYD to /data/3306/data/mysql/time_zone_transition.MYDinnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/tables_priv.frm to /data/3306/data/mysql/tables_priv.frminnobackupex: Copying /tmp/2016-07-03_21-07-38/mysql/slow_log.frm to /data/3306/data/mysql/slow_log.frminnobackupex: Creating directory /data/3306/data/cyhinnobackupex: Copying /tmp/2016-07-03_21-07-38/cyh/db.opt to /data/3306/data/cyh/db.optinnobackupex: Creating directory /data/3306/data/wuyiinnobackupex: Copying /tmp/2016-07-03_21-07-38/wuyi/db.opt to /data/3306/data/wuyi/db.optinnobackupex: Starting to copy InnoDB system tablespaceinnobackupex: in /tmp/2016-07-03_21-07-38innobackupex: back to original InnoDB data directory /data/3306/datainnobackupex: Copying /tmp/2016-07-03_21-07-38/ibdata1 to /data/3306/data/ibdata1innobackupex: Starting to copy InnoDB undo tablespacesinnobackupex: in /tmp/2016-07-03_21-07-38innobackupex: back to /data/3306/datainnobackupex: Starting to copy InnoDB log filesinnobackupex: in /tmp/2016-07-03_21-07-38innobackupex: back to original InnoDB log directory /data/3306/datainnobackupex: Copying /tmp/2016-07-03_21-07-38/ib_logfile1 to /data/3306/data/ib_logfile1innobackupex: Copying /tmp/2016-07-03_21-07-38/ib_logfile0 to /data/3306/data/ib_logfile0innobackupex: Copying /tmp/2016-07-03_21-07-38/ib_logfile2 to /data/3306/data/ib_logfile2innobackupex: Finished copying back files.160704 00:03:38 innobackupex: completed OK! 我们可以在查看结果 mysqlselect * from student; #我们添加的数据也都回来了 —-——–—–—— | id | name | age | dept | —-——–—–—— | 0 | 1 | 0 | NULL | | 2 | oldboy | 0 | NULL | | 3 | good | 0 | NULL | | 3 | good | 15 | NULL | —-——–—–—— 4 rows in set (0.00 sec) 增量备份二进制文件 [rootdb02 2016-07-03_21-07-38]# mysqlbinlog --start-position107 /data/3306/mysql-bin.000029 /tmp/$(date %F).sql注--start-position107可以不指定因为107是一个日志的默认起始位置。 还原增量备份 为了防止还原时产生大量的二进制日志在还原可临时关闭二进制日志后再还原 mysql set sql_log_bin0; Query OK, 0 rows affected (0.00 sec)mysql SOURCE /tmp/2016-07-03_21-07-38/2016-07-03.sql 使用innobackupex进行增量备份 前面我们进行增量备份时使用的还是老方法备份二进制日志。其实xtrabackup还支持进行增量备份。 每个InnoDB的页面都会包含一个LSN信息每当相关的数据发生改变相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础即innobackupex通过备份上次完整备份之后发生改变的页面来实现。 增量备份基本语法 1.模拟插入数据 mysqlcreate table test( - id int(4) not null, - name char(20) not null, - age tinyint(2) NOT NULL default 0, - dept varchar(16) default NULL - );Query OK, 0 rows affected (0.03 sec)mysqlinsert into test(id,name,age) values(1,oldboy,19);mysqlinsert into test(id,name,age) values(2,oldgro;,20); 2.进行增量备份 [rootdb02 tmp]# innobackupex --userroot --password123456 --socket/data/3306/mysql.sock --defaults-file/data/3306/my.cnf --incremental /tmp/ --incremental-basedir/tmp/2016-07-04_00-21-06/ 如果没有错误提示就说说明没有问题 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p160704 00:22:03 innobackupex: Executing a version check against the server...160704 00:22:03 innobackupex: Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_file/data/3306/my.cnf;mysql_read_default_groupxtrabackup;mysql_socket/data/3306/mysql.sock as root (using password: YES).160704 00:22:03 innobackupex: Connected to MySQL server160704 00:22:03 innobackupex: Done.160704 00:22:03 innobackupex: Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_file/data/3306/my.cnf;mysql_read_default_groupxtrabackup;mysql_socket/data/3306/mysql.sock as root (using password: YES).160704 00:22:03 innobackupex: Connected to MySQL server160704 00:22:03 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints completed OK!.innobackupex: Using server version 5.5.49-loginnobackupex: Created backup directory /tmp/2016-07-04_00-22-03160704 00:22:03 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file/data/3306/my.cnf --defaults-groupmysqld --backup --suspend-at-end --target-dir/tmp/2016-07-04_00-22-03 --tmpdir/tmp --extra-lsndir/tmp --incremental-basedir/tmp/2016-07-04_00-21-06/innobackupex: Waiting for ibbackup (pid3312) to suspendinnobackupex: Suspend file /tmp/2016-07-04_00-22-03/xtrabackup_suspended_2xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)incremental backup from 2099425 is enabled.xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/3306/dataxtrabackup: open files limit requested 1024, set to 65535xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir ./xtrabackup: innodb_data_file_path ibdata1:128M:autoextendxtrabackup: innodb_log_group_home_dir ./xtrabackup: innodb_log_files_in_group 3xtrabackup: innodb_log_file_size 4194304 log scanned up to (2099425)xtrabackup: Generating a list of tablespaces[01] Copying ./ibdata1 to /tmp/2016-07-04_00-22-03/ibdata1.delta[01] ...done log scanned up to (2099425)xtrabackup: Creating suspend file /tmp/2016-07-04_00-22-03/xtrabackup_suspended_2 with pid 3312160704 00:22:05 innobackupex: Continuing after ibbackup has suspended160704 00:22:05 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...160704 00:22:05 innobackupex: Executing FLUSH TABLES WITH READ LOCK...160704 00:22:05 innobackupex: All tables locked and flushed to disk160704 00:22:05 innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of /data/3306/data/innobackupex: Backing up file /data/3306/data//qqqqqqq41233/db.optinnobackupex: Backing up file /data/3306/data//dadadadadadad/db.optinnobackupex: Backing up file /data/3306/data//qqqqqqqq3/db.opt log scanned up to (2099425)innobackupex: Backing up file /data/3306/data//qqqqqqqq/db.optinnobackupex: Backing up file /data/3306/data//qqqqqqq43/db.optinnobackupex: Backing up file /data/3306/data//oldboy_gbk/db.optinnobackupex: Backing up file /data/3306/data//oldboy/student.frminnobackupex: Backing up file /data/3306/data//oldboy/db.optinnobackupex: Backing up file /data/3306/data//ooooooooooooooooo/db.optinnobackupex: Backing up files /data/3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par} (18 files)innobackupex: Backing up file /data/3306/data//qqqqqqqq1/db.optinnobackupex: Backing up file /data/3306/data//wordpress/db.optinnobackupex: Backing up file /data/3306/data//aaa/db.optinnobackupex: Backing up files /data/3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par} (72 files)innobackupex: Backing up file /data/3306/data//cyh/db.optinnobackupex: Backing up file /data/3306/data//wuyi/db.opt160704 00:22:06 innobackupex: Finished backing up non-InnoDB tables and files160704 00:22:06 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...160704 00:22:06 innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): 2099425xtrabackup: Stopping log copying thread.. log scanned up to (2099425)xtrabackup: Creating suspend file /tmp/2016-07-04_00-22-03/xtrabackup_log_copied with pid 3312xtrabackup: Transaction log of lsn (2099425) to (2099425) was copied.160704 00:22:07 innobackupex: All tables unlockedinnobackupex: Backup created in directory /tmp/2016-07-04_00-22-03innobackupex: MySQL binlog position: filename mysql-bin.000029, position 1258160704 00:22:07 innobackupex: Connection to database server closed160704 00:22:07 innobackupex: completed OK! 查看xtrabackup_checkpoints backup_type incrementalfrom_lsn 1768597to_lsn 1769413last_lsn 1769413compact 0recover_binlog_info 0 准备 准备prepare增量备份与整理完全备份有着一些不同尤其是要注意的是 1需要在每个备份包括完全和增量备份上将已经提交的事务进行“重放”。 “重放”之后所有的备份数据将合并到完全备份上 2基于所有的备份将末提交的事务进行“回滚”。 执行完全备份的redo # innobackupex –apply-log –redo-only BASE-DIR [rootdb02 data]# innobackupex --userroot --password123456 --socket/data/3306/mysql.sock --defaults-file/data/3306/my.cnf --apply-log --redo-only /tmp/2016-07-04_00-21-06/ 接着执行第一个增量 例子# innobackupex --apply-log --redo-only BASE-DIR --incremental-dirINCREMENTAL-DIR-1[rootdb02 data]# innobackupex --userroot --password123456 --socket/data/3306/mysql.sock --defaults-file/data/3306/my.cnf --apply-log --redo-only /tmp/2016-07-04_00-21-06/ --incremental-dir/tmp/2016-07-04_00-34-48/ 而后是第二个增量因为我们只执行了一次增量所以只有一个文件夹 例子# innobackupex --apply-log --redo-only BASE-DIR --incremental-dirINCREMENTAL-DIR-2[rootdb02 data]# innobackupex --userroot --password123456 --socket/data/3306/mysql.sock --defaults-file/data/3306/my.cnf --apply-log --redo-only /tmp/2016-07-04_00-21-06/ --incremental-dir/tmp/2016-07-04_00-34-48/ 提示 其中BASE-DIR指的是完全备份所在的目录而INCREMENTAL-DIR-1指定的是第一次增量备份的目录INCREMENTAL-DIR2指的是第二次增量备份的目录其他依次类推即如果有多次增量备份每一次都要执行如上操作。 Xtrabackup的“流”及“备份压缩”功能 Xtrabackup对备份的数据文件支持“流”功能即可以将备份的数据通过STDOUT传输给tar程序进行归档而不是默认的直接保存至某备份目录中。要使用此功能仅需要使用–stream选项即可。如 # innobackupex –streamtar /backup | gzip /backup/date %F_%H-%M-%S.tar.gz 甚至也可以使用类似如下命令将数据备份至其它服务器 # innobackupex –streamtar /backup | ssh userwww.magedu.com “cat – /backups/date %F_%H-%M-%S.tar” 此外在执行本地备份时还可以使用–parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然在实际进行备 份时要利用此功能的便利性也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选 项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下 # innobackupex –parallel /path/to/backup 同时innobackupex备份的数据文件也可以存储至远程主机这可以使用–remote-host选项来实现 # innobackupex –remote-hostrootwww.magedu.com /path/IN/REMOTE/HOST/to/backup 导入或导出单张表 默认情况下InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植即便使用了innodb_file_per_table 选项。而使用Xtrabackup工具可以实现此种功能不过此时需要“导出”表的mysql服务器启用了innodb_file_per_table 选项严格来说是要“导出”的表在其创建之前mysql服务器就启用了innodb_file_per_table选项并且“导入”表的服务器同 时启用了innodb_file_per_table和innodb_expand_import选项。 “导出”表 导出表是在备份的prepare阶段进行的因此一旦完全备份完成就可以在prepare过程中通过–export选项将某表导出了 # innobackupex –apply-log –export /path/to/backup 此命令会为每个innodb表的表空间创建一个以.exp结尾的文件这些以.exp结尾的文件则可以用于导入至其它服务器。 “导入”表 要在mysql服务器上导入来自于其它服务器的某innodb表需要先在当前服务器上创建一个跟原表表结构一致的表而后才能实现将表导入mysql CREATE TABLE mytable (...) ENGINEInnoDB;然后将此表的表空间删除mysql ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;接下来将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录然后使用如下命令将其“导入”mysql ALTER TABLE mydatabase.mytable IMPORT TABLESPACE; 使用Xtrabackup对数据库进行部分备份 Xtrabackup也可以实现部分备份即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能必须启用 innodb_file_per_table选项即每张表保存为一个独立的文件。同时其也不支持–stream选项即不支持将数据通过管道传输给 其它程序进行处理。 此外还原部分备份跟还原全部数据的备份也有所不同即你不能通过简单地将prepared的部分备份使用–copy-back选项直接复制回数 据目录而是要通过导入表的方向来实现还原。当然有些情况下部分备份也可以直接通过–copy-back进行还原但这种方式还原而来的数据多数会 产生数据不一致的问题因此无论如何不推荐使用这种方式。 创建部分备份 创建部分备份的方式有三种正则表达式(–include), 枚举表文件(–tables-file)和列出要备份的数据库(–databases)。 使用–include 使用–include时要求为其指定要备份的表的完整名称即形如databasename.tablename如 # innobackupex –include’^mageedu[.]tb1′ /path/to/backup 使用–tables-file 此选项的参数需要是一个文件名此文件中每行包含一个要备份的表的完整名称如 # echo -e mageedu.tb1\nmageedu.tb2 /tmp/tables.txt # innobackupex --tables-file/tmp/tables.txt /path/to/backup 使用–databases 此选项接受的参数为数据名如果要指定多个数据库彼此间需要以空格隔开同时在指定某数据库时也可以只指定其中的某张表。此外此选项也可以接受一个文件为参数文件中每一行为一个要备份的对象。如 # innobackupex –databases”mageedu testdb” /path/to/backup 整理(preparing)部分备份 prepare部分备份的过程类似于导出表的过程要使用–export选项进行 # innobackupex –apply-log –export /pat/to/partial/backup 此命令执行过程中innobackupex会调用xtrabackup命令从数据字典中移除缺失的表因此会显示出许多关于“表不存在”类的警告信息。同时也会显示出为备份文件中存在的表创建.exp文件的相关信息。 还原部分备份 还原部分备份的过程跟导入表的过程相同。当然也可以通过直接复制prepared状态的备份直接至数据目录中实现还原不要此时要求数据目录处于一致状态。 本文转载http://www.toxingwang.com/database/mysql/1539.html
