mysql备份恢复中的常见错误

mysql备份恢复中的常见错误2013-04-14从A主机备份到B主机

mysqldump -uroot -p vw>vw.sql

现备份数据库文件，需要恢复到目标机B，B的数据库版本为5.5.23，A机器的mysql版本为5.0.22

mysql>source /root/vw.sql; …………………………………………………… Query OK, 6748 rows affected （0.13 sec）Records: 6748Duplicates: 0Warnings: 0Query OK, 6807 rows affected （0.12 sec）Records: 6807Duplicates: 0Warnings: 0Query OK, 6752 rows affected （0.13 sec）Records: 6752Duplicates: 0Warnings: 0Query OK, 6659 rows affected （0.13 sec）Records: 6659Duplicates: 0Warnings: 0Query OK, 6676 rows affected （0.13 sec）Records: 6676Duplicates: 0Warnings: 0Query OK, 6583 rows affected （0.12 sec）Records: 6583Duplicates: 0Warnings: 0Query OK, 6598 rows affected （0.13 sec）Records: 6598Duplicates: 0Warnings: 0ERROR 1064 （42000）: 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 1mysql>

在B机器上面导入的时候出现以上错误，排除数据库本身问题，后来通过navicat工具备份A的数据，再次导入机器B却成功了。

经过测试发现是字符集问题导致。解决办法如下

在A机器上面备份，使用如下命令，恢复到B机器，测试OK。

mysqldump -uroot --default-character-set=utf8 -p vw>vw.sql

以下附mysql字符集查看以及修改命令,

一、查看 MySQL 数据库服务器和数据库字符集

mysql> show variables like "%char%";+--------------------------+----------------------------+| Variable_name| Value|+--------------------------+----------------------------+| character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+

二、查看 MySQL 数据表（table）的字符集。

mysql> show full columns from s_type; +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+| Field | Type | Collation | Null | Key | Default | Extra| Privileges| Comment |+---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+| tid | mediumint（10） unsigned | NULL| NO | PRI | NULL| auto_increment | select,insert,update,references | | | tname | varchar（125） | utf8_general_ci | NO | MUL | NULL|| select,insert,update,references | | | en-name | varchar（125） | utf8_general_ci | NO | | NULL|| select,insert,update,references | | | type| tinyint（3） | NULL| NO | | NULL|| select,insert,update,references | | +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+