现在我们给MySQL做备份的时候经常会考虑到数据量的增长,数据量较小的时候用mysqldump,随着数据量越来越大mysqldump也就不太合适了.第一不支持增量备份,第二恢复的时候也较慢.这里推荐使用Percona公司的XtraBackup.

简单介绍一下热备份工具XtraBackup,它是Percona公司推出的一款热备份工具,备份的时候不影响数据读写操作,是商业工具 HotBackup的一个替代(现在应该叫MySQL Enterprise Backup这个工具不止可以备份InnoDB还可以备份MyISAM等等)

XtraBackup有两个工具:xtrabackup和innobackupex

xtrabackup本身只能备份InnoDB和XtraDB,不能备份MyISAM.

innobackupex本身是Hot Backup脚本修改而来,同时可以备份MyISAM和InnoDB,但是备份MyISAM需要加读锁.

下面简单来实验XtraBackup备份

一、安装XtraBackup(这步略过不说了.^_^)

二、使用XtraBackup备份DB

(1)全库备份

[mysql@localhost mysql]$ innobackupex-1.5.1 --user=root --password=xxxxxxx /mysql/backup --no-timestamp --parallel=4

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140226 16:09:06  innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
140226 16:09:06  innobackupex-1.5.1: Connected to MySQL server
140226 16:09:06  innobackupex-1.5.1: Executing a version check against the server...
140226 16:09:06  innobackupex-1.5.1: Done.
140226 16:09:12  innobackupex-1.5.1: Finished backing up non-InnoDB tables and files
..........
..........
..........
140226 16:09:12  innobackupex-1.5.1: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '183748588'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (183748588)

xtrabackup: Creating suspend file '/mysql/backup/xtrabackup_log_copied' with pid '3759'
xtrabackup: Transaction log of lsn (183748588) to (183748588) was copied.
140226 16:09:13  innobackupex-1.5.1: All tables unlocked

innobackupex-1.5.1: Backup created in directory '/mysql/backup'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000021', position 1649
140226 16:09:13  innobackupex-1.5.1: Connection to database server closed
140226 16:09:13  innobackupex-1.5.1: completed OK!
[mysql@localhost mysql]$ 

查看一下备份出来的文件

[mysql@localhost mysql]$ ls
backup  data  log  mysqld.log  mysqld.pid  slowquery.log
[mysql@localhost mysql]$ cd backup/
[mysql@localhost backup]$ ls
backup-my.cnf  ibdata1  mysql  percona  performance_schema  test  xtrabackup_binary  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_logfile

尝试恢复一下

恢复前的准备
[mysql@localhost backup]$ innobackupex  --apply-log --use-memory=10M /mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

140226 16:12:16  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/mysql/backup/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/mysql/backup --use-memory=10M --tmpdir=/tmp

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: cd to /mysql/backup
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(183748588)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
.......
.......
.......
.......
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 10.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=183749693
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.11 started; log sequence number 183750156

[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 1426, file name mysql-bin.000021

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 183751245
140226 16:12:22  innobackupex: completed OK!

恢复备份

停止MySQL,清空DATADIR
[mysql@localhost backup]$ mysqladmin -uroot -pxxxxxx shutdown
Warning: Using a password on the command line interface can be insecure.
140226 16:30:03 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
[1]+  Done                    mysqld_safe  (wd: ~)
(wd now: /mysql/backup)
[mysql@localhost mysql]$ mv data/ data.bak
[mysql@localhost mysql]$ ls
backup  data.bak  log  mysqld.log  slowquery.log
[mysql@localhost mysql]$ mkdir -p data
[mysql@localhost mysql]$ ls
backup  data  data.bak  log  mysqld.log  slowquery.log

恢复备份数据
[mysql@localhost mysql]$ innobackupex-1.5.1 --copy-back  --rsync /mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex-1.5.1
           prints "completed OK!".

innobackupex-1.5.1: Starting to copy files in '/mysql/backup'
innobackupex-1.5.1: back to original data directory '/mysql/data'
innobackupex-1.5.1: Copying '/mysql/backup/xtrabackup_binlog_pos_innodb' to '/mysql/data/xtrabackup_binlog_pos_innodb'
........
........
........
........
innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/mysql/backup'
innobackupex-1.5.1: back to original InnoDB log directory '/mysql/data'
innobackupex-1.5.1: Copying '/mysql/backup/ib_logfile0' to '/mysql/data/ib_logfile0'
innobackupex-1.5.1: Copying '/mysql/backup/ib_logfile1' to '/mysql/data/ib_logfile1'
innobackupex-1.5.1: Finished copying back files.

140226 16:32:59  innobackupex-1.5.1: completed OK!

启动MySQL
[mysql@localhost mysql]$ mysqld_safe &
[1] 3990
[mysql@localhost mysql]$ 140226 16:34:12 mysqld_safe Logging to '/mysql/mysqld.log'.
140226 16:34:12 mysqld_safe Starting mysqld daemon with databases from /mysql/data

(2)增量备份
创建0级备份

[mysql@localhost mysql]$ innobackupex-1.5.1 --user=root --password=xxxxx  /mysql/hotbackup/zero --no-timestamp --parallel=4

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
.....
.....
.....
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000022', position 120
140226 16:38:11  innobackupex-1.5.1: Connection to database server closed
140226 16:38:11  innobackupex-1.5.1: completed OK!

创建1级备份
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> 
mysql> insert into t values (5);
Query OK, 1 row affected (0.17 sec)

[mysql@localhost hotbackup]$ innobackupex-1.5.1 --user=root --password=xxxxxx  --incremental /mysql/hotbackup/one --incremental-basedir=/mysql/hotbackup/zero  --no-timestamp --parallel=4
.........
.........
.........
.........
基于1级备份,创建2级备份

mysql> insert into t values (6);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> 

[mysql@localhost hotbackup]$ innobackupex-1.5.1 --user=root --password=xxxxx  --incremental /mysql/hotbackup/two --incremental-basedir=/mysql/hotbackup/one  --no-timestamp --parallel=4
.........
.........
.........
.........

恢复前的准备,应用日志中提交的事物.
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --apply-log --redo-only --use-memory=10M /mysql/hotbackup/zero

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
..........
..........
..........
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 183751255
140226 16:50:32  innobackupex-1.5.1: completed OK!

将1级备份应用到0级备份
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --apply-log --redo-only --use-memory=10M /mysql/hotbackup/zero --incremental-dir=/mysql/hotbackup/one/

将2级备份应用到0级备份
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --apply-log --redo-only --use-memory=10M /mysql/hotbackup/zero --incremental-dir=/mysql/hotbackup/two/

当应用0级备份恢复增量备份的时候记住要使用--redo-only参数

当恢复完增量备份,在一次应用0级备份回滚没有提交的事物
innobackupex --apply-log --use-memory=10M /mysql/hotbackup/zero

停止MySQL恢复备份,启动MySQL

mysql@localhost hotbackup]$ mysqladmin -u root -proot shutdown
Warning: Using a password on the command line interface can be insecure.
[mysql@localhost hotbackup]$ 140226 17:00:40 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended

[1]+  Done                    mysqld_safe  (wd: /mysql)
(wd now: /mysql/hotbackup)
[mysql@localhost hotbackup]$ 
[mysql@localhost mysql]$ mv data data.bak1
[mysql@localhost mysql]$ mkdir data
[mysql@localhost mysql]$ innobackupex-1.5.1 --copy-back --rsync /mysql/hotbackup/zero
[mysql@localhost mysql]$ mysqld_safe &
[1] 6722
[mysql@localhost mysql]$ 140226 16:34:12 mysqld_safe Logging to '/mysql/mysqld.log'.
140226 16:34:12 mysqld_safe Starting mysqld daemon with databases from /mysql/data

1级备份恢复完毕,看看数据是否正常.查看一下t表
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> 

XtraBackup选择性备份

选择性备份有三个参数

--databases=LIST
例子:--databases="mysql percona.t"

--tables-file=FILE
例子:--tables-file=tables.sql

[mysql@localhost ~]$ cat tables.sql 
percona.check

 --include=REGEXP
例子:--include='^database(7|8)\.sorts'

实验中涉及到的参数英文解释如下:

  --apply-log
        Prepare a backup in BACKUP-DIR by applying the transaction log file
        named "xtrabackup_logfile" located in the same directory. Also,
        create new transaction logs. The InnoDB configuration is read from
        the file "backup-my.cnf".

 --copy-back
        Copy all the files in a previously made backup from the backup
        directory to their original locations.

    --incremental
        This option tells xtrabackup to create an incremental backup, rather
        than a full one. It is passed to the xtrabackup child process. When
        this option is specified, either --incremental-lsn or
        --incremental-basedir can also be given. If neither option is given,
        option --incremental-basedir is passed to xtrabackup by default, set
        to the first timestamped backup directory in the backup base
        directory.

 --no-timestamp
        This option prevents creation of a time-stamped subdirectory of the
        BACKUP-ROOT-DIR given on the command line. When it is specified, the
        backup is done in BACKUP-ROOT-DIR instead.

  --redo-only
        This option should be used when preparing the base full backup and
        when merging all incrementals except the last one. This option is
        passed directly to xtrabackup's --apply-log-only option. This forces
        xtrabackup to skip the "rollback" phase and do a "redo" only. This
        is necessary if the backup will have incremental changes applied to
        it later. See the xtrabackup documentation for details.

    --rsync
        Uses the rsync utility to optimize local file transfers. When this
        option is specified, innobackupex uses rsync to copy all non-InnoDB
        files instead of spawning a separate cp for each file, which can be
        much faster for servers with a large number of databases or tables.
        This option cannot be used together with --stream.

三、XtraBackup备份从库

使用XtraBackup备份从库的时候可以使用以下两个参数

 --safe-slave-backup
        此参数在备份的时候会暂停Slave的SQL线程,等到没有打开的临时表的时候开始备份.备份完成后SQL线程会自动启动,这样来保证备份一致性.

--safe-slave-backup-timeout
        此参数设置Slave_open_temp_tables超时时间,默认300

--slave-info
        此参数在备份的时候会记录主Master的BINLOG位置和名称,它会把信息记录到"xtrabackup_slave_info" ,作为CHANGE MASTER的时候从xtrabackup_slave_info查看二进制日志的位置.