增城高端网站建设,公司注册资本最低多少,团购网站开发,石家庄网站建设求职简历MySQL in-place升级数据库 从5.7 到8.0.36 升级前检查升级中升级后回滚 升级前检查
mysql版本升级#xff0c;升级方式有就地升级和逻辑升级2种方法#xff0c;这次就用就地升级#xff0c;之前不同版本主备库的升级方式#xff0c;后续慢慢补充进来
In-Place Upgrade升级方式有就地升级和逻辑升级2种方法这次就用就地升级之前不同版本主备库的升级方式后续慢慢补充进来
In-Place Upgrade就地升级就地升级关闭旧的MySQL服务器用MySQL8的文件夹替换MySQL5.7文件夹重新启动MySQL以及升级现有安装中需要升级的任何剩余部分。逻辑升级有几种一般常用的是mysqldump导入导出以及搭建逻辑备库这个方法后续慢慢更新
使用mysqlsh升级前检查 根据自己的操作系统选择版本进行下载mysql shell升级前的数据库检查 下载地址https://dev.mysql.com/downloads/shell/ linux查看glibc版本 ldd --version [rootD2-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. 上传到服务器解压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 使用工具进行升级前的检查,检查项有可能返回三种 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 { --userroot --socket/tmp/mysql.sock } --target-version8.0.36 --config-path/etc/my5.cnf [rootdD2-TEST bin]# /u01/mysql5.7/mysqlsh/bin/mysqlsh – util check-for-server-upgrade { --userroot --socket/tmp/mysql.sock } --target-version8.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… Usage of old temporal type No issues found MySQL 8.0 syntax check for routine-like objects No issues found Usage of db objects with names conflicting with new reserved keywords No issues found 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 Table names in the mysql schema conflicting with new tables in 8.0 No issues found Partitioned tables using engines with non native partitioning No issues found Foreign key constraint names longer than 64 characters No issues found Usage of obsolete MAXDB sql_mode flag No issues found 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 ENUM/SET column definitions containing elements longer than 255 characters No issues found Usage of partitioned tables in shared tablespaces No issues found Circular directory references in tablespace data file paths No issues found Usage of removed functions No issues found Usage of removed GROUP BY ASC/DESC syntax No issues found Removed system variables for error logging to the system log configuration No issues found Removed system variables No issues found 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 Zero Date, Datetime, and Timestamp values No issues found Schema inconsistencies resulting from file removal or corruption No issues found Tables recognized by InnoDB that belong to a different engine No issues found Spatial data columns created in MySQL 5.6 No issues found Issues reported by ‘check table x for upgrade’ command No issues found 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_pluginmysql_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 Columns which cannot have default values No issues found Check for invalid table names and schema names used in 5.7 No issues found Check for orphaned routines in 5.7 No issues found Check for indexes that are too large to work on higher versions of MySQL Server than 5.7 No issues found Check for deprecated ‘.’ syntax used in routines. No issues found 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 下载对应glibc版本的MySQL 8.0.36 binaries (mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz) 下载地址 https://dev.mysql.com/downloads/mysql/ 更改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_shutdown0; 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备份MySQL5.7整个目录 mkdir -p /u01/backup cp /u01/mysql5.7/* /u01/backup mysqldump备份整个数据库 /u01/mysql5.7/bin/mysqldump -uroot -p12345678 -q --single-transaction --events --routines --triggers --set-gtid-purgedOFF --all-databases |gzip /root/all_db.gz 解压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 5043622024-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.sock2024-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升级后
升级数据字典 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 --upgradeNONE 说明在 mysqld 額外添加了一個新的選項 --upgrade。可選值爲 NONEAUTOMINIMALFORCE。 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 --port3306 登录数据查看数据字典 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) 简单数据库操作 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路径和数据文件拷贝回原始位置然后重新启动。
