centos7 mysql5.6升级5.7

因业务要求，需要升级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) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/ 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=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ 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) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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