MYSQL备份恢复数据（使用INTO OUTFILE和LOAD DATA INFILE）

备份语句：SELECT * FROM  表名 INTO OUTFILE '文件路径' CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY ' ';

例：SELECT * FROM MT INTO OUTFILE 'MT.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';

注：如果程序操作出现异常需要恢复数据：

恢复语句：LOAD DATA INFILE '文件路径' INTO TABLE 表名 CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';

      例：LOAD DATA INFILE 'MT.csv ' INTO TABLE MT CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';

问题1导出时报错：[Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方法：

        执行show variables like 'secure_file_priv';  VALUE为NULL就是禁止备份，修改my.ini，输入secure_file_priv=

问题2导入时报错：[Err] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 's_playtime' at row 1       set session sql_mode=’’;

 

 还有另外的两种备份的方法，可以参考：https://cloud.tencent.com/developer/article/1343387