56,687
社区成员
发帖
与我相关
我的任务
分享关于mysql for windows的备份问题
我是使用mysqldump进行备份，但是备份出来的只有表结构和数据，却没有备份出视图，存储和触发器等其他的内容，想请教如何能够把数据库中所有内容全部备份出来，谢谢。我的语句：
C:\Documents and Settings\biao.yang>mysqldump --opt test -u root --password=qwe123 > d:\ab.sql
C:\Documents and Settings\biao.yang>mysqldump --opt test -u root --password=qwe123 > d:\ab.sql
...全文
请发表友善的回复…
发表回复
ybgba 2009-11-27
- 打赏
- 举报
好的 谢谢ACMAIN_CHM，我明白了 。
ybgba 2009-11-26
- 打赏
- 举报
对不起 搞错了 是备份出来了 但是怎么全部都是以备注的方式显示的呢 不能还原出来啊
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
show procedure status 看一下，这些存储过程都是你这个数据库的吗？
ybgba 2009-11-26
- 打赏
- 举报
谢谢楼上的兄台，但是很奇怪，我加了这个选项后，存储备份出来的不完全，不知道怎么回事，源数据库中有存储过程90条，但是现在备份出来的只有2条，这样是怎么回事.谢谢。
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
需要加上 -R
mysqldump --opt test -u root --password=qwe123 -R > d:\ab.sql
因为存储过程不是存储在你的数据库中而是在mysql数据库文件夹中。
--routines, -R
Dump stored routines (procedures and functions) from the dumped databases. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.
If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.
This option was added in MySQL 5.1.2. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.1.8. This means that before 5.1.8, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.
mysqldump --opt test -u root --password=qwe123 -R > d:\ab.sql
因为存储过程不是存储在你的数据库中而是在mysql数据库文件夹中。
--routines, -R
Dump stored routines (procedures and functions) from the dumped databases. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.
If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.
This option was added in MySQL 5.1.2. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.1.8. This means that before 5.1.8, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
[Quote]可能是由于sqlyog不支持这样的格式吧。[/Quote]
很多工具把一些字符吃掉了。
所以尽可能的用MYSQL的官方工具。
很多工具把一些字符吃掉了。
所以尽可能的用MYSQL的官方工具。
ybgba 2009-11-26
- 打赏
- 举报
谢谢ACMAIN_CHM，我想我找到原因了。
是我恢复的方式不对。我刚才一直都是在用sqlyog这个客户端工具恢复，如果用工具恢复的话，那就会出现这样的问题，如果使用命令行source XXXX进行恢复的话，就没有问题了.可能是由于sqlyog不支持这样的格式吧。
是我恢复的方式不对。我刚才一直都是在用sqlyog这个客户端工具恢复，如果用工具恢复的话，那就会出现这样的问题，如果使用命令行source XXXX进行恢复的话，就没有问题了.可能是由于sqlyog不支持这样的格式吧。
ybgba 2009-11-26
- 打赏
- 举报
我是从linux平台上的数据库备份下来的，然后恢复到windows平台的数据库上，恢复过程是完全正常的，而且表、数据、存储等等都恢复出来了。然后我写脚本是从windows平台的数据库备份，然后使用windows平台的备份文件进行恢复，就出现这个问题。这和不同平台有关么。
ybgba 2009-11-26
- 打赏
- 举报
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pdm_update_player_online_by_time` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`dbo_endwar`@`%`*/ /*!50003 PROCEDURE `pdm_update_player_online_by_time`()
MODIFIES SQL DATA
COMMENT 'Procedure for updating dm_player_online_by_time table.'
BEGIN
DECLARE v_timeslot int unsigned default TIME_TO_SEC(CURTIME());
DECLARE v_date date default CURDATE();
IF (v_timeslot % 3600) <= 180 THEN
SET v_timeslot = ROUND(v_timeslot/3600)*3600;
UPDATE dm_player_online_by_time,
(SELECT c_faction.c_faction_id,
COUNT(*) as players_by_faction
FROM presence, c_faction left join c_player on c_faction.c_faction_id = c_player.c_faction_id
WHERE presence.userid = c_player.c_player_id
AND presence.isonline is true
GROUP BY c_faction.c_faction_id
) as online_players_by_faction
SET dm_online_players = players_by_faction,
dm_is_updated = true
WHERE dm_timeslot = v_timeslot
AND dm_faction_id = c_faction_id
AND dm_is_updated is false;
END IF;
END */;;
DELIMITER ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pdm_update_player_online_by_time` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`dbo_endwar`@`%`*/ /*!50003 PROCEDURE `pdm_update_player_online_by_time`()
MODIFIES SQL DATA
COMMENT 'Procedure for updating dm_player_online_by_time table.'
BEGIN
DECLARE v_timeslot int unsigned default TIME_TO_SEC(CURTIME());
DECLARE v_date date default CURDATE();
IF (v_timeslot % 3600) <= 180 THEN
SET v_timeslot = ROUND(v_timeslot/3600)*3600;
UPDATE dm_player_online_by_time,
(SELECT c_faction.c_faction_id,
COUNT(*) as players_by_faction
FROM presence, c_faction left join c_player on c_faction.c_faction_id = c_player.c_faction_id
WHERE presence.userid = c_player.c_player_id
AND presence.isonline is true
GROUP BY c_faction.c_faction_id
) as online_players_by_faction
SET dm_online_players = players_by_faction,
dm_is_updated = true
WHERE dm_timeslot = v_timeslot
AND dm_faction_id = c_faction_id
AND dm_is_updated is false;
END IF;
END */;;
DELIMITER ;
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
[Quote]有啊 一前一后 都有的 但是还是报错[/Quote]
贴出来你这一段。
贴出来你这一段。
ybgba 2009-11-26
- 打赏
- 举报
有啊 一前一后 都有的 但是还是报错
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
前面的 DELIMITER ;; 你有没有一起贴进去执行啊？
ybgba 2009-11-26
- 打赏
- 举报
Error Code : 1064
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 'ELSEIF p_total_cr_point>=2500 AND p_total_cr_point<2700 THEN
return 11' at line 1
(0 ms taken)
Error Code : 1064
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 'END IF' at line 1
(0 ms taken)
Error Code : 1064
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 'return 12' at line 1
(0 ms taken)
Error Code : 1064
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 'END */' at line 1
(0 ms taken)
这个就是错误信息 语法错误 我就奇怪了？
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 'ELSEIF p_total_cr_point>=2500 AND p_total_cr_point<2700 THEN
return 11' at line 1
(0 ms taken)
Error Code : 1064
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 'END IF' at line 1
(0 ms taken)
Error Code : 1064
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 'return 12' at line 1
(0 ms taken)
Error Code : 1064
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 'END */' at line 1
(0 ms taken)
这个就是错误信息 语法错误 我就奇怪了？
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
如果这个可以执行，那脚本中的应该也可以执行。
你直接把这段到MYSQL中执行，看错误信息是什么？
你直接把这段到MYSQL中执行，看错误信息是什么？
ybgba 2009-11-26
- 打赏
- 举报
还有就是我只导出PROCEDURE的脚本，然后再还原也是不能还原出来的。有什么方法去掉前面的那些东西么？
ybgba 2009-11-26
- 打赏
- 举报
你写的那个是可以执行的。
ybgba 2009-11-26
- 打赏
- 举报
现在我的脚本这样写的
C:\Documents and Settings\biao.yang>mysqldump --opt -h 127.0.0.1 --routines test -u root --password=qwe123 > d:\t1.sql
备份出来的sql文件，就是恢复不出来PROCEDURE和FUNCTION。奇怪了
C:\Documents and Settings\biao.yang>mysqldump --opt -h 127.0.0.1 --routines test -u root --password=qwe123 > d:\t1.sql
备份出来的sql文件，就是恢复不出来PROCEDURE和FUNCTION。奇怪了
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
测试一下这个
/*!50003 select 1 */;
/*!50003 select 1 */;
mysql> /*!50003 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql>ybgba 2009-11-26
- 打赏
- 举报
/*!50003 这个 但是并没有执行 比如下面的
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 DROP PROCEDURE IF EXISTS `resolve_winner_for_primary_frontline` */;
--
-- WARNING: old server version. The following dump may be incomplete.
--
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 DROP PROCEDURE IF EXISTS `resolve_winner_for_primary_frontline` */;
--
-- WARNING: old server version. The following dump may be incomplete.
--
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
ACMAIN_CHM 2009-11-26
- 打赏
- 举报
/*! MySQL-specific code */
In this case, MySQL Server parses and executes the code within the comment as it would any other SQL statement, but other SQL servers will ignore the extensions. For example, MySQL Server recognizes the STRAIGHT_JOIN keyword in the following statement, but other servers will not:
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
If you add a version number after the “!” character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The TEMPORARY keyword in the following comment is executed only by servers from MySQL 3.23.02 or higher:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
The comment syntax just described applies to how the mysqld server parses SQL statements. The mysql client program also performs some parsing of statements before sending them to the server. (It does this to determine statement boundaries within a multiple-statement input line.)
In this case, MySQL Server parses and executes the code within the comment as it would any other SQL statement, but other SQL servers will ignore the extensions. For example, MySQL Server recognizes the STRAIGHT_JOIN keyword in the following statement, but other servers will not:
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
If you add a version number after the “!” character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The TEMPORARY keyword in the following comment is executed only by servers from MySQL 3.23.02 or higher:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
The comment syntax just described applies to how the mysqld server parses SQL statements. The mysql client program also performs some parsing of statements before sending them to the server. (It does this to determine statement boundaries within a multiple-statement input line.)
加载更多回复（2）
