2.Mysql升级
1.背景介绍
当前随着Mysql版本的迭代，Mysql的性能也是越来越好且新版中又添加了新的特性，因此对于目前服务器上的低版本Mysql升级也是一件刻不容缓的事情了！
2.升级前知道的事情
-
支持从 MySQL 5.7 升级到 8.0。但是，仅在正式发布 (GA) 版本之间支持升级。对于 MySQL 8.0，需要从 MySQL 5.7 GA 版本（5.7.9 或更高版本）升级。不支持从 MySQL 5.7 的非 GA 版本升级。
-
在升级到下一个版本之前，建议升级到最新版本。例如，在升级到 MySQL 8.0 之前升级到最新的 MySQL 5.7 版本。
-
不支持跳过版本的升级。例如，不支持直接从 MySQL 5.6 升级到 8.0。
-
一旦发布系列达到正式发布 (GA) 状态，就支持在发布系列内升级（从一个 GA 版本到另一个 GA 版本）。例如，从 MySQL 8.0 升级。
x到 8.0。y支持。（不支持涉及处于开发状态的非 GA 版本的升级。）还支持跳过版本。例如，从 MySQL 8.0 升级。x到 8.0。z支持。MySQL 8.0.11 是 MySQL 8.0 版本系列中的第一个 GA 状态版本。
3.Mysql升级主要的事情
第一步：检查当前Mysql版本下的数据库是否存在问题
-->mysqlcheck -u root -p -S /data/3307/mysql.sock --all-databases --check-upgrade
第二步：不得有使用不具有本机分区支持的存储引擎的分区表。要识别此类表，请执行以下查询
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
.....(省略)
具体查看官网升级前要做的检查！ https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
3.1 Mysql升级预检查
在上面主要介绍了mysql从低版本升级到高版本的一个预检查，这种就需要我们按照上面的步骤一步一步的进行操作检查了，那么有没有一种比较简单的检查呢？ 答案是有的，可以通过mysql shell内置的函数进行预检查。如果我们要想把mysql从5.7.21 升级到 ---> 8.0.25, 可以按照如下方法进行预检查。
- 下载安装一个和要升级mysql目标版本的mysql shell(通常用二进制包安装)，比如我这里要升级到8.0.25，就需要下载一个mysql shell(8.0.25),下载地址可以去官网：https://downloads.mysql.com/archives/shell/
- 登录mysqlshell,然后通过mysqlshell 连上mysql server(5.7.21) 使用util.checkForServerUpgrade()检查
[root@db04 bin]# mysqlsh MySQL Shell 8.0.25 Copyright (c) 2016, 2021, 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 > MySQL JS > MySQL JS > shell.connect('root@localhost:3306') # 这里是连接登录到mysql server(5.7.21)
- 通过内置的函数：util.checkForServerUpgrade()
util.checkForServerUpgrade('root@db04:3306', {"password":"123", "targetVersion":"8.0.25", "configPath":"/data/mysql/my.cnf"})
这里要写上要检查的mysql instance 的用户名、密码、端口号、目标版本、以及配置文件，显示结果如下：
[root@db04 bin]# ./mysqlsh MySQL Shell 8.0.25 Copyright (c) 2016, 2021, 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 > util.checkForServerUpgrade('root@db04:3306', {"password":"123", "targetVersion":"8.0.25", "configPath":"/data/mysql/my.cnf"}) Util.checkForServerUpgrade: Can't connect to MySQL server on 'db04:3306' (113) (MySQL Error 2003) MySQL JS > util.checkForServerUpgrade('root@192.168.10.135:3306', {"password":"123", "targetVersion":"8.0.25", "configPath":"/data/mysql/my.cnf"}) The MySQL server at 192.168.10.135:3306, version 5.7.21 - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.25... 1) Usage of old temporal type No issues found 2) Usage of db objects with names conflicting with new reserved keywords No issues found 3) Usage of utf8mb3 charset No issues found 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found 5) Partitioned tables using engines with non native partitioning No issues found 6) Foreign key constraint names longer than 64 characters No issues found 7) Usage of obsolete MAXDB sql_mode flag No issues found 8) Usage of obsolete sql_mode flags Notice: The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgrade to 8.0. More information: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER option 9) ENUM/SET column definitions containing elements longer than 255 characters No issues found 10) Usage of partitioned tables in shared tablespaces No issues found 11) Circular directory references in tablespace data file paths No issues found 12) Usage of removed functions No issues found 13) Usage of removed GROUP BY ASC/DESC syntax No issues found 14) Removed system variables for error logging to the system log configuration No issues found 15) Removed system variables No issues found 16) System variables with new default values Warning: Following system variables that are not defined in your configuration file will have new default values. Please review if you rely on their 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 change character_set_server - default value will change from latin1 to utf8mb4 collation_server - default value will change from latin1_swedish_ci to utf8mb4_0900_ai_ci event_scheduler - default value will change from OFF to ON explicit_defaults_for_timestamp - default value will change from OFF to ON innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to 2 (interleaved) innodb_flush_method - default value will change from NULL to fsync (Unix), unbuffered (Windows) innodb_flush_neighbors - default value will change from 1 (enable) to 0 (disable) innodb_max_dirty_pages_pct - default value will change from 75 (%) 90 (%) 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 ON innodb_undo_tablespaces - default value will change from 0 to 2 log_bin - default value will change from OFF to ON log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning) log_slave_updates - default value will change from OFF to ON master_info_repository - default value will change from FILE to TABLE max_allowed_packet - default value will change from 4194304 (4MB) to 67108864 (64MB) max_error_count - default value will change from 64 to 1024 optimizer_trace_max_mem_size - default value will change from 16KB to 1MB performance_schema_consumer_events_transactions_current - default value will change from OFF to ON performance_schema_consumer_events_transactions_history - default value will change from OFF to ON relay_log_info_repository - default value will change from FILE to TABLE server_id - default value will change from 0 to 1 slave_rows_search_algorithms - default value will change from 'INDEX_SCAN, TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN' table_open_cache - default value will change from 2000 to 4000 transaction_write_set_extraction - default value will change from OFF to XXHASH64 17) Zero Date, Datetime, and Timestamp values No issues found 18) Schema inconsistencies resulting from file removal or corruption No issues found 19) Tables recognized by InnoDB that belong to a different engine No issues found 20) Issues reported by 'check table x for upgrade' command No issues found 21) New default authentication plugin considerations Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file: [mysqld] default_authentication_plugin=mysql_native_password However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security. If you are using replication please take time to understand how the authentication 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-issues https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication Errors: 0 Warnings: 27 Notices: 1 No 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. MySQL JS >
然后根据上面的error，warning信息进行修改，修改完了之后，再进行升级
- 上面检查完毕之后，对对应的地方进行，重复用上面的函数进行检查，直到无error出现，warning这个可以忽略，然后下载mysql server(8.0.25)安装包，解压缩，修改权限等等，而且其中一个比较重要的一个地方就是修改my.cnf中的basedir这个路径，将其改成mysql server(8.0.25)包的路径，修改完了之后在进行启动，可以从日志中看到升级信息
2023-04-23T01:31:57.307505Z 0 [System] [MY-010116] [Server] /usr/local/src/mysql80/bin/mysqld (mysqld 8.0.25) starting as process 4925 2023-04-23T01:31:57.320157Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2023-04-23T01:31:57.320260Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-04-23T01:31:58.513716Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-04-23T01:31:59.092550Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock 2023-04-23T01:32:00.112038Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. 2023-04-23T01:32:01.158670Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80025' started. 2023-04-23T01:32:07.571482Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80025' completed. 2023-04-23T01:32:08.100586Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-04-23T01:32:08.100987Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2023-04-23T01:32:08.320759Z 0 [System] [MY-010931] [Server] /usr/local/src/mysql80/bin/mysqld: ready for connections. Version: '8.0.25' socket: '/data/mysql/data/mysql.sock' port: 3306 MySQL Community Server - GPL.
显示结果中：server upgrade from '50700' to '80025' commplete，表示升级完成！
参考：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
4.开始升级
方法1：in-place upgrade(就地升级) 5.7-->8.0
第一步：如果您通常运行配置为innodb_fast_shutdownset to 2(cold shutdown) 的MySQL 服务器 ，请通过执行以下任一语句将其配置为执行快速或慢速关机
SET GLOBAL innodb_fast_shutdown = 1; -- fast shutdown SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown
第二步：关闭旧的 MySQL 服务器。例如：
-->mysqladmin -u root -p shutdown
第三步：下载新版版的Mysql（比如我的：这里可以将软连接重新指到最新版本的Mysql上）
第四步：使用现有数据目录启动 MySQL 8.0 服务器。例如
-->mysqld_safe --user=mysql --datadir=/path/to/existing-datadir & ##这里的datadir数据目录是旧版本5.7的数据目录路径
第五步: MySQL 8.0服务器启动成功后，执行Mysql_upgrade执行剩下的升级任务
-->mysql_upgrade -u root -p
第六步：然后关闭并重新启动 MySQL 服务器以确保对系统表所做的任何更改生效。例如：
-->mysqladmin -u root -p shutdown
-->mysqld_safe --user=mysql --datadir=/path/to/existing-datadir &
方法2： logical upgrade(逻辑升级)
第一步：从以前的 MySQL 安装导出现有数据(备份全库)：
mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > data-for-upgrade.sql
第二步：关闭旧的 MySQL 服务器。例如：
-->mysqladmin -u root -p shutdown
第三步：安装 MySQL 8.0 (此处可以参考前一章Mysql的安装流程) 包括初始化，启动，安全加固等等
第四步：导入数据
-->mysql -u root -p --force < data-for-upgrade.sql
第五步：执行剩余的升级操作：在 MySQL 8.0.16 及更高版本中，关闭服务器，然后使用--upgrade=FORCE执行剩余升级任务的选项重新启动它 ：
-->mysqladmin -u root -p shutdown ##关闭数据库
-->mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE & ##启动并强制升级数据库
第六步：在 MySQL 8.0.16 之前，执行 mysql_upgrade执行剩余的升级任务：
-->mysql_upgrade -u root -p
然后关闭并重新启动 MySQL 服务器以确保对系统表所做的任何更改生效。例如：
-->mysqladmin -u root -p shutdown
-->mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &
补充：
关于mysql_upgrade命令参数：只升级系统表，不升级数据，这样在升级数据量很大的数据库时，可以缩短升级的时间（这里在姜承尧的资料中有提到过，不过官方上升级并没有具体说明）
-s, --upgrade-system-tables Only upgrade the system tables, do not try to upgrade the data.
浙公网安备 33010602011771号