窮得只剩技術

依照官方建議一步一步來升級吧

檢查是否能升級

首先先檢查是否能升級

mysqlcheck -u root -p --all-databases --check-upgrade

我運氣不錯，全部都是ok

按照官方文件

不能有在不支援partition的engine做partition

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';

也不能有table名字是8.0的保留字

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'check_constraints',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);

也不能有foreign key名字長度大於64

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
  (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
               INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
   FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
   WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

備份資料

直接使用MySQLDump

mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

再備份原有的設定檔

cd ~
tar zcvf mysql_config.tar.gz /etc/mysql/

設定新版本的APT倉庫

直接從官方下載

再用dpkg指令安裝

dpkg -i mysql-apt-config_0.8.13-1_all.deb
apt-get update

安裝新版本

首先停止現有的MySQL Service

service mysql stop

再安裝新版本

apt-get install mysql-server

安裝過程中我都使用新版本的config

避免舊的config被移掉而無法啟動

安裝完成後如果直接啟動會有以下錯誤

2019-05-06T07:05:33.706606Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.16) starting as process 5145
2019-05-06T07:05:34.129029Z 4 [System] [MY-013381] [Server] Server upgrade from '50700' to '80016' started.
2019-05-06T07:05:36.525407Z 4 [ERROR] [MY-013384] [Server] Could not create server upgrade info file at '/var/lib/mysql/'.
2019-05-06T07:05:36.532786Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2019-05-06T07:05:36.533010Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-05-06T07:05:37.952071Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.16)  MySQL Community Server - GPL.

Google後發現是權限問題

將底下檔案owner都改為 mysql即可

chown mysql:mysql /var/lib/mysql/mysql_upgrade_info

之後再重新啟動

service mysql start