使用innobackupex在线备份及恢复mysql数据库

这几天一直在研究mysql备份这一块,前几天由于手误对线上生产数据库造成误操作,虽然没有太大影响,但是为了把风险度降到最低,因此最近一直在倒腾mysql的在线热备份,在线热备份有免费开源的,也收费的产品,在这里我们值讨论免费的产品,支持热备的的软件mysqlLVM以及接下来的这款Xtrabackup;Xtrabackup是由percona开发的一个开源软件,它是innodb热备工具ibbackup(收费的商业软件)的一个开源替代品。Xtrabackup由个部分组成:xtrabackup和innobackupex,其中xtrabackup工具用于备份innodb和 xtraDB引擎的表;而innobackupex工具用于备份myisam和innodb引擎的表,本文将介绍如何用innobackupex工具做全量和增量备份。    

######一、安装解压
1.安装数据库
官网:http://www.percona.com
下载地址:http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.8/binary/Linux/x86_64/percona-xtrabackup-2.0.8-587.tar.gz
yum -y install mysql mysql-libs mysql-server MySQL-python

2.创建数据库
create database dotatest;
mysql -uroot -p dotatest > zhan_data.sql
python mysql_update.py –host localhost –user root –passwd “” –db dotatest
use dotatest;
show tables;
desc item;
insert into item (player, item, count) values (1, 1, 1 ); 插入一条数据
insert into item (player, item, count) values (2, 2, 2 );
select * from item;

3.安装依赖包  
    yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel libgcrypt libgcrypt-devel perl-Time-HiRes   

4.解压安装   
    [root@localhost home]# tar xf percona-xtrabackup-2.0.8-587.tar.gz   
    [root@localhost home]# mysql --version   
    mysql  Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64) using readline 5.1  
    [root@localhost percona-xtrabackup-2.0.8]# pwd  
    /home/percona-xtrabackup-2.0.8  
    [root@localhost percona-xtrabackup-2.0.8]# cp bin/  
    innobackupex        xbstream            xtrabackup_51       xtrabackup_56       
    innobackupex-1.5.1  xtrabackup          xtrabackup_55       
    [root@localhost percona-xtrabackup-2.0.8]# cp bin/innobackupex /usr/bin/  
    [root@localhost percona-xtrabackup-2.0.8]# cp bin/xtrabackup* /usr/bin/  

######二、常规备份文件以及对单个数据库备份

备份全部数据库
    [root@localhost /]# mkdir /home/data/backup/  
    [root@localhost /]# innobackupex --defaults-file=/etc/my.cnf --user=root  /home/data/backup/   
        --defaults-file 指定mysql的配置文件  
        --user 指定用户  
        /home/data/backup/ 备份文件的路径  
    需要注意的是 如果不指定--defaults-file 默认会读取/etc/my.cnf。如果你的配置文件是自定义的,那么一定要指定目录,然后会在目录下创建一个时间戳的目录(我们本次创建的/home/data/backup/2014-03-11_11-08-23)  
查看备份是否成功  
    [root@localhost /]# cd /home/data/backup/2014-03-11_11-08-23/  
    [root@localhost 2014-03-11_11-08-23]# pwd  
    /home/data/backup/2014-03-11_11-08-23 
    [root@localhost 2014-03-11_11-08-23]# ls  
    backup-my.cnf  ibdata1  test               xtrabackup_binlog_info   xtrabackup_logfile
    dotatest       mysql    xtrabackup_binary  xtrabackup_checkpoints  zabbix 
    [root@localhost 2014-03-11_11-08-23]#   
只针对一个数据库备份  
    --database 是指定需要备份的数据库  
    [root@localhost ~]# innobackupex --user=root  --defaults-file=/etc/my.cnf --database=dotatest  /home/data/backup/   

######三、常规恢复数据库

恢复之前先停止数据库,并且要删除数据和日志文件
系统环境:
    [root@localhost lib]# cat /etc/redhat-release   
    CentOS release 6.3 (Final)
    [root@localhost lib]# uname -a  
    Linux localhost.localdomain 2.6.32-279.el6.x86_64 #1 SMP Fri Jun 22 12:19:21 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux  
    [root@localhost lib]# uname -r  
    2.6.32-279.el6.x86_64
    [root@localhost lib]# 

    01.首先停止数据库
        [root@localhost 2014-03-11_16-52-44]# /etc/rc.d/init.d/mysqld stop  
        Stopping mysqld:                                           [  OK  ]
        [root@localhost 2014-03-11_16-52-44]# 
    02.备份删除mysql文件
        [root@localhost lib]# cp -a mysql/ mysql.bak  
        [root@localhost lib]# ls  
        alternatives  dav   dhclient  games             misc     mysql      nfs  plymouth  postfix  random-seed  rpm      stateles
        authconfig    dbus  fprint    logrotate.status  mlocate  mysql.bak  ntp  polkit-1  prelink  readahead    rsyslog  udev
        [root@localhost lib]# cd mysql  
        [root@localhost mysql]# ls  
        dotatest  ibdata1  ib_logfile0  ib_logfile1  mysql  test  zabbix
        [root@localhost mysql]# rm -rf *  

    03.来恢复文件
        恢复文件分两步,  
            第1步是apply-log,为了加快速度,一般建议设置--use-memory,这个步骤完成之后,目录/backup/mysql/data/2013-10-29_09-05-25下的备份文件已经准备就绪。  
        因为我们的文件比较少,因此我们不需要设置--user-memory产数文件  
            第2步是copy-back,即把备份文件拷贝至原数据目录下。  
                [root@localhost mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root  --apply-log /home/data/backup/2014-03-11_16-52-44/  
                [root@localhost mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /home/data/backup/2014-03-11_16-52-44/  

    04.修改权限 启动服务
        [root@localhost lib]# chown mysql:mysql /var/lib/mysql/ -R  
        [root@localhost lib]# /etc/rc.d/init.d/mysqld start  
        Starting mysqld:                                           [  OK  ]
        [root@localhost lib]# 

######四、配置增量备份

增量备份及其恢复
注意:innobackupex 增量备份仅针对InnoDB这类支持事务的引擎,对于MyISAM等引擎,则仍然是全备。
    01.首先到数据库中插入一条数据  
        mysql> use dotatest;  
        Reading table information for completion of table and column names  
        You can turn off this feature to get a quicker startup with -A  

        Database changed
        mysql> select * from item;  
        +----+--------+------+-------+
        | id | player | item | count |
        +----+--------+------+-------+
        |  1 |      1 |    1 |     1 |
        |  2 |      2 |    2 |     2 |
        +----+--------+------+-------+
        2 rows in set (0.00 sec)

        mysql> insert into item (player, item, count) values (3, 3, 3 );  
        Query OK, 1 row affected (0.00 sec)

        mysql> select * from item;  
        +----+--------+------+-------+
        | id | player | item | count |
        +----+--------+------+-------+
        |  1 |      1 |    1 |     1 |
        |  2 |      2 |    2 |     2 |
        |  3 |      3 |    3 |     3 |
        +----+--------+------+-------+
        3 rows in set (0.00 sec)

        mysql> 
    02.增量备份是基于一个数据库的全部备份文件的,在刚才我们备份了一个全备份,我们现在的增量备份就基于刚才的全部备份来做(2014-03-11_16-52-44)  
    需要注意如下两个参数:  
        --incremental-basedir指向全备目录;  
        --incremental指向增量备份的目录;  
    如下增量备份:  
        [root@localhost backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --incremental-basedir=/home/data/backup/2014-03-11_16-52-44/ --incremental /home/data/backup/  
        ……略……
        innobackupex: Backup created in directory '/home/data/backup/2014-03-11_17-33-57'
        innobackupex: MySQL binlog position: filename '', position 
        140311 17:34:16  innobackupex: completed OK!  
    03.查看是否有备份文件,最新的配置文件就是我们刚才的增量备份文件  
        [root@localhost backup]# pwd  
        /home/data/backup  
        [root@localhost backup]# ls  
        2014-03-11_16-52-44  2014-03-11_17-33-57 
        [root@localhost backup]#   


    在如下的文件中我们可以发现增量备份的文件from_lsn刚好等于全部备份的to_lsn 
    [root@localhost 2014-03-11_17-33-57]# cat xtrabackup_checkpoints     增量备份文件  
    backup_type = incremental  
    from_lsn = 657645
    to_lsn = 659090
    last_lsn = 659090     
    [root@localhost 2014-03-11_17-33-57]# cat ../2014-03-11_16-52-44/  xtrabackup_checkpoints     全局备份  
    backup_type = full-prepared
    from_lsn = 0
    to_lsn = 657645
    last_lsn = 657645
    [root@localhost 2014-03-11_17-33-57]# 

######五、在增量备份的基础上再做增量备份

    在备份之前我们在item表中再插入数据  
    mysql> insert into item (player, item, count) values (4, 4, 4 );  
    Query OK, 1 row affected (0.03 sec)  
    mysql> select * from item;  
    +----+--------+------+-------+
    | id | player | item | count |
    +----+--------+------+-------+
    |  1 |      1 |    1 |     1 |
    |  2 |      2 |    2 |     2 |
    |  3 |      3 |    3 |     3 |
    |  4 |      4 |    4 |     4 |
    +----+--------+------+-------+
    4 rows in set (0.00 sec)

    mysql> 
在增量备份的基础上再做增量备份,我们需要添加如下参数   
--incremental-basedir    执行上一次增量备份的目录  
如下备份:
    [root@localhost 2014-03-11_17-33-57]# innobackupex --defaults-file=/etc/my.cnf --user=root --incremental-basedir=/home/data/backup/2014-03-11_17-33-57/ --incremental /home/data/backup/    
    ………中间部分略………
innobackupex: Backup created in directory '/home/data/backup/2014-03-11_18-03-54'
innobackupex: MySQL binlog position: filename '', position 
140311 18:04:13  innobackupex: completed OK!  

innobackupex: Backup created in directory '/home/data/backup/2014-03-11_18-10-13'
innobackupex: MySQL binlog position: filename '', position 
140311 18:10:31  innobackupex: completed OK!  
然后来查看lsn号来:  
[root@localhost 2014-03-11_18-10-13]# cat xtrabackup_checkpoints   
backup_type = incremental
from_lsn = 659090
to_lsn = 659214
last_lsn = 659090
[root@localhost 2014-03-11_18-10-13]# cat ../2014-03-11_17-33-57/  
backup-my.cnf           ibdata1.meta            xtrabackup_binary       xtrabackup_logfile      
dotatest/               mysql/                  xtrabackup_binlog_info  zabbix/                 
ibdata1.delta           test/                   xtrabackup_checkpoints  
[root@localhost 2014-03-11_18-10-13]# cat ../2014-03-11_17-33-57/  xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 657645
to_lsn = 659090
last_lsn = 659090
[root@localhost 2014-03-11_18-10-13]#  

######六、恢复增量数据库
停止数据库
[root@localhost backup]# /etc/rc.d/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@localhost backup]#
备份数据库
[root@localhost lib]# mv mysql mysql.bak.2
[root@localhost lib]# pwd
/var/lib
[root@localhost lib]#

这个恢复比恢复全部备份稍微复杂点,接下来我们来恢复  
    innobackupex --apply-log --redo-only BASE-DIR    BASE-DIR指完整的全部备份目录  
    innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1              INCREMENTAL-DIR-1指第一次增量备份的目录  
    innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2                            BASE-DIR指完整的全部备份目录 INCREMENTAL-DIR-2第二次增量备份的目录  

01.在我们的环境中我们来执行如下操作,因为我们只有两次的增量备份如下即可:  
    innobackupex --apply-log --redo-only /home/data/backup/2014-03-11_16-52-44/  
    innobackupex --apply-log --redo-only /home/data/backup/2014-03-11_16-52-44 --incremental-dir=/home/data/backup/2014-03-11_17-33-57/  
    innobackupex --apply-log /home/data/backup/2014-03-11_16-52-44/ --incremental-dir=/home/data/backup/2014-03-11_18-10-13/  

02.回滚未完成的日志(需要注意的是恢复的时候,我们只恢复全备份文件就可以了)  
    innobackupex --apply-log /home/data/backup/2014-03-11_16-52-44/  
    innobackupex --copy-back /home/data/backup/2014-03-11_16-52-44/  

03.授权启动
    [root@localhost lib]# chown mysql:mysql /var/lib/mysql -R
    [root@localhost lib]# /etc/rc.d/init.d/mysqld start
    Starting mysqld:                                           [  OK  ]
    [root@localhost lib]# 

04.检查备份是否回来
    mysql> use dotatest;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> select * from item;
    +----+--------+------+-------+
    | id | player | item | count |
    +----+--------+------+-------+
    |  1 |      1 |    1 |     1 |
    |  2 |      2 |    2 |     2 |
    |  3 |      3 |    3 |     3 |
    |  4 |      4 |    4 |     4 |
    +----+--------+------+-------+
    4 rows in set (0.00 sec)

    mysql> 


注意:
    mkdir /var/lib/mysql
    避免如下报错
    Original data directory '/var/lib/mysql' does not exist! at /usr/bin/innobackupex line 586.
OpsNotes wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!