MySQL 5.1.x升级到MySQL 5.5.x

Liam Mazy Database, Web&Server

在调用微信接口的时候，如果遇到包含表情的昵称或者消息的时候，在将数据写入到MySQL的时候会出现类似 Incorrect string value: '\xF0\x8F...' for column 'XXX' 的错误提示。原因是UTF-8编码有可能是两个、三个或四个字节，而上述中的表情为Emoji表情，在iPhone手机中很常见，很多都是四个字节的。但是MySQL 5.1.x不支持这样四个字节的utf-8编码，只有5.5.x才支持，所以只能将MySQL升级到5.5.x。

1. 安装MySQL
windows下面安装MySQL 5.5.x非常简单，这里不再详述。Linux下面因为从MySQL 5.5开始使用cmake来做config了，所以与之前版本的安装方式不同，这里说明一下不同的地方。

首先安装基础依赖库：

LANG=C
yum -y install gcc-c++ gperf ncurses-devel readline-devel libaio-devel

然后下载编译安装cmake：

wget http://www.cmake.org/files/v2.8/cmake-2.8.12.2.tar.gz
tar xzvf cmake-2.8.12.2.tar.gz
cd cmake-2.8.12.2
./configure && make && make install

安装完成之后可以执行 cmake -version 命令查看cmake是否正常安装。

基础工作做完之后就可以下载安装mysql了，这里以mysql-5.5.36为例：

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
tar xzvf mysql-5.5.36.tar.gz
cd mysql-5.5.36
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=yes -DENABLED_LOCAL_INFILE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all
make && make install

其中cmake的安装选项可以在MySQL Source-Configuration Options进行详细的查看，默认情况下MyISAM、MERGE、MEMORY和CSV引擎默认配置是按照的，所以上面只使用 WITH_INNOBASE_STORAGE_ENGINE=1 配置安装了InnoDB引擎。

如果CentOS版本低于6.x的话可能在安装的过程中出现下面的错误：

/root/downloads/mysql-5.5.32/vio/viossl.c: In function ‘ssl_do’:
/root/downloads/mysql-5.5.32/vio/viossl.c:175: 错误：‘SSL_OP_NO_COMPRESSION’ 未声明 (在此函数内第一次使用)
/root/downloads/mysql-5.5.32/vio/viossl.c:175: 错误：(即使在一个函数内多次出现，每个未声明的标识符在其
/root/downloads/mysql-5.5.32/vio/viossl.c:175: 错误：所在的函数内只报告一次。)
make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] 错误 1
make[1]: *** [vio/CMakeFiles/vio.dir/all] 错误 2
make: *** [all] 错误 2

这是mysql 5.5.32的一个bug，原因是无法在openssl-1.0.0及更低版本下编译，如果不需要ssl可以去掉WITH_SSL配置项，也可以升级openssl的版本，另外更方便的方法可以在cmake之前打一个补丁：

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
tar xzvf mysql-5.5.36.tar.gz
cd mysql-5.5.36
wget -c "http://bugs.mysql.com/file.php?id=19941&bug_id=68999" -O mysql-openssl.patch
patch -p1 < mysql-openssl.patch
然后再进行上面的cmake操作。

最后初始化数据库，先要确保是否创建了mysql用户，如果没有则使用下面命令进行创建

groupadd mysql
useradd -s /sbin/nologin -M -g mysql mysql

设置目录权限并安装数据库

cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
# 安装数据库，这里的脚本位置跟5.1.x有所不同
scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data

如果不是升级的话可以先使用mysql默认的配置文件，然后启动mysql：

# 下面的文件在5.5.x放到了support-files目录下了
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
/etc/init.d/mysql start
# 修改root密码
bin/mysqladmin -uroot password 123
history -c
/etc/init.d/mysql restart
# 开机启动
cat >> /etc/rc.local < /etc/init.d/mysql start
> EOF

2. 修改mysql 5.1.x的配置文件
如果是升级的话使用原来的配置文件之前需要做一些修改，常用的变化的配置项如下：

  • default-character-set 修改成了 character-set-server，在linux安装的过程中已经指定了编码，所以不需要在配置文件中进行设置，但是windows下需要进行设置
  • innodb_file_io_threads已经弃用，修改成了 innodb_read_io_threads 和 innodb_write_io_threads
  • skip-locking 已经弃用，修改成了 skip-external-locking
  • log-long-format 已经弃用，修改成了 log-short-format
  • log_slow_queries 已经弃用，修改成了 slow-query-log，并且该参数设置为1启用慢查询日志，0为关闭，使用slow_query_log_file指定日志文件的名称，默认文件名为host_name-slow.log
  • lower_case_table_name 已经弃用，建议修改成 lower_case_table_names

    • 另外可以把以前的built-in文件格式Antelope改为新的Barracuda格式：
    innodb_file_format=Barracuda
    linux下为了与windows保持一致，设置表名或字段名的大小写不敏感：
    lower_case_table_names=1

    3. 原有数据库修复及升级
    为了保证数据的完整性，在将旧的数据导入到新数据库中之后（为了方便管理，可以将旧的mysql数据库合并到新的数据库中，然后重新mysql server使其生效），需要使用mysql_upgrade命令升级修复数据库，否则有可能会出现 "Cannot load from mysql.proc. The table is probably corrupted"错误。在mysql的bin目录下执行下面的命令（参数及其含义请见 mysql_upgrade — Check and Upgrade MySQL Tables）：

    mysql_upgrade -P3300 -uroot -p --force

    确保mysql的密码输入正确，否则会出现 FATAL ERROR: Upgrade failed 的错误。

    这里需要指出的是，如果在windows环境下执行上述命令会出现下面的错误：

    Looking for 'mysql.exe' as: c:\mysql\bin\mysql.exe
    FATAL ERROR: Can't execute 'c:\mysql\bin\mysql.exe'

    如果执行mysql命令则会出现 “unknown variable 'character-set-server=utf8'” 的错误，这个是mysql的一个bug，可以首先将mysql的配置文件my.ini中的character-set-server全部修改为旧的default-character-set，然后再执行上述命令，执行命令的时候确保mysql服务是启动状态，等到升级完成之后再把配置文件中的default-character-set替换为新的character-set-server。

    最后解决最开始说的问题的方法是将表中对应的字段编码由utf-8升级为utf8mb4：

    ALTER TABLE `table_name` CHANGE `colum_name` `colum_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL ;

    附件：
    mysql-openssl.patch

    参考资料：
    中文写入数据库乱码及Incorrect string value: '\xF0\x9F...' for column 'XXX' at row 1解决
    mysql 5.5.17编译安装配置小计
    MySQL Source-Configuration Options
    SSL_OP_NO_COMPRESSION not defined
    【MySQL运维】MySQL5.1升级到MySQ 5.5实战
    How to Resolve MySQL Error Code: 1548 Cannot load from mysql.proc. The table is probably corrupted
    MySQL Upgrade 5.1 > 5.5
    Bug #60316 mysql_upgrade.exe fails on windows 7 x64
    How to enable the MySQL Slow query log (5.5.15)
    The Slow Query Log

    发表回复

    您的电子邮箱地址不会被公开。 必填项已用 * 标注