mysql备份脚本-mysqldump

时间:2022-09-21 23:40:26

背景:全库备份

备份流程:

  1.生成DB列表,将DB名字写入文件

  2.定义备份函数,结果写入SQL文件

  3.压缩文件,减少磁盘占用量

  4.设置保留天数,定期删除n天之前的

  5.通过for循环读取DB列表文件,依次进行mysqldump

 1 #!/bin/bash
 2 
 3 PORT='5001'
 4 PASSWD='admin'
 5 SOCKED="/data/socket/mysql${PORT}.sock"
 6 BACKUP_FILE='/data/mysqlData/backup'
 7 BACKUP_SAVE_DAYS='2'
 8 
 9 CMD1='show databases;'
10 Time=`date +%Y%m%d_%H%M%S`
11 EVERY_DAY_BACKUP_DIR="${BACKUP_FILE}/${Time}_backup"
12 DB_LIST="${EVERY_DAY_BACKUP_DIR}/db_list.txt"
13 
14 # function : check_db ===> db_list.txt
15 check_db()
16 {
17     mkdir -pv ${EVERY_DAY_BACKUP_DIR} >>/dev/null
18     touch ${DB_LIST} && chmod 755 ${DB_LIST} && chown -R mysql.mysql ${DB_LIST}
19     /usr/local/mysql/bin/mysql -uadmin -p${PASSWD} -S ${SOCKED} -NBe"${CMD1}"|grep -v 'information_schema\|performance_schema\|mysql'>${DB_LIST}
20 
21 }
22 
23 # function : backup_db ===>xx.sql
24 backup_db()
25 {
26     /usr/local/mysql/bin/mysqldump -uadmin -p${PASSWD} -S ${SOCKED} $1 >> ${EVERY_DAY_BACKUP_DIR}/$1.sql
27 }
28 
29 # function : tar_file ===>xx.tar.gz
30 tar_file()
31 {
32     cd ${BACKUP_FILE} 
33     tar -cf `date +%Y%m%d_%H%M%S`.tar.gz ${Time}_backup
34     rm -fr ${Time}_backup
35 }
36 
37 # function : delete xx.tar.gz : <before n days>
38 delete_file()
39 {
40     cd ${BACKUP_FILE}
41     /usr/bin/find -name "*.tar.gz" -mtime +${BACKUP_SAVE_DAYS} -exec rm {} \;
42 }
43 
44 # main_process
45 # 1
46 check_db
47 
48 # 2
49 cat ${DB_LIST}| while read line
50 do 
51     oldIFS=${IFS}
52     backup_db ${line}
53     IFS=${oldIFS}
54     sleep 20
55 done
56 
57 # 3 
58 tar_file
59 
60 # 4 delete xx.tar.gz : before n days
61 
62 delete_file ${BACKUP_SAVE_DAYS}