触发器+脚本备份单表数据

原理：

　　1.备份方法：使用mysql的select ... into outfile将表的数据导出成外部文件进行备份

　　2.备份时机：在表中设置触发器，当有增删改事务提交时就执行备份方法

　　3.关键问题：outfile的对应输出的文件需要是未设置的文件，即每次写入的文件必须是新建的文件

　　4.解决方法：

　　　　方法一：拼接select ... into outfile语句，将时间戳设置为文件名

　　　　方法二：输出文件后，将文件名通过外部命令修改为时间戳

　　5.采用：方法一还未实现，方法二的实现方式如下：

实现方案

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

　　链接：https://pan.baidu.com/s/1MCrf1u_SRWwcZoxM9JDNiw 

　　提取码：kgt0 

 

2.解压后放进：mysql安装路径/lib/plugin/

该组件有两个版本：×64和×86版

xampp lite2016带的mysql版本为5.5.47，使用时发现能用×86版，×64版无法使用

 

3.打开mysql可以执行命令行的地方创建自定义函数,下面创建两个函数一个是返回执行结果,另一个是返回命令行的字符串

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

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

 

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

 

4.sys_exec使用示例

Create TRIGGER trig_test AFTER Insert ON <table1>
FOR EACH ROW
BEGIN
4     DECLARE redata INT;
5     Select sys_exec('/home/user1/test.sh') INTO redata;
END

 以上参考自：https://www.zhaokeli.com/article/8212.html

5.执行脚本test.bat

创建扩展名为".bat"的文件，内容如下

@echo off
set plus=.log
set dttm=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%%plus%
set oldname=E:\database\backups.log
set newname=%dttm%
ren "%oldname%" "%newname%"

 

6.设置触发器 

设置增、删、查、改对应的触发器，此处省去设置触发器的代码，只贴出触发器操作：

BEGIN
    DECLARE redata INT;
    SELECT * FROM captcha INTO OUTFILE "E:\database\backups.log" FIELDS TERMINATED BY ',';
    SELECT sys_exec('E:/test.bat') INTO redata;
END

 

　　E:/test.bat 替换为对应存放脚本的路径

　　E:\database\backups.log 替换为保存表数据的路径，其中文件名backups.log需与test.bat中oldname的值对应

　　captcha 为备份的表名，下同

　　仅供参考--创建触发器：

--更新操作触发器
CREATE TRIGGER `update-change` AFTER UPDATE ON `captcha`
FOR EACH ROW BEGIN
     DECLARE redata INT;
     SELECT * FROM captcha INTO OUTFILE "E:\database\backups.log" FIELDS TERMINATED BY ',';
     SELECT sys_exec('E:/test.bat') INTO redata;
END;

--插入操作触发器
CREATE TRIGGER `update-change` AFTER INSERT ON `captcha`
FOR EACH ROW BEGIN
     DECLARE redata INT;
     SELECT * FROM captcha INTO OUTFILE "E:\database\backups.log" FIELDS TERMINATED BY ',';
     SELECT sys_exec('E:/test.bat') INTO redata;
END;

--删除操作触发器
CREATE TRIGGER `update-change` AFTER DELETE ON `captcha`
FOR EACH ROW BEGIN
     DECLARE redata INT;
     SELECT * FROM captcha INTO OUTFILE "E:\database\backups.log" FIELDS TERMINATED BY ',';
     SELECT sys_exec('E:/test.bat') INTO redata;
END;

 

 7.恢复数据

使用load data infile ... intotable 恢复数据即可，但因为导出的数据没有字段信息，所以在恢复时，应当是按照原表的字段建好表之后，执行下面的sql语句

load data infile "E:\database\xxxxxxx.log" into table captcha fields terminated by ',';