MySQL使用触发器进行备份

触发器+脚本备份
实现方案：

1.配置mysql调用外部脚本的插件mysqludf

链接：https://pan.baidu.com/s/1MCrf1u_SRWwcZoxM9JDNiw
提取码：kgt0

解压

2.解压后放进：

mysql安装路径/lib/plugin/

3.在mysql执行命令创建自定义函数，下面创建两个函数的返回执行结果，另一个是返回命令行的字符串

1 DROP FUNCTION IF EXISTS sys_exec;
2 DROP FUNCTION IF EXISTS sys_eval;
3 CREATE FUNCTION sys_exec RETURNS integer SONAME 'lib_mysqludf_sys_x64.dll';
4 CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys_x64.dll';
5 SELECT sys_eval("ipconfig/all");

执行结果显示为lib_mysqludf_sys_x64.dll要替换成mysql安装路径/lib/plugin/路径中的文件名

即为成功，且可在名为mysql的数据库->func表中看到

4、使用sys_exec备份

创建备份bat文件

@echo off
set BACKUP_DIR=E:\database
set MYSQL_USER=roXXX   // 自己的mysql用户名
set MYSQL_PASS=1XXXX   // 自己的mysql密码
set MYSQL_HOST=localhost
set DATABASE_NAME=XXXXXX // 自己的数据库表名
set DATE=%date:~0,4%-%date:~5,2%-%date:~8,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%
set DATE=%DATE: =0%// 跳转自己mysql安装位置
pushd C:\Program Files\MySQL\MySQL Server 8.0\bin  //存储
REM 创建备份文件mysqldump -u %MYSQL_USER% -p%MYSQL_PASS% -h %MYSQL_HOST% %DATABASE_NAME% > %BACKUP_DIR%\mysql_backup_%DATABASE_NAME%_%DATE%.sqlREM 压缩备份文件（可选）
REM 你也可以选择使用 zip 或其他压缩工具进行备份压缩
REM 例如使用 7zip
REM "C:\Program Files\7-Zip\7z.exe" a -tzip %BACKUP_DIR%\mysql_backup_%DATE%.zip %BACKUP_DIR%\mysql_backup_%DATE%.sqlREM 删除超过30天的备份文件（可选）
forfiles /p "%BACKUP_DIR%" /m *.sql /d -30 /c "cmd /c del @path"[mysqldump]user=user_namepassword=password

创建触发器命令

-- 每次触发器进行备份
新增
CREATE TRIGGER `insert-change` AFTER INSERT ON `表名`
FOR EACH ROW 
BEGINDECLARE redata INT;SELECT sys_exec('E:/database/XXXXX备份文件.bat') INTO redata;
END;修改
CREATE TRIGGER `update-change` AFTER UPDATE ON `表名`
FOR EACH ROW 
BEGINDECLARE redata INT;SELECT sys_exec('E:/database/XXXXX.bat') INTO redata;
END;删除
CREATE TRIGGER `delete-change` AFTER DELETE ON `表名`
FOR EACH ROW 
BEGINDECLARE redata INT;SELECT sys_exec('E:/database/XXXXX.bat') INTO redata;
END;

当表被修改时，会进行备份

5.可能的报错信息

解决办法：把my.ini 中的

secure-file-priv=“”

1.secure_file_priv = NULL ，限制文件的读取和写入。2.secure_file_priv = 文件路径 ，限制文件的读取和写入只能在设定的文件夹中。3.secure_file_priv= “”, 对文件和读取和写入没有任何限制。