mysql备份单实例(一)shell

最新推荐文章于 2022-08-13 15:38:06 发布

寰宇001

最新推荐文章于 2022-08-13 15:38:06 发布

阅读量197

点赞数

分类专栏： shell

原文链接：https://blog.51cto.com/u_7938217/1669168

版权

shell 专栏收录该内容

77 篇文章 3 订阅 ¥99.90 ¥299.90

订阅专栏

超级会员免费看

本文详细介绍了如何使用shell脚本备份MySQL 5.5实例，包括备份两个数据库、启用四个进程、备份压缩选项、剔除不需要的数据库、设定备份用户权限、设置计划任务以及清理旧binlog的步骤。内容涵盖脚本说明、具体脚本、配置信息和备份结果展示。

摘要由CSDN通过智能技术生成

一、脚本说明

1、需要备份2个库(mysqltest1 mysqltest)
2、开启4个进程
3、可以选择进行备份不压缩和备份压缩
4、可以对不需要的数据库剔除
5、backup用户在数据库里的权限

grant select,reload,super,lock tables,create view,show view on *.* to 'backup'@'localhost' identified by 'backup';
flush privileges;

6、计划任务

00 00 * * * cd /opt/dbbak && bash mysqlback.sh mysqlbackupconf >> /opt/dbbak/dbbackup.log 2>&1

7、会删除三天前的binlog
8、mysql版本5.5的
9、debian7.4 (3.2.54-2 x86_64)

二、具体脚本如下

cat mysqlback.sh
#!/bin/bash
#--------------------------------------------------
#Author:jimmygong
#Email:jimmygong@taomee.com
#FileName:mysqlback.sh
#Function: 
#Version:1.0 
#Created:2015-06-29
#--------------------------------------------------
if [[ $# -ne 1 ]]
then
    echo -e "Usage:$0 mysqlbackupconf"
    exit 1
else
    config=$1
    if [[ ! -f $config ]]
    then
        echo -e "Usage:$0 mysqlbackupconf"
        exit 1
    fi
fi
source $config
dbname=""
currdate=$(date +%Y%m%d)
haveinnodb=0
localip=$(ifconfig eth0|awk '/inet addr:/'|awk -F: '{print $2}'|awk -F" " '{print $1}')
function purgebinlog ()
{
    purdate=`date "+%F %T" --date='3 day ago'`
    pur="purge master logs before '$purdate'"
    echo "$pur"|mysql -u"$dbuser" -p"$dbpass"
}
function redirectlog ()
{
    logfile=$logdir/${currdate}_${localip}_log
    mkdir -p $logdir
    exec 1>$logfile
    exec 2>$logfile
}
function checkdestdir ()
{
    destdir=$destdir/$currdate
    if [[ ! -d $destdir ]]
    then
        mkdir -p $destdir
    fi
}
function runcommand ()
{
    comm=$1
    mysql -u"$dbuser" -p"$dbpass" -sNe "$comm"
}
function generateschema ()
{
    i=0
    result=`runcommand 'show databases'`
    for db in $result
    do
        rv=`echo $exclude|grep -w -i $db`
        if [[ -n "$rv" ]]
        then
            continue;
        fi
        dbname[i]=$db
        let i++
    done
}
function guessengine ()
{
    innodb=`runcommand 'show engines'|grep -i innodb|grep -i yes`
    if [[ ! -z "$innodb" ]]
    then
        haveinnodb=1
    fi
}
function dobackupsql ()
{
    db=$1
    destname=$destdir/$db.sql
    if [[ $haveinnodb -eq 1 ]]
    then
        dumpcomm="mysqldump --opt -u$dbuser -p$dbpass --single-transaction --databases"
    else
        dumpcomm="mysqldump --opt -u$dbuser -p$dbpass --databases"
    fi
    $dumpcomm $db > $destname
}
function dobackupgzip ()
{
    db=$1
    destname=$destdir/$db.sql.gz
    if [[ $haveinnodb -eq 1 ]]
    then
        dumpcomm="mysqldump --opt -u$dbuser -p$dbpass --single-transaction --databases"
    else
        dumpcomm="mysqldump --opt -u$dbuser -p$dbpass --databases"
    fi
    $dumpcomm $db|gzip > $destname
}
function backup ()
{
    actioncommand=$1
    if [[ $commpress -eq 1 ]]
    then
        actioncommand="dobackupgzip"
    else
        actioncommand="dobackupsql"
    fi
    echo -en "`date`\tBACKUP\t$db\n"    
    $actioncommand $db
}
function backupalldb ()
{
    count=0
    for db in ${dbname[@]}
    do
        backup $db &
        let count+=1
        [[ $((count%$processnum)) -eq 0 ]] && wait
    done
    wait
    echo "all backup done"
}

purgebinlog
redirectlog
checkdestdir
generateschema
guessengine
runcommand "flush logs"
backupalldb
exit 0

三、具体配置信息

cat mysqlbackupconf
dbuser="backup"
dbpass="backup"
exclude='mysql information_schema performance_schema'
destdir=/opt/dbbak
logdir=/opt/dbbak/log
commpress=1
processnum=4

四、备份后结果

1、root@10.131.172.202:~# ll /opt/dbbak/20150629/
-rw-r--r-- 1 root root 4443602410 Jun 29 19:18 mysqltest1.sql.gz
-rw-r--r-- 1 root root 4443601959 Jun 29 19:19 mysqltest.sql.gz

2、root@10.131.172.202:~# ll /opt/dbbak/log/
-rw-r--r-- 1 root root 109 Jun 29 19:19 20150629_10.131.172.202_log

参考链接：

mysql备份单实例(一)shell
https://blog.51cto.com/u_7938217/1669168