Common errors in MySQL backup recovery

Backup from Host A to B host

Mysqldump-uroot-p Vw>vw.sql

Now back up the database files, you need to restore to the target computer b,b version of the 5.5.23,a machine MySQL version for 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 affect Ed (0.12 sec)    
records:6807  duplicates:0  warnings:0    
Query OK, 6752 rows affected (0.13 sec)    
Recor ds: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    
Query OK, 6598 rows affected (0.13 sec)    
records:6598  duplicates:0  warnings:0    
ERROR 1064 (42000): Yo u 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> ;

The above error occurred when the B machine was imported, and the database itself was excluded, then the data of a was backed up by the Navicat tool, and the machine B was successfully imported again.

The test found that it was a character set problem. The solution is as follows

Back up on a machine, use the following command, restore to B machine, test OK.

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

The following is attached to the MySQL character set view and modify command,

First, view MySQL database server and database character set

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

Second, view the MySQL Datasheet (table) of the character set.

Mysql> Show full columns from S_type; +---------+------------------------+-----------------+------+-----+---------+----------------+----------------- ----------------+---------+    
| Field | Type | Collation | Null | Key | Default | Extra | privileges |    
Comment | +---------+------------------------+-----------------+------+-----+---------+----------------+----------------- ----------------+---------+    
| Tid | Mediumint (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 |     
| +---------+------------------------+-----------------+------+-----+---------+----------------+----------------- ----------------+---------+