Mysql数据库备份的几种解决方案

一、Navicat定时自动备份

通过使用Navicat中的自动运行任务来实现定时生成.nb3备份文件，此文件所占内存相比.SQL文件要小很多。

配置流程如下：

1.1 打开Navicat下的自动运行

1.2 新建批处理作业

点击Navicat菜单栏下的【自动运行】功能——选择下方的新建批处理作业

1.3 创建配置文件

这一步完成后如下所示

1.4 配置定时触发器

1.5 常用触发器的配置

配置每天23:00备份一次整个数据库

触发器创建完成后，不要单击触发器下的【确定】，切换到【常规】选项卡

设置无论用户是否登录都需要执行此任务

至此，就成功创建了一个每天23:00执行一次的定时备份任务

1.6 测试演示

我们可以通过手动修改window系统时间来进行定时任务的测试
修改系统时间
按下win键-搜索“时间和日期”，进入window的更改日期和时间

待系统时间到达触发器所设定时间时，可以进入到Navicat的本地存放路径查看是否生成了备份文件

由于是每天执行一次的，所以建议多次修改系统时间进行测试，以确保触发器正常运行。

连续修改系统时间进行测试

触发器中还有每天、每周、每月等触发周期，在此不多赘述，感兴趣的朋友可以自己去研究下。

二、bat脚本文件+window定时任务

实现原理是在.bat脚本文件中写入mysql的备份命令，通过cmd命令窗口的形式去执行数据库的备份，生成的文件类型为.SQL，所占内存较大。

2.1 创建数据库备份脚本

首先我们可以使用本机的cmd命令窗口尝试手动写入备份命令来检测运行环境是否支持（此种方式需要配置数据库环境变量）

2.1.1测试本地环境

win+R-CMD 打开命令窗口
写入命令mysqldump -uroot -p123456 zhiguang_db >C:\Users\cgl\Desktop\dataBackup\db\test.sql
此命令表示的含义为：
mysqldump：MySQL的备份命令
-u:用户名
-p:密码
zhiguang_db：需要备份的数据库名
路径：备份文件的存放地址（可以是局域网内的共享文件夹地址）

提示如上界面，且存放路径中生产了所命名的.sql文件则说明运行成功，且环境支持使用mysqldump进行备份

如果执行命令时提示“‘mysqldump’ 不是内部或外部命令，也不是可运行的程序或批处理文件。

说明MySQL未配置环境变量，有两种方式可以解决
1、将mysql安装路径下的bin目录配置一下环境变量
2、在mysqldump 命令前加上安装的bin目录前缀（建议这种）

环境变量添加：
找到本机mysql的bin路径

打开环境变量配置


注意：操作完成后需要重新打开命令窗口才会生效！

2.1.2创建.bat备份文件

新建一个.txt文件，写入如下命令

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
set host=127.0.0.1
set port=3306
set username=root
set password=123456
set dbname=testdb
set backupfile=\\192.168.1.5\MesResource\testDBBack\remoteFile\%dbname%-%Ymd%.sql
set orderPath=D:\mysql\mysql-8.0.29\bin\mysqldump
%orderPath% -h%host% -P%port% -u%username% -p%password% --single-transaction --quick %dbname%>%backupfile%
@echo on

将文件类型重新命名为.bat

测试脚本

2.2 配置window定时任务

（1）右键点击“我的电脑”—“管理”；
（2）点击“系统工具”—“任务计划程序”—“任务计划程序库”—“创建基本任务”；
依照下图配置完成后，单击下一步
依照下图配置完成后，单击下一步

依照下图配置完成后，单击下一步

依照下图配置完成后，单击下一步

单击【浏览】，选择自己的.bat文件，完成后单击下一步

最后检查配置是否正确，确认无误后-单击【完成】

随后可以在任务栏中看到我们刚刚创建的任务

注意看刚刚创建的任务上方就是上文中在Navicat中所创建的定时任务，点开查看详细信息可以发现Navicat中的定时任务也跟此处原理类似，只是所执行的程序不同

2.3 测试定时任务

测试同上文中的Navicat一样，也是通过手动修改系统时间进行测试，此处不多赘述，详细操作可翻看上文。

三、Navicat+.bat脚本+window定时任务

比较以上两种方式优缺点

方式	优点	缺点
Navicat自动备份	备份文件所占内存较小，恢复数据方便快捷	商用存在版权限制，备份文件只能存储在本地
.bat脚本备份	备份文件存放路径可变，可以是本地路径也可以是局域网中的共享文件夹路径	所生成的.SQL备份文件所占内存容量巨大，存储和恢复数据不便

以上两种实现方式各有优缺点，在不考虑Navicat商用权限的情况下，我们可以将两种方式整合到一起，实现备份文件所占内存小，且能够自动将备份文件移植到外部存储介质中。
实现思路：
1、使用Navica定时生成.nb3备份文件；
2、编写.bat脚本将Navicat所生成的备份文件copy到外部存储介质中
3、配置window定时任务执行此copy脚本
实现步骤：
在生产环境中不建议直接操作生产库，另外创建一个测试库，确保定时任务运行正常后再对生产库进行操作

3.1 使用Navicat配置定时备份任务

详细操作请翻阅上文

3.2 编写.bat脚本将Navicat备份文件copy到外部存储介质

同样的，在编写.bat脚本文件前，可以使用命令窗口先进行测试一下
打开cmd命令窗口，输入如下命令

set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
xcopy C:\Users\cgl\Documents\Navicat\MySQL\Servers\localhost\zhiguang_db\%Ymd%*.nb3  \\192.168.1.5\MesResource\testDBBack\remoteFile

执行后，打开共享文件夹，可以看到其中复制了一个.nb3备份文件进去，说明运行正常

新建一个.txt文件，将一下脚本复制到文件中，并将文件重命名为.bat。
需要修改的是Navicat备份文件的存放路径和文件所复制的存放路径

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
set navicatFilePath=C:\Users\cgl\Documents\Navicat\MySQL\Servers\localhost\zhiguang_db\%Ymd%*.nb3
set backupfile=\\192.168.1.5\MesResource\testDBBack\remoteFile
xcopy %navicatFilePath%  %backupfile%
@echo on

双击此脚本文件，即可实现将Navicat的备份文件copy到外部地址中。

脚本含义如下：

脚本	含义
@echo off	关闭命令显示（不重要）
set “Ymd=%date:,4%%date:5,2%%date:~8,2%”	将Ymd设置为当日的日期前缀，如：20230416，可以使用%Ymd%获取这个值
set navicatFilePath=C:\Users\cgl\Documents\Navicat\MySQL\Servers\localhost\zhiguang_db%Ymd%*.nb3	Navicat本地备份文件存放地址
set backupfile=\192.168.1.5\MesResource\testDBBack\remoteFile	文件复制地址
xcopy	复制文件命令
%Ymd%*.nb3	以20230416为前缀，*统配符，.nb3备份文件
\192.168.1.5\MesResource\testDBBack\remoteFile	外部存储介质地址（共享盘）
@echo on	开启命令显示（不重要）
以上脚本的含义是，使用xcopy命令，将Navicat的备份文件夹下当日生成的.nb3文件复制到外部存储地址中，因为Navicat所生成的.nb3文件是以日期和时分秒为前缀，所以%Ymd%*.nb3 就可以检索到当日所生成的备份文件

3.3 创建window定时任务执行复制脚本

详细操作流程请看2.2 配置windows
注意：复制脚本定时任务的执行时间要在Navicat生成备份文件之后，因为此脚本的作用是复制Navicat的备份文件到外部存储地址中，建议复制脚本在Navicat备份任务后30分钟/1小时

将一个.SQL文件为2GB左右，.nb3文件为115MB的数据库进行手动测试的所需要执行时间为3分19秒

3.4 测试

以上配置完成后，可以在windows的定时任务界面中看到这两个定时任务

四、Navicat访问远程库备份

4.1 创建远程数据库连接

4.1.1 新建连接

4.1.2 填写连接信息

主机地址为远程的服务器IP地址

4.1.3 自定义文件存放位置

切换到高级选项卡，选择备份文件存放位置
注：如果是通过客户机访问服务器的数据库进行备份，建议将此文件位置选择一个容量较大的磁盘。

4.2 开启服务器数据库远程访问权限

如果测试连接提示1130错误码，说明所访问主机的数据库不允许远程访问，需要到服务器开启远程访问权限。

来到所访问的服务器上进行以下操作

4.2.1 打开命令窗口

win+R 输入CMD 打开命令行窗口

4.2.2 输入mysql登录命令

mysql -uroot -p

按下回车，输入密码，登录Mysql

如果提示：mysql不是内部或外部命令，也不是可运行的程序

说明mysql的bin目录没有配置环境变量

有两种方式解决方式：

1、在mysql的bin路径下打开cmd命令窗口

会打开bin目录下的cmd命令窗口

2、环境变量添加：

找到本机的mysql的bin路径

打开环境变量配置

注意：操作完成后需要重新打开命令窗口才会生效！

4.2.3 查看一下所有的库

show databases;

4.2.4 进入mysql数据库

use mysql;

4.2.5 查看mysql数据库中所有的表

show tables;

4.2.6 查看user表中的数据

select Host,User,authentication_string from user;

4.2.7 修改user表权限

update user set Host=‘%’ where User=‘root’;

4.2.8 刷新

flush privileges;

4.2.9 测试连接

4.3 创建Navicat自动备份任务

详细步骤参阅上文一、Navicat定时自动备份
注意：此处需要选择刚刚创建的远程数据库连接下的需要备份的数据库

五、测试备份时操作数据库会怎样

5.1 Navicat生成.nb3备份文件时操作数据库

经过多次测试发现，Navicat执行备份时是以数据库的表名排序自上而下进行的，默认排序为a~z，所以我们可以新建两张测试表，分别以字母a和z开头，当备份任务执行到一半时，也就是a表备份完成，z表还未备份时，分别操作这两个表，看最终结果如何。

两个测试表的位置如下所示


为此数据库创建一个每天23:00执行的备份任务

当备份任务执行时，同时新建一个查询操作数据库

修改

新增

删除

待备份任务执行完成后，再次查看a、z表的数据内容

对比前后的数据可知，a、z两个表在备份时所执行的SQL都生效了，说明Navicat备份时可以正常对数据库进行增删改查操作。

然后将数据进行还原，再次对比三次的数据有何不同。


由测试数据可知，还原后的a表数据和备份前的a表数据一致，还原后的z表数据和测试后的z表数据一致，所以可以得出以下结论：

1. Navicat执行备份任务时可以正常操作数据库，但是已经备份完成的表的操作数据不会存放在备份文件中；
2. Navicat执行备份时是以数据库中的表顺序依次进行备份；

5.2 执行mysqldump数据库备份时操作数据库

当执行脚本加了–single-transaction --quick属性，在运行此脚本时可以正常操作数据库

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
set host=127.0.0.1
set port=3306
set username=root
set password=123456
set dbname=testdb
set backupfile=\\192.168.1.5\MesResource\testDBBack\remoteFile\%dbname%-%Ymd%.sql
set orderPath=D:\mysql\mysql-8.0.29\bin\mysqldump
%orderPath% -h%host% -P%port% -u%username% -p%password% --single-transaction --quick %dbname%>%backupfile%
@echo on

如果没加此属性，就会出现如下情况，执行备份时无法操作数据库

SQL显示一直处于查询状态，所以使用mysqldump备份数据库时如果执行脚本无–single-transaction --quick属性，则不可操作数据库