基于xtrabackup备份MySQL-sandy

downmovies

　　很久没有写博了，恍然间今天已经是2012年的最后一天，就让此博文纪念2012吧！
　　前些天接手了两台基于MySQL5.0的互为主从复制架构，OS是CentoS5.3,引擎主要是innoDB，当然问题很多，主从库的数据不一致，时常自动来回切换，还有最可恶的一点，就是主库时不时的会hang住，备份主要采用的是mysqldump。那么处理问题的思路就有必要整理一下了，我目前做的主要是两个方面：
　　一、保证主从复制的一致性，优化相关参数，增加主从一致性的监控，这里不详细介绍。
　　二、增加使用xtrabackup进行备份，下面对此介绍一下。
　　Xtrabackup 是percona公司（mysqlperformanceblog）的开源项目，目的是实现类似innodb官方的热备份工具InnoDB Hot Backup的功能，可以帮助我们对MySQL InnoDB类型的数据库表进行在线热备份。
　　Xtrabackup中包含两个工具：
　　
* xtrabackup – 用于热备份innodb, xtradb表的工具，不能备份其他表；
　　
* innobackupex – 对xtrabackup封装的perl脚本，提供了myisam表备份的能力；
　　目前最新版本已经是2.0.4，下载地址是http://www.percona.com/software/percona-xtrabackup，有RPM版本的，直接下载安装即可，更多内容还请参考官方手册。
　　那么安装完成后，会生成以下命令，通过此命令可以实现本文的目的
　　[root@vegaga_db1 ~]# which innobackupex
　　
/usr/bin/innobackupex
　　以下是备份脚本，备份目录是/backupdir/，需要提前建立该目录，备份产生的日志文件输出到/backupdir/backup.log

　　

• 
  

　　

　　

• #!/bin/sh
  
• ## A sample shell script to perform full backups
  
• ## Usage:/path/shellname
  
• ## Author:花狐
  
• ## Mail：jzws2008@139.com
  
• ## QQ:804586747    WEIBO:http://weibo.com/careersh
  
• ## ---------------------------------------------------------
  
• 
  
• USER=huahu
  
• PASSWORD=12345678
  
• time=`date +"%y-%m-%d-%H-%M"`
  
• date >> /backupdir/backup.log
  
• 
  
• #First begin Full Backups
  
• 
  
• echo "begin backup--------------------------------" >> /backupdir/backup.log
  
•     /usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=$USER --password=$PASSWORD   --slave-info /backupdir 2>>/backupdir/backup.log
  
• echo "end backup----------------------------------" >> /backupdir/backup.log
  
• 
  
• #second begin compress
  
• echo "Begin compress------------------------------" >> /backupdir/backup.log
  
•     archivename=`find /backupdir/ -maxdepth 1 -type d | awk -F'/' 'NR==2{printf $3}'`
  
• echo "The archive time is: $archivename" >> /backupdir/backup.log
  
•     tarname=master$archivename.tar.gz
  
• echo "The tar name is: $tarname" >> /backupdir/backup.log
  
•     /bin/tar -zcvf /backupdir/$tarname /backupdir/$archivename >> /backupdir/backup.log 2>&1
  
• 
  
• #Three Delete Document
  
• file=/backupdir/$tarname
  
• if [[ -e $file ]]
  
•     then
  
•         echo "The $tarname file exists.begin to perform delete action"   >> /backupdir/backup.log
  
•         /bin/rm -rf /backupdir/$archivename
  
•     else
  
•         echo "The file is not exists." >> /backupdir/backup.log
  
•     fi
  
• 
  
• date >> /backupdir/backup.log
  
• mail -s &quot;backup report&quot; &quot;shibeibei@vegaga.com&quot; < /backupdir/backup.log
  
• exit 0
  

　　

　　

　　那么细心的同学该问了，innobackupex不是提供了一个参数 --stream=tar吗，可以实现打包压缩的功能，是的，的确是有这么个参数，但是呢，我在测试过程中遇见一个问题，如果使用该参数的话，那么，会出现以下问题：
　　

• Sun Dec 23 01:20:01 CST 2012
  
• begin backup--------------------------------
  
• 
  
• InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
  
• and Percona Inc 2009-2012.  All Rights Reserved.
  
• 
  
• This software is published under
  
• the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
  
• 
  
• 121223 01:20:01  innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='admin' --unbuffered --
  
• 121223 01:20:01  innobackupex: Connected to database with mysql child process (pid=29288)
  
• 121223 01:20:07  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 &quot;completed OK!&quot;.
  
• 
  
• innobackupex: Using mysql  Ver 14.14 Distrib 5.5.10, for Linux (x86_64) using readline 5.1
  
• innobackupex: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
  
• 
  
• innobackupex: Created backup directory /backupdir
  
• 121223 01:20:07  innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='admin' --unbuffered --
  
• 121223 01:20:07  innobackupex: Connected to database with mysql child process (pid=29326)
  
• 121223 01:20:09  innobackupex: Connection to database server closed
  
• 
  
• 121223 01:20:09  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file=&quot;/etc/my.cnf&quot;  --defaults-group=&quot;mysqld&quot; --backup --suspend-at-end --target-dir=/tmp --stream=tar
  
• innobackupex: Waiting for ibbackup (pid=29345) to suspend
  
• innobackupex: Suspend file '/tmp/xtrabackup_suspended'
  
• 
  
• xtrabackup_55 version 2.0.4 for Percona Server 5.5.16 Linux (x86_64) (revision id: 484)
  
• xtrabackup: uses posix_fadvise().
  
• xtrabackup: cd to /var/lib/mysql
  
• xtrabackup: Target instance is assumed as followings.
  
• 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 = 2
  
• xtrabackup:   innodb_log_file_size = 5242880
  
• >> log scanned up to (4450580261)
  
• [01] Streaming ./ibdata1
  
• >> log scanned up to (4450580261)
  
• >> log scanned up to (4450580261)
  
• >> log scanned up to (4450581296)
  
• >> log scanned up to (4450581296)
  
• >> log scanned up to (4450581296)
  
• >> log scanned up to (4450581296)
  
• >> log scanned up to (4450583001)
  
• >> log scanned up to (4450584282)
  
• >> log scanned up to (4450586172)
  
• >> log scanned up to (4450586994)
  
• [01]        ...done
  
• 
  
• 121223 01:21:05  innobackupex: Continuing after ibbackup has suspended
  
• 121223 01:21:05  innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='admin' --unbuffered --
  
• 121223 01:21:05  innobackupex: Connected to database with mysql child process (pid=29455)
  
• >> log scanned up to (4450586994)
  
• 121223 01:21:07  innobackupex: Starting to lock all tables...
  
• >> log scanned up to (4450587838)
  
• >> log scanned up to (4450587838)
  
• >> log scanned up to (4450587838)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  
• >> log scanned up to (4450587848)
  

　　

　　会一直这么log scanned up to (4450587848)下去，直到你手动停在，所以才会采用这种应对方法，如果哪位仁兄有更好的方法，还望指点一下，关于这个问题，以下文章描述的很细致，可以参考：http://www.mysqlops.com/2011/05/16/xtrabackup_mysql_hang.html那么会在backupdir目录下才生成以备份启动时间命名的目录，脚本的最后一部分就是实现打包压缩，删除目录保留压缩包的功能。 先写到这里，后续继续改进！