Brief analysis of the MySQL character set causing database recovery errors

Brief analysis of the MySQL character set causing database recovery errors

Importing data with incorrect MySQL character set encoding will prompt an error. This is the same as inserting data. If the saved data is different from the MySQL encoding, there will definitely be problems with importing garbled characters or inserting data being lost. Let's take a look at an example.

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

Database recovery error: Due to character set issues, the original database default encoding is latin1, and the encoding of the newly backed up database is utf8, which leads 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

Repair method (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 */;
0
1
[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 related:

MySQL

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 | 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 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql>system cat /etc/my.cnf | grep default #Client sets the character set client below default-character-set=gbk
mysql>show variables like '%character_set%';
--------------------------------------------- 
| Variable_name | Value |
--------------------------------------------- 
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql> system cat /etc/my.cnf|grep character-set-server #Client sets the character set mysqld below character-set-server = cp1250 
mysql> show variables like '%character_set%';
-------------------------- -------------------------------------------- 
| Variable_name | Value |
-------------------------- -------------------------------------------- 
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | cp1250 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | cp1250 |
| character_set_system | utf8 |
| character_sets_dir | /byrd/service/mysql/5.6.26/share/charsets/ |
-------------------------- -------------------------------------------- 
8 rows in set (0.00 sec)

Some other setting methods:

Modify the character set of the database

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

Create a database and specify the character set of the database

  mysql>create database mydb character set utf-8;

Modify through the configuration file:

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

default-character-set=latin1
default-collation=latin1_swedish_ci

for

default-character-set=utf8
default-collation=utf8_general_ci

Restart MySQL:

[root@bogon ~]# /etc/rc.d/init.d/mysql restart

Modify via the MySQL command line:

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)

Check:

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)

Summarize

The above is all the content of this article about briefly analyzing the problem of MySQL character set causing database recovery errors. I hope it will be helpful to everyone. If you have any questions, please leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!

You may also be interested in:
  • How to modify the database encoding (database character set) and the character encoding of the table in MySQL
  • Sharing of MySQL character set and database engine modification methods
  • When DW is linked to the MySQL database, how to solve the problem of garbled characters in Chinese when creating a character set
  • How to unify the character set on an existing mysql database

<<:  Vue encapsulates a TodoList example and implements the application of browser local cache

>>:  Linux system to view CPU, machine model, memory and other information

Recommend

Sample code for cool breathing effect using CSS3+JavaScript

A simple cool effect achieved with CSS3 animation...

Tutorial on installing and configuring MySql5.7 in Alibaba Cloud ECS centos6.8

The default MySQL version under the Alibaba Cloud...

How to recover accidentally deleted messages files in Linux

If there are files that are being used by a proce...

Detailed explanation of map overlay in openlayers6

1. Overlay Overview Overlay means covering, as th...

Execute the shell or program inside the Docker container on the host

In order to avoid repeatedly entering the Docker ...

Docker: Modifying the DOCKER_OPTS parameter in /etc/default/docker does not take effect

By default, /etc/default/docker configuration wil...

Ubuntu 18.0.4 installs mysql and solves ERROR 1698 (28000): Access denied for user ''root''@''localhost''

Preface I recently learned Linux, and then change...

JS ES new features: Introduction to extension operators

1. Spread Operator The spread operator is three d...

Detailed explanation of the usage of grep command in Linux

1. Official Introduction grep is a commonly used ...

Implementation of Docker batch container orchestration

Introduction Dockerfile build run is a manual ope...

Ideas and codes for implementing iframe to intercept part of the website content

Copy code The code is as follows: <div style=&...

Optimized implementation of count() for large MySQL tables

The following is my judgment based on the data st...

Implementation of Docker private warehouse registry deployment

As more and more Docker images are used, there ne...

Detailed explanation of keywords and reserved words in MySQL 5.7

Preface The keywords of MySQL and Oracle are not ...

Vue implements horizontal scrolling of marquee style text

This article shares the specific code for Vue to ...