mysql数据库备份脚本

脚本内容

脚本名mysql_backup_every_db.sh,示例放置在/root/script目录下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
#! /bin/bash

ARGS=`getopt -o h:u:p::d: --long host:,user:,password::,backup_dir:,help -n 'mysql_backup_every_db' -- "$@"`
eval set -- "${ARGS}"

function usage() {
        echo "USAGE:"
        echo "  mysql_backup_every_db [-h|--host <host>] [-u|--user <user>] [-p|--password <password>] [-d|--backup_dir <backup_dir>] [--help]"
        exit -1
}

#数据库连接参数
mysql_host='127.0.0.1'
mysql_user='root'
mysql_pass=''

#bin-tool-path
mysql_bin=`whereis mysql|grep 'bin'|awk '{print $2}'`
mysqldump_bin=`whereis mysqldump|grep 'bin'|awk '{print $2}'`

#备份存放目录
backup_dir="/backup/database"

while [ $# -gt 0 ]
do
    case "$1" in
        --help) 
            usage
            exit
            ;;
        -h)
            mysql_host=${2:-'127.0.0.1'}
            shift 2
            ;;
        -u)
            mysql_user=${2:-'root'}
            shift 2
            ;;
        -p|--password)
            mysql_pass=$2
            shift 2
            ;;
        -d|--backup_dir)
            backup_dir=$2
            shift 2
            ;;
        --)
            shift
            break
            ;;
        -*)
            echo "Invalid option: $1"
            usage
            exit 2
            ;;
        *)
            usage
            exit 2
            break
            ;;
    esac
done


#备份日志
backup_log="${backup_dir}/backup.log"

#判断目标目录是否已经存在
if [ ! -d $backup_dir ]; then
    mkdir -p $backup_dir
fi

echo "[$(date +%Y%m%d_%H%M%S)] starting to backup" >> $backup_log

MYCMD="$mysql_bin -u${mysql_user} -h${mysql_host} -p${mysql_pass}"
MYDUMP="$mysqldump_bin -u${mysql_user} -h${mysql_host} -p${mysql_pass} --flush-logs --single-transaction --master-data=2 --triggers --routines --events "
#列出并排除部分不备份的数据库
for database in `$MYCMD -e "show databases;"|sed '1,2d'|egrep -v 'mysql|test|performance_schema|information_schema'`
do
    if [ ! -d $backup_dir/$database ]; then
        mkdir -p $backup_dir/$database
    fi
    echo "[$(date +%Y%m%d_%H%M%S)] $database backup successfully" >> $backup_log
    $MYDUMP $database |gzip > $backup_dir/$database/$(date +%Y%m%d_%H%M%S)-all.sql.gz
done

echo "[$(date +%Y%m%d_%H%M%S)] all backup successfully" >> $backup_log

使用方法

查看命令参数

1
/root/script/mysql_backup_every_db.sh --help

结果

USAGE:
  mysql_backup_every_db [-h|--host <host>] [-u|--user <user>] [-p|--password <password>] [-d|--backup_dir <backup_dir>] [--help]

执行备份

1
2
#备份127.0.0.1主机的数据库到/backup目录下
/root/script/mysql_backup_every_db.sh -h127.0.0.1 -uroot -p123456 -d /backup

计划任务配置

1
2
#每天凌晨3点执行一次全备份
0 3 * * * /root/script/mysql_backup_every_db.sh -h127.0.0.1 -uroot -p123456 -d /backup