因業務要求，需要升級5.6到5.7 支援json，升級過程如下：

作業系統：CentOS Linux release 7.5.1804

mysql版本：5.6.41

檢視作業系統版本和資料庫版本

[root@ssl yum.repos.d]# cat /etc/redhat-release

CentOS Linux release 7.5.1804 (Core)

mysql -V

mysql Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using EditLine wrapper

[root@ssl yum.repos.d]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.41 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database update_test;

Query OK, 1 row affected (0.00 sec)

mysql> use update_test;

Database changed

mysql> create table update_test(id int,name varchar(100));

Query OK, 0 rows affected (0.05 sec)

mysql> insert into update_test values (1,'hello');

Query OK, 1 row affected (0.00 sec)

mysql> insert into update_test values (2,'world');

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from update_test;

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

| id | name |

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

| 1 | hello |

| 2 | world |

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

2 rows in set (0.00 sec)

mysql> create user 'test'@'%' identified by 'test';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on update_test.* to 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

5.6版本不支援JSON

mysql> create table t (t JSON);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON)' at line 1

另開一個連線修改mysql-community.repo

vim /etc/yum.repos.d/mysql-community.repo

# Enable to use MySQL 5.6

[mysql56-community]

name=MySQL 5.6 Community Server

baseurl=

enabled=0 //修改為0

gpgcheck=1

gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Note: MySQL 5.7 is currently in development. For use at your own risk.

# Please read with sub pages: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/

[mysql57-community-dmr]

name=MySQL 5.7 Community Server Development Milestone Release

baseurl=

enabled=1 //修改為1

gpgcheck=0

systemctl stop mysqld.service

systemctl status mysqld.service

檢視可升級的版本

[root@ssl ~]# yum check-update | grep mysql

mysql-community-client.x86_64 5.7.23-1.el7 mysql57-community-dmr

mysql-community-common.x86_64 5.7.23-1.el7 mysql57-community-dmr

mysql-community-libs.x86_64 5.7.23-1.el7 mysql57-community-dmr

mysql-community-server.x86_64 5.7.23-1.el7 mysql57-community-dmr

升級mysql-server

[root@ssl ~]# yum update mysql-server

Loaded plugins: fastestmirror, langpacks

Loading mirror speeds from cached hostfile

* base: mirror.bit.edu.cn

* extras: mirror.bit.edu.cn

* updates: mirrors.huaweicloud.com

Resolving Dependencies

There are unfinished transactions remaining. You might consider running yum-complete-transaction, or "yum-complete-transaction --cleanup-only" and "yum history redo last", first to finish them. If those don't work you'll have to try removing/installing packages by hand (maybe package-cleanup can help).

--> Running transaction check

---> Package mysql-community-server.x86_64 0:5.6.41-2.el7 will be updated

---> Package mysql-community-server.x86_64 0:5.7.23-1.el7 will be an update

--> Processing Dependency: mysql-community-common(x86-64) = 5.7.23-1.el7 for package: mysql-community-server-5.7.23-1.el7.x86_64

--> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for package: mysql-community-server-5.7.23-1.el7.x86_64

--> Running transaction check

---> Package mysql-community-client.x86_64 0:5.6.41-2.el7 will be updated

---> Package mysql-community-client.x86_64 0:5.7.23-1.el7 will be an update

--> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.23-1.el7.x86_64

---> Package mysql-community-common.x86_64 0:5.6.41-2.el7 will be updated

---> Package mysql-community-common.x86_64 0:5.7.23-1.el7 will be an update

--> Running transaction check

---> Package mysql-community-libs.x86_64 0:5.6.41-2.el7 will be updated

--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64

--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64

---> Package mysql-community-libs.x86_64 0:5.7.23-1.el7 will be an update

--> Running transaction check

---> Package mysql-community-libs-compat.x86_64 0:5.7.23-1.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================

Package Arch Version Repository Size

=====================================================================================================================

Updating:

mysql-community-server x86_64 5.7.23-1.el7 mysql57-community-dmr 165 M

Installing for dependencies:

mysql-community-libs-compat x86_64 5.7.23-1.el7 mysql57-community-dmr 2.0 M

Updating for dependencies:

mysql-community-client x86_64 5.7.23-1.el7 mysql57-community-dmr 24 M

mysql-community-common x86_64 5.7.23-1.el7 mysql57-community-dmr 274 k

mysql-community-libs x86_64 5.7.23-1.el7 mysql57-community-dmr 2.2 M

Transaction Summary

=====================================================================================================================

Install ( 1 Dependent package)

Upgrade 1 Package (+3 Dependent packages)

Total download size: 194 M

Is this ok [y/d/N]: y

Downloading packages:

No Presto metadata available for mysql57-community-dmr

(1/5): mysql-community-common-5.7.23-1.el7.x86_64.rpm | 274 kB 00:00:01

(2/5): mysql-community-libs-5.7.23-1.el7.x86_64.rpm | 2.2 MB 00:00:05

(3/5): mysql-community-libs-compat-5.7.23-1.el7.x86_64.rpm | 2.0 MB 00:00:04

(4/5): mysql-community-client-5.7.23-1.el7.x86_64.rpm | 24 MB 00:00:42

(5/5): mysql-community-server-5.7.23-1.el7.x86_64.rpm | 165 MB 00:04:33

---------------------------------------------------------------------------------------------------------------------

Total 697 kB/s | 194 MB 00:04:44

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Updating : mysql-community-common-5.7.23-1.el7.x86_64 1/9

Updating : mysql-community-libs-5.7.23-1.el7.x86_64 2/9

Updating : mysql-community-client-5.7.23-1.el7.x86_64 3/9

Updating : mysql-community-server-5.7.23-1.el7.x86_64 4/9

Installing : mysql-community-libs-compat-5.7.23-1.el7.x86_64 5/9

Cleanup : mysql-community-server-5.6.41-2.el7.x86_64 6/9

Cleanup : mysql-community-client-5.6.41-2.el7.x86_64 7/9

Cleanup : mysql-community-libs-5.6.41-2.el7.x86_64 8/9

Cleanup : mysql-community-common-5.6.41-2.el7.x86_64 9/9

Verifying : mysql-community-server-5.7.23-1.el7.x86_64 1/9

Verifying : mysql-community-libs-5.7.23-1.el7.x86_64 2/9

Verifying : mysql-community-client-5.7.23-1.el7.x86_64 3/9

Verifying : mysql-community-libs-compat-5.7.23-1.el7.x86_64 4/9

Verifying : mysql-community-common-5.7.23-1.el7.x86_64 5/9

Verifying : mysql-community-server-5.6.41-2.el7.x86_64 6/9

Verifying : mysql-community-client-5.6.41-2.el7.x86_64 7/9

Verifying : mysql-community-libs-5.6.41-2.el7.x86_64 8/9

Verifying : mysql-community-common-5.6.41-2.el7.x86_64 9/9

Dependency Installed:

mysql-community-libs-compat.x86_64 0:5.7.23-1.el7

Updated:

mysql-community-server.x86_64 0:5.7.23-1.el7

Dependency Updated:

mysql-community-client.x86_64 0:5.7.23-1.el7 mysql-community-common.x86_64 0:5.7.23-1.el7

mysql-community-libs.x86_64 0:5.7.23-1.el7

Complete!

升級完成，啟動mysql服務

[root@ssl ~]# systemctl start mysqld.service

[root@ssl ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.23 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| update_test |

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

4 rows in set (0.00 sec)

mysql> use update_test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from update_test;

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

| id | name |

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

| 1 | hello |

| 2 | world |

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

2 rows in set (0.00 sec)

升級後支援JSON

mysql> create table t (t JSON);

Query OK, 0 rows affected (0.12 sec)

mysql> insert into t values ('{"name":"t"}');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t;

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

| t |

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

| {"name": "t"} |

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

1 row in set (0.01 sec)

建立使用者失敗，系統執行mysql_upgrade命令

mysql> create user 'test02'@'%' identified by 'test02';

ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50641, now running 50723. Please use mysql_upgrade to fix this error.

另一個視窗執行

[root@ssl yum.repos.d]# mysql_upgrade

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv OK

mysql.db OK

mysql.engine_cost OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.gtid_executed OK

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.innodb_index_stats OK

mysql.innodb_table_stats OK

mysql.ndb_binlog_index OK

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.server_cost OK

mysql.servers OK

mysql.slave_master_info OK

mysql.slave_relay_log_info OK

mysql.slave_worker_info OK

mysql.slow_log OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.user OK

Upgrading the sys schema.

Checking databases.

sys.sys_config OK

update_test.t OK

update_test.update_test OK

Upgrade process completed successfully.

Checking if update is needed.

[root@ssl yum.repos.d]#

再次執行建立使用者成功

mysql> create user 'test02'@'%' identified by 'test02';

Query OK, 0 rows affected (0.00 sec)

升級完成重啟資料庫

systemctl restart mysqld.service

centos7 mysql5.6升級5.7

相關文章