mysql之数据备份还原

场景：

①由于权限控制，远程数据库在外网不能访问；

②远程数据库连接和查询比较慢，影响工作效率；

③即将发布生产时，验证数据库更新脚本是否无遗漏（如字段是否完整）；

分析：

对于①、②，可以将远程数据库的表结构和已有数据COPY到本地的mysql服务器，并将配置的信息指向本地的mysql连接即可。

对于③还需要运行数据库更新脚本，并测试一遍新增功能，检查是否无异常；

操作（基于navicat for mysql）

方法一【推荐】

1、首先打开数据库连接；

2、右键数据库，选择【数据传输】，如下；

3、设置传输属性；

注意：

（1）常规-要根据需要勾选对应的数据库对象；

（2）常规-如果目标是选择【文件】，则注意选择编码，我这里是UTF-8；

（3）高级-根据需要选择只导出表结构、只导出数据、导出表和数据；

（4）高级-【强烈建议】勾选使用完整插入语句；（因为如果你导出了数据，后面改了表结构等，需要只还原数据的话，能使用完整的插入语句【insert into TableA （Row1,Row2，……） values（'1','2',……）】，如果使用扩展插入语句的话【insert into TableA  values（'1','2',……）】则插不进去，毕竟可能调整了字段的顺序，增加/删除了字段，导致对不上）；

（5）点击“开始”按钮开始导出数据；

4、还原/导入刚刚传输/导出的表及数据/sql

右键数据库，选择【运行sql文件】

优点：可根据实际需要直接传输结构和数据 或者 导出对应的sql文件，很灵活，实用性很强；

缺点：操作繁琐；

方法二

【转储SQL文件】是直接导出整个数据库的sql语句；

（1）如果要导出某个表的sql语句，则可在表名右键使用此功能；

（2）此功能导出的数据插入数据，是使用【扩展插入语句】；

优点：一键导出；

缺点：insert语句不是完整的插入语句，而是扩展性插入语句；

（3）导入sql

参照方法一的操作；

方法三

使用navicat for mysql自带的备份、还原功能

根据备份向导操作即可；

优点：备份速度很快；

缺点：备份包只能在navicat中使用；

注意：

（1）导入sql时，一定要使用有足够权限的用户；

否则会报错：

[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[Err] CREATE TABLE ……

（2）导出的DDL语句格式不是通用语句（通常是创建视图，函数/储存过程），如：

 CREATE ALGORITHM=UNDEFINED DEFINER=`User1`@`%` SQL SECURITY DEFINER VIEW `View1` AS select ……;

执行时会报错：

[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[Err] CREATE ALGORITHM=UNDEFINED DEFINER=`User1`@`%` SQL SECURITY DEFINER VIEW `View1` AS select ……;

解决：将红色部分去掉即可；

（3）如果表之间存在外键外键，那么在还原整个数据库时，可能会报错，如下；

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`DtBase`.`mer_relation_log`, CONSTRAINT `mer_relation_log_ibfk_1` FOREIGN KEY (`MER_ID`) REFERENCES `mer_info` (`ID`))

[Err] INSERT INTO `mer_relation_log` VALUES ('20', '1', '2016-05-29 16:47:22', '976', '44', '42', null, '69');

解决：将源表数据先行还原即可；

（4）导入时报 Table 'DbTest.SYS_CONF' doesn't exist；

可能原因1是该表的确不存在；

可能原因2是mysql配置成字母大小写敏感；（默认情况下，MySQL在windows下是不区分大小写的，在linux服务器中是区分的；解决：在linux下更改MySQL的设置使其也不区分大小写即可；

（5）网络要良好，不能中断，否则会报错如下；

[Err] 2003 - Can't connect to MySQL server on '192.168.1.7' (10060)

[Err] INSERT INTO `ba_info` VALUES ('61445', '0', '2016-01-27 12:00:00', '403');

后果就是之前的数据插进去了，后面的没有，需要单独插入数据；

最好连接网线，而不是无线；