MYSQL安裝教程 mysql最新版安裝配置教程

2026-04-06 小知識 mysql安裝教程

文章插圖

MYSQL安裝教程 mysql最新版安裝配置教程

文章插圖
升級版本選擇原則和建議MySQL的升級需要遵循以下幾條原則和建議：支持從MySQL5.7升級到8.0，注意僅支持GA版本之間的升級。不支持跨大版本的升級，如從5.6升級到8.0是不支持的。建議升級大版本前先升級到當前版本的最近小版本，如5.7先升級到5.7.35后再升級到8.0 。在大版本內部GA的小版本可以直接升級，如從8.0.9直接升級到8.0.26 。MySQL8.0的一些變化
在升級到8.0之前需要充分考慮版本變化帶來的使用兼容性的問題，其中不兼容的部分需要特別關注，建議升級大版本前做充分的測試。下面簡單介紹下MySQL8.0的部分變化。
數據字典
不同于之前的版本將字典數據存儲在元數據文件和非事務系統表中，MySQL8.0將包含數據庫對象的全局數據字典存儲在事務表中。在使用上如果設置了innodb_read_only 參數會導致所有表的創建、刪除、analyze、修改表引擎操作無法執行。CREATE TABLE dst_tbl LIKE src_tbl 要求src_tbl必須是base table 。mysqldump和mysqlpump不會導出information_schema，不會導出MySQL Schema中數據字典表，需導出存儲過程和事件需指定–routines和–events選項，并且用戶需要全局selet權限。
Authentication Plugin
MySQL8.0將默認身份驗證插件從mysql_native_password變更為caching_sha2_password，客戶端需要驗證現有版本是否支持。
Configuration ChangesMySQL8.0開始只有InnoDB和NDB引擎支持分區表，升級前需確保不存在非InnoDB引擎的分區表。部分error code被啟用，詳見https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals默認字符集從latin1變更為utf8mb4，默認排序規則為utf8mb4_0900_ai_ci 。注意這可能會導致新舊數據庫對象的字符集不一致，造成隱式類型轉換的問題。8.0.11版本開始,如使用與初始化配置不同的lower_case_table_names值啟動數據庫時會報錯。[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (‘0’) and data dictionary (‘1’).[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.[ERROR] [MY-010119] [Server] AbortingServer Changes8.0.11開始部分賬戶管理功能被刪除，如Grant命令修改用戶非權限特性，NO_AUTO_CREATE_USER模式，PASSWORD() 函數和 old_passwords 系統變量。8.0.11開始刪除了部分兼容 SQL 模式：DB2、MAXDB、MSSQL、MYSQL323、MYSQL40、ORACLE、POSTGRESQL、NO_FIELD_OPTIONS、NO_KEY_OPTIONS、NO_TABLE_OPTIONS 。從 MySQL 8.0.3 開始，空間數據類型允許 SRID 屬性，以明確指示存儲在列中的值的空間參考系統 (SRS) 。并刪除了部分非ST_前綴的空間函數。詳見https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals在線切換binlog format增加了更多限制。InnoDB Changesinformation_Schema中，innodb_sys_% 改名為 innodb_%zlib庫版本從1.2.3升級到1.2.11 。只讀變量innodb_directories應該包含file-per-table和絕對路徑創建的通用表空間文件的路徑。undo日志從系統表空間移出。默認會在innodb_undo_directory指定位置（未指定則在data dir）創建兩個undo表空間。從5.7升級到8.0時innodb-fast-shutdown需設置成0 。8.0.17開始在創建表空間時，路徑不允許含 circular directory reference(/../)；升級前可以查詢INFORMATION_SCHEMA.INNODB_DATAFILES表檢查表空間路徑。mysql> CREATE TABLESPACE ts11 ADD DATAFILE ‘/data/mysql/log/test/../ts11.ibd’;ERROR 3121 (HY000): The ADD DATAFILE filepath cannot contain circular directory references.mysql> CREATE TABLESPACE ts11 ADD DATAFILE ‘/data/mysql/log/ts11.ibd’; Query OK, 0 rows affected (10.02 sec)SQL changegroup by 不再支持DESC和ASC，GROUP BY {col_name | expr | position}, … [WITH ROLLUP]]保留字變更。https://dev.mysql.com/doc/refman/8.0/en/keywords.html升級后建議測試optimizer hints，在8.0中部分hint可能不再適用。部分默認配置的變化。升級過程包含哪些操作
升級MySQL時需要對現版本MySQL中的數據字典和元數據等進行更新。在MySQL Schema中需更新數據字典表和系統表，在其他Schema中需要更新一些內置的MySQL持有的表，如performance_Schema、information_schema和sys schema等。
升級過程大致分為兩個部分，升級數據字典和升級服務。
升級數據字典包括升級MySQL Schema中的數據字典表，以及performance schema，information_schema 。升級數據字典表時，如當前版本低于期望版本，則服務器將創建新版本的數據字典表并將持久化的元數據拷貝到新表，在新舊表做原子性替換后重新初始化數據字典。服務器啟動后會按需執行，可以使用 –upgrade=NONE（8.0.16以后）或–no-dd-upgrade （8.0.16之前）參數啟動MySQL，阻止數據字典表的升級。升級服務及其余升級任務，包括MySQL Schema中的非數據字典表，sys schema和用戶schema 。
在8.0.16之前需使用mysql_upgrade執行除數據字典表外的其余升級步驟，在8.0.16以后該步驟由MySQL服務在啟動后執行。MySQL服務會根據升級到的版本以及in-place或logical升級的指示確定是否執行所有的升級步驟。
8.0.16開始啟動參數–upgrade= 控制MySQL服務在啟動時執行自動升級的動作。

--upgrade=AUTO MySQL升級所有過時的內容--upgrade=NONE MySQL跳過升級步驟，可能會導致報錯--upgrade=MINIMAL MySQL在必要時升級數據字典表，information_schema和information_schema 。這可能會導致部分功能不能正常使用，例如MGR 。--upgrade=FORCE MySQL會升級所有的內容，這會檢查所有schema的所有對象，導致MySQL需要更長的時間啟動 。此模式下MySQL會重新創建系統表 if they are missing 。

升級前的檢查
在執行升級操作前需要做一些檢查工作，確認準備工作是否就緒，避免升級過程中出現異常。
可以使用MySQL Shell使用util.checkForServerUpgrade進行檢查，返回內容包括不符合遷移要求的問題，error的問題需要遷移前修改。
下面的例子中就存在一個不兼容的問題，ymh.t1表是一個MyISAM引擎的分區表，需將該表引擎調整為innodb后方可升級。

MySQLJS > util.checkForServerUpgrade([email protected]:3307', {"password":"XXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"})The MySQL server at 127.0.0.1:3307, version 5.7.23-log - MySQL Community Server(GPL), will now be checked for compatibility issues for upgrade to MySQL8.0.26...1) Usage of old temporal typeNo issues found2) Usage of db objects with names conflicting with new reserved keywordsNo issues found3) Usage of utf8mb3 charsetNo issues found4) Table names in the mysql schema conflicting with new tables in 8.0No issues found5) Partitioned tables using engines with non native partitioningError: In MySQL 8.0 storage engine is responsible for providing its ownpartitioning handler, and the MySQL server no longer provides genericpartitioning support. InnoDB and NDB are the only storage engines thatprovide a native partitioning handler that is supported in MySQL 8.0. Apartitioned table using any other storage engine must be altered—either toconvert it to InnoDB or NDB, or to remove its partitioning—before upgradingthe server, else it cannot be used afterwards.More information:https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-configuration-changesymh.t1 - MyISAM engine does not support native partitioning6) Foreign key constraint names longer than 64 charactersNo issues found7) Usage of obsolete MAXDB sql_mode flagNo issues found8) Usage of obsolete sql_mode flagsNo issues found9) ENUM/SET column definitions containing elements longer than 255 charactersNo issues found10) Usage of partitioned tables in shared tablespacesNo issues found11) Circular directory references in tablespace data file pathsNo issues found12) Usage of removed functionsNo issues found13) Usage of removed GROUP BY ASC/DESC syntaxNo issues found14) Removed system variables for error logging to the system log configurationNo issues found15) Removed system variablesNo issues found16) System variables with new default valuesWarning: Following system variables that are not defined in yourconfiguration file will have new default values. Please review if you rely ontheir current values and if so define them before performing upgrade.More information:https://mysqlserverteam.com/new-defaults-in-mysql-8-0/back_log - default value will changecollation_server - default value will change from latin1_swedish_ci toutf8mb4_0900_ai_cievent_scheduler - default value will change from OFF to ONexplicit_defaults_for_timestamp - default value will change from OFF to ONinnodb_autoinc_lock_mode - default value will change from 1 (consecutive) to2 (interleaved)innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10(%)innodb_undo_log_truncate - default value will change from OFF to ONinnodb_undo_tablespaces - default value will change from 0 to 2log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)max_error_count - default value will change from 64 to 1024optimizer_trace_max_mem_size - default value will change from 16KB to 1MBperformance_schema_consumer_events_transactions_current - default value willchange from OFF to ONperformance_schema_consumer_events_transactions_history - default value willchange from OFF to ONtransaction_write_set_extraction - default value will change from OFF toXXHASH6417) Zero Date, Datetime, and Timestamp valuesWarning: By default zero date/datetime/timestamp values are no longer allowedin MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included inSQL_MODE by default. These modes should be used with strict mode as they willbe merged with strict mode in a future release. If you do not include thesemodes in your SQL_MODE setting, you are able to insertdate/datetime/timestamp values that contain zeros. It is strongly advised toreplace zero values with valid ones, as they may not work correctly in thefuture.More information:https://lefred.be/content/mysql-8-0-and-wrong-dates/global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATEwhich allows insertion of zero datessession.sql_mode -of 1 session(s) does not contain either NO_ZERO_DATE orNO_ZERO_IN_DATE which allows insertion of zero dates18) Schema inconsistencies resulting from file removal or corruptionNo issues found19) Tables recognized by InnoDB that belong to a different engineNo issues found20) Issues reported by 'check table x for upgrade' commandNo issues found21) New default authentication plugin considerationsWarning: The new default authentication plugin 'caching_sha2_password' offersmore secure password hashing than previously used 'mysql_native_password'(and consequent improved client connection authentication). However, it alsohas compatibility implications that may affect existing MySQL installations.If your MySQL installation must serve pre-8.0 clients and you encountercompatibility issues after upgrading, the simplest way to address thoseissues is to reconfigure the server to revert to the previous defaultauthentication plugin (mysql_native_password). For example, use these linesin the server option file:[mysqld]default_authentication_plugin=mysql_native_passwordHowever, the setting should be viewed as temporary, not as a long term orpermanent solution, because it causes new accounts created with the settingin effect to forego the improved authentication security.If you are using replication please take time to understand how theauthentication plugin changes may impact you.More information:https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issueshttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replicationErrors:1Warnings: 17Notices:01 errors were found. Please correct these issues before upgrading to avoid compatibility issues.#修改t1表引擎為InnoDBmysql> alter table t1 engine=innodb;Query OK, 0 rows affected (0.02 sec)Records: 0Duplicates: 0Warnings: 0

MySQL Shell的util.checkForServerUpgrade工具主要做了以下檢查，當然我們也可以手動進行相關的檢查。
不應存在以下問題使用過時的數據類型和函數的表。如5.6.4之前的不支持小數點的時間格式。需在執行In-place升級前執行repair table修復。沒有孤立的.frm文件。觸發器不能缺失或空的definer或無效的creation context ?？蓤绦幸韵旅顧z查上述問題：mysqlcheck -u root -p –all-databases –check-upgrade不應存在非InnoDB或NDB引擎的分區表，如存在需變更引擎或轉換成非分區表 ?？赏ㄟ^以下SQL檢查：

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

檢查是否使用了8.0新增的保留字。MySQL Schema中不應存在8.0數據字典表同名的表，可用以下SQL檢查：

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' );

不得有外鍵約束名稱長度超過 64 個字符的表，如存在則刪除后重建。可用以下SQL檢查：

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_FOREIGNWHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

sql_mode系統參數不能含前面提到棄用模式。視圖中不能有顯式定義超過64個字符的列名。可查看INFORMATION_SCHEMA.VIEWS檢查。表和存儲過程中單個枚舉和SET列元素不能超過255個字符或1020個字節。升級到8.0.13版本前，包括系統表空間和通用表空間在內的共享表空間中不能存在表分區。

#5.7版本以下SQL檢查SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESWHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';#8.0早期版本以下SQL檢查SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESWHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single'; #如存在，可使用reorganize partition命令將共享表空間中的分區遷移到 file-per-table表空間 ALTER TABLE $table_name REORGANIZE PARTITION $partition_nameINTO (partition_definition TABLESPACE=innodb_file_per_table);

查詢和存儲過程中不能在group by子句中使用ASC或者DESC 。不能使用8.0中不支持的功能和配置參數。https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals從 MySQL 8.0.19 開始，如果lower_case_table_names=1，升級過程會檢查表和模式名稱以確保所有字符都是小寫。如果發現表或架構名稱包含大寫字符，升級過程將失敗并顯示錯誤。

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);

如果出現上述問題導致的升級失敗，MySQL會將變更還原，這時刪除redo log并重啟5.7版本實例即可。注意關閉前一定設置innodb_fast_shutdown=0 。
Linux系統升級Binary或Package-based安裝的MySQL
在這種場景下可以選擇in-place或者logical方式進行升級。
in-place升級
關閉現有版本MySQL，將二進制或包替換成新版本并在現有數據目錄上啟動MySQL并執行升級任務的方式，稱為in-place升級。升級過程分為以下幾步：
完成升級前檢查，并處理不合規問題。如果使用了XA事務，升級前通過命令xa recover未提交XA事務，并使用xa commit或xa rollback命令提交或回滾。如當前版本低于5.7.11且使用了加密表空間，升級前輪換keyring的master keyALTER INSTANCE ROTATE INNODB MASTER KEY 。將innodb_fast_shutdown改為0或1 。關閉現版本MySQL 。升級MySQL二進制文件或軟件包。在現有數據目錄上啟動新版本MySQL 。如果有加密的 InnoDB 表空間，請使用 –early-plugin-load選項加載keyring插件。如升級失敗請刪除redolog，啟動5.7版本并修復錯誤，設置innodb_fast_shutdown為0后關閉MySQL 。再使用8.0版本MySQL啟動。如目標版本小于8.0.16，MySQL啟動后還需執行mysql_upgrade后重啟MySQL 。
如下所示：

#當前版本為5.7.23mysql> select @@global.version;+------------------+| @@global.version |+------------------+| 5.7.23-log|+------------------+1 row in set (0.01 sec)#使用mysql shell 命令util.checkForServerUpgrade([email protected]:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 檢查升級到目標版本8.0.26，確認沒有error級別的問題Errors:0Warnings: 17Notices:0No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.#檢查沒有未提交的xa事務mysql> xa recover;Empty set (0.00 sec)#將innodb_fast_shutdown改為0或1mysql> set global innodb_fast_shutdown=0;select @@global.innodb_fast_shutdown;Query OK, 0 rows affected (0.00 sec)+-------------------------------+| @@global.innodb_fast_shutdown |+-------------------------------+|0 |+-------------------------------+1 row in set (0.00 sec)#關閉MySQLmysql> shutdown;Query OK, 0 rows affected (0.00 sec)#因目標版本8.0.26，直接在現有數據目錄上啟動新版本MySQL 。由MySQL服務執行升級任務，可指定--upgrade=FORCE參數[[email protected] ~]# cd /usr/local/mysql-8.0.26/bin/[[email protected] bin]# ./mysqld_safe--defaults-file=/etc/my3307.cnf --user=mysql --upgrade=FORCE &[1] 106547[[email protected] bin]#mysqld_safe Adding '/usr/lib/libtcmalloc.so' to LD_PRELOAD for mysqld2021-10-15T03:24:11.019020Z mysqld_safe Logging to '/data/mysql3307/log/mysqld.err'.2021-10-15T03:24:11.073416Z mysqld_safe Starting mysqld daemon with databases from /data/mysql3307/data#啟動后查看當前服務版本，確認已升級到目標版本mysql> \s--------------mysqlVer 8.0.26 for Linux on x86_64 (Source distribution)Connection id:11Current database:Current user:[email protected]:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256Current pager:stdoutUsing outfile:''Using delimiter:;Server version:8.0.26-debug Source distributionProtocol version:10Connection:127.0.0.1 via TCP/IPServer characterset:utf8mb4Dbcharacterset:utf8mb4Client characterset:utf8mb4Conn.characterset:utf8mb4TCP port:3307Binary data as:HexadecimalUptime:2 min 39 secThreads: 2Questions: 11Slow queries: 0Opens: 656Flush tables: 4Open tables: 35Queries per second avg: 0.069--------------

logical升級
邏輯升級是指使用邏輯備份從舊版本MySQL中導出數據，安裝新版本MySQL并導入數據的升級方式。由于可能存在的不兼容問題會導致導入失敗，導出前需要做升級前檢查，導入前可能還需要對備份文件進行修改。
升級步驟如下：
對舊版本數據做全量導出。mysqldump -u root -p –add-drop-table –routines –events –all-databases –force > data-for-upgrade.sql關閉當前版本數據庫。安裝8.0版本數據庫并初始化（從error log中獲取’root’@’localhost’用戶初始密碼）。在新的數據目錄中啟動MySQL8.0，并重置初始密碼。將備份文件導入mysql -u root -p –force < data-for-upgrade.sql 。如導出文件包含系統表，則不建議導入時開啟GTID（gtid_mode=ON）。執行剩余的升級操作。目標版本大于8.0.16時需重啟服務器，啟動時使用–upgrade=FORCE參數。8.0.16之前的版本先執行mysql_upgrade再重啟MySQL 。

#8.0.16以后的版本mysqladmin -u root -p shutdownmysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE &#8.0.16之前的版本mysql_upgrade -u root -pmysqladmin -u root -p shutdownmysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &#確認升級成功后，mysql schema中兩張不再使用的表可以自行清理DROP TABLE mysql.event;DROP TABLE mysql.proc;

上一篇：上班族搞什么副業賺點小錢干點什么副業能掙錢

下一篇：電腦音響滋滋響怎么回事電腦音響滋滋滋的響怎么辦