首页 > 代码库 > mysql之数据备份还原

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)网络要良好,不能中断,否则会报错如下;

[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‘);

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

最好连接网线,而不是无线;



本文出自 “ydhome” 博客,请务必保留此出处http://ydhome.blog.51cto.com/8948432/1876167

mysql之数据备份还原