Xtrabackup备份工具(一)

                                             Xtrabackup备份工具(一)

       MySQL备份主要的方式是使用mysqldump来做逻辑导出备份,Mysqldump是由mysql服务器自带的一个工具,作为一种逻辑导出工具，
 其主要优点有:使用简单;备份生成的文件格式为sql脚本格式.

   缺点是:导出的结果可能会不一致，如果强行一致则需要锁定所有的表(myisam,innodb,etc..),导出需要经过sql层处理，随着数据库变大，速度会变得非常慢.

   针对以上的特点,目前业界主要的针对innodb的热备工具有:mysql商业版中的备份工具(需要收费);percona公司的开源工具xtrabackup工具.

   Xtrabackup工具的主要特点有:热备innodb表(不需要锁,myisam表需要read lock);不需要通过sql层处理,直接物理拷贝文件;支持增量备份;支持并行备份;支持straming;开源.

 二,Xtrabackup安装
从percona的网站下载过来一个tar.gz文件，直接解压即可(例：安装在/root/soft目录下)

[root@localhost soft]# pwd
/root/soft
[root@localhost soft]# ls
percona-xtrabackup-2.1.8-733-Linux-x86_64.tar.gz  tmp
[root@localhost soft]# tar -xvf percona-xtrabackup-2.1.8-733-Linux-x86_64.tar.gz

解压后生成一个目录:
percona-xtrabackup-2.1.8-Linux-x86_64

把/root/soft/percona-xtrabackup-2.1.8-Linux-x86_64/bin 加到用户的.bash_profil文件的PATH变量中,然后source或重新登录用户生效.

确认相关文件有执行权限
[root@localhost bin]# ls -l
total 114080
-rwxr-xr-x 1 root root   169217 Mar  3 08:02 innobackupex
lrwxrwxrwx 1 root root       12 Mar 28 08:59 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x 1 root root  2226551 Mar  3 08:02 xbcrypt
-rwxr-xr-x 1 root root  2300304 Mar  3 08:02 xbstream
-rwxr-xr-x 1 root root 13177944 Mar  3 08:02 xtrabackup
-rwxr-xr-x 1 root root 16599128 Mar  3 07:57 xtrabackup_55
-rwxr-xr-x 1 root root 82194272 Mar  3 08:00 xtrabackup_56

三,Xtracbackup备份

源库有一个表frank.t1
localhost>select * from frank.t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

比如我当前全备份所有数据库到/root/bak目录中.

[root@localhost bin]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock /root/bak

 

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:

140328 09:06:14  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
140328 09:06:14  innobackupex: Connected to MySQL server
140328 09:06:14  innobackupex: Executing a version check against the server...
140328 09:06:14  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

……………………………………….

140328 09:06:17  innobackupex: Waiting for log copying to finish

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

xtrabackup: Creating suspend file '/root/bak/2014-03-28_09-06-14/xtrabackup_log_copied' with pid '5469'
xtrabackup: Transaction log of lsn (2944130080) to (2944130080) was copied.
140328 09:06:18  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/root/bak/2014-03-28_09-06-14'
innobackupex: MySQL binlog position: filename 'mysqlbin145.000019', position 107
140328 09:06:18  innobackupex: Connection to database server closed
140328 09:06:18  innobackupex: completed OK!

输出有completed OK,确认备份完成

备份的用户可以使用其他的帐号，但是该帐号需要有reload,locks tables,replication client相关权限.

备份完成后，需要进行apply-log,这样整个备份才会是一个致性的状态

[root@localhost bin]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --apply-log /root/bak

140328 09:09:02  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/root/bak/2014-03-28_09-06-14/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/root/bak/2014-03-28_09-06-14 --tmpdir=/tmp

xtrabackup_55 version 2.1.8 for Percona Server 5.5.35 Linux (x86_64) (revision id: 733)
xtrabackup: cd to /root/bak/2014-03-28_09-06-14
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2944130080)
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.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
140328  9:09:02 InnoDB: The InnoDB memory heap is disabled
140328  9:09:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140328  9:09:02 InnoDB: Compressed tables use zlib 1.2.3
140328  9:09:02 InnoDB: Initializing buffer pool, size = 100.0M
140328  9:09:02 InnoDB: Completed initialization of buffer pool

………………………….
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 1153, file name ./mysqlbin145.000076
140328  9:09:18  InnoDB: Waiting for the background threads to start
140328  9:09:19 Percona XtraDB () 5.5.35-33.0 started; log sequence number 2944130572

[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 1153, file name ./mysqlbin145.000076

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140328  9:09:19  InnoDB: Starting shutdown...
140328  9:09:24  InnoDB: Shutdown completed; log sequence number 2944130572
140328 09:09:24  innobackupex: completed OK!

在这个过程中xtrabackup会进行crash recovery,这个innodb并不是真正的关闭正在使用的mysqld，而是xtrabackup本身有innodb引擎，详细的原理的可以参考xtrabackup的用户手册.

四,Xtrakbackup的恢复
使用之前的全备可以直接在文件系统级别恢复就可以了.在测试中我把/root/bak/2014-03-28_09-06-14打包，传到另一台服务器上（服务器b）,

在服务器b上，安装相同版本的mysql服务器,安装目录/usr/local/mysql

解压备份文件到/usr/local/mysql/data目录中
[root@c12 data]#tar -xvf 2014-03-28_09-06-14.tar

启动mysql服务器(注意/etc/my.cnf文件定义的innodb redo log文件大小，如果不同，就删掉redo log，让innodb重新初始化)

[root@c12 ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data

验证数据的正确性
c12>select * from frank.t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)