MySQL 升级:MySQL Shell 檢查升級

MySQL 升级:MySQL Shell 檢查升級

ShawnYan Lv.6

MySQL Shell 是一個功能強大的命令行界面,用於管理 MySQL 服務和進行各種操作。

其中一個有用的功能是它的升級檢查器(upgrade checker),它幫助用戶檢查當前 MySQL 服務實例與新版本之間的兼容性問題,從而減輕升級過程中的負擔。

以下是使用 MySQL Shell 檢查 MySQL 升級的一般步驟:

  1. 安裝 MySQL Shell:首先,確保你的系統上安裝了 MySQL Shell。

  2. 如果尚未安裝,你可以從 MySQL 官方網站下載並安裝適合你操作系統的版本。

本文以安裝 mysql-shell 8.0.36 爲例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@shawnyan ~ 12:19:23]$ yum install mysql-shell-8.0.36-1.el7.x86_64.rpm 
Loaded plugins: fastestmirror, langpacks
Examining mysql-shell-8.0.36-1.el7.x86_64.rpm: mysql-shell-8.0.36-1.el7.x86_64
Marking mysql-shell-8.0.36-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-shell.x86_64 0:8.0.36-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================
Package Arch Version
=======================================================================================
Installing:
mysql-shell x86_64 8.0.36-1.el7

Transaction Summary
=======================================================================================
Install 1 Package

Total size: 253 M
Installed size: 253 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-shell-8.0.36-1.el7.x86_64
Verifying : mysql-shell-8.0.36-1.el7.x86_64

Installed:
mysql-shell.x86_64 0:8.0.36-1.el7

Complete!
  1. 打開 MySQL Shell:在你的命令行或終端中,啟動 MySQL Shell,可以看到輸出版本號為 8.0.36。
1
2
3
4
5
6
7
8
9
10
[root@shawnyan ~ 12:19:52]$ mysqlsh
MySQL Shell 8.0.36

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > \q
Bye!
  1. 連接到 MySQL 服務:使用 mysqlsh 命令連接到你的 MySQL 服務實例。

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
[root@shawnyan ~ 12:20:52]$ mysqlsh --mysql -uroot -h localhost --py
Please provide the password for 'root@localhost': **********
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.36

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 8.0.35 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost Py > \s
MySQL Shell version 8.0.36

Connection Id: 12
Current schema:
Current user: root@localhost
SSL: Not in use.
Using delimiter: ;
Server version: 8.0.35 MySQL Community Server - GPL
Protocol version: Classic 10
Client library: 8.0.36
Connection: Localhost via UNIX socket
Unix socket: /var/lib/mysql/mysql.sock
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Result characterset: utf8mb4
Compression: Disabled
Uptime: 18 hours 4 min 24.0000 sec

Threads: 3 Questions: 27 Slow queries: 0 Opens: 152 Flush tables: 3 Open tables: 68 Queries per second avg: 0.000
  1. 使用升級檢查工具:

在 MySQL Shell 中,你可以使用 util.checkForServerUpgrade() 函數來檢查升級兼容性。

這個函數會返回一個報告,列出可能影響升級的問題,包括配置文件中的系統變量、已過時的功能、默認值更改等。

1
2
3
4
5
6
7
8
9
10
11
12
13
 MySQL  localhost  JS > util.checkForServerUpgrade();
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 8.0.35 - MySQL
Community Server - GPL, will now be checked for compatibility issues for
upgrade to MySQL 8.0.36...

1) Issues reported by 'check table x for upgrade' command
No issues found

Errors: 0
Warnings: 0
Notices: 0

No known compatibility errors or issues were found.

可以看到,當前 MySQL 服務器的版本為 8.0.35,升級到 8.0.36 沒有任何問題。

這期間,通過 general log 可以看到 mysql shell 執行了如下查詢。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
2024-03-21T04:54:14.645245Z	   18 Connect	root@localhost on  using Socket
2024-03-21T04:54:14.647414Z 18 Query SELECT CURRENT_USER()
2024-03-21T04:54:14.648546Z 18 Query SELECT PRIVILEGE_TYPE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE='\'root\'@\'localhost\'' LIMIT 1
2024-03-21T04:54:14.649930Z 18 Query SHOW PRIVILEGES
2024-03-21T04:54:14.650531Z 18 Query show GLOBAL variables where `variable_name` in ('activate_all_roles_on_login')
2024-03-21T04:54:14.655569Z 18 Query SELECT default_role_user, default_role_host FROM mysql.default_roles WHERE user='root' AND host='localhost'
2024-03-21T04:54:14.658020Z 18 Query show GLOBAL variables where `variable_name` in ('partial_revokes')
2024-03-21T04:54:14.659533Z 18 Query SHOW GRANTS FOR 'root'@'localhost'
2024-03-21T04:54:14.659955Z 18 Query select @@version, @@version_comment, UPPER(@@version_compile_os)
2024-03-21T04:54:14.660269Z 18 Query USE mysql
2024-03-21T04:54:14.660535Z 18 Query SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys')
2024-03-21T04:54:14.664652Z 18 Query CHECK TABLE `mysql`.`columns_priv` FOR UPGRADE
2024-03-21T04:54:14.665860Z 18 Query CHECK TABLE `mysql`.`component` FOR UPGRADE
2024-03-21T04:54:14.666866Z 18 Query CHECK TABLE `mysql`.`db` FOR UPGRADE
2024-03-21T04:54:14.667809Z 18 Query CHECK TABLE `mysql`.`default_roles` FOR UPGRADE
2024-03-21T04:54:14.668556Z 18 Query CHECK TABLE `mysql`.`engine_cost` FOR UPGRADE
2024-03-21T04:54:14.669643Z 18 Query CHECK TABLE `mysql`.`func` FOR UPGRADE
2024-03-21T04:54:14.670143Z 18 Query CHECK TABLE `mysql`.`general_log` FOR UPGRADE
2024-03-21T04:54:14.670547Z 18 Query CHECK TABLE `mysql`.`global_grants` FOR UPGRADE
2024-03-21T04:54:14.670895Z 18 Query CHECK TABLE `mysql`.`gtid_executed` FOR UPGRADE
2024-03-21T04:54:14.671253Z 18 Query CHECK TABLE `mysql`.`help_category` FOR UPGRADE
2024-03-21T04:54:14.671537Z 18 Query CHECK TABLE `mysql`.`help_keyword` FOR UPGRADE
2024-03-21T04:54:14.671873Z 18 Query CHECK TABLE `mysql`.`help_relation` FOR UPGRADE
2024-03-21T04:54:14.672134Z 18 Query CHECK TABLE `mysql`.`help_topic` FOR UPGRADE
2024-03-21T04:54:14.672421Z 18 Query CHECK TABLE `mysql`.`innodb_index_stats` FOR UPGRADE
2024-03-21T04:54:14.672829Z 18 Query CHECK TABLE `mysql`.`innodb_table_stats` FOR UPGRADE
2024-03-21T04:54:14.673244Z 18 Query CHECK TABLE `mysql`.`ndb_binlog_index` FOR UPGRADE
2024-03-21T04:54:14.674656Z 18 Query CHECK TABLE `mysql`.`password_history` FOR UPGRADE
2024-03-21T04:54:14.675465Z 18 Query CHECK TABLE `mysql`.`plugin` FOR UPGRADE
2024-03-21T04:54:14.676176Z 18 Query CHECK TABLE `mysql`.`procs_priv` FOR UPGRADE
2024-03-21T04:54:14.676949Z 18 Query CHECK TABLE `mysql`.`proxies_priv` FOR UPGRADE
2024-03-21T04:54:14.677612Z 18 Query CHECK TABLE `mysql`.`replication_asynchronous_connection_failover` FOR UPGRADE
2024-03-21T04:54:14.678347Z 18 Query CHECK TABLE `mysql`.`replication_asynchronous_connection_failover_managed` FOR UPGRADE
2024-03-21T04:54:14.679068Z 18 Query CHECK TABLE `mysql`.`replication_group_configuration_version` FOR UPGRADE
2024-03-21T04:54:14.679673Z 18 Query CHECK TABLE `mysql`.`replication_group_member_actions` FOR UPGRADE
2024-03-21T04:54:14.680428Z 18 Query CHECK TABLE `mysql`.`role_edges` FOR UPGRADE
2024-03-21T04:54:14.681366Z 18 Query CHECK TABLE `mysql`.`server_cost` FOR UPGRADE
2024-03-21T04:54:14.682284Z 18 Query CHECK TABLE `mysql`.`servers` FOR UPGRADE
2024-03-21T04:54:14.683046Z 18 Query CHECK TABLE `mysql`.`slave_master_info` FOR UPGRADE
2024-03-21T04:54:14.683939Z 18 Query CHECK TABLE `mysql`.`slave_relay_log_info` FOR UPGRADE
2024-03-21T04:54:14.684783Z 18 Query CHECK TABLE `mysql`.`slave_worker_info` FOR UPGRADE
2024-03-21T04:54:14.685483Z 18 Query CHECK TABLE `mysql`.`slow_log` FOR UPGRADE
2024-03-21T04:54:14.686066Z 18 Query CHECK TABLE `mysql`.`tables_priv` FOR UPGRADE
2024-03-21T04:54:14.686688Z 18 Query CHECK TABLE `mysql`.`time_zone` FOR UPGRADE
2024-03-21T04:54:14.687357Z 18 Query CHECK TABLE `mysql`.`time_zone_leap_second` FOR UPGRADE
2024-03-21T04:54:14.688046Z 18 Query CHECK TABLE `mysql`.`time_zone_name` FOR UPGRADE
2024-03-21T04:54:14.688604Z 18 Query CHECK TABLE `mysql`.`time_zone_transition` FOR UPGRADE
2024-03-21T04:54:14.689244Z 18 Query CHECK TABLE `mysql`.`time_zone_transition_type` FOR UPGRADE
2024-03-21T04:54:14.689878Z 18 Query CHECK TABLE `mysql`.`user` FOR UPGRADE
2024-03-21T04:54:14.690560Z 18 Quit
  1. 準備升級:

確認沒有阻礙升級的問題,你可以開始準備升級過程,包括備份數據庫、計劃維護時間窗口等。

由於是測試環境,接下來直接執行升級操作,即,從 MySQL 8.0.35 升級到 8.0.36。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@shawnyan ~ 16:48:07]$ yum install ./mysql-community-*
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-client.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-client-plugins.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-client-plugins.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-common.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-common.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-devel.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-devel.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-embedded-compat.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-embedded-compat.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-icu-data-files.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-icu-data-files.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-libs.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-libs.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-libs-compat.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-libs-compat.x86_64 0:8.0.36-1.el7 will be an update
---> Package mysql-community-server.x86_64 0:8.0.35-1.el7 will be updated
---> Package mysql-community-server.x86_64 0:8.0.36-1.el7 will be an update
--> Finished Dependency Resolution
...
Updated:
mysql-community-client.x86_64 0:8.0.36-1.el7
mysql-community-client-plugins.x86_64 0:8.0.36-1.el7
mysql-community-common.x86_64 0:8.0.36-1.el7
mysql-community-devel.x86_64 0:8.0.36-1.el7
mysql-community-embedded-compat.x86_64 0:8.0.36-1.el7
mysql-community-icu-data-files.x86_64 0:8.0.36-1.el7
mysql-community-libs.x86_64 0:8.0.36-1.el7
mysql-community-libs-compat.x86_64 0:8.0.36-1.el7
mysql-community-server.x86_64 0:8.0.36-1.el7

Complete!
  1. 升級后檢查,由於 MySQL 已將原有的 mysql_upgrade 功能内置到 mysqld 中,默認情況如果有需要升級,會自動執行。

通過 general 日志,可以看到,新版本的 MySQL 服務器在啓動時,自動創建了新表 innodb_redo_log_files

1
2
3
4
5
6
7
8
9
10
11
12
/usr/sbin/mysqld, Version: 8.0.36 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2024-03-21T08:49:45.542858Z 0 Execute CREATE TABLE performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on smaller levels than this value, have already consumed this file.'
)engine = 'performance_schema'

總結一下,MySQL Shell 是升級檢查利器,這只是 MySQL Shell 强大功能的冰山一角,還有更多强大的功能,歡迎使用。

– END –

logo.jpg

如果这篇文章为你带来了灵感或启发,就帮忙点『赞』or『在看』or『转发』吧,这对我非常重要,感谢!(๑˃̵ᴗ˂̵)

  • Title: MySQL 升级:MySQL Shell 檢查升級
  • Author: ShawnYan
  • Created at: 2024-03-21 21:00:00
  • Updated at: 2024-03-21 21:00:00
  • Link: https://shawnyan.cn/2024/mysql/mysql-upgrade-mysql-shell-check/
  • License: This work is licensed under CC BY-NC-SA 4.0.
 Comments
On this page