文章目录

mysqldump引发的一系列问题问题背景解决过程问题1解决问题2解决 总结

mysqldump引发的一系列问题

问题背景

生产环境数据异常，需要批量执行sql进行修复。修复之前需要把生产数据导入测试环境数据库，先对测试环境数据进行修复验证。

解决过程

第一步：首先征求项目经理意见，允许将生产环境数据导入到测试环境数据库。第二步：生产数据导出，我选择的是通过mysqldump命令：

mysqldump -h10.11.20.98 -P3306 -uweifan -pweifan aipc --skip-lock-tables --skip-add-locks > D:\aipc_prd.dump

第三步：将生产数据导入测试环境数据库，我选择的是通过mysql命令：

mysql -h172.30.1.75 -P3306 -uaipc -pweifan aipc-uat < D:\aipc_prd.dump

执行mysql导入命令，报错：

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

最直观的翻译是说权限的问题，一般是mysql的用户创建后没给权限；但也可能是其他原因！！！

问题1

用编辑器打开dump文件（其实是想定位第18行内容），意外发现导出的dump文件中，全是乱码！

解决

使用mysqldump命令时，增加参数：–default-character-set=utf8：

mysqldump -h10.11.20.98 -P3306 -uweifan -pweifan aipc --skip-lock-tables --skip-add-locks --default-character-set=utf8 > D:\aipc_prd.dump

再次执行数据导出，发现dump文件依旧乱码！！！

究其原因发现，数据库中表字段使用了blob类型，在面对BINARY, VARBINARY, BLOB, BIT类型时，mysqldump只能导出为“乱码”，可以使用参数：–hex-blob 避免

mysqldump -h10.11.20.98 -P3306 -uweifan -pweifan aipc --skip-lock-tables --skip-add-locks --default-character-set=utf8 --hex-blob > D:\aipc_prd.dump

再次执行mysqldump命令，成功导出无乱码dump文件。

问题2

用编辑器打开dump文件，定位到18行显示的是一个这样的语句： SET @@SESSION.SQL_LOG_BIN= 0；

mysql导数据遇到错误提示ERROR 1227,Acess denied

使用用Mysql导入数据库提示 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for

解决

手动将dump文件的第18行内容删除，并将SET @@ 格式内容一并删除，再执行mysql导入命令，确实可以成功导入，但是dump文件一旦过大，不可能采用此手动删除方式去解决问题。究其根本原因：如果数据库用GTID模式，在mysqldump数据时，应该加上参数–set-gtid-purged=OFF，此时导出的sql文件将不会包含SET @@等内容

mysqldump -h10.11.20.98 -P3306 -uweifan -pweifan aipc --skip-lock-tables --skip-add-locks --default-character-set=utf8 --hex-blob --set-gtid-purged=OFF > D:\aipc_prd.dump

mysqldump导出时 --set-gtid-purged=OFF

Mysqldump参数大全

MySQL5.6 新特性之GTID

到此，mysqldump导出可执行的dump文件已经结束。再次执行mysql导入命令：

mysql -h172.30.1.75 -P3306 -uaipc -pweifan aipc-uat --default-character-set=utf8 < D:\aipc_prd.dump

因为导出的时候指定了字符集，导入时也需要指定字符集。

总结

本来以为很简单的mysql数据导出导入，引发了一系列问题，对mysql数据库加深了认识。技术的领域博大精深，任何小问题都会引出一堆未知领域的问题，比如：Mysql GTID。