论坛 › MySQL运维 › 基于xtrabackup备份MySQL-sandy

downmovies 发表于 2018-10-3 10:39:28

基于xtrabackup备份MySQL-sandy

　　很久没有写博了，恍然间今天已经是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版本的，直接下载安装即可，更多内容还请参考官方手册。
　　那么安装完成后，会生成以下命令，通过此命令可以实现本文的目的
　　# 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:01innobackupex: Starting mysql with options:--defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='admin' --unbuffered --
[*]121223 01:20:01innobackupex: Connected to database with mysql child process (pid=29288)
[*]121223 01:20:07innobackupex: 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 mysqlVer 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:07innobackupex: Starting mysql with options:--defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='admin' --unbuffered --
[*]121223 01:20:07innobackupex: Connected to database with mysql child process (pid=29326)
[*]121223 01:20:09innobackupex: Connection to database server closed
[*]
[*]121223 01:20:09innobackupex: 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)
[*] 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)
[*]      ...done
[*]
[*]121223 01:21:05innobackupex: Continuing after ibbackup has suspended
[*]121223 01:21:05innobackupex: Starting mysql with options:--defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='admin' --unbuffered --
[*]121223 01:21:05innobackupex: Connected to database with mysql child process (pid=29455)
[*]>> log scanned up to (4450586994)
[*]121223 01:21:07innobackupex: 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目录下才生成以备份启动时间命名的目录，脚本的最后一部分就是实现打包压缩，删除目录保留压缩包的功能。 先写到这里，后续继续改进！
　　

查看完整版本: 基于xtrabackup备份MySQL-sandy