依照官方建議一步一步來升級吧
檢查是否能升級
首先先檢查是否能升級
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