mysql数据库版本升级

5.7.23 升级到5.7.26

centos版本：CentOS Linux release 7.6.1810 (Core)

1、查看当前版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.23    |
+-----------+
1 row in set (0.00 sec)

2、关闭当前数据库

[root@mini04 mysql5.7.23]# mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
	设置参数使数据库关闭时会清空缓存避免版本间缓存格式差异
[root@mini04 mysql5.7.23]# systemctl stop mysqld
[root@mini04 mysql5.7.23]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Thu 2021-02-04 10:50:11 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 16446 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 16369 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 16449 (code=exited, status=0/SUCCESS)
Feb 04 10:42:39 mini04 systemd[1]: Starting MySQL Server...
Feb 04 10:42:47 mini04 systemd[1]: Started MySQL Server.
Feb 04 10:50:09 mini04 systemd[1]: Stopping MySQL Server...
Feb 04 10:50:11 mini04 systemd[1]: Stopped MySQL Server.

3、数据库升级

[root@mini04 apps]# cd mysql5.7.26
[root@mini04 mysql5.7.26]# ll #只用这四个
total 196764
-rw-r--r-- 1 root root  25381952 Feb  4 10:37 mysql-community-client-5.7.26-1.el7.x86_64.rpm
-rw-r--r-- 1 root root    280904 Feb  4 10:37 mysql-community-common-5.7.26-1.el7.x86_64.rpm
-rw-r--r-- 1 root root   2274268 Feb  4 10:37 mysql-community-libs-5.7.26-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 173541272 Feb  4 10:37 mysql-community-server-5.7.26-1.el7.x86_64.rpm
[root@mini04 mysql5.7.26]# rpm -Uvh *
warning: mysql-community-client-5.7.26-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.26-1.e################################# [ 13%]
   2:mysql-community-libs-5.7.26-1.el7################################# [ 25%]
   3:mysql-community-client-5.7.26-1.e################################# [ 38%]
   4:mysql-community-server-5.7.26-1.e################################# [ 50%]
Cleaning up / removing...
   5:mysql-community-server-5.7.23-1.e################################# [ 63%]
   6:mysql-community-client-5.7.23-1.e################################# [ 75%]
   7:mysql-community-libs-5.7.23-1.el7################################# [ 88%]
   8:mysql-community-common-5.7.23-1.e################################# [100%]
[root@mini04 mysql5.7.26]# rpm -qa | grep mysql
mysql-community-libs-5.7.26-1.el7.x86_64
mysql-community-client-5.7.26-1.el7.x86_64
mysql-community-server-5.7.26-1.el7.x86_64
mysql-community-common-5.7.26-1.el7.x86_64

4、启动并更新

[root@mini04 mysql5.7.26]# systemctl start mysqld
[root@mini04 mysql5.7.26]# mysql_upgrade -uroot -p
Enter password:
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
The sys schema is already up to date (version 1.5.1).
Checking databases.
db2021.dept                                        OK
db2021.emp                                         OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

5、验证

[root@mini04 mysql5.7.26]# mysql -uroot -p123456
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)
mysql> select * from db2021.emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
|  8000 | KK     | SALESMAN  | 7839 | 2021-02-04 09:52:14 | 1500.00 |    NULL |     30 |
|  8001 | KB     | SALESMAN  | 7839 | 2021-02-04 09:53:45 | 1600.00 |    NULL |     30 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
16 rows in set (0.00 sec)

5.7.26 升级到8.0.16

1、升级

[root@mini04 mysql8.0.16]# mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
Enter password:
[root@mini04 mysql8.0.16]# systemctl stop mysqld
[root@mini04 mysql8.0.16]# ls
mysql-community-client-8.0.16-2.el7.x86_64.rpm  mysql-community-libs-8.0.16-2.el7.x86_64.rpm
mysql-community-common-8.0.16-2.el7.x86_64.rpm  mysql-community-server-8.0.16-2.el7.x86_64.rpm
[root@mini04 mysql8.0.16]# rpm -Uvh *
warning: mysql-community-client-8.0.16-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-8.0.16-2.e################################# [ 13%]
   2:mysql-community-libs-8.0.16-2.el7################################# [ 25%]
   3:mysql-community-client-8.0.16-2.e################################# [ 38%]
   4:mysql-community-server-8.0.16-2.e################################# [ 50%]
Cleaning up / removing...
   5:mysql-community-server-5.7.26-1.e################################# [ 63%]
   6:mysql-community-client-5.7.26-1.e################################# [ 75%]
   7:mysql-community-libs-5.7.26-1.el7################################# [ 88%]
   8:mysql-community-common-5.7.26-1.e################################# [100%]
[root@mini04 mysql8.0.16]# rpm -qa | grep mysql
mysql-community-common-8.0.16-2.el7.x86_64
mysql-community-client-8.0.16-2.el7.x86_64
mysql-community-server-8.0.16-2.el7.x86_64
mysql-community-libs-8.0.16-2.el7.x86_64
[root@mini04 mysql8.0.16]# systemctl start mysqld



-- 不用执行下面的 mysql_upgrade
[root@mini04 mysql8.0.16]# mysql_upgrade -uroot -p
Enter password:
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

2、验证

[root@mini04 mysql8.0.16]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from db2021.emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
|  8000 | KK     | SALESMAN  | 7839 | 2021-02-04 09:52:14 | 1500.00 |    NULL |     30 |
|  8001 | KB     | SALESMAN  | 7839 | 2021-02-04 09:53:45 | 1600.00 |    NULL |     30 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
16 rows in set (0.00 sec)