* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com
正文
MySQL数据库定时备份的几种实现方法
更新时间:2024年07月22日 10:44:48 作者:师小师
本文主要介绍了MySQL数据库定时备份的几种实现方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
+
目录
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_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!"
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_password= "123456"
mysql_host= "101.133.170.222"
mysql_port= "3306"
mysql_charset= "utf8"
backup_db_arr=( "ruoyi" )
backup_location= /data/mysql/bakup
expire_backup_delete= "ON"
expire_days=7
backup_time=` date +%Y%m%d%H%M`
backup_Ymd=` date +%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
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 -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_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
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_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
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 ]
[root@izuf61151k3ad2dso6mo9oz cron ]
[root@izuf61151k3ad2dso6mo9oz cron ]
|
执行 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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?
2017-08-27 Centos7 设置静态IP地址
2017-08-27 快速理解VirtualBox的四种网络连接方式