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_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE 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]# mysqlshMySQL Shell 8.0.25Copyright (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.25Copyright (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 CommunityServer (GPL), will now be checked for compatibility issues for upgrade to MySQL8.0.25...1) Usage of old temporal type  No issues found2) Usage of db objects with names conflicting with new reserved keywords  No issues found3) Usage of utf8mb3 charset  No issues found4) Table names in the mysql schema conflicting with new tables in 8.0  No issues found5) Partitioned tables using engines with non native partitioning  No issues found6) Foreign key constraint names longer than 64 characters  No issues found7) Usage of obsolete MAXDB sql_mode flag  No issues found8) Usage of obsolete sql_mode flags  Notice: The following DB objects have obsolete options persisted forsql_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_USERoption9) ENUM/SET column definitions containing elements longer than 255 characters  No issues found10) Usage of partitioned tables in shared tablespaces  No issues found11) Circular directory references in tablespace data file paths  No issues found12) Usage of removed functions  No issues found13) Usage of removed GROUP BY ASC/DESC syntax  No issues found14) Removed system variables for error logging to the system log configuration  No issues found15) Removed system variables  No issues found16) System variables with new default values  Waing: 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 change  character_set_server - default value will change from latin1 to utf8mb4  collation_server - default value will change from latin1_swedish_ci toutf8mb4_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) to2 (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 (Waing)  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 willchange from OFF to ON  performance_schema_consumer_events_transactions_history - default value willchange 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 toXXHASH6417) Zero Date, Datetime, and Timestamp values  No issues found18) Schema inconsistencies resulting from file removal or corruption  No issues found19) Tables recognized by InnoDB that belong to a different engine  No issues found20) Issues reported by 'check table x for upgrade' command  No issues found21) New default authentication plugin considerations  Waing: 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:0Waings: 27Notices:  1No 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，waing信息进行修改，修改完了之后，再进行升级

• 上面检查完毕之后，对对应的地方进行，重复用上面的函数进行检查，直到无error出现，waing这个可以忽略，然后下载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 49252023-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.sock2023-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 [Waing] [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 shutdownSET 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.