Innobackupex全备数据库

数据库|mysql教程 对于MySQL数据库的热备，xtrabackup是除了MySQL enterprise backup之外的不二之 对于MySQL数据库的热备，xtrabackup是除了MySQL enterprise backup之外的不二之选。该工具提供了基于innodb存储引擎的热备，支持全量，增量备份，部分备份，时点恢复以及使用xtrabackup备份来重做slave等。xtrabackup工具包包含一个innobackupex命令行工具，同时支持InnoDB引擎以及MyISAM引擎。本文主要描述的是trabackup的备份原理并给出了相关演示。

1、安装部署Xtrabackup
下载地址：http://www.percona.com/software/percona-xtrabackup
# 本次安装的版本为2.2.5，下载后解压为rpm文件，直接安装即可

# tar -xvf Percona-XtraBackup-2.2.5-r5027-el5-x86_64-bundle.tar
# rpm -Uvh percona-xtrabackup-2.2.5-5027.el5.x86_64.rpm
# rpm -Uvh percona-xtrabackup-debuginfo-2.2.5-5027.el5.x86_64.rpm
# rpm -Uvh percona-xtrabackup-test-2.2.5-5027.el5.x86_64.rpm
[root@app ~]# ls /usr/bin/xtraba* /usr/bin/inno*
/usr/bin/innobackupex /usr/bin/innochecksum /usr/bin/xtrabackup

Xtrabackup有两个主要的工具：xtrabackup、innobackupex
a、xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表
b、innobackupex是一个perl脚本封装了xtrabackup。支持同时备份InnoDB和MyISAM引擎的表。
注：本文描述的xtrabackup与innobackupex可以看做是同一概念。

2、xtrabackup工作原理
InnoDB引擎很大程度上与Oracle类似，使用redo，undo机制，因此在热备期间需要考虑对于日志缓冲区在线事物日志及时写出到文件的问题。如果log buffer没有及时写出将被日志的循环写特性覆盖。xtrabackup在启动时会记住log sequence number（LSN），然后一页一页地复制InnoDB的数据。与此同时，监控log buffer中的日志情况，一旦log buffer发生变化，即数据发生了不一致，该过程会立即被捕获并把变化的页面复制到xtrabckup log，直到全部innoDB数据文件复制完成之后，停止监控log buffer及日志复制。
xtrabackup在恢复期间对提交的事务前滚，未提交或失败的事务进行回滚，从而保证数据的一致性。因此对于InnoDB表在备份期间不会锁表。由于XtraBackup其内置的InnoDB库打开文件的时候是rw的，所以运行XtraBackup的用户，必须对InnoDB的数据文件具有读写权限。

3、extrabackup的备份步骤

4、演示全备

#当前环境robin@localhost[(none)]> show variables like 'version';+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.6.12-log |+---------------+------------+robin@localhost[(none)]> show variables like '%default_storage%';+------------------------+--------+| Variable_name          | Value  |+------------------------+--------+| default_storage_engine | InnoDB |+------------------------+--------+#全备数据库[mysql@app ~]$ innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock \> --defaults-file=/data/inst3606/data3606/my3606.cnf /data/bak/hotbakInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy               ............141105 15:41:59  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;  .............innobackupex: Using mysql server version 5.6.12-loginnobackupex: Created backup directory /data/bak/hotbak/2014-11-05_15-42-02 #在指定备份目录下创建一个基于时间戳的文件夹141105 15:42:02  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/inst3606/data3606/my3606.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/bak/hotbak/2014-11-05_15-42-02 --tmpdir=/tmp --extra-lsndir='/tmp'innobackupex: Waiting for ibbackup (pid=27441) to suspendinnobackupex: Suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_suspended_2'#下面从配置文件获取有关innodb的配置信息xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/inst3606/data3606xtrabackup: open files limit requested 0, set to 1024      # Author : Leshamixtrabackup: using the following InnoDB configuration:      # Blog   : http://blog.csdn.net/leshamixtrabackup:   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 = 50331648>> log scanned up to (380655683)   #扫描innodb日志lsn并复制inndodb系统表空间[01] Copying ./ibdata1 to /data/bak/hotbak/2014-11-05_15-42-02/ibdata1>> log scanned up to (380655683)>> log scanned up to (380655683)[01]        ...done[01] Copying ./mysql/slave_relay_log_info.ibd to /data/bak/hotbak/2014-11-05_15-42-02/mysql/slave_relay_log_info.ibd            #类似部分省略，全部是基于innodb引擎的ibd文件...[01] Copying ./tempdb/tb_user.ibd to /data/bak/hotbak/2014-11-05_15-42-02/tempdb/tb_user.ibd[01]        ...done>> log scanned up to (380655683)xtrabackup: Creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_suspended_2' with pid '27441'141105 15:42:30  innobackupex: Continuing after ibbackup has suspended141105 15:42:30  innobackupex: Executing FLUSH TABLES WITH READ LOCK... #缓冲写出到数据文件并锁表141105 15:42:30  innobackupex: All tables locked and flushed to disk141105 15:42:30  innobackupex: Starting to backup non-InnoDB tables and files #复制非innodb表及相关文件innobackupex: in subdirectories of '/data/inst3606/data3606'innobackupex: Backing up files '/data/inst3606/data3606/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)>> log scanned up to (380655683)innobackupex: Backing up files '/data/inst3606/data3606/salary/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (99 files)innobackupex: Backing up files '/data/inst3606/data3606/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)innobackupex: Backing up file '/data/inst3606/data3606/tempdb/tb_user.frm'innobackupex: Backing up file '/data/inst3606/data3606/tempdb/db.opt'141105 15:42:31  innobackupex: Finished backing up non-InnoDB tables and files141105 15:42:31  innobackupex: Executing FLUSH ENGINE LOGS...    #innodb logfile写出到磁盘141105 15:42:31  innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '380655683' #获取最新的lsnxtrabackup: Stopping log copying thread..>> log scanned up to (380655683)xtrabackup: Creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_log_copied' with pid '27441'xtrabackup: Transaction log of lsn (380655683) to (380655683) was copied.141105 15:42:32  innobackupex: All tables unlocked#unlock tableinnobackupex: Backup created in directory '/data/bak/hotbak/2014-11-05_15-42-02'  #列出binlog的位置innobackupex: MySQL binlog position: filename 'inst3606bin.000001', position 352, GTID of the last change ''141105 15:42:32  innobackupex: Connection to database server closed141105 15:42:32  innobackupex: completed OK! 

5、备份的相关信息

除了从输入的日志可以看到备份的相关信息之外，也可以从备份位置获取备份期间产生的相关信息#查看备份期间生成的文件[mysql@app ~]$ ls /data/bak/hotbak/2014-11-05_15-42-02/backup-my.cnf  mysql               salary  xtrabackup_binlog_info  xtrabackup_infoibdata1        performance_schema  tempdb  xtrabackup_checkpoints  xtrabackup_logfile#查看备份有关的总体信息[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_infouuid = 4d49753a-64bf-11e4-8850-8c89a5d108aename = tool_name = innobackupextool_command = --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock   --defaults-file=/data/inst3606/data3606/my3606.cnf  /data/bak/hotbaktool_version = 1.5.1-xtrabackupibbackup_version = xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )server_version = 5.6.12-logstart_time = 2014-11-05 15:41:59end_time = 2014-11-05 15:42:32lock_time = 2binlog_pos = filename 'inst3606bin.000001', position 352, GTID of the last change ''innodb_from_lsn = 0innodb_to_lsn = 380655683partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N#查看备份binlog有关的信息[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_binlog_infoinst3606bin.000001      352#检查点的相关信息[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 380655683last_lsn = 380655683compact = 0 

6、innobackupexe备份产生的相关文件
backup-my.cnf
MySQL files +
xtrabackup_binlog_info
xtrabackup_binlog_pos_innodb
xtrabackup_slave_info（When –slave-info is used）
xtrabackup_checkpoints
Only after –apply-log
xtrabackup_logfile
xtrabackup_binary