数据库5.6升级到5.7后乱码

数据库升级之后乱码问题

# 部署集群拓扑图:
porxysql <----> proxysql 版本:1.4.8
                |
                |
         master 版本:5.7.21
               |
------------
|                      |
slave            slave


# 通过mysqldump进行升级:

# 5.7升级后:

# 问题一:
直接连接mysql服务select数据出现乱码,而连接proysql没有出现乱码?
问题描述如下:
## 连接5.7master上: 出现乱码
root@localhost:mysql5501.sock [(none)]>select id,code,name,source_id from xxx.xxx limit 2;
+-------+------+----------------------+-------------------------------------------+
| id | code | name | source_id |
+-------+------+----------------------+-------------------------------------------+
| 00001 | 01 | èº«ä»½è¯ | 慢病-è¯ä»¶ç±»åž‹ |
| 00001 | 02 | å†›å®˜è¯ | 慢病-è¯ä»¶ç±»åž‹ |
+-------+------+----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

## 连接proxysql: 没有出现乱码
mysql> select id,code,name,source_id from xxx.xxx limit 2;
+-------+------+-----------+---------------------+
| id | code | name | source_id |
+-------+------+-----------+---------------------+
| 00001 | 01 | 身份证 | 慢病-证件类型 |
| 00001 | 02 | 军官证 | 慢病-证件类型 |
+-------+------+-----------+---------------------+
2 rows in set (0.01 sec)


## 5.6: 没有出现乱码
root@pudong-mysql 17:04: [(none)]> select id,code,name,source_id from healthcloud_chronic.cdc_dic_personinfo limit 2;
+-------+------+-----------+---------------------+
| id | code | name | source_id |
+-------+------+-----------+---------------------+
| 00001 | 01 | 证      |    件类型 |
| 00001 | 02 | 证     |     件类型 |
+-------+------+-----------+---------------------+
2 rows in set (0.00 sec)


# 5.7数据库信息:
root@localhost:mysql5501.sock [(none)]>show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| 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/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)


root@localhost:mysql5501.sock [(none)]>show variables like '%coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)


root@localhost:mysql5501.sock [(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id: 10982
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql5501.sock
Uptime: 5 days 20 hours 46 min 50 sec


# 5.6数据库信息
root@xxx-mysql 17:04: [(none)]> show variables like '%char%';
+--------------------------+----------------------------------------+
| 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 | /home/mysql/mysql_9036/share/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.00 sec)

root@xxx-mysql 17:05: [(none)]> show variables like '%coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)



# 5.6上数据备份命令:
[root@xxx-mysql ~]# grep utf8 db_back.sh
$mysqldump -u${Bk_user} -hlocalhost -p${Bk_pwd} -S ${Bk_sock} --default-character-set=utf8 --single-transaction --master-data=2 -B ${db} |/bin/gzip > ${Fullbk_filename}_${Hour}.gz


# 备份的sql:
DROP TABLE IF EXISTS `xxx`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `xxx` (
`id` varchar(5) NOT NULL COMMENT '类d',
`code` varchar(32) NOT NULL COMMENT '代码',
`name` varchar(120) DEFAULT NULL,
`pinyin` varchar(50) DEFAULT NULL COMMENT '音',
`pcode` varchar(3) DEFAULT NULL COMMENT '上级(职业)',
`del_flag` varchar(1) NOT NULL DEFAULT '0' COMMENT '标记',
`source_id` varchar(32) DEFAULT NULL COMMENT '来源',
`create_by` varchar(32) DEFAULT NULL COMMENT '者',
`create_date` datetime DEFAULT NULL COMMENT '时间',
`update_by` varchar(32) DEFAULT NULL COMMENT '者',
`update_date` datetime DEFAULT NULL COMMENT '时间',
`sort` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='字典表';
/*!40101 SET character_set_client = @saved_cs_client */;


# 5.7
| cdc_dic_personinfo | CREATE TABLE `xxx` (
`id` varchar(5) NOT NULL COMMENT '类型id',
`code` varchar(32) NOT NULL COMMENT '标准代ç ',
`name` varchar(120) DEFAULT NULL,
`pinyin` varchar(50) DEFAULT NULL COMMENT '拼音',
`pcode` varchar(3) DEFAULT NULL COMMENT '上级代ç ï¼ˆèŒä¸šï¼‰',
`del_flag` varchar(1) NOT NULL DEFAULT '0' COMMENT '删除标记',
`source_id` varchar(32) DEFAULT NULL COMMENT 'æ¥æºä»£ç ',
`create_by` varchar(32) DEFAULT NULL COMMENT '创建者',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(32) DEFAULT NULL COMMENT '更新者',
`update_date` datetime DEFAULT NULL COMMENT '更新时间',
`sort` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='å¥åº·æ¡£æ¡ˆå­—典表'



# 问题二:
直接连接mysql服务select数据没有乱码,而连接proysql出现乱码?

连接master: 没有出现乱码
root@localhost:mysql5501.sock [(none)]>select * from xxxx.xxx limit 2\G;
*************************** 1. row ***************************
personid: 000008c32f444b86ba0c4ba85cd185c4
registerid: NULL
name: 欧阳兰啦
gender: 2
birth: 1947-07-20
resident_recordid: NULL
personcard_type: 01
personcard_no: 3101088888
company_name: NULL
company_zcode: NULL
company_address: NULL
phone: NULL
mobile_phone: NULL


连接proxysql: 出现乱码
mysql> select * from healthcloud_basic.app_tb_person_info2 limit 2\G;
*************************** 1. row ***************************
personid: 000008c32f444b86ba0c4ba85cd185c4
registerid: NULL
name: ???
gender: 2
birth: 1947-07-20
resident_recordid: NULL
personcard_type: 01
personcard_no: 3101888

问题一和问题二都是在同一套实例上面,报错,麻烦老师同学帮忙看看,谢谢:)
已邀请:

yejr

赞同来自: zhagyilig

先说问题1
1、5.7时,通过proxysql连接后,执行 show variables like '%char%'; 的结果是啥
2、mysqldump出来的文件,直接用less/more查看,会乱码吗
 

yejr

赞同来自: zhagyilig

后期要解决时,可能需要用mysqldump逻辑备份,再用iconv之类的工具把文件内码全部转成utf8,然后导入。
另外,你这个实例里,应该有些表是正常的(utf8字符集表,也用utf8字符集传输并写入),有些才是有问题的。

zhagyilig - 90后IT男

赞同来自:

好的,谢谢叶老师:
1.
mha@100.102.114.193:9036 [(none)]>show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| 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/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.01 sec)
 
2.不会乱码,如下:[root@pudong-tomcat-1 recovery]#  file pudong-mysql_healthcloud_chronic_full_03 
pudong-mysql_healthcloud_chronic_full_03: UTF-8 Unicode text, with very long lines
[root@pudong-tomcat-1 recovery]# more pudong-mysql_healthcloud_chronic_full_03 
-- MySQL dump 10.13  Distrib 5.6.31, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: healthcloud_chronic
-- ------------------------------------------------------
-- Server version       5.6.31-log
 

zhagyilig - 90后IT男

赞同来自:

感谢叶老师指点,同时做个小小总结:
  1. 5.7下,无论直连还是走proxysql,都先执行一次 set names utf8,再查询,都乱码(这和之前的还不一样,proxysql连也乱码了);
  2. 都set names latin1,再查询,都显示正常。

 
  • 综上:

     表虽然是utf8,但实际写入可能用的latin1(或其他字符集);
     目前只是在用cli查询乱码,明天上业务,和会测试一起验证对业务的影响。
 
  • 注意:

      这种情况下,不删除现有5.7的数据,能解决乱码吗?
      叶师傅建议:建议维持现状;需要人工、手工查询时,再set names latin1;
      当然,后期也会解决这问题。

zhagyilig - 90后IT男

赞同来自:

1. 您说的是从现有5.7故障环境备份到导入,注意事项;
2. 如果我重新备份(mysqldump)5.6, 导入5.7,需要注意哪些问题?
2.1 备份字符集

2.2 导入客户端字符集
[client]  
default-character-set=utf8     

[mysql]   
default-character-set=utf8  

[mysqld]  
collation-server = utf8_unicode_ci  
character-set-server = utf8  


3. 如果使用xtrabackup备份5.6,导入5.7,需要注意哪些问题?

要回复问题请先登录注册