mysql控制台无法输入中文的解决办法

mysql控制台无法输入中文的解决办法
aws_ec2_cn

[背景] AWS中国区的一个实例上安装了 mysql56 版本的数据库,远程shell连上去发现mysql控制台无法输入中文,但shell本身可以输入中文,使用的macpro默认的终端,按理说不应该是ssh终端的问题,本以就是简单的mysql字符集的问题,但着实费了一番牛劲才最终搞定。

AWS中国区的操作系统版本:

# cat /etc/issue
Amazon Linux AMI release 2016.09
Kernel \r on an \m

# uname -a
Linux ip-172-31-xx-xxx 4.4.23-31.51.amzn1.x86_64 #1 SMP Tue Oct 11 17:10:29 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Amazon Linux AMI的字符集:

# locale
LANG=zh_CN.UTF-8
LC_CTYPE=zh_CN.UTF-8
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=

mysql版本具体信息:

# mysql --version
mysql  Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using  EditLine wrapper
mysql-character
mysql-character

试过多种方式来修改系统或者mysql的字符集:

(1)修改系统字符集
export LANG=en_US.UTF-8

(2)修改my.cnf的客户端和服务端的字符集
cat /etc/my.cnf
[client]
default-character-set = utf8

[mysqld]
collation_server = utf8_general_ci
character_set_server = utf8

[mysql]
default-character-set = utf8

(3)登录mysql时指定字符集
mysql -uroot -p -default-character-set=utf8
或者
mysql -uroot -p -default-character-set=gbk

(4)登录mysql后修改client字符集:
set names utf8;
或者
set names gbk;

(5)mysql里面指定character和collation
set character_set_database=utf8;  
set character_set_server=utf8;  
set character_set_client=utf8;  
set character_set_connection=utf8;  

实际上mysql的字符集状态如下:
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.00 sec)

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+------------------------------+
| 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/mysql56/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.00 sec)

尝试了以上各种方法,不过在 mysql> 提示符下始终无法输入中文,我也是醉了,最后google发现有一篇文章说mysql5.5升级到mysql5.6时出现的无法输入中文的问题,是因为EditLine版本的问题,于是从 lib-editline 入手进行分析。

AWS的yum源上libedit已经是最新的了,版本为libedit-2.11。
# rpm -qa|grep libedit
libedit-2.11-4.20080712cvs.1.6.amzn1.x86_64

看来只能手动源码更新,如下是具体步骤:

# wget http://thrysoee.dk/editline/libedit-20160903-3.1.tar.gz
tar zxvf libedit-20160903-3.1.tar.gz
cd libedit-20160903-3.1
./configure
make && make install

编译后的libedit.so默认放在了/usr/local/lib/下面,需要做软链接替换旧的libedit版本。

# find / | grep libedit.so
/usr/local/lib/libedit.so.0.0.55
/usr/local/lib/libedit.so.0
/usr/local/lib/libedit.so
/usr/lib64/libedit.so.0
/usr/lib64/libedit.so.0.0.27

# unlink /usr/lib64/libedit.so.0
# ln -s /usr/local/lib/libedit.so.0.0.55 /usr/lib64/libedit.so.0

最后重启mysqld服务,然后测试:

mysql>
mysql> 中文测试
    -> ;
mysql>

word哥,mysql56 终于可以输入中文了!!!