Analysis of mysql Character Set errors caused by database recovery

Analysis of mysql Character Set errors caused by database recovery

An error will be prompted when the mysql character set encoding is incorrect. This is the same as inserting data. If the stored data is different from the mysql encoding, it will certainly cause import garbled characters or insertion data loss, let's take a look at an example.

<Script> ec (2); </script>

Database recovery error: Due to Character Set problems, the default encoding of the original database is latin1, And the encoding of the newly backed up database is utf8, leading to recovery errors.

[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/11x-B-2014-06-18.sql ERROR 1064 (42000) at line 292: 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 ''[caption id=\"attachment_271\" align=\"aligncenter\" width=\"300\"]<a href=\"ht' at line 1

Solution (not tested ):

[root@Test ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' --default-character-set=latin1 t4x < /tmp/11x-B-2014-06-18.sql MySQL-- MySQL dump 10.13 Distrib 5.5.37, for Linux (x86_64)---- Host: localhost  Database: t4x-- -------------------------------------------------------- Server version    5.5.37-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE=' 00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Current Database: `t4x`--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t4x` /*!40100 DEFAULT CHARACTER SET utf8 */;---- Table structure for table `wp_baidusubmit_sitemap`--DROP TABLE IF EXISTS `wp_baidusubmit_sitemap`;/*!40101 SET @saved_cs_client   = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `wp_baidusubmit_sitemap` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(255) NOT NULL DEFAULT '', `type` tinyint(4) NOT NULL, `create_time` int(10) NOT NULL DEFAULT '0', `start` int(11) DEFAULT '0', `end` int(11) DEFAULT '0', `item_count` int(10) unsigned DEFAULT '0', `file_size` int(10) unsigned DEFAULT '0', `lost_time` int(10) unsigned DEFAULT '0', PRIMARY KEY (`sid`), KEY `start` (`start`), KEY `end` (`end`)) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;01[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/t4x-B-2014-06-17.sql ERROR 1064 (42000) at line 295: 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 ''i' at line 1

MySQL

-- MySQL dump 10.11---- Host: localhost  Database: t4x-- -------------------------------------------------------- Server version    5.0.95-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE=' 00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;-- -- Current Database: `t4x`--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t4x` /*!40100 DEFAULT CHARACTER SET latin1 */;USE `t4x`;---- Table structure for table `wp_baidusubmit_sitemap`--DROP TABLE IF EXISTS `wp_baidusubmit_sitemap`;/*!40101 SET @saved_cs_client   = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `wp_baidusubmit_sitemap` ( `sid` int(11) NOT NULL auto_increment, `url` varchar(255) NOT NULL default '', `type` tinyint(4) NOT NULL, `create_time` int(10) NOT NULL default '0', `start` int(11) default '0', `end` int(11) default '0', `item_count` int(10) unsigned default '0', `file_size` int(10) unsigned default '0', `lost_time` int(10) unsigned default '0', PRIMARY KEY (`sid`), KEY `start` (`start`), KEY `end` (`end`)) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;

Character Set:

MySQL

Mysql> show variables like '% character_set % '; -------------------------- ---------------------------- | Variable_name | Value | -------------------------- ---------------------------- | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | bytes | utf8 | latin1 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| ------------------------ -------------------------- mysql> set names gbk; mysql> show variables like '% character_set % '; -------------------------- ---------------------------- | Variable_name | Value | -------------------------- ---------------------------- | character_set_client | gbk | character_set_connection | gbk | character_set_database | utf8 | bytes | binary | bytes | gbk | latin1 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| -------------------------- mysql> system cat/etc/my. cnf | grep default # default-character-set = gbkmysql> show variables like '% character_set %' under the client set character set '; -------------------------- ---------------------------- | Variable_name | Value | -------------------------- ---------------------------- | character_set_client | gbk | character_set_connection | gbk | character_set_database | latin1 | bytes | binary | bytes | gbk | latin1 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| -------------------------- mysql> system cat/etc/my. cnf | grep character-set-server # character-set-server = cp1250 mysql> show variables like '% character_set %' under the character set mysqld on the client '; -------------------------- -------------------------------------------- | Variable_name | Value | -------------------------- -------------------------------------------- | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | cp1250 | bytes | binary | bytes | utf8 | average | cp1250 | character_set_system | utf8 | character_sets_dir |/byrd/service/mysql/5.6.26/share/charsets/| ---------------------- routing 8 rows in set (0.00 sec)

Other settings:

Modify the character set of a Database

  mysql>use mydb  mysql>alter database mydb character set utf-8;

Creates a database and specifies the character set of the database.

  mysql>create database mydb character set utf-8;

Modify the configuration file:

Modify/var/lib/mysql/mydb/db. opt

default-character-set=latin1default-collation=latin1_swedish_ci

Is

default-character-set=utf8default-collation=utf8_general_ci

Restart MySQL:

[Root @ bogon ~] #/Etc/rc. d/init. d/mysql restart

Use the MySQL command line to modify:

mysql> set character_set_client=utf8;Query OK, 0 rows affected (0.00 sec)mysql> set character_set_connection=utf8;Query OK, 0 rows affected (0.00 sec)mysql> set character_set_database=utf8;Query OK, 0 rows affected (0.00 sec)mysql> set character_set_results=utf8;Query OK, 0 rows affected (0.00 sec)mysql> set character_set_server=utf8;Query OK, 0 rows affected (0.00 sec)mysql> set character_set_system=utf8;Query OK, 0 rows affected (0.01 sec)mysql> set collation_connection=utf8;Query OK, 0 rows affected (0.01 sec)mysql> set collation_database=utf8;Query OK, 0 rows affected (0.01 sec)mysql> set collation_server=utf8;Query OK, 0 rows affected (0.01 sec)

View:

mysql> show variables like 'character_set_%';-------------------------- ---------------------------- | Variable_name       | Value            |-------------------------- ---------------------------- | character_set_client   | utf8            || character_set_connection | utf8            || character_set_database  | utf8            || character_set_filesystem | binary           || character_set_results   | utf8            || character_set_server   | utf8            || character_set_system   | utf8            || character_sets_dir    | /usr/share/mysql/charsets/ |-------------------------- ---------------------------- 8 rows in set (0.03 sec)mysql> show variables like 'collation_%';---------------------- ----------------- | Variable_name     | Value      |---------------------- ----------------- | collation_connection | utf8_general_ci || collation_database  | utf8_general_ci || collation_server   | utf8_general_ci |---------------------- ----------------- 3 rows in set (0.04 sec)

Summary

The above is all about the problem of restoring database errors caused by mysql character set. I hope it will be helpful to you. If you have any questions, you can leave a message at any time. The editor will reply to you in a timely manner. Thank you for your support!