关于MySQL数据导入导出的困惑

显示全部楼层 · 2011-6-11 13:26:00

根据公司的业务需求，需要升级数据库版本，从mysql5.1.47升级到5.1.57，原本以为版本差别不大，直接使用MYSQLDUMP导出再导入就OK了，没想到出现了几个问题，虽然最终都得到了解决，但是还有一些困惑的地方，希望各位高手指点一二，不胜感激。同时也将遇到的问题和解决办法罗列一下，可能对新人也会有所帮助，呵呵。
一.数据库导出：
整库导出：
mysqldump --opt --events --routines --triggers --log-error=xxx_20110609.log -h 192.168.xxx.xxx -u root -p123456 --databases xxx > xxx_20110609.dmp
只导结构:
mysqldump --opt --events --routines --triggers --no-data --log-error=xxx_20110609.log -h 192.168.xxx.xxx -u root -p123456 --databases xxx > xxx_20110609.dmp
只导数据:
mysqldump --no-create-info --log-error=xxx_20110609.log -h 192.168.xxx.xxx -u root -p123456 --databases xxx > xxx_20110609.dmp
由于导出文件的个别地方有些小问题，直接使用导出文件导入时会报一堆错误，存储过程、函数和例程会导入失败，需要先进行个别地方的修改后才能批量导入。如果数据量非常大的时候，直接修改整库导出文件将会非常慢甚至成为不可能完成的任务，这时候就需要分别导出结构和数据。
二.数据库导入：
mysql -u root -pxxxxxx --socket=/tmp/mysql.sock < xxx_20110609.dmp
这部分比较简单，直接利用导出文件导入即可。但是如果数据库非常大的情况下，个人建议还是先导结构，无误后再导数据比较好，否则导入中途出现意外就比较麻烦，一方面可能出现的多次返工会导致无谓的时间消耗，在故障恢复时大大影响应用系统的恢复效率。另一方面在新数据库上也会产生大量无效的二进制日志文件，占用大量的磁盘空间。
三.出现问题：
数据对象的管理机制：
原有的MYSQL数据对象管理比较混乱，存储过程、函数和例程的所有者有好几个，如果个别用户在新数据库上不存在的话，相应的数据对象会创建失败，因此一个完善的数据库应用系统应当具有完善统一的管理机制，否则会导致系统管理和应用上的不便。
DELIMITER分隔符：
MYSQL默认的命令行分隔符是";"，批量提交指令时需要使用DELIMITER进行重定义，提交完毕后再使用DELIMITER恢复。但是使用MYSQLDUMP导出的SQL文件中存储过程、函数以及例程部分全都重定义成了";;"，如果不进行相应的编辑和替换导入操作会报错，导致存储过程、函数以及例程导入失败，如果相应代码正好在SQL导出文件的中间部分的话也会导致一些数据表导入失败。我个人猜测导入操作过程中将分隔符";;"中的第一个";"当成了命令行结束符处理，这才报了errcode:1064的错误。
四. 解决办法：

上面提到的两个问题的解决办法都是在导入之前预先编辑修改下导出的SQL文件，先将存储过程、函数和例程的所有者根据业务规则进行统一整理，然后再将命令行的结束符统一替换为"$$"，无误后再进行导入操作。
特别需要注意的是进行字符替换时一定要基于只包含数据结构的导出文件，因为应用数据中极有可能会存在相应的分隔符，特别是在linux操作系统下进行字符转码后。
Linux下批量替换字符串的脚本如下所示：
sed -i 's/;;/$$/g' xxx_structure_20110609.dmp && sed -i 's/`user1`/`user3`/g' && sed -i 's/`user2`/`user3`/g'
&&表示SHELL命令顺序执行，这样可以节省盯着屏幕等待的时间，但是需要注意的是前一个执行失败的话后一个将不再执行。
五.一点困惑：
利用上面的解决办法批量替换字符串后，再次进行数据导入就可以成功导入数据，数据核查也没有问题。当导出的数据库存在多个例程时例程虽然也能成功导入，但会报告下面错误:
ERROR at line 2144: DELIMITER must be followed by a 'delimiter' character or string
ERROR at line 2162: DELIMITER must be followed by a 'delimiter' character or string
ERROR at line 2180: DELIMITER must be followed by a 'delimiter' character or string
分析SQL数据导出文件，个人猜测可能是由于多次使用了同样的DELIMITER指令进行命令行结束符的重定义所致，具体原因不是很清楚，也没有找到办法如何避免这个错误。

千问 · 2011-6-11 13:26:00

upupup