mysql備份恢複中的常見錯誤

從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: 6748  Duplicates: 0  Warnings: 0    Query OK, 6807 rows affected (0.12 sec)    Records: 6807  Duplicates: 0  Warnings: 0    Query OK, 6752 rows affected (0.13 sec)    Records: 6752  Duplicates: 0  Warnings: 0    Query OK, 6659 rows affected (0.13 sec)    Records: 6659  Duplicates: 0  Warnings: 0    Query OK, 6676 rows affected (0.13 sec)    Records: 6676  Duplicates: 0  Warnings: 0    Query OK, 6583 rows affected (0.12 sec)    Records: 6583  Duplicates: 0  Warnings: 0    Query OK, 6598 rows affected (0.13 sec)    Records: 6598  Duplicates: 0  Warnings: 0    ERROR 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 1    mysql>

在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 |         |     +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+