xtrabackup之Innobackupex全备数据库
一、Xtrabackup是什么:
- Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。
- Xtrabackup有两个主要的工具：xtrabackup、innobackupex
- xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表
- innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢 复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。
二、利用二进制包安装xtrabackup
[root@DBMysql ~]# tar xvfz percona-xtrabackup-2.0.7-552.tar.gz -C /usr/local [root@DBMysql local]# mv percona-xtrabackup-2.0.7 xtrabackup [root@DBMysql local]# chown mysql:mysql -R /usr/local/xtrabackup/ 在mysql用户下的 .bash_profile文件中添加环境变量 export PATH=/usr/local/xtrabackup/bin:$PATH
三、创建备份专用账户
[mysql@DBMysql ~]$ mysql --defaults-file=/mysql/mysqldata/3306/my.cnf -usystem -p'123456' [(none) 10:09:13]>create user xtrabk@'127.0.0.1' identified by 'onlybackup'; Query OK, 0 rows affected (0.08 sec) [(none) 10:09:57]>grant reload,lock tables,Replication client, super on *.* to xtrabk@'127.0.0.1'; Query OK, 0 rows affected (0.07 sec) [(none) 10:10:57]>
四.innobackupex全量备份
[mysql@hadoop1 ~]$ innobackupex --defaults-file=/mysql/mysqldata/3306/my.cnf --host=127.0.0.1 --user=xtrabk --password='onlybackup' /mysql/mysqldata/backup/3306_full InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Ireland Ltd 2009-2012. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 160527 00:22:59 innobackupex: Starting mysql with options: --defaults-file='/mysql/mysqldata/3306/my.cnf' --password=xxxxxxxx --user='xtrabk' --host='127.0.0.1' --unbuffered -- 160527 00:22:59 innobackupex: Connected to database with mysql child process (pid=12647) 160527 00:23:05 innobackupex: Connection to database server closed IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". innobackupex: Using mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1 innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. innobackupex: Created backup directory /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 160527 00:23:05 innobackupex: Starting mysql with options: --defaults-file='/mysql/mysqldata/3306/my.cnf' --password=xxxxxxxx --user='xtrabk' --host='127.0.0.1' --unbuffered -- 160527 00:23:05 innobackupex: Connected to database with mysql child process (pid=12670) 160527 00:23:07 innobackupex: Connection to database server closed 160527 00:23:07 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/mysqldata/3306/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 --tmpdir=/mysql/mysqldata/3306/tmp innobackupex: Waiting for ibbackup (pid=12676) to suspend innobackupex: Suspend file '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/xtrabackup_suspended' xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /mysql/mysqldata/3306/data xtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata01:100M:autoextend xtrabackup: innodb_log_group_home_dir = /mysql/mysqldata/3306/redo xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 134217728 xtrabackup: using O_DIRECT InnoDB: Allocated tablespace 6, old maximum was 0 InnoDB: Opened 5 undo tablespaces >> log scanned up to (1527120) [01] Copying ./ibdata01 to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/ibdata01 >> log scanned up to (1527120) [01] ...done [01] Copying ./mysql/innodb_table_stats.ibd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/mysql/innodb_table_stats.ibd >> log scanned up to (1527120) [01] ...done [01] Copying ./mysql/innodb_index_stats.ibd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/mysql/innodb_index_stats.ibd [01] ...done [01] Copying ./mysql/slave_relay_log_info.ibd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/mysql/slave_relay_log_info.ibd [01] ...done [01] Copying ./mysql/slave_master_info.ibd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/mysql/slave_master_info.ibd [01] ...done [01] Copying ./mysql/slave_worker_info.ibd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/mysql/slave_worker_info.ibd [01] ...done [01] Copying ./pls_innodb/t_polestar02.ibd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/pls_innodb/t_polestar02.ibd [01] ...done [01] Copying /mysql/mysqldata/3306/undo/undo001 to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/undo001 [01] ...done [01] Copying /mysql/mysqldata/3306/undo/undo002 to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/undo002 [01] ...done [01] Copying /mysql/mysqldata/3306/undo/undo003 to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/undo003 [01] ...done [01] Copying /mysql/mysqldata/3306/undo/undo004 to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/undo004 >> log scanned up to (1527120) [01] ...done [01] Copying /mysql/mysqldata/3306/undo/undo005 to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/undo005 [01] ...done >> log scanned up to (1527120) 160527 00:23:12 innobackupex: Continuing after ibbackup has suspended 160527 00:23:12 innobackupex: Starting mysql with options: --defaults-file='/mysql/mysqldata/3306/my.cnf' --password=xxxxxxxx --user='xtrabk' --host='127.0.0.1' --unbuffered -- 160527 00:23:12 innobackupex: Connected to database with mysql child process (pid=12684) >> log scanned up to (1527120) >> log scanned up to (1527120) 160527 00:23:14 innobackupex: Starting to lock all tables... >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) >> log scanned up to (1527120) 160527 00:23:24 innobackupex: All tables locked and flushed to disk 160527 00:23:24 innobackupex: Starting to backup non-InnoDB tables and files innobackupex: in subdirectories of '/mysql/mysqldata/3306/data' innobackupex: Backing up files '/mysql/mysqldata/3306/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files) innobackupex: Backing up files '/mysql/mysqldata/3306/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files) innobackupex: Backing up file '/mysql/mysqldata/3306/data/pls_innodb/db.opt' innobackupex: Backing up file '/mysql/mysqldata/3306/data/pls_innodb/t_polestar02.frm' innobackupex: Backing up file '/mysql/mysqldata/3306/data/pls_myisam/t_polestar01.MYD' innobackupex: Backing up file '/mysql/mysqldata/3306/data/pls_myisam/db.opt' innobackupex: Backing up file '/mysql/mysqldata/3306/data/pls_myisam/t_polestar01.MYI' innobackupex: Backing up file '/mysql/mysqldata/3306/data/pls_myisam/t_polestar01.frm' 160527 00:23:25 innobackupex: Finished backing up non-InnoDB tables and files 160527 00:23:25 innobackupex: Waiting for log copying to finish xtrabackup: The latest check point (for incremental): '1527120' xtrabackup: Stopping log copying thread. .>> log scanned up to (1527120) xtrabackup: Transaction log of lsn (1527120) to (1527120) was copied. 160527 00:23:28 innobackupex: All tables unlocked 160527 00:23:28 innobackupex: Connection to database server closed innobackupex: Backup created in directory '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05' innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 1870 160527 00:23:28 innobackupex: completed OK! [mysql@hadoop1 ~]$
五、innobackupexe备份产生的相关文件
[mysql@hadoop1 2016-05-27_00-23-05]$ ls -l total 153824 -rw-rw-r-- 1 mysql mysql 290 May 27 00:23 backup-my.cnf -rw-rw---- 1 mysql mysql 104857600 May 27 00:23 ibdata01 drwx------ 2 mysql mysql 4096 May 27 00:23 mysql drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 performance_schema drwx------ 2 mysql mysql 4096 May 27 00:23 pls_innodb drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 pls_myisam drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 test -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo001 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo002 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo003 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo004 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo005 -rw-rw-r-- 1 mysql mysql 13 May 27 00:23 xtrabackup_binary -rw-rw-r-- 1 mysql mysql 24 May 27 00:23 xtrabackup_binlog_info -rw-rw---- 1 mysql mysql 77 May 27 00:23 xtrabackup_checkpoints -rw-rw---- 1 mysql mysql 2560 May 27 00:23 xtrabackup_logfile [mysql@hadoop1 2016-05-27_00-23-05]$
