centos升级到最新的mysql

去站点下载mysql的yum源。地址例如以下：

http://repo.mysql.com/

在linux上先查看系统的版本，依据版本相应下载

more /etc/redhat-release

rpm -Uvh http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

yum -y upgrade mysql

mysql_upgrade

改动database,table,column字符集

# For each database:

ALTER DATABASE vipmonk CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

# For each table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

# For each column:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

改动my.ini(linux下为my.cnf)

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

init_connect='SET NAMES utf8mb4'

又一次启动Mysql，检查字符集：

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'

+--------------------------+--------------------+

Variable_name | Value

+--------------------------+--------------------+

character_set_client | utf8mb4

character_set_connection | utf8mb4

character_set_database | utf8mb4

character_set_filesystem | binary

character_set_results | utf8mb4

character_set_server | utf8mb4

character_set_system | utf8

collation_connection | utf8mb4_unicode_ci

collation_database | utf8mb4_unicode_ci

collation_server | utf8mb4_unicode_ci

+--------------------------+--------------------+

10 rows in set (0.00 sec)

改动全部表和字段的字符集

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 如：

ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

//批量改动全部表和字段的字符集

SELECT

CONCAT('alter table ',table_name,' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')

FROM

INFORMATION_SCHEMA.table

WHERE

TABLE_SCHEMA='database_name'