centos 最新mysql_centos升级到最新的mysql

去网站下载mysql的yum源，地址如下：

http://repo.mysql.com/

在linux上先查看系统的版本号，根据版本号对应下载

more /etc/redhat-release

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.tables

WHERE

TABLE_SCHEMA='database_name'