Mysql热备xtrabackup的使用

InnoDB 有个商业的InnoDB Hotbackup，可以对InnoDB引擎的表实现在线热备。而 percona出品的Xtrabackup，是InnoDB Hotbackup的一个开源替代品，可以在线对InnoDB/XtraDB引擎的表进行物理备份。mysqldump支持在线备份，不过是逻辑备份，效率比较差。当数据量比较小的时候，mysqldump还可以胜任，当数据量大的时候，恢复时间却让人无法忍受，于是开源工具xtrabackup就应运而生了，xtrabackup属于物理备份，效率很不错。

xtrabackup提供了两种命令行工具：

xtrabackup：用于备份InnoDB引擎的数据（不会备份myisam比如mysql权限相关表等，也不会自动copy frm文件）；

innobackupex：一个perl脚本，在执行过程中会调用xtrabackup命令，用该命令即可以备份InnoDB，也可以备份MyISAM/copy frm文件，只不过在备份myisam表时候会添加一个读锁。

实验环境：CentOS release 6.5 (Final)，mysql  Ver 14.14 Distrib 5.6.14

xtrabackup安装

#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.13/binary/redhat/6/x86_64/percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm

#yum  -y install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.* package  bzr bison ncurses-devel zlib-devel

#rpm -ivh percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm

 部分参数说明

  --user                   指定执行备份的用户。

  --password               指定执行备份用户的密码。

  --defaults-file          指定mysql的选项文件路径。

  --no-timestamp           不要显示时间戳。

  --incremental            告诉xtrabackup这次是创建增量备份。  

  --incremental-basedir    指定一个全量备份的路径作为增量备份的基础。

  --redo-only       如果进行准备工作完成后，还有其他的增量备份集待处理，就需要指定这个参数。

  --apply-log       从指定的选项文件中读取配置信息并应用日志等，这就意味对备份集做恢复的准备工作。

  --copy-back       将指定备份集恢复到指定的路径下。

全备

#将全备的数据备份到/data/backup/base

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --no-timestamp  /data/backup/base

   ......................

innobackupex: Backup created in directory '/data/backup/base'

innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 733

160201 15:26:07  innobackupex: Connection to database server closed

160201 15:26:07  innobackupex: completed OK!

先对数据库进行一些修改。

mysql> create database sharelinux;

Query OK, 1 row affected (0.00 sec)

mysql> use sharelinux;

Database changed

mysql> create table t1(id int,name varchar(10));

Query OK, 0 rows affected (0.12 sec)

mysql> insert into t1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');

Query OK, 3 rows affected (0.08 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;

+------+----------+

| id   | name     |

+------+----------+

|    1 | zhangsan |

|    2 | lisi     |

|    3 | wangwu   |

+------+----------+

3 rows in set (0.00 sec)

第一次增量备份

#第一次增量备份目录/data/backup/incremental_one

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --no-timestamp --incremental /data/backup/incremental_one --incremental-basedir=/data/backup/base/

    ......................................................

xtrabackup: Creating suspend file '/data/backup/incremental_one/xtrabackup_log_copied' with pid '19979'

xtrabackup: Transaction log of lsn (22333659) to (22333659) was copied.

160201 15:39:26  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/data/backup/incremental_one'

innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1238

160201 15:39:26  innobackupex: Connection to database server closed

160201 15:39:26  innobackupex: completed OK!

再对数据库进行修改

mysql> create database db01;

Query OK, 1 row affected (0.00 sec)

mysql> use db01;

Database changed

mysql> create table t2(id int,name varchar(10));

Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;

+------+----------+

| id   | name     |

+------+----------+

|    1 | zhangsan |

|    2 | lisi     |

|    3 | wangwu   |

+------+----------+

3 rows in set (0.00 sec)

第二次增量备份

#第二次增量备份目录/data/backup/incremental_two

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --no-timestamp --incremental /data/backup/incremental_two --incremental-basedir=/data/backup/incremental_one/

   ........................................

innobackupex: Backup created in directory '/data/backup/incremental_two'

innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1689

160201 15:59:10  innobackupex: Connection to database server closed

160201 15:59:10  innobackupex: completed OK!

模拟故障，删除数据库的数据文件

# ls /usr/local/webserver/mysql5.6/data/

auto.cnf  ib_logfile0  mysql-bin.000001  node1.pid           test

db01      ib_logfile1  mysql-bin.index   performance_schema  zabbix

ibdata1   mysql        node1.err         sharelinux

# rm -rf /usr/local/webserver/mysql5.6/data/*

恢复准备

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log --redo-only /data/backup/base/

   ........................................

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 22327338

160201 16:09:44  innobackupex: completed OK!

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log --redo-only /data/backup/base/ --incremental-dir=/data/backup/incremental_one/

  ...........................................

innobackupex: Copying '/data/backup/incremental_one/performance_schema/events_stages_history.frm' to '/data/backup/base/performance_schema/events_stages_history.frm'

innobackupex: Copying '/data/backup/incremental_one/performance_schema/setup_instruments.frm' to '/data/backup/base/performance_schema/setup_instruments.frm'

160201 16:13:15  innobackupex: completed OK!

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log  /data/backup/base/ --incremental-dir=/data/backup/incremental_two/

innobackupex: Copying '/data/backup/incremental_two/performance_schema/setup_instruments.frm' to '/data/backup/base/performance_schema/setup_instruments.frm'

innobackupex: Copying '/data/backup/incremental_two/db01/db.opt' to '/data/backup/base/db01/db.opt'

innobackupex: Copying '/data/backup/incremental_two/db01/t2.frm' to '/data/backup/base/db01/t2.frm'

160201 16:18:27  innobackupex: completed OK!

数据恢复

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --copy-back  /data/backup/base/

    ...........................................

innobackupex: Starting to copy InnoDB system tablespace

innobackupex: in '/data/backup/base'

innobackupex: back to original InnoDB data directory '/usr/local/webserver/mysql5.6/data'

innobackupex: Copying '/data/backup/base/ibdata1' to '/usr/local/webserver/mysql5.6/data/ibdata1'

innobackupex: Starting to copy InnoDB undo tablespaces

innobackupex: in '/data/backup/base'

innobackupex: back to '/usr/local/webserver/mysql5.6/data'

innobackupex: Starting to copy InnoDB log files

innobackupex: in '/data/backup/base'

innobackupex: back to original InnoDB log directory '/usr/local/webserver/mysql5.6/data'

innobackupex: Finished copying back files.

160201 16:23:08  innobackupex: completed OK!

查看目录

# ll /usr/local/webserver/mysql5.6/data/  #这个目录下的数据已经恢复回来

total 79916

drwxr-x---. 2 root root     4096 Feb  1 16:18 db01

-rw-r-----. 1 root root 79691776 Feb  1 16:18 ibdata1

drwx------. 2 root root     4096 Feb  1 15:26 mysql

drwxr-xr-x. 2 root root     4096 Feb  1 15:26 performance_schema

drwxr-x---. 2 root root     4096 Feb  1 16:13 sharelinux

drwxr-xr-x. 2 root root     4096 Feb  1 15:26 test

-rw-r--r--. 1 root root       22 Feb  1 16:18 xtrabackup_binlog_info

-rw-r-----. 1 root root       91 Feb  1 16:18 xtrabackup_checkpoints

-rw-r--r--. 1 root root      722 Feb  1 16:18 xtrabackup_info

-rw-r-----. 1 root root  2097152 Feb  1 16:09 xtrabackup_logfile

drwx------. 2 root root    12288 Feb  1 15:26 zabbix

#chown mysql:mysql /usr/local/webserver/mysql5.6/data/ -R  #将目录更改为mysql用户

# pkill mysql                                              #杀死原来的mysql进程，然后重启数据库

# service mysqld start

Starting MySQL..                                           [  OK  ]

数据已经恢复回来了

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| db01               |

| mysql              |

| performance_schema |

| sharelinux         |

| test               |

| zabbix             |

+--------------------+

7 rows in set (0.14 sec)

mysql> select * from sharelinux.t1;   #第一次增量备份的数据

+------+----------+

| id   | name     |

+------+----------+

|    1 | zhangsan |

|    2 | lisi     |

|    3 | wangwu   |

+------+----------+

3 rows in set (0.03 sec)

mysql> select * from db01.t2;          #第二次增量备份的数据

+------+----------+

| id   | name     |

+------+----------+

|    1 | zhangsan |

|    2 | lisi     |

|    3 | wangwu   |

+------+----------+

3 rows in set (0.08 sec)

©著作权归作者所有：来自51CTO博客作者sharelinux的原创作品，如需转载，请注明出处，否则将追究法律责任

mysql数据库xtrabackupMysql