# /opt/mysql8.0/bin/mysqld_safe --defaults-file=/data/mysql8.0/my.cnf & mysqld_safe Logging to '/data/mysql8.0/log/alert.log' mysqld_safe Starting mysqld daemon with databases from /data/mysql8.0/data
从错误日志中，可以看到数据库自动进行了版本升级。
1 2
[System] [MY-013381] [Server] Server upgrade from '80032' to '80039' started. [System] [MY-013381] [Server] Server upgrade from '80032' to '80039' completed.
# /opt/mysql8.0/bin/mysqld_safe --defaults-file=/data/mysql8.0/my.cnf & mysqld_safe Logging to '/data/mysql8.0/log/alert.log' mysqld_safe Starting mysqld daemon with databases from /data/mysql8.0/data
这里我们想将版本降级到8.0.32，但是你会发现数据库无法启动。查看错误日志，可以看到数据库无法降级的信息“Cannot boot server version 80032 on data directory built by version 80039. Downgrade is not supported”。
1 2 3 4 5 6 7
[System] [MY-010116] [Server] /opt/mysql8.0/bin/mysqld (mysqld 8.0.32) starting as process 18932 [System] [MY-013576] [InnoDB] InnoDB initialization has started. [ERROR] [MY-013171] [InnoDB] Cannot boot server version 80032 on data directory built by version 80039. Downgrade is not supported mysqld: Can't open file: 'mysql.ibd' (errno: 0 - ) [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. [ERROR] [MY-010119] [Server] Aborting
MySQL 8.0.35版本开始才支持原地降级，我们来试一下将数据库降级到8.0.35版本。
1 2 3 4 5
# rm mysql8.0 # ln -s mysql-8.0.35-linux-glibc2.17-x86_64 mysql8.0 # /opt/mysql8.0/bin/mysqld_safe --defaults-file=/data/mysql8.0/my.cnf & mysqld_safe Logging to '/data/mysql8.0/log/alert.log'. mysqld_safe Starting mysqld daemon with databases from /data/mysql8.0/data
查看错误日志，可以看到数据库的版本从8.0.39降级到了8.0.35。
1 2 3 4 5 6 7
[System] [MY-010116] [Server] /opt/mysql8.0/bin/mysqld (mysqld 8.0.35) starting as process 949 [System] [MY-013576] [InnoDB] InnoDB initialization has started. [System] [MY-013577] [InnoDB] InnoDB initialization has ended. [System] [MY-014064] [Server] Server downgrade from '80039' to '80035' started. [System] [MY-014064] [Server] Server downgrade from '80039' to '80035' completed. ...... [Server] /opt/mysql8.0/bin/mysqld: ready for connections. Version: '8.0.35' socket: '/data/mysql8.0/run/mysql.sock' port: 3380 MySQL Community Server - GPL.
连接到数据库后，可以看到版本确实是8.0.35。
1 2 3 4 5 6 7 8
# mysql -uroot -h127.0.0.1 -pabc123 -P3380 Server version: 8.0.35 MySQL Community Server - GPL mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.35 | +-----------+
原地升级/降级在数据库内部做了什么？
MySQL 8.0.16版本开始，启动数据库时，如果软件版本比数据库的实际版本更高，默认会自动进行升级操作。如果是要升级到8.0.16之前的版本，需要执行mysql_upgrade命令，由于这些版本太老了，我们这里就不做介绍了。
mysql> SET SESSION debug='+d,skip_dd_table_access_check'; mysql> select substring(convert(properties using utf8mb4),1,256) as prop from mysql.dd_properties\G *************************** 1. row *************************** prop: DD_VERSION=80023;IS_VERSION=80030;LCTN=0;MINOR_DOWNGRADE_THRESHOLD=80023;MYSQLD_VERSION=80032;MYSQLD_VERSION_HI=80032;MYSQLD_VERSION_LO=80032;MYSQLD_VERSION_UPGRADED=80032;PS_VERSION=80032;SDI_VERSION=80019;.......
mysql> SET SESSION debug='+d,skip_dd_table_access_check'; Query OK, 0 rows affected (0.00 sec) mysql> select a.name, b.name from schemata a, tables b where a.id = b.schema_id and b.hidden='System' and type='BASE TABLE'; +--------------------+------------------------------+ | name | name | +--------------------+------------------------------+ | mysql | dd_properties | | mysql | innodb_dynamic_metadata | | mysql | innodb_ddl_log | | mysql | catalogs | | mysql | character_sets | | mysql | check_constraints | ......
[System] [MY-013576] [InnoDB] InnoDB initialization has started. [System] [MY-013577] [InnoDB] InnoDB initialization has ended. [ERROR] [MY-013377] [Server] Server shutting down because upgrade is required, yet prohibited by the command line option '--upgrade=NONE'. [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
MySQL 8.0 InnoDB有很大的变化。Undo段放到了单独的Undo表空间中。Redo文件的存储方式也发生了变化，从原先的ib_logfile转到了#innodb_redo目录下。DoubleWrite Buffer拆分到了单独的dblwr文件。8.0还新增了临时表空间。这些变化，对比升级前后datadir下的文件和目录，也能看出来。
MySQL 8.0无法直接原地降级到5.7。你需要使用数据导入导出的方法，或者使用5.7版本的备份文件来恢复数据。
MySQL 5.6升级到8.0
MySQL 5.6或更早的版本无法直接升级到8.0，如果你使用8.0的软件启动5.6的库，可以看到类似下面这样的报错信息。
1 2 3 4 5 6 7 8
[System] [MY-010116] [Server] /opt/mysql8.0/bin/mysqld (mysqld 8.0.39-debug) starting as process 26388 [System] [MY-011012] [Server] Starting upgrade of data directory. [System] [MY-013576] [InnoDB] InnoDB initialization has started. [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (v0). The redo log was created before MySQL 5.7.9 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted at srv0start.cc[1856] with error Generic error. [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine. [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. [ERROR] [MY-010119] [Server] Aborting
# mysql -h127.0.0.1 -P3356 -uroot -e 'set global innodb_fast_shutdown=0' # /opt/mysql5.6/bin/mysqladmin -uroot -h127.0.0.1 -P3356 shutdown
使用5.7版本的软件启动数据库
1 2 3
/opt/mysql5.7/bin/mysqld_safe --defaults-file=/data/mysql5.6/my_57.cnf & mysqld_safe Logging to '/data/mysql5.6/log/alert.log'. Starting mysqld daemon with databases from /data/mysql5.6/data
[ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16). [ERROR] mysql.user has no `Event_priv` column at position 28 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. [Note] /opt/mysql5.7/bin/mysqld: ready for connections. Version: '5.7.41-log' socket: '/data/mysql5.6/run/mysql.sock' port: 3356 MySQL Community Server (GPL)
运行mysql_upgrade
数据库启动后，执行mysql_upgrade，升级系统表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
# /opt/mysql5.7/bin/mysql_upgrade -h 127.0.0.1 -P3356 Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. mysql.columns_priv OK ...... Upgrading the sys schema. Checking databases. employees.departments OK employees.dept_emp OK ...... Warning : Trigger sakila.rental.rental_date does not have CREATED attribute. status : OK sakila.staff OK ...... Upgrade process completed successfully. Checking if update is needed.
/opt/mysql5.7/bin/mysql_upgrade -h 127.0.0.1 -P3355 -uroot Checking if update is needed. ...... sakila.actor error : Table rebuild required. Please do "ALTER TABLE `actor` FORCE" or dump/reload to fix it! sakila.address error : Table rebuild required. Please do "ALTER TABLE `address` FORCE" or dump/reload to fix it! ...... Repairing tables sakila.customer Note : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. status : OK sakila.film Note : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. status : OK ...... `sakila`.`actor` Running : ALTER TABLE `sakila`.`actor` FORCE status : OK `sakila`.`address` Running : ALTER TABLE `sakila`.`address` FORCE status : OK
/opt/mysql5.7/bin/mysqld_safe --defaults-file=/data/mysql5.7_upgrade/my.cnf & mysqld_safe Logging to '/data/mysql5.7_upgrade/log/alert.log'. mysqld_safe Starting mysqld daemon with databases from /data/mysql5.7_upgrade/data
1 2 3
# tail -2 /data/mysql5.7_upgrade/log/alert.log [Note] /opt/mysql5.7/bin/mysqld: ready for connections. Version: '5.7.38-log' socket: '/data/mysql5.7_upgrade/run/mysql.sock' port: 3357 MySQL Community Server (GPL)
