【MYSQL数据库升级,从MYSQL5.7到MySQL8】MySQL in-place升级数据库 从5.7 到8.0.36

发布时间:2024年01月23日

MySQL in-place升级数据库 从5.7 到8.0.36

升级前检查

mysql版本升级,升级方式有就地升级和逻辑升级2种方法,这次就用就地升级,之前不同版本主备库的升级方式,后续慢慢补充进来

  • In-Place Upgrade(就地升级):就地升级关闭旧的MySQL服务器,用MySQL8的文件夹替换MySQL5.7文件夹,重新启动MySQL,以及升级现有安装中需要升级的任何剩余部分。
  • 逻辑升级有几种:一般常用的是mysqldump导入导出以及搭建逻辑备库,这个方法后续慢慢更新

使用mysqlsh升级前检查

  1. 根据自己的操作系统选择版本进行下载mysql shell,升级前的数据库检查
    下载地址:https://dev.mysql.com/downloads/shell/

  2. linux查看glibc版本
    ldd --version

    [root@D2-TEST bin]# ldd --version
    ldd (GNU libc) 2.28
    Copyright ? 2018 Free Software Foundation, Inc.
    This is free software; see the source for copying conditions. There is NO
    warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
    Written by Roland McGrath and Ulrich Drepper.

  3. 上传到服务器,解压tar包,并添加到环境变量中
    tar -xvf mysql-shell-8.0.36-linux-glibc2.28-x86-64bit.tar.gz

    vi ~/.bash_profile
    PATH= P A T H : PATH: PATH:HOME/bin:/u01/software/mysql-shell-8.0.36-linux-glibc2.28-x86-64bit/bin

  4. 使用工具进行升级前的检查,检查项有可能返回三种 Upgrade_issue,分别是 ERROR、WARNING、NOTICE。
    ERROR:必须整改,否则升级失败。
    WARNING: 可能需要整改,否则升级后有功能异常或不符合预期。
    NOTICE: 升级程序会帮您自动处理好的,仅仅通知您升级程序会干这操作。

    使用语句: mysqlsh -uroot -p12345678 -e “util.checkForServerUpgrade();”

    或者以下语法
    /u01/mysql5.7/mysqlsh/bin/mysqlsh – util check-for-server-upgrade { --user=root --socket=/tmp/mysql.sock } --target-version=8.0.36 --config-path=/etc/my5.cnf

    [root@dD2-TEST bin]# /u01/mysql5.7/mysqlsh/bin/mysqlsh – util check-for-server-upgrade { --user=root --socket=/tmp/mysql.sock } --target-version=8.0.36 --config-path=/etc/my5.cnf
    Please provide the password for ‘root@/tmp%2Fmysql.sock’: ********
    Save password for ‘root@/tmp%2Fmysql.sock’? [Y]es/[N]o/Ne[v]er (default No):
    The MySQL server at /tmp%2Fmysql.sock, version 5.7.16-log - MySQL Community
    Server (GPL), will now be checked for compatibility issues for upgrade to MySQL
    8.0.36…

    1. Usage of old temporal type
      No issues found

    2. MySQL 8.0 syntax check for routine-like objects
      No issues found

    3. Usage of db objects with names conflicting with new reserved keywords
      No issues found

    4. Usage of utf8mb3 charset
      Warning: The following objects use the utf8mb3 character set. It is
      recommended to convert them to use utf8mb4 instead, for improved Unicode
      support.
      More information:
      https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

    eser.durian_task.APP_CLUSID - column’s default character set: utf8
    。。。。。。
    中间省略
    eser.t_vit_qtion.STUS - column’s default character set: utf8

    1. Table names in the mysql schema conflicting with new tables in 8.0
      No issues found

    2. Partitioned tables using engines with non native partitioning
      No issues found

    3. Foreign key constraint names longer than 64 characters
      No issues found

    4. Usage of obsolete MAXDB sql_mode flag
      No issues found

    5. 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

    1. ENUM/SET column definitions containing elements longer than 255 characters
      No issues found

    2. Usage of partitioned tables in shared tablespaces
      No issues found

    3. Circular directory references in tablespace data file paths
      No issues found

    4. Usage of removed functions
      No issues found

    5. Usage of removed GROUP BY ASC/DESC syntax
      No issues found

    6. Removed system variables for error logging to the system log configuration
      No issues found

    7. Removed system variables
      No issues found

    8. 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_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
    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

    1. Zero Date, Datetime, and Timestamp values
      No issues found

    2. Schema inconsistencies resulting from file removal or corruption
      No issues found

    3. Tables recognized by InnoDB that belong to a different engine
      No issues found

    4. Spatial data columns created in MySQL 5.6
      No issues found

    5. Issues reported by ‘check table x for upgrade’ command
      No issues found

    6. 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

    7. Columns which cannot have default values
      No issues found

    8. Check for invalid table names and schema names used in 5.7
      No issues found

    9. Check for orphaned routines in 5.7
      No issues found

    10. Check for indexes that are too large to work on higher versions of MySQL
      Server than 5.7
      No issues found

    11. Check for deprecated ‘.

      ’ syntax used in routines.
      No issues found

    12. Check for columns that have foreign keys pointing to tables from a diffrent
      database engine.
      No issues found

    Errors: 0
    Warnings: 404
    Notices: 1

    NOTE: No fatal errors were f

  5. 下载对应glibc版本的MySQL 8.0.36 binaries (mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz)
    下载地址: https://dev.mysql.com/downloads/mysql/

  6. 更改mysql5.7数据库的参数,要不然启动时候需要数据库恢复(前滚&回滚)
    mysql> show variables like ‘%shutdown%’;
    ±------------------------------------±------+
    | Variable_name | Value |
    ±------------------------------------±------+
    | innodb_buffer_pool_dump_at_shutdown | ON |
    | innodb_fast_shutdown | 1 |
    ±------------------------------------±------+
    2 rows in set (0.00 sec)

    mysql> set global innodb_fast_shutdown=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like ‘%shutdown%’;
    ±------------------------------------±------+
    | Variable_name | Value |
    ±------------------------------------±------+
    | innodb_buffer_pool_dump_at_shutdown | ON |
    | innodb_fast_shutdown | 0 |
    ±------------------------------------±------+

升级中

1.关闭5.7版本数据库
service mysqld stop

或者
mysqladmin -uroot -p12345678 -S /u01/mysql5.7/mysql5.7.sock shutdown
  1. 备份MySQL5.7整个目录
    mkdir -p /u01/backup
    cp /u01/mysql5.7/* /u01/backup

  2. mysqldump备份整个数据库
    /u01/mysql5.7/bin/mysqldump -uroot -p12345678 -q --single-transaction --events --routines --triggers --set-gtid-purged=OFF --all-databases |gzip > /root/all_db.gz

  3. 解压MySQL 8.0.36 binaries到MYSQL5.7的目录中,并覆盖,启动mysql
    service mysqld start

    或者
    /u01/mysql5.7/bin/mysqld_safe --defaults-file=/etc/my5.cnf &

    以下是mysqld.log的输出,可以看出,数据库启动时候自动升级了数据字典或者后续手动升级也可以

     		2024-01-23T08:33:35.169307Z 0 [Warning] [MY-000081] [Server] option 'server_id': unsigned value 101638420357 adjusted to 4294967295.
     		2024-01-23T08:33:35.169557Z 0 [System] [MY-010116] [Server] /u01/mysql5.7/bin/mysqld (mysqld 8.0.36) starting as process 504362
     		2024-01-23T08:33:35.185326Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
     		2024-01-23T08:33:35.185440Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
     		2024-01-23T08:33:35.923313Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
     		2024-01-23T08:33:37.729442Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
     		2024-01-23T08:33:38.604014Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' started.
     		2024-01-23T08:33:43.481128Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' completed.
     		2024-01-23T08:33:44.583318Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
     		2024-01-23T08:33:44.583438Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
     		2024-01-23T08:33:44.896924Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/u01' in the path is accessible to all OS users. Consider choosing a different directory.
     		2024-01-23T08:33:44.921966Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
     		2024-01-23T08:33:44.922144Z 0 [System] [MY-010931] [Server] /u01/mysql5.7/bin/mysqld: ready for connections. Version: '8.0.36'  socket: '/u01/mysql5.7/mysql5.7.sock'  port: 3306  MySQL Community Server - GPL.
     		2024-01-23T08:34:11.839745Z 11 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
    

升级后

  1. 升级数据字典
  • MySQL 8.0.16 的新的升級方式(参考:https://dev.mysql.com/doc/mysql-installation-excerpt/8.3/en/upgrading-what-is-upgraded.html)

    /u01/mysql5.7/bin/mysqld --upgrade=NONE

    说明:在 mysqld 額外添加了一個新的選項 --upgrade。可選值爲 NONE,AUTO,MINIMAL,FORCE。
    NONE:不尝试升级
    AUTO: 默认选项,MYSQL进行数据字典和服务升级
    MINIMAL:仅升级数据字典
    FORCE:強制升級,类似于 mysql_upgrade –force

  • MySQL 8.0.16 以前的升级方式:
    The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.

mysql_upgrade --uroot -p12345678 --socket=/u01/mysql5.7/mysql5.7.sock --port=3306

  1. 登录数据查看数据字典:
    mysql> select user,host,plugin from user;
    ±-----------------±----------±----------------------+
    | user | host | plugin |
    ±-----------------±----------±----------------------+
    | root | % | mysql_native_password |
    | mysql.infoschema | localhost | caching_sha2_password |
    | mysql.session | localhost | caching_sha2_password |
    | mysql.sys | localhost | mysql_native_password |
    | root | localhost | mysql_native_password |
    ±-----------------±----------±----------------------+
    5 rows in set (0.00 sec)
    mysql> select user,host,plugin from user;
    ±-----------------±----------±----------------------+
    | user | host | plugin |
    ±-----------------±----------±----------------------+
    | root | % | mysql_native_password |
    | mysql.infoschema | localhost | caching_sha2_password |
    | mysql.session | localhost | caching_sha2_password |
    | mysql.sys | localhost | mysql_native_password |
    | root | localhost | mysql_native_password |
    ±-----------------±----------±----------------------+
    5 rows in set (0.00 sec)

  2. 简单数据库操作
    mysql> create table t1 (a int);
    Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

回滚

如果要回退的话只需要将新版本的base文件和数据文件移动到其他地方,将之前备份的MySQL 5.7的base路径和数据文件拷贝回原始位置,然后重新启动。

文章来源:https://blog.csdn.net/gsforget321/article/details/135777002
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。