mysql备份中含有自定义函数无法恢复的问题

Question

一直用mysqldump备份数据库，命令如下：mysqldump -uuser -ppsd --opt -R temp > temp.sql
再用mysql命令恢复数据库，命令如下：mysql -uuser -ppsd --opt -R temp < temp.sql
没有出过问题。最近因为业务需要在mysql里增加了几个自定义函数，备份数据库没有问题，但用上面的语句恢复数据库时报语法错。
试着用可视化之类的客户端工具恢复数据库依然失败，后来打开备份文件看到备份sql如下，感觉语法根本不对。部分语法如下：
--
-- Dumping routines for database 'temp'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `get_split_count` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`kmadmin`@`%`*/ /*!50003 FUNCTION `get_split_count`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END */;;
上面的一堆/*!50003导致的报错，不知怎么来的。但我是用mysql自带的工具和标准命令备份的，难道也会出错。
我再试着用客户端工具备份（带自定义函数），则恢复没有问题，看着导出的语法也很正常。看来确实是mysqldump的问题我再试着加了视图、存储过程、触发器，发现都不能恢复成功，有经验的朋友请看看怎么解决这个问题？不是mysqldump的bug吧？？？？
我的mysql版本是5.0.67，window和linux环境情况都试了，结果一样。

Answer 1

1、 两个MYSQL是否是相同版本；
2、MYSQLDUMP -uroot --all-databases --add-drop-database -B -E -R -p >123.TXT
备份所有数据库、SP、UDF等待
3、恢复用
mysql -uroot -p*** database  < aaa.sqL

Answer 2

show variables like 'log_bin_trust_function_creators';看看是否有权限。
还原前先执行下面这个试试。
set log_bin_trust_function_creators=1;

Answer 3

mysqldump -uuser -ppsd --opt -R temp > temp.sql
检查一下  temp.sql 中有没有 create function 语句。

Answer 4

在一楼我贴的内容里有备份sql你内容，create function 有，但被一堆/*!50003 包围着，不知为什么。

Answer 5

/*!50003 包围着 这个是没问题的，在高版本的MYSQL中可以执行，但低版本中则不执行。
确认一下你的MYSQL的版本号。
源数据库和新数据库中都select version() 贴出来看一下。

Answer 6

在一楼也说过了，版本是5.0.67，源库和新库是同一个数据库，我就是在一个库上测试含有自定函数的备份恢复。我的恢复是用 mysql -uuser -ppsd --opt -R temp < temp.sql ，不行
用客户端工具sqlyog也不行但是，用sqlyog备份和恢复自定义函数没有问题，另sqlyog备份的自定义函数库用mysql -u..恢复也没问题。我觉得就是mysqldump的bug，否则真无法解释，唉......另外我翻出了一年前你回答别人的一个和我这个问题很类似的帖子，但似乎和我现在问题又不完全一样，还是无解。原帖如下：http://topic.csdn.net/u/20091126/11/d8b9ea89-9ca8-49e8-99a5-43b967fe35e4.html

Answer 7

你直接在MYSQL命令行工具中先试一下。DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `get_split_count` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`kmadmin`@`%`*/ /*!50003 FUNCTION `get_split_count`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END */;;另外你的那些参数是MYSQLDUMP的，MYSQL不要加这些参数，改成如下试试。
mysql -uuser -ppsd  temp < temp.sql

Answer 8

试了还是不行。
现在我把数据库里所有的对象都清了，就留下一个自定义函数，然后用mysqldump加-R参数到处的完整sql语句如下：-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version 5.0.67-community-nt/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `get_split_value` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`kmadmin`@`%`*/ /*!50003 FUNCTION `get_split_value`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2010-11-08 13:56:24
但是用mysql -uuser -ppsd temp < temp.sql 恢复还是不行，报语法错误，用sqlyog执行也不行，报一堆语法错误，但是sqlyog备份的sql就没有问题，mysql命令行也能恢复
都是同一个数据库，官方的脚本命令就不行，无语啊......

Answer 9

ERROR 1064 (42000) at line 24:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2谢谢你的一直关注

Answer 10

24行就是这一句
/*!50003 CREATE*/ /*!50020 DEFINER=`kmadmin`@`%`*/ /*!50003 FUNCTION `get_split_value`(
一开始执行自定义函数的脚本就报错

Answer 11

奇了怪了，mysqldump 为什么会加一堆/*!50003
用sqlyog备份就没这些，恢复时不论是sqlyog本身还是mysql命令都很成功，
唉........................................
免费的东西不好用啊............

Answer 12

我用这个做了个文本文件，测试没有任何问题。[code=BatchFile]-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version    5.0.67-community-nt/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `get_split_value` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`kmadmin`@`%`*/ /*!50003 FUNCTION `get_split_value`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2010-11-08 13:56:24[/code]

Answer 13

可以上传到 http://www.access911.net/csdn

Answer 14

不好意思，传了两次，因为不知道怎么写注释（不再传了，否则就3次了呵呵）。
文件名为test.sql。就是我在本地用 mysqldump -uroot -ppsd --opt -R test > test.sql
导出的。先清掉test库所有对象，再用 mysql -ukmadmin -pkmadmin@#$ test < test.sql
恢复就会报
ERROR 1064 (42000) at line 48:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2太晚了，先休息吧，谢谢。

Answer 15

测试没有任何问题 (由于我的数据库中不存在`kmadmin`@`%`用户，所以将这个删除了)C:\Users\xxxxx\myData\MySQL\mysql-5.1.52-win32\bin>mysql -uroot csdn < test.sqlC:\Users\xxxxx\myData\MySQL\mysql-5.1.52-win32\bin>mysql -uroot csdn
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.52-community MySQL Community Server (GPL)Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show tables;
+----------------+
| Tables_in_csdn |
+----------------+
| suggest        |
+----------------+
1 row in set (0.00 sec)mysql> show function status;
+------+-----------------+----------+----------------+---------------------+----
-----------------+---------------+---------+----------------------+-------------
---------+--------------------+
| Db   | Name            | Type     | Definer        | Modified            | Cre
ated             | Security_type | Comment | character_set_client | collation_co
nnection | Database Collation |
+------+-----------------+----------+----------------+---------------------+----
-----------------+---------------+---------+----------------------+-------------
---------+--------------------+
| csdn | get_split_count | FUNCTION | root@localhost | 2010-11-09 00:20:53 | 201
0-11-09 00:20:53 | DEFINER       |         | utf8                 | utf8_general
_ci      | latin1_swedish_ci  |
+------+-----------------+----------+----------------+---------------------+----
-----------------+---------------+---------+----------------------+-------------
---------+--------------------+
1 row in set (0.01 sec)mysql>

Answer 16

在你做测试的同时，我卸载了5.0.67版本，从官网上下载了较新的5.1.52版本，测试也通过了。（忘了说了，我都是在windowsXP环境下测试的）
然后我又卸载了5.1.52版本，再装上了5.0.67版本，同样的脚本，错误依旧。
如果您有时间的话可以试试5.0.67版本。如果没错，那我无语了。
如果有错，除了bug，我不知道该怎么解释...这么晚了还在操劳，实在是太感谢你了。我去睡觉去了，不然会被老婆骂死。呵呵