MonthFebruary 2018

mysql中Incorrect string value乱码解决

你是否遇到过类似以下错误?

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x9C’ for column ‘content’ at row 1.

产生这种异常的原因在于,mysql中的utf8编码最多会用3个字节存储一个字符,如果一个字符的utf8

编码占用4个字节(最常见的就是ios中的emoji表情字符),那么在写入数据库时就会报错。

mysql从5.5.3版本开始,才支持4字节的utf8编码,编码名称为utf8mb4(mb4的意思是max bytes 4),这种编码方式最多用4个字节存储一个字符。

要想证明这个问题,可以执行以下sql:

select * from
information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'utf8%'

结果如图:

因此,要解决上述异常的发生,需要使用utf8mb4编码。

解决数据库编码后,还需要解决客户端Connection连接对象使用的编码问题。

调用创建的Connection对象执行以下sql:

conn.createStatement().execute("SET names 'utf8mb4'");

如果项目中使用了DataSource数据源,只需要对数据源进行相关配置即可,这里以apache的DBCP数据源为例讲解,在spring框架下配置如下:

<!-- 数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql://${${data-source.prefix}.data-source.host-name}:3306/${${data-source.prefix}.data-source.db-name}?characterEncoding=utf8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;maxReconnects=10&amp;allowMultiQueries=true" />
		<property name="username" value="${${data-source.prefix}.data-source.username}" />
		<property name="password" value="${${data-source.prefix}.data-source.password}" />
		<property name="maxActive" value="150" />
		<property name="maxIdle" value="2" />
		<property name="testOnBorrow" value="true" />
		<property name="testOnReturn" value="true" />
		<property name="testWhileIdle" value="true" />
		<property name="validationQuery" value="select 1" />
		<!-- 此配置用于在创建Connection对象时执行指定的初始化sql -->
		<property name="connectionInitSqls">
			<list>
				<value>set names 'utf8mb4'</value>
			</list>
		</property>
	</bean>

以下解释引用自mysql参考手册:

SET NAMES ‘charset_name’

SET NAMES显示客户端发送的SQL语句中使用什么字符集。

因此,SET NAMES ‘utf8mb4’语句告诉服务器:“将来从这个客户端传来的信息采用字符集utf8mb4”。它还为服务器发送回客户端的结果指定了字符集。(例如,如果你使用一个SELECT语句,它表示列值使用了什么字符集。)

SET NAMES ‘x’ 语句与这三个语句等价:

mysql> SET character_set_client = x;

mysql> SET character_set_results = x;

mysql> SET character_set_connection = x;

执行完此sql语句后,通过此连接对象后续创建的Statement都会成功地执行了。

讲到这里,问题已经得到完美解决,但是我又联想到一个新的问题:

jvm虚拟机运行时,内存中的字符串采用utf-16编码,对于ios中的emoji表情这种用4字节utf-8编码存储的字符,在java运行时又是怎样存储的呢?

于是,我找了一个emoji字符(4个字节的值分别为0xf0,0x9F,0x92,0x9c),做了以下试验。

byte[] bytes = new byte[] { (byte0xf0, (byte0x9F, (byte0x92, (byte0x9c };
		String s = new String(bytes, Charset.forName("utf-8"));
		System.out.println("length:"+s.length());
		for (int i=0;i<s.length();i++) {
			int ch = s.charAt(i);
			System.out.println("0x"+Integer.toHexString(ch));
		}

执行结果如下:

由结果可以看出,unicode值(也叫codePoint码点,后面介绍API会用到)大于0xffff的单个字符,jvm内部占用2个char的长度(也就是4个字节)存储。

所有大于0xffff的字符,全都在UTF编码表的辅助平面内(域辅助平台对应的是基础平面,简称BMP)。因此对于String中的某个char,是基础平面字符,还是辅助平面字符的一部分,也很好做出判断。下面介绍java.lang.Character中的一些API:

以下描述中,码点即是字符的unicode值

Character中API 描述
isValidCodePoint(int codePoint):boolean 判断输入码点是否是有效的,所有属于UTF定义平面的码点都是有效的
isBmpCodePoint(int codePoint):boolean 判断输入码点是否属于基础平面,即:0x0000~0xffff
isSupplementaryCodePoint(int codePoint):boolean 判断输入码点是否属于辅助平面,即:码点>0xffff
isSurrogate(char ch):boolean 判断输入的字符是否辅助平面字符的一部分

获取String中某个字符的码点也很容易,调用String.codePointAt(int index):int即可。

最后,关于unicode、UCS-2、UCS-4、UTF-8、UTF-16编码之间的关系,请读者自行百度。文章太多了,在此就不多做介绍了。

参考资料:

  • mysql utf8mb4与emoji表情:

http://my.oschina.net/wingyiu/blog/153357

  • 关于 MySQL UTF8 编码下生僻字符插入失败/假死问题的分析

http://my.oschina.net/leejun2005/blog/343353

MySQL Group Replicaiton配置

MySQL Group Replication(下简称:MGR)是MySQL官方推出的一种基于Paxos协议的状态机复制。在MGR出现之前,用户常见的MySQL高可用方式,无论怎么变化架构,本质就是Master-Slave架构。MySQL 5.7版本开始支持无损半同步复制(lossless semi-sync replication),从而进一步提示数据复制的强一致性。

Master-Slave始终无法解决的一个问题是选主(Leader Election),特别是当由于网络分区发生脑裂时,目前大多的高可用解决方案都会导致双写的问题,这在金融场景下显然是无法接受的。为避免此问题的发生,有时不得不强行关闭一台服务,从而保证同一时间只有一个节点可以写入,然而这时数据库集群可用性又可能会收到极大的影响。

MongoDB、TiDB这些出现的后起之后通过Raft协议来进行选主,从而避免脑裂问题的产生。然而他们依然是单写的场景,即一个高可用集群下,写入依然是单个节点。

MGR的解决方案现在来看可说非常完美:

  • 数据一致性保障:确保集群中大部分节点收到日志
  • 多节点写入支持:多写模式下支持集群中的所有节点都可以写入
  • Fault Tolerance: 确保系统发生故障(包括脑裂)依然可用,双写对系统无影响

从MGR公布的特性来基本是数据库所追求的最终完美形式。然而很多同学还会问MGR和无损半同步复制的区别,比如1个集群5个节点,无损半同步复制可以设置至少有2个节点收到ACK请求再提交事务,也能保障数据一致性。

Quorum原则(大部分原则)只是Paxos实现的一小部分,因此无损半同步复制解决的只是日志完整性的问题。若把日志看成是value,则只是解决了日志丢失问题。但是如何在分布式异常场景下确定这个value值,则需要Paxos协议来解决。比如,当发生脑裂情况下,谁是Primary,则MGR通过Paxos协议可以清楚的判断,从而避免双写问题。

组复制的限制

  • 存储引擎必须为Innodb
  • 每个表必须提供主键
  • 只支持ipv4,网络需求较高
  • 一个组最多只能有9台服务器
  • 不支持 Replication event checksums,
  • 不支持 Savepoints
  • 多主模式不支持SERIALIZABLE事务隔离级别
  • 多主模式不能完全支持级联外键约束
  • 多主模式不支持在不同节点上对同一个数据库对象并发执行DDL(在不同节点上对同一行并发进行RW事务,后发起的事务会失败)

配置过程

每个节点执行如下命令安装官方仓库的MySQL Server

apt-get update
cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb
dpkg -i mysql-apt-config_0.8.9-1_all.deb
apt-get update
aptitude install -y mysql-server

验证复制插件是否存在:

root@iZwz98pmxwulw67n9gxnl2Z:/etc/mysql# ll /usr/lib/mysql/plugin/
total 4120
drwxr-xr-x 3 root root    4096 Nov 11 11:14 ./
drwxr-xr-x 3 root root    4096 Nov 11 11:14 ../
...
-rw-r--r-- 1 root root 1751560 Sep 14 01:01 group_replication.so
...

注意: 不要安装Ubuntu 16.04自带的MySQL, 16.04自带的MySQL安装后没有 group_replication.so 这个东西, 一定要通过 mysql-apt-config_0.8.9-1_all.deb 提供的仓库安装. MySQL 官方版本的最新版本的仓库可以在这里下载: https://dev.mysql.com/downloa…

每个节点创建目录, 用于放置MGR(MySQL Group Replication)的配置文件

mkdir /etc/mysql/mgr.d

修改MGR配置文件

配置文件在本文Git仓库Markdown文件相同目录下, 文件名称依次为:

mgr-01.conf
mgr-02.conf
mgr-03.conf

修改上述三个文件对应的IP地址, 详细的说明参考: https://www.howtoing.com/how-…

生成复制组所需要的UUID备用.

root# uuidgen
00d17eae-73c8-4a7d-abf5-051bb68a9d7d

用上面生成的UUID替换 loose-group_replication_group_name

上传配置文件

https://github.com/developerw…

#!/bin/bash
# mkdir /etc/mysql/mgr.d
scp mgr-01.conf root@172.18.149.213:/etc/mysql/mgr.d/mgr.cnf
scp mgr-02.conf root@172.18.149.214:/etc/mysql/mgr.d/mgr.cnf
scp mgr-03.conf root@172.18.149.215:/etc/mysql/mgr.d/mgr.cnf

在每一个节点的 /etc/mysql/my.cnf 文件最后一行添加如下指令:

!includedir /etc/mysql/mgr.d/

更换MySQL的数据盘

双十一新购了3台本地SSD的ECS, 想把MySQL的数据目录移动到独立的SSD(/dev/vdb)上. 因此停止数据指标移动数据目录:

格式化磁盘

fdisk /dev/vdb

创建挂载点

mkdir /data

创建文件系统

mkfs.ext4 /dev/vdb1

查看磁盘UUID

blkid

复制 /dev/vdb1 的UUID, 在 /etc/fstab 下添加:

UUID=${UUID} /data           ext4    errors=remount-ro 0       1

用你自己的磁盘UUID替换 ${UUID}

挂载文件系统

mount /dev/vdb1 /data

创建软连接并启动MySQL

mv /var/lib/mysql /data
ln -s /data/mysql /var/lib/mysql
systemctl start mysql

目录权限问题

启动数据库查看日志(tail -f /var/log/mysql/error.log)发现如下错误消息:

2017-11-11T03:24:49.003621Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2017-11-11T03:24:49.003637Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2017-11-11T03:24:49.003642Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-11-11T03:24:49.604050Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-11-11T03:24:49.604070Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-11-11T03:24:49.604076Z 0 [ERROR] Failed to initialize plugins.
2017-11-11T03:24:49.604079Z 0 [ERROR] Aborting

解决办法

编辑如下文件

vi /etc/apparmor.d/usr.sbin.mysqld

在文件末尾 } 的上一行添加下面两行:

/data/mysql/ r,
/data/mysql/** rwk,

/data/mysql 为新的数据目录路径. 上面两行的作用是分配目录的读写权限, Ubuntu 16.04 默认的MySQL数据目录为 /var/lib/mysql.

复制配置

进入MySQL控制台

mysql -uroot -p

在所有节点执行如下命令:

SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 验证插件是否安装成功
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
...
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+

配置第一个节点

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

配置余下节点

START GROUP_REPLICATION;

加入组的问题

事务问题, 各个节点的事务状态不一致. 所有节点加入集群前最好不要修改任何数据, 否则就会出现下面的错误.

2017-11-10T19:07:26.918531Z 0 [ERROR] Plugin group_replication reported: 'This member has more > executed transactions than those present in the group. Local transactions: c3c274ff-c63e-11e7-> b339-00163e0c0288:1-4 > Group transactions: 2e6bfa69-0439-41c9-add7-795a9acfd499:1-10,
c5898a84-c63e-11e7-bc8b-00163e0af475:1-4'

解决办法:
http://blog.csdn.net/yuanlin6…

set global group_replication_allow_local_disjoint_gtids_join=ON;

IP地址变化的问题

对于一个3节点的单主集群来说, 当主节点挂了, 另外两个节点会自动选主. 其中一个会成为主节点, 并自动切换为读写模式.
因为对于单主模式来说, 只有主节点能够执行写操作. 那么我们如何知道主节点的IP地址呢?

可以在任意一个MySQL节点上通过如下SQL获取主节点的IP地址

SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');

参考资料

https://dev.mysql.com/doc/ref…
https://www.howtoing.com/how-…
http://blog.csdn.net/yuanlin6…
https://stackoverflow.com/que…

© 2019 Init dba

Theme by Anders NorenUp ↑