我是使用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
因为存储过程不是存储在你的数据库中而是在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.
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 ;;
/*!50003 select 1 */;mysql> /*!50003 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)mysql>
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。奇怪了
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)
这个就是错误信息 语法错误 我就奇怪了？
/*!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 ;
是我恢复的方式不对。我刚才一直都是在用sqlyog这个客户端工具恢复，如果用工具恢复的话，那就会出现这样的问题，如果使用命令行source XXXX进行恢复的话，就没有问题了.可能是由于sqlyog不支持这样的格式吧。
很多工具把一些字符吃掉了。
所以尽可能的用MYSQL的官方工具。