您现在的位置是：首页 >学无止境 >通过yum：mysql5.6-msyql5.7-mysql8.0升级之路网站首页学无止境
通过yum：mysql5.6-msyql5.7-mysql8.0升级之路
简介通过yum：mysql5.6-msyql5.7-mysql8.0升级之路
一 前言
mysql的yum源
https://dev.mysql.com/downloads/repo/yum/
https://dev.mysql.com/get/mysq57-community-release-el7-7.noarch.rpm
服务器信息
2c2g40GB
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@localhost ~]# uname -a
Linux bogon 3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
二 安装mysql5.6
1 卸载mysql，mariadb
[root@localhost ~]# rpm -qa|grep -i mariadb
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-devel-5.5.68-1.el7.x86_64
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# rpm -qa|grep mariadb|xargs rpm -e --nodeps
2查看mysql可用的下载
[root@localhost ~]# yum repolist all | grep mysql
mysql-connectors-community/x86_64 MySQL Connectors Community 启用: 178
mysql-connectors-community-source MySQL Connectors Community - Sour 禁用
mysql-tools-community/x86_64 MySQL Tools Community 启用: 83
mysql-tools-community-source MySQL Tools Community - Source 禁用
mysql55-community/x86_64 MySQL 5.5 Community Server 禁用
mysql55-community-source MySQL 5.5 Community Server - Sour 禁用
mysql56-community/x86_64 MySQL 5.6 Community Server 启用: 644
mysql56-community-source MySQL 5.6 Community Server - Sour 禁用
mysql57-community-dmr/x86_64 MySQL 5.7 Community Server Develo 禁用
mysql57-community-dmr-source MySQL 5.7 Community Server Develo 禁用
3指定版本安装
#命令包
yum -y install yum-utils
禁用 mysql8.0:
yum-config-manager --disable mysql80-community
启用mysql 5.6
yum-config-manager --enable mysql56-community
4安装
[root@localhost ~]# yum install mysql-server
[root@localhost tmp]# rpm -qa | grep mysql
mysql-community-server-5.6.51-2.el6.x86_64
mysql80-community-release-el7-7.noarch
mysql-community-client-5.6.51-2.el6.x86_64
mysql-community-libs-5.6.51-2.el6.x86_64
mysql-community-common-5.6.51-2.el6.x86_64
5my.cnf的配置，尽量环境一致，字符集必须指定，在实际环境中mysql8.0字符集也被改了(utf-8)
[root@localhost ~]# cat /etc/my.cnf
#For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
collation-server=utf8_general_ci
character-set-server=utf8
#max_connections =1000
#default-storage-engine=MYISAM
#default-tmp-storage-engine=MYISAM
#AFETY #
max-allowed-packet = 16M
max-connect-errors = 1000
lower_case_table_names = 1
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 150M
max-allowed-packet = 16M
max-connect-errors = 1000
lower_case_table_names = 1
# # CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 1000
thread-cache-size = 50
open-files-limit = 1024
performance_schema_max_table_instances= 600
table-definition-cache = 512
table-open-cache = 256
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#bind=127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8
更新root密码，授权登录
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.51 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> set password for root@localhost = password('a^X1wIKwskU0D6Li');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to'root'@'localhost' identified by 'a^X1wIKwskU0D6Li';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to'root'@'127.0.0.1' identified by 'a^X1wIKwskU0D6Li';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to'root'@'192.168.56.%' identified by 'a^X1wIKwskU0D6Li';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
6 导入数据
三 从mysql5.6升级到mysql5.7
1 备份所有库，配置文件
备份的时候直接复制mysql数据存储目录(/var/lib/mysql)，当然配置文件也是需要备份的，备份了旧的数据存储目录后，如果在升级时失败，好回滚
[root@bogon ~]# cp /etc/my.cnf /etc/my.cnf_20230602
[root@bogon ~]# mysqldump -uroot -p'a^X1wIKwskU0D6Li' --set-gtid-purged=OFF --all-databases > /opt/mysqlbackup_20230602.bak
Warning: Using a password on the command line interface can be insecure.
[root@bogon ~]# du -sh /opt/mysqlbackup_20230602.bak
64M /opt/mysqlbackup_20230602.bak
#或者
[root@bogon ~]# cp -r /var/lib/mysql /var/lib/mysql_20230602
2.停止mysql服务
1通过与数据库的交互页面关闭，确保数据最大写入磁盘
[root@bogon ~]# mysql -u root -p'a^X1wIKwskU0D6Li'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.51 MySQL Community Server (GPL)
Copyright (c) 2000, 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 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.51 |
+-----------+
1 row in set (0.01 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.01 sec)
#确保数据都刷到硬盘上，更改成0
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> exit
Bye
[root@bogon ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 998/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1186/master
tcp6 0 0 :::22 :::* LISTEN 998/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1186/master
innodb_fast_shutdown
使用场景：在做数据库关闭升级的时候 set global innodb_fast_shutdown=0，这个时候能最大保障数据的完整性。
设置为1：关闭MySQL的时候不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘
设置为0：会做清除脏页和插入缓冲区的合并操作，也会将脏页全部刷新到磁盘上面去，但是这个时候关闭的速度也是最慢的
设置为2：不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘，但是会刷新到redo log里面，再下次启动mysql的时候恢复
2直接关闭
systemctl stop mysqld
[root@bogon ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 998/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1186/master
tcp6 0 0 :::22 :::* LISTEN 998/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1186/master
3开启mysql5.7版本
[root@bogon ~]# yum-config-manager --disable mysql56-community
[root@bogon ~]# yum-config-manager --enable mysql57-community
上述命令修改失败
yum 源的问题 ，删除旧的源，安装新的源即可
https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
rpm -h mysql80-community-release-el7-7.noarch.rpm
[root@bogon ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Comm 禁用
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Comm 禁用
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Comm 禁用
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Comm 禁用
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Comm 禁用
mysql-cluster-8.0-community-debuginfo/x86_64 MySQL Cluster 8.0 Comm 禁用
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Comm 禁用
mysql-connectors-community/x86_64 MySQL Connectors Commu 启用: 178
mysql-connectors-community-debuginfo/x86_64 MySQL Connectors Commu 禁用
mysql-connectors-community-source MySQL Connectors Commu 禁用
mysql-tools-community/x86_64 MySQL Tools Community 启用: 83
mysql-tools-community-debuginfo/x86_64 MySQL Tools Community 禁用
mysql-tools-community-source MySQL Tools Community 禁用
mysql-tools-preview/x86_64 MySQL Tools Preview 禁用
mysql-tools-preview-source MySQL Tools Preview - 禁用
mysql57-community/x86_64 MySQL 5.7 Community Se 禁用
mysql57-community-source MySQL 5.7 Community Se 禁用
mysql80-community/x86_64 MySQL 8.0 Community Se 启用: 405
mysql80-community-debuginfo/x86_64 MySQL 8.0 Community Se 禁用
mysql80-community-source MySQL 8.0 Community Se 禁用
[root@bogon ~]# yum-config-manager --disable mysql80-community
[root@bogon ~]# yum-config-manager --enable mysql5-community
[root@bogon ~]# yum-config-manager --enable mysql57-community
[root@bogon ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Comm 禁用
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Comm 禁用
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Comm 禁用
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Comm 禁用
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Comm 禁用
mysql-cluster-8.0-community-debuginfo/x86_64 MySQL Cluster 8.0 Comm 禁用
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Comm 禁用
mysql-connectors-community/x86_64 MySQL Connectors Commu 启用: 220
mysql-connectors-community-debuginfo/x86_64 MySQL Connectors Commu 禁用
mysql-connectors-community-source MySQL Connectors Commu 禁用
mysql-tools-community/x86_64 MySQL Tools Community 启用: 98
mysql-tools-community-debuginfo/x86_64 MySQL Tools Community 禁用
mysql-tools-community-source MySQL Tools Community 禁用
mysql-tools-preview/x86_64 MySQL Tools Preview 禁用
mysql-tools-preview-source MySQL Tools Preview - 禁用
mysql57-community/x86_64 MySQL 5.7 Community Se 启用: 660
mysql57-community-source MySQL 5.7 Community Se 禁用
mysql80-community/x86_64 MySQL 8.0 Community Se 禁用
mysql80-community-debuginfo/x86_64 MySQL 8.0 Community Se 禁用
mysql80-community-source MySQL 8.0 Community Se 禁用
[root@bogon ~]#
4.升级mysql
yum update mysql-server
[root@bogon ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.42, for Linux (x86_64) using EditLine wrapper
[root@bogon ~]# systemctl start mysqld
[root@bogon ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 998/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1186/master
tcp6 0 0 :::3306 :::* LISTEN 5743/mysqld
tcp6 0 0 :::22 :::* LISTEN 998/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1186/master
5 更新数据库权限,并再次重新启动
[root@bogon ~]# mysql_upgrade -u root -p'a^X1wIKwskU0D6Li' --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database
[root@bogon ~]# systemctl restart mysqld
四 从mysql5.7升级到mysql8.0
1修改my.cnf
[root@bogon ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.42, for Linux (x86_64) using EditLine wrapper
[root@bogon ~]# vim /etc/my.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
2备份数据
备份的时候直接复制mysql数据存储目录(/var/lib/mysql)，当然配置文件也是需要备份的，备份了旧的数据存储目录后，如果在升级时失败，好回滚
[root@bogon ~]# cp /etc/my.cnf /etc/my.cnf_20230602
[root@bogon ~]# mysqldump -uroot -p'a^X1wIKwskU0D6Li' --set-gtid-purged=OFF --all-databases > /opt/mysqlbackup_20230602.bak
Warning: Using a password on the command line interface can be insecure.
[root@bogon ~]# du -sh /opt/mysqlbackup_20230602.bak
64M /opt/mysqlbackup_20230602.bak
#或者
[root@bogon ~]# cp -r /var/lib/mysql /var/lib/mysql_20230602
3 确保数据最大写入磁盘
[root@bogon ~]# mysql -u root -p'a^X1wIKwskU0D6Li'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.42 MySQL Community Server (GPL)
Copyright (c) 2000, 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 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.42 |
+-----------+
1 row in set (0.01 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.01 sec)
#确保数据都刷到硬盘上，更改成0
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> exit
Bye
#mysql数据库已经停止
[root@bogon ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 998/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1186/master
tcp6 0 0 :::22 :::* LISTEN 998/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1186/master
innodb_fast_shutdown
使用场景：在做数据库关闭升级的时候 set global innodb_fast_shutdown=0，这个时候能最大保障数据的完整性。
设置为1：关闭MySQL的时候不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘
设置为0：会做清除脏页和插入缓冲区的合并操作，也会将脏页全部刷新到磁盘上面去，但是这个时候关闭的速度也是最慢的
设置为2：不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘，但是会刷新到redo log里面，再下次启动mysql的时候恢复
4 开启mysql8.0安装
[root@bogon ~]# yum-config-manager --disable mysql57-community
[root@bogon ~]# yum-config-manager --enable mysql80-community
[root@bogon ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Comm 禁用
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Comm 禁用
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Comm 禁用
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Comm 禁用
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Comm 禁用
mysql-cluster-8.0-community-debuginfo/x86_64 MySQL Cluster 8.0 Comm 禁用
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Comm 禁用
mysql-connectors-community/x86_64 MySQL Connectors Commu 启用: 220
mysql-connectors-community-debuginfo/x86_64 MySQL Connectors Commu 禁用
mysql-connectors-community-source MySQL Connectors Commu 禁用
mysql-tools-community/x86_64 MySQL Tools Community 启用: 98
mysql-tools-community-debuginfo/x86_64 MySQL Tools Community 禁用
mysql-tools-community-source MySQL Tools Community 禁用
mysql-tools-preview/x86_64 MySQL Tools Preview 禁用
mysql-tools-preview-source MySQL Tools Preview - 禁用
mysql57-community/x86_64 MySQL 5.7 Community Se 禁用
mysql57-community-source MySQL 5.7 Community Se 禁用
mysql80-community/x86_64 MySQL 8.0 Community Se 启用: 405
mysql80-community-debuginfo/x86_64 MySQL 8.0 Community Se 禁用
mysql80-community-source MySQL 8.0 Community Se 禁用
5 开始升级
yum update mysql-server
1 错误1：启动失败
[root@bogon ~]# mysql -V
mysql Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)
[root@bogon ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@bogon ~]# tail -F /var/log/messages
Jun 2 13:59:14 localhost mysqld: 2023-06-02T05:59:14.630091Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80033' completed.
Jun 2 13:59:14 localhost mysqld: 2023-06-02T05:59:14.770369Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
Jun 2 13:59:14 localhost mysqld: 2023-06-02T05:59:14.770416Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
Jun 2 13:59:14 localhost mysqld: 2023-06-02T05:59:14.775298Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query-cache-type=0'.
Jun 2 13:59:14 localhost mysqld: 2023-06-02T05:59:14.775400Z 0 [ERROR] [MY-010119] [Server] Aborting
Jun 2 13:59:19 localhost mysqld: 2023-06-02T05:59:19.405507Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.33) MySQL Community Server - GPL.
Jun 2 13:59:19 localhost systemd: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jun 2 13:59:19 localhost systemd: Failed to start MySQL Server.
Jun 2 13:59:19 localhost systemd: Unit mysqld.service entered failed state.
Jun 2 13:59:19 localhost systemd: mysqld.service failed.
MySQL8.0取消查询缓存,MySQL8.0新增加了对性能干预的工具，例如，现在可以利用查询重写插件，在不更改应用程序的同时，插入优化器提示语句。另外，还有像ProxySQL这样的第三方工具，它们可以充当中间缓存。
2 :错误1处理：修改配置文件吧
关闭下面俩个参数
vim /etc/my.cnf
#query-cache-type = 0
#query-cache-size = 0
[root@bogon ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 998/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1186/master
tcp6 0 0 :::33060 :::* LISTEN 6614/mysqld
tcp6 0 0 :::3306 :::* LISTEN 6614/mysqld
tcp6 0 0 :::22 :::* LISTEN 998/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1186/master
启动了2个端口3306 33306，并且日志输出到系统日志中
6 更新数据库权限,并再次重新启动
1升级表权限的时候发现已经被弃用了
[root@bogon ~]# mysql_upgrade -u root -p'a^X1wIKwskU0D6Li' --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
2 数据库中查看版本
[root@bogon ~]# mysql -u root -p'a^X1wIKwskU0D6Li'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 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 'h' for help. Type 'c' to clear the current input statement.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.04 sec)
mysql> exit
Bye
7 其他问题 主要是字符集
参考看看
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
1更新mysql的字符集为utf8mb4
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
character_set_server=utf8mb4
[client]
default-character-set=utf8mb4
改完重启就报错
2还是改回utf-8
[root@localhost ~]# vim /etc/my.cnf
#character_set_server=utf8mb4
character-set-server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8
#default-character-set=utf8mb4
五 mysql-shell
下载地址
https://dev.mysql.com/downloads/shell/
https://dev.mysql.com/doc/mysql-shell/8.0/en/
配置yum源直接下载即可
[root@bogon ~]# rpm -qa | grep mysql
mysql-community-client-plugins-8.0.33-1.el7.x86_64
mysql-community-icu-data-files-8.0.33-1.el7.x86_64
mysql80-community-release-el7-7.noarch
mysql-community-common-8.0.33-1.el7.x86_64
mysql-community-libs-compat-5.7.42-1.el7.x86_64
mysql-community-client-8.0.33-1.el7.x86_64
mysql-community-server-8.0.33-1.el7.x86_64
mysql-community-libs-8.0.33-1.el7.x86_64
[root@bogon ~]# yum install mysql-shell-8.0.33
注意事项
https://www.cnblogs.com/zhouwanchun/p/13141682.html
MySQL 5.6升级到MySQL 5.7都有什么注意事项？
一、升级方式
MySQL升级的方式一般来说有两种
1、通过inplace方式原地升级，升级系统表
2、通过新建实例，高版本作为低版本的从库进行滚动升级
MySQL5.7版本做了非常多的改变，升级5.6到5.7时需要考虑兼容性，避免升级到5.7之后因为种种参数设置不正确导致业务受影响，建议首先逐一查看release note
二、需要注意的参数及问题：
1、sql_mode：MySQL 5.7采用严格模式，例如ONLY_FULL_GROUP_BY等
2、innodb_status_output_locks：MySQL 5.7支持将死锁信息打印到error log（其实这个参数MySQL 5.6就已支持）
3、innodb_page_cleaners：MySQL 5.7将脏页刷新线程从master线程独立出来了，对应参数为innodb_page_cleaners
4、innodb_strict_mode：控制CREATE TABLE, ALTER TABLE, CREATE INDEX, 和 OPTIMIZE TABLE的语法问题
5、show_compatibility_56=ON：控制show变量及状态信息输出，如果未开启show status 命令无法获取Slave_xxx 的状态
6、log_timestamps：控制error log/slow_log/genera log日志的显示时间，该参数可以设置为:UTC 和 SYSTEM，但是默认使用 UTC
7、disable_partition_engine_check：在表多的情况下可能导致启动非常慢
8、range_optimizer_max_mem_size：范围查询优化参数，这个参数限制范围查询优化使用的内存，默认8M
9、MySQL 5.7新增优化器选项derived_merge=on，可能导致SQL全表扫描，而在MySQL 5.6下可能表现为auto key
10、innodb_undo_directory && innodb_undo_logs：MySQL 5.7支持将undo从ibdata1独立出来（只支持实例初始化，不支持在线变更）
11、主从复制问题：MySQL5.7到小于5.6.22的复制存在bug(bug 74683)
12、SQL兼容性问题：SQL在MySQL 5.7和MySQL 5.6环境下结果可能不一致，因此建议获取线上SQL，在同样数据的环境下，在两个实例运行获取到的结果计算hash，比较hash值做兼容性判断
三、友情提醒
1、升级前一定要做好备份！！！
2、升级正式环境前提前在测试环境进行仔细测试，确认无误以后再升级正式环境。
3、做好相应的回退方案。
风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。
站长推荐
- stm32使用HAL库配置串口中断收发数据（保姆级教程）
stm32使用HAL库配置串口中断收发数据（保姆级教程） - SpringSecurity实现前后端分离认证授权
SpringSecurity实现前后端分离认证授权 - U8W/U8W-Mini使用与常见问题解决
U8W/U8W-Mini使用与常见问题解决 - 【社区图书馆】伴我前行的一本书《The C Programming Language》
【社区图书馆】伴我前行的一本书《The C Programming Language》 - 【云原生进阶之容器】第六章容器网络6.6.1--Cilium网络方案概述
【云原生进阶之容器】第六章容器网络6.6.1--Cilium网络方案概述