|
一 简介: Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle。几年前使用过，但现在忘记的差不多了，所以闲的没事就重新拾起看看。xtrabackup有两个主要的工具：xtrabackup、innobackupex(1).xtrabackup只能备份InnoDB和XtraDB 两种数据表(2).innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表Innobackupex完整备份后生成了几个重要的文件：xtrabackup_binlog_info：记录当前最新的LOG Positionxtrabackup_binlog_pos_innodb:innodb log postionxtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化]Xtrabackup特点：(1)备份过程快速、可靠(2)备份过程不会打断正在执行的事务(3)能够基于压缩等功能节约磁盘空间和流量(4)自动实现备份检验(5)还原速度快二 安装: http://www.percona.com/downloads/下载安装：1）直接下载二进制包，不编译，编译需要和MySQL源码包配合。wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/x86_64/percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz1）tar zxvf percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz2）ls -lhdrwxr-xr-x 2 root root 4.0K 5月 2 2014 bindrwxr-xr-x 4 root root 4.0K 5月 2 2014 share3）ls -lh bin/-rwxr-xr-x 1 root root 165K 5月 2 2014 innobackupexlrwxrwxrwx 1 root root 12 5月 2 2014 innobackupex-1.5.1 -> innobackupex-rwxr-xr-x 1 root root 2.2M 5月 2 2014 xbcrypt-rwxr-xr-x 1 root root 2.2M 5月 2 2014 xbstream-rwxr-xr-x 1 root root 13M 5月 2 2014 xtrabackup-rwxr-xr-x 1 root root 16M 5月 2 2014 xtrabackup_55-rwxr-xr-x 1 root root 79M 5月 2 2014 xtrabackup_564）cp innobackupex-1.5.1 /usr/bin/innobackupexcp xtrabackup_55 /usr/bin/xtrabackup#cp xtrabackup /usr/bin 2）Ubuntu 下的安装:http://www.percona.com/doc/percona-xtrabackup/2.1/installation/apt_repo.html1：执行apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A2：在 vi /etc/apt/sources.list 添加deb http://repo.percona.com/apt VERSION maindeb-src http://repo.percona.com/apt VERSION main3：安装$ apt-get update$ apt-get install percona-xtrabackup支持的版本有：Debian6.0 (squeeze)7.0 (wheezy)Ubuntu10.04LTS (lucid)12.04LTS (precise)12.10 (quantal)13.04 (raring)13.10 (saucy)把2中的 VERSION 按照自己的系统把上面的代入。deb http://repo.percona.com/apt precise maindeb-src http://repo.percona.com/apt precise main# precise是Ubuntu-12.04的版本代号，如果是其它系统版本，需要更换。三 使用说明:安装完之后会生成几个工具：http://www.percona.com/doc/percona-xtrabackup/2.1/manual.htmlinnobackupex：这个是其实是下面三个工具的一个perl脚本封装，可以备份MyISAM, InnoDB, XtraDB表。但在处理Myisam时需要加一个读锁。xtrabackup：一个由C编译而来的二进制文件，只能备份InnoDB和XtraDB数据。xbcrypt：用来加密或解密备份的数据。xbstream：用来解压或压缩xbstream格式的压缩文件。innobackupex ：xtrabackup命令只备份数据文件,并不备份数据表结构(.frm)，所以使用xtrabackup恢复的时候必须有对应表结构文件(.frm)。用innobackupex命令，此命令相当于冷备份，复制数据目录的索引，数据，结构文件，但会有短暂的锁表（时间依赖于MyISAM大小）。参数：--defaults-file：指定my.cnf参数文件的位置[此配置文件里必须指定datadir]--apply-log：同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir--remote-host=HOSTNAME： 通过ssh将备份数据存储到进程服务器上--stream=[tar]：备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题.--tmpdir=DIRECTORY：当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir--redo-only --apply-log：强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要--use-memory=*：该参数在prepare的时候使用,控制prepare时innodb实例使用的内存--databases=LIST：列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份--slave-info：备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0--socket=SOCKET：指定mysql.sock所在位置，以便备份进程登录mysql.更多参数见：http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html四 测试：1)全量备份&还原 #初始化mysql> create database xtra_test default charset utf8;Query OK, 1 row affected (0.00 sec)mysql> use xtra_testDatabase changedmysql> create table M(id int,name varchar(10))engine=myisam;Query OK, 0 rows affected (0.00 sec)mysql> create table I(id int,name varchar(10))engine=innodb;Query OK, 0 rows affected (0.00 sec)mysql> insert into M values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> insert into I values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from M;+------+------+| id | name |+------+------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e |+------+------+5 rows in set (0.00 sec)mysql> select * from I;+------+------+| id | name |+------+------+| 11 | A || 22 | B || 33 | C || 44 | D || 55 | E |+------+------+5 rows in set (0.00 sec) #备份root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test /home/zhoujy/xtrabackup/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/p141111 11:56:58 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.200.51' as 'zjy' (using password: YES).141111 11:56:58 innobackupex: Connected to MySQL server141111 11:56:58 innobackupex: Executing a version check against the server...141111 11:56:58 innobackupex: Done.141111 11:56:58 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.38-0ubuntu0.12.04.1-loginnobackupex: Created backup directory /home/zhoujy/xtrabackup/2014-11-11_11-56-58141111 11:56:58 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file='/etc/mysql/my.cnf' --defaults-group='mysqld' --backup --suspend-at-end --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58 --innodb_log_file_size='5242880' --innodb_data_file_path='ibdata1:10M:autoextend' --tmpdir=/tmp --extra-lsndir='/tmp' --databases='xtra_test'innobackupex: Waiting for ibbackup (pid=10539) to suspendinnobackupex: Suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2'xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )xtrabackup: uses posix_fadvise().xtrabackup: cd to /var/lib/mysqlxtrabackup: open files limit requested 0, set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880>> log scanned up to (7363097930)xtrabackup: Generating a list of tablespaces[01] Copying ./ibdata1 to /home/zhoujy/xtrabackup/2014-11-11_11-56-58/ibdata1>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)>> log scanned up to (7363097930)[01] ...done>> log scanned up to (7363097930)xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2' with pid '10540'141111 11:57:11 innobackupex: Continuing after ibbackup has suspended141111 11:57:11 innobackupex: Executing FLUSH TABLES WITH READ LOCK...141111 11:57:11 innobackupex: All tables locked and flushed to disk141111 11:57:11 innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of '/var/lib/mysql/'innobackupex: Backing up file '/var/lib/mysql//xtra_test/db.opt'innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYI'>> log scanned up to (7363097930)innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.frm'innobackupex: Backing up file '/var/lib/mysql//xtra_test/I.frm'innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYD'141111 11:57:11 innobackupex: Finished backing up non-InnoDB tables and files141111 11:57:11 innobackupex: Executing FLUSH ENGINE LOGS...141111 11:57:11 innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '7363097930'xtrabackup: Stopping log copying thread..>> log scanned up to (7363097930)xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_log_copied' with pid '10540'xtrabackup: Transaction log of lsn (7363097930) to (7363097930) was copied.141111 11:57:12 innobackupex: All tables unlockedinnobackupex: Backup created in directory '/home/zhoujy/xtrabackup/2014-11-11_11-56-58'innobackupex: MySQL binlog position: filename 'mysql-bin51.000001', position 967141111 11:57:12 innobackupex: Connection to database server closed141111 11:57:12 innobackupex: completed OK!查看备份出来的文件：ls -lh 2014-11-11_11-56-58/total 1.2G-rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf-rw-r----- 1 root root 1.2G 11月 11 11:57 ibdata1-rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info-rw-r----- 1 root root 95 11月 11 11:57 xtrabackup_checkpoints-rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info-rw-r----- 1 root root 2.5K 11月 11 11:57 xtrabackup_logfiledrwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test/home/zhoujy/xtrabackup/ 备份存放的位置，备份会在该目录下生成一个按照时间命名的文件夹。用--no-timestamp参数可以指定到自己想要的备份文件夹，不受时间命名的文件夹限制。利用--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。root@ha1:/home/zhoujy/xtrabackup# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_11-56-58/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/p141111 12:02:42 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!'.141111 12:02:42 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file='/home/zhoujy/xtrabackup/2014-11-11_11-56-58/backup-my.cnf' --defaults-group='mysqld' --prepare --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )xtrabackup: cd to /home/zhoujy/xtrabackup/2014-11-11_11-56-58xtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(7363097930)xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M: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:10M: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.3.4InnoDB: 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 7363094028 and 7363094028 in ibdata files do not match the log sequence number 7363097930 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 967, file name /var/log/mysql/mysql-bin51.000001InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.6.21 started; log sequence number 7363097930[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 7363100603141111 12:02:44 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file='/home/zhoujy/xtrabackup/2014-11-11_11-56-58/backup-my.cnf' --defaults-group='mysqld' --prepare --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58for creating ib_logfile*xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )xtrabackup: cd to /home/zhoujy/xtrabackup/2014-11-11_11-56-58xtrabackup: 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:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880xtrabackup: 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.3.4InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Setting log file ./ib_logfile101 size to 5 MBInnoDB: Setting log file ./ib_logfile1 size to 5 MBInnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN=7363100603InnoDB: Highest supported file format is Barracuda.InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.6.21 started; log sequence number 7363100684[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 7363103417141111 12:02:45 innobackupex: completed OK!应用完之后再查看备份文件看是否有变化：root@ha1:/home/zhoujy/xtrabackup# ls -lh 2014-11-11_11-56-58/total 1.2G-rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf-rw-r----- 1 root root 1.2G 11月 11 12:02 ibdata1-rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile0-rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile1-rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info-rw-r--r-- 1 root root 38 11月 11 12:02 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root 95 11月 11 12:02 xtrabackup_checkpoints-rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info-rw-r----- 1 root root 2.0M 11月 11 12:02 xtrabackup_logfiledrwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test看到redo log已经出现。xtrabackup_开头的几个文件记录了一些日志偏移量的信息和日志名和时间等信息。在xtrabackup_checkpoints中记录了备份的模式：backup_type = full-backuped。xtrabackup_binlog_info：记录当前最新的LOG Positionxtrabackup_binlog_pos_innodb:innodb log postionxtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化xtrabackup_info:备份的一些具体信息日志 #还原mysql> drop database xtra_test;Query OK, 2 rows affected (0.00 sec)1) 删除原始目录里的文件：root@ha1:/var/lib/mysql# rm ib_logfile0 ib_logfile1 ibdata1关闭数据库：1) root@ha1:/var/lib/mysql# /etc/init.d/mysql stop开始恢复2) 1：innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /home/zhoujy/xtrabackup/2014-11-11_11-56-58/ 执行上面的命令需要保证数据库目录是空的，否则会失败，因为目录里有binlog日志，要么移走，要么就用手动复制需要的文件。所以说日志不要和数据放在一起！ 2：cp ib_logfile0 ib_logfile1 ibdata1 /var/lib/mysql/ cp -R xtra_test /var/lib/mysql/修改权限和开启数据库3) root@ha1:/var/lib/mysql# chown -R mysql.mysql ib* root@ha1:/var/lib/mysql# chown -R mysql.mysql xtra_test/开启数据库 root@ha1:/var/lib/mysql# /etc/init.d/mysql start ... mysql start/running, process 15937数据已恢复：mysql> use xtra_testDatabase changedmysql> select * from I;+------+------+| id | name |+------+------+| 11 | A || 22 | B || 33 | C || 44 | D || 55 | E |+------+------+5 rows in set (0.00 sec)2)增量备份&还原#备份在做增量备份的时候需要做一次全量备份，和上面一样，只是针对所有库的备份：root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf /home/zhoujy/xtrabackup/...innobackupex: MySQL binlog position: filename 'mysql-bin51.000002', position 107141111 13:57:24 innobackupex: Connection to database server closed141111 13:57:24 innobackupex: completed OK!增量备份这里开始1.数据库操作：mysql> select * from I;+------+------+| id | name |+------+------+| 11 | A || 22 | B || 33 | C || 44 | D || 55 | E |+------+------+5 rows in set (0.00 sec)mysql> insert into I values(111,'A'),(222,'B'),(333,'C'),(444,'D'),(555,'E');Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from M;+------+------+| id | name |+------+------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e |+------+------+5 rows in set (0.00 sec)mysql> update M set name = upper(name);Query OK, 5 rows affected (0.00 sec)Rows matched: 5 Changed: 5 Warnings: 0mysql> create table X(name varchar(20))default charset utf8;Query OK, 0 rows affected (0.01 sec)mysql> insert into X values('zjy');Query OK, 1 row affected (0.00 sec)2：增量备份（基于全量的增量备份）root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/zhoujy/xtrabackup/2014-11-11_13-57-11/ /home/zhoujy/increment_data/......trabackup: Creating suspend file '/home/zhoujy/increment_data/2014-11-11_14-14-56/xtrabackup_log_copied' with pid '19492'xtrabackup: Transaction log of lsn (7363106612) to (7363106612) was copied.141111 14:15:06 innobackupex: All tables unlockedinnobackupex: Backup created in directory '/home/zhoujy/increment_data/2014-11-11_14-14-56'innobackupex: MySQL binlog position: filename 'mysql-bin51.000002', position 981141111 14:15:06 innobackupex: Connection to database server closed141111 14:15:06 innobackupex: completed OK! 其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。增量备份的文件：root@ha1:/home/zhoujy/increment_data# ls -lh 2014-11-11_14-14-56/total 440K-rw-r--r-- 1 root root 188 11月 11 14:14 backup-my.cnf-rw-r----- 1 root root 400K 11月 11 14:15 ibdata1.delta-rw-r----- 1 root root 44 11月 11 14:14 ibdata1.metadrwxr-xr-x 2 root root 4.0K 11月 11 14:15 mha_testdrwxr-xr-x 2 root root 4.0K 11月 11 14:15 mysqldrwxr-xr-x 2 root root 4.0K 11月 11 14:15 performance_schema-rw-r--r-- 1 root root 25 11月 11 14:15 xtrabackup_binlog_info-rw-r----- 1 root root 102 11月 11 14:15 xtrabackup_checkpoints-rw-r--r-- 1 root root 738 11月 11 14:15 xtrabackup_info-rw-r----- 1 root root 2.5K 11月 11 14:15 xtrabackup_logfiledrwxr-xr-x 2 root root 4.0K 11月 11 14:15 xtra_testroot@ha1:/home/zhoujy/increment_data# cat 2014-11-11_14-14-56/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 7363103427 对应增量中的to_lsnto_lsn = 7363106612last_lsn = 7363106612compact = 03:继续增量备份数据库操作mysql> insert into X values('dxy');Query OK, 1 row affected (0.01 sec)mysql> insert into X values('浙江');Query OK, 1 row affected (0.01 sec)增量备份（基于增量的增量备份）root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/zhoujy/increment_data/2014-11-11_14-14-56/ /home/zhoujy/increment_data/141111 14:25:46 innobackupex: Connection to database server closed141111 14:25:46 innobackupex: completed OK!增量备份的文件root@ha1:/home/zhoujy/increment_data# cat 2014-11-11_14-25-36/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 7363106612 对应上一个备份的to_lsnto_lsn = 7363107216last_lsn = 7363107216compact = 0要是在第2次做增量备份的时候--incremental-basedir 指向全量备份，则第一次增量备份中的数据会被第2次包含，只需要还原一次就可以恢复，现在则需要还原2次增量备份。#还原#还原全量备份：root@ha1:/home/zhoujy# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/#第一次的增量备份应用日志,应用完日志后,将合并到全备上,恢复使用全备恢复 root@ha1:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2014-11-11_14-14-56/#第二次的增量备份应用日志,应用完日志后,将合并到全备上,恢复使用全备恢复 root@ha1:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2014-11-11_14-25-36/此时两次增量备份其实都合并到全备上了,恢复是只需要使用全备进行恢复就可以了还原操作执行完之后，查看其lsn的信息：root@ha1:/home/zhoujy/xtrabackup/2014-11-11_13-57-11# cat xtrabackup_checkpoints backup_type = full-preparedfrom_lsn = 0to_lsn = 7363107216last_lsn = 7363107216compact = 0说明了，还原操作执行没问题。按照之前还原操作的步骤来恢复数据：先删除数据，再把文件复制过去，看看是否成功。测试下来，还原成功。3）压缩备份：必须使用-i参数拆包解压打包(Tar)备份innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/zhoujy/xtrabackup/ 1>/home/zhoujy/xtrabackup/xtra_test.tar拆包还原root@ha1:/home/zhoujy/xtrabackup# tar ixvf xtra_test.tar ./backup-my.cnfibdata1xtra_test/db.optxtra_test/M.MYIxtra_test/M.frmxtra_test/I.frmxtra_test/M.MYD./xtrabackup_binlog_infoxtrabackup_logfilextrabackup_checkpoints./xtrabackup_info还原方法和上面一样，就不多做说明了。打包压缩：root@ha1:/home/zhoujy/xtrabackup# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/zhoujy/xtrabackup/ | gzip >/home/zhoujy/xtrabackup/xtra_test.tar.gz解压：root@ha1:/home/zhoujy/xtrabackup# tar izxvf xtra_test.tar.gz./backup-my.cnfibdata1xtra_test/db.optxtra_test/M.MYIxtra_test/M.frmxtra_test/I.frmxtra_test/M.MYD./xtrabackup_binlog_infoxtrabackup_logfilextrabackup_checkpoints./xtrabackup_info还原方法和上面一样。
(以上内容不代表本站观点。) --------------------------------- |
本網站是"非商業"(non-commercial)。
