mysql备份脚本

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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
#!/bin/bash
##### by jwgod 20230928
##### mysql全库/单库备份

day=`date +'%Y-%m-%d'`
anynowtime="date +'%Y-%m-%d %H:%M:%S'"
NOW="echo [\`$anynowtime\`][PID:$$]"

##### 可在脚本开始运行时调用，打印当时的时间戳及PID。
#function job_start
job_start()
{
   echo "`eval $NOW` job_start"
}

##### 可在脚本执行成功的逻辑处调用，打印当时的时间戳及PID。
job_success()
{
    MSG="$*"
    echo "`eval $NOW` job_success:[$MSG]"
    exit 0
}

##### 可在脚本执行失败的逻辑分支处调用，打印当时的时间戳及PID。
job_fail()
{
    MSG="$*"
    echo "`eval $NOW` job_fail:[$MSG]"
    exit 1
}

job_start

##### 可在此处开始编写您的脚本逻辑代码
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="xxxx@xxxx!"
mysql_host="192.168.1.xx"
mysql_port="3306"
mysql_charset="utf8mb4"

# 备份文件存放地址(根据实际情况填写)
backup_location=/home/sql_bak/`date +%Y%m%d%H`

test -d "$backup_location" || mkdir -p $backup_location

# 是否删除过期数据
expire_backup_delete="ON"
expire_days=30
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"

# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "ERROR:MySQL is not running! backup stop!"
        exit
else
        echo $welcome_msg
fi
##### 删除30天之前备份
del_bak()
{
  # 删除过期数据
  if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
    `find $backup_location -type f -mtime +$expire_days | xargs rm -rf`
    if [ $? -eq 0 ] ; then
      echo "Expired backup data delete complete!"
      job_success "$*"
    else
      job_fail "$*"
    fi  
  fi
}

##### 全库备份
all_bak()
{
  # # 备份指定数据库中数据(此处假设数据库是all)
  # mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password  --all-databases --triggers --routines--events > $backup_dir/mysql_all_backup-$backup_time.sql
  # flag=`echo $?`
  # if [ $flag == "0" ];then
    # zip -jr $backup_dir/mysql_all_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
    # #打包成功后删除sql文件
    # if [ $? = 0 ]; then
      # rm -rf $backup_dir/mysql_all_backup-$backup_time.sql
    # fi
    # echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
    # job_success "$*"
  # else
    # echo "database mysql_backup backup fail!"
    # job_fail "$*"
  # fi

  # 备份的数据库数组
  db_arr=$(echo "show databases;" | mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password |grep -v "sys"|grep -v "Database"|grep -v "information_schema" |grep -v "performance_schema")
  echo "show databases： $db_arr"
  # 不需要备份的单例数据库
  nodeldb="test"
  # 循环备份
  for dbname in ${db_arr}
  do
   if [ $dbname != $nodeldb ]; then
     echo "mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
     mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql
     flag=`echo $?`
     if [ $flag == "0" ];then
       # tar打包所有的sql文件
       #tar -zcPpf  $backup_dir/mysql_${dbname}_backup-$backup_time.zip --warning=no-file-changed --directory /   $backup_dir
       zip -jr $backup_dir/mysql_${dbname}_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
       #打包成功后删除sql文件
       if [ $? = 0 ]; then
         rm -rf $backup_dir/mysql_${dbname}_backup-$backup_time.sql
       fi
       echo "database mysql_backup success backup to $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
       MSG="$*"
       echo "`eval $NOW` job_success:[$MSG]"
     else
       echo "database mysql_backup backup fail!"
       job_fail "$*"
     fi
   fi
  done
}

##### 单库备份
one_bak()
{
  # 备份指定数据库中数据(此处假设数据库是hseysh)
  mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql
  #echo "mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql"
  flag=`echo $?`
  if [ $flag == "0" ];then
    # tar打包所有的sql文件
    # echo "tar -zcPpf $backup_dir/mysql_$1_backup-$backup_time.zip --directory /   $backup_dir"
    # tar -zcPpf $backup_dir/mysql_$1_backup-$backup_time.zip --warning=no-file-changed --directory /   $backup_dir
    zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
    #打包成功后删除sql文件
    if [ $? = 0 ]; then
      echo "rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql"
      rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql
    fi
    echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
    job_success "$*"
  else
    echo "database mysql_backup backup fail!"
    job_fail "$*"
  fi
}

job_shell()
{
case "$1" in
  0)
    del_bak
    ;;
  1)
    all_bak
    ;;   
  xxx_test)
    one_bak $1
    ;; 	
  nacos)
    one_bak $1
    ;; 	
  xxl_job)
    one_bak $1
  *)
    echo "Usage: $0 {0[删除30天之前备份]|1[全备]|单库名[ xxxx_test | nacos | xxl_job ]}"
    exit 1

esac
exit 0    
}

##### 作业平台中执行脚本成功和失败的标准只取决于脚本最后一条执行语句的返回值
##### 如果返回值为0，则认为此脚本执行成功，如果非0，则认为脚本执行失败
#read -r -p "请选择全库/单库备份：｛0[删除30天之前备份]|1[全备]|单库名[ xxxx_test | nacos | xxl_job]｝" bak_sh
#job_shell "$bak_sh"
all_bak

#echo "mysql备份的第一个版本! "
#echo "version: 1.0.0"