MySQL資料庫定時備份的幾種實現方法

一品堂.技术学习笔记發表於2024-08-27
MySql資料庫

MySQL資料庫定時備份的幾種實現方法

更新時間：2024年07月22日 10:44:48 作者：師小師
本文主要介紹了MySQL資料庫定時備份的幾種實現方法,文中透過示例程式碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

AI可以解決你遇到的任何問題，ChatGPT+國內所有大模型 免費用！
【 點選立即使用 (對話+繪畫) 】

1、檢視 MySQL 是否安裝了 crontab

1
2
yum -y install vixie-cron
yum -y install crontabs

2、crontab 常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 檢視程序是否正在進行
ps aux | grep crond
# 檢視執行狀態
systemctl status crond 或者 service crond status
# 啟動服務
systemctl start crond 或者 service crond status
# 關閉服務
systemctl stop crond 或者 service crond status
# 重啟服務
systemctl restart crond 或者 service crond restart
# 重新載入配置
systemctl reload crond
# 加入開機啟動
systemctl enable crond
# 檢視定時任務
crontab -l
# 編輯
crontab -e

3、編寫 dump_mysql.sh 指令碼

方式一：簡單版

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 設定mysql的登入使用者名稱和密碼(根據實際情況填寫)
mysql_user="root"
mysql_password="123456"
mysql_host="101.133.170.222"
mysql_port="3306"
mysql_charset="utf8"
# 備份檔案存放地址(根據實際情況填寫)
backup_location=/data/mysql/bakup
# 是否刪除過期資料
expire_backup_delete="ON"
expire_days=7
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# 備份指定資料庫中資料(此處假設資料庫是ruoyi )mysql容器裡面找到mysqldump我的路徑是/usr/bin/mysqldump
docker exec mysql /usr/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B ruoyi > $backup_dir/mysql-$backup_time.sql
# 刪除過期資料
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
`find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
echo "Expired backup data delete complete!"
fi

方式二：複雜版

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
mysql_user="root" #MySQL備份使用者
mysql_password="123456" #MySQL備份使用者的密碼
mysql_host="101.133.170.222"
mysql_port="3306"
mysql_charset="utf8" #MySQL編碼0 2 * * * docker exec -i mysql sh /root/mysql8.0.20/mysqlbfplus/run.sh
backup_db_arr=("ruoyi") #要備份的資料庫名稱，多個用空格分開隔開 如("db1" "db2" "db3")
backup_location=/data/mysql/bakup #備份資料存放位置，末尾請不要帶"/",此項可以保持預設，程式會自動建立資料夾
expire_backup_delete="ON" #是否開啟過期備份刪除 ON為開啟 OFF為關閉
expire_days=7 #過期時間天數 預設為三天，此項只有在expire_backup_delete開啟時有效
backup_time=`date +%Y%m%d%H%M` #定義備份詳細時間
backup_Ymd=`date +%Y-%m-%d` #定義備份目錄中的年月日時間
#backup_day_ago=`date -d 'expire_days days ago' +%Y-%m-%d` #備份之前的日期
backup_dir=$backup_location/$backup_Ymd #備份資料夾全路徑
welcome_msg="Welcome to use MySQL backup tools!" #歡迎語
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
echo "MySQL connect ok! Please wait......"
fi
# 判斷有沒有定義備份的資料庫，如果有定義則開始備份，否則退出備份
if [ "$backup_db_arr" != "" ];then
#dbnames=$(cut -d ',' -f1-5 $backup_database)
#echo "arr is (${backup_db_arr[@]})"
for dbname in ${backup_db_arr[@]}
do
echo "database $dbname backup start..."
`mkdir -p $backup_dir`
docker exec mysql /usr/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz
flag=`echo $?`
if [ $flag == "0" ];then
echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
else
echo "database $dbname backup fail!"
fi
done
else
echo "ERROR:No database to backup! backup stop"
exit
fi
# 如果開啟了刪除過期備份，則進行刪除操作
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
#`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;`
`find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf`
echo "Expired backup data delete complete!"
fi

方式三：docker定時備份，根據備份數量刪除

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
#使用者名稱
mysql_user="root"
#密碼
mysql_password="root"
mysql_host="101.133.170.222"
#埠號
mysql_port="3306"
#將要備份的資料庫
database_name="ruoyi"
#編碼格式
mysql_charset="utf8"
#儲存備份檔案最多個數
count=3
#備份儲存路徑
backup_path=/data/mysql/bakup
#日期
date_time=`date +%Y-%m-%d-%H-%M`
#如果資料夾不存在則建立
if [ ! -d $backup_path ];
then
mkdir -p $backup_path;
fi
#檢視MySQL是否在執行
mysql_ps=`ps -ef |grep mysql |wc -l`
echo "檢視MySQL是否在執行：$mysql_ps" >> $backup_path/dump.log
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!" >> $backup_path/dump.log
exit
else
echo "Welcome to use MySQL backup tools!" >> $backup_path/dump.log
echo "MySQL connect ok! Please wait......" >> $backup_path/dump.log
fi
#開始備份
echo "開始備份..." >> $backup_path/dump.log
docker exec h3_mysql /usr/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database_name --default-character-set=$mysql_charset > $backup_path/$database_name-$date_time.sql
#開始壓縮
cd $backup_path
tar -zcvf $database_name-$date_time.tar.gz $database_name-$date_time.sql
#更新備份日誌
echo "壓縮原檔案 create $backup_path/$database_name-$date_time.tar.gz" >> $backup_path/dump.log
#刪除原始檔
rm -rf $backup_path/$database_name-$date_time.sql
echo "壓縮後刪除原檔案 delete $backup_path/$database_name-$date_time.sql" >> $backup_path/dump.log
#找出需要刪除的備份
delfile=`ls -l -crt $backup_path/*.tar.gz | awk '{print $9 }' | head -1`
#判斷現在的備份數量是否大於閾值
number=`ls -l -crt $backup_path/*.tar.gz | awk '{print $9 }' | wc -l`
if [ $number -gt $count ]
then
#刪除最早生成的備份，只保留count數量的備份
rm $delfile
#更新刪除檔案日誌
echo "$date 刪除 $count 前的檔案 delete $delfile" >> $backup_path/dump.log
fi

方式四：不是 docker 安裝備份

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
#使用者名稱
mysql_user="root"
#密碼
mysql_password="root"
mysql_host="101.133.170.222"
#埠號
mysql_port="3306"
#將要備份的資料庫
database_name="ruoyi"
#編碼格式
mysql_charset="utf8"
#儲存備份檔案最多個數
count=3
#備份儲存路徑
backup_path=/data/mysql/bakup
#日期
date_time=`date +%Y-%m-%d-%H-%M`
#如果資料夾不存在則建立
if [ ! -d $backup_path ];
then
mkdir -p $backup_path;
fi
#檢視MySQL是否在執行
mysql_ps=`ps -ef |grep mysql |wc -l`
echo "$date_time 檢視MySQL是否在執行：$mysql_ps" >> $backup_path/dump.log
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "$date_time ERROR:MySQL is not running! backup stop!" >> $backup_path/dump.log
exit
else
echo "$date_time Welcome to use MySQL backup tools!" >> $backup_path/dump.log
echo "$date_time MySQL connect ok! Please wait......" >> $backup_path/dump.log
fi
#開始備份
echo "$date_time 開始備份...$backup_path" >> $backup_path/dump.log
docker exec h3_mysql /usr/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database_name --default-character-set=$mysql_charset > $backup_path/$database_name-$date_time.sql
echo "$date_time 備份完成...$backup_path" >> $backup_path/dump.log
#開始壓縮
cd $backup_path
tar -zcvf $database_name-$date_time.tar.gz $database_name-$date_time.sql
#更新備份日誌
echo "$date_time 壓縮原檔案 create $backup_path/$database_name-$date_time.tar.gz" >> $backup_path/dump.log
#刪除原始檔
rm -rf $backup_path/$database_name-$date_time.sql
echo "$date_time 壓縮後刪除原檔案 delete $backup_path/$database_name-$date_time.sql" >> $backup_path/dump.log
#找出需要刪除的備份
delfile=`ls -l -crt $backup_path/*.tar.gz | awk '{print $9 }' | head -1`
#判斷現在的備份數量是否大於閾值
number=`ls -l -crt $backup_path/*.tar.gz | awk '{print $9 }' | wc -l`
if [ $number -gt $count ]
then
#刪除最早生成的備份，只保留count數量的備份
rm $delfile
#更新刪除檔案日誌
echo "$date_time 刪除 $count 前的檔案 delete $delfile" >> $backup_path/dump.log
echo "$date_time 備份成功" >> $backup_path/dump.log
fi

4、配置定時任務

時間格式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 常用時間格式
每五分鐘執行 */5 * * * *
每小時執行 0 * * * *
每天執行 0 0 * * *
每週執行 0 0 * * 0
每月執行 0 0 1 * *
每年執行 0 0 1 1 *
# crontab 檔案的格式
{minute} {hour} {day-of-month} {month} {day-of-week} {full-path-to-shell-script}
minute: 區間為 0 – 59
hour: 區間為0 – 23
day-of-month: 區間為0 – 31
month: 區間為1 – 12. 1 是1月. 12是12月.
Day-of-week: 區間為0 – 7. 週日可以是0或7.

常用時間：

1
2
3
4
# 執行 crontab -e 命令，寫入以下命令儲存，每 5 分鐘執行一次
*/5 * * * * sh /data/mysql/dump_mysql.sh
# 每天凌晨 5 點執行
0 5 * * * sh /data/mysql/dump_mysql.sh

儲存如果 出現一下問題

在這裡插入圖片描述

進入/var/spool下檢視cron目錄是正常的，但是在cron裡面沒有許可權建立檔案這個根源。

（1）可以嘗試先在/var/spool/cron目錄下用vim編輯一個測試檔案，看是否可以儲存在這個cron目錄下，如果無法儲存提示許可權問題。那麼可能目錄有什麼特殊的地方，root使用者也被約束了

1
2
3
4
5
6
# 檢視是否有特殊的屬性
[root@izuf61151k3ad2dso6mo9oz cron]# lsattr /var/spool/cron/root
# 去掉特殊的屬性
[root@izuf61151k3ad2dso6mo9oz cron]# chattr -ai /var/spool/cron/root
# 再次編輯定時任務
[root@izuf61151k3ad2dso6mo9oz cron]# crontab -e

執行 lsattr /var/spool/cron/root 我們可以看到和常規的許可權設定不一樣，所以清楚這些特殊的屬性

在這裡插入圖片描述

清除屬性我們可以看到正常的許可權的問題

在這裡插入圖片描述

再次執行 crontab -e ，編輯模式，新增 */5 * * * * sh /data/mysql/dump_mysql.sh ，儲存即可

在這裡插入圖片描述

補充說明

crontab 時間格式說明

1
0 5 * * * /root/bin/backup.sh

到此這篇關於MySQL資料庫定時備份的幾種實現方法的文章就介紹到這了,更多相關MySQL 定時備份內容請搜尋指令碼之家以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援指令碼之家！


@echo off

echo 設定MySql資料庫的連線資訊
set host=127.0.0.1
set port=
set user=
set pass=

echo 設定要備份的MySql資料庫名稱
#（庫名）
set dbname=forestrybureau_ys

echo 獲取當天的日期格式，例如：20221102231300

set hour=%time:~0,2%
if "%time:~0,1%"==" " set hour=0%time:~1,1%

set backup_date=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2%


echo 設定備份檔案的路徑 （注意路徑！！！）
set backupfile=E:\lyxmjgzxxt\mysql_dataBack\forestrybureau_ys\%dbname%_backup_%backup_date%.sql

echo 使用mysqldump對指定的MySql進行備份
echo 注意mysqldump實際路徑（注意路徑！！！）

"E:\lyxmjgzxxt\mysql-5.7.44-win32\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile%

echo 刪除過期檔案，這裡是超過60天就刪除（注意路徑！！！）

forfiles /p E:\lyxmjgzxxt\mysql_dataBack\forestrybureau_ys /s /m *.sql /d -60 /c "cmd /c del @file /f"S



相關文章