测试环境 MySQL 5.7.31 单实例升级 MySQL 8.0.22
升级MySQL-5.7.31至 MySQL-8.0.22注意事项:
1.升级前需要收集完整源库信息，字符集、sql_mode、数据量、用户等主要信息
2.如果是生产环境需要做好源库的备份，配置文件的备份，干净关闭数据库
3.升级过程中需要确保应用程序全部关闭，也可以把业务账户删除或者账户加锁、也可以通过设置限制连接数
4.升级后进行业务验证
1.原MySQL系统环境
1.1.系统版本
[root@mysql~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.8 (Maipo)
1.2.查看当前数据库版本
[root@mysql~]# mysql --version
mysql Ver 14.14 Distrib 5.7.31, for linux-glibc2.12 (x86_64) using EditLine wrapper
1.3.数据库字符集
mysql> show variables like '%character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
1.4.SQL_MODE查看
mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.添加MySQL 8.0 参数
MySQL5.7和8.0区别，参考官方文档MySQL8.0主要增加以下几点新特性:
默认字符集由latin1变为utf8mb4、MyISAM系统表全部换成InnoDB表、JSON特性增强、支持不可见索引，支持直方图
sql_mode参数默认值变化、默认密码策略变更
在升级过程中列举出以下几点注意事项：
2.1.字符集设置:保持与MySQL5.7一致的字符集。
2.2.密码认证插件:升级8.0后采用5.7的mysql_native_password认证插件。
2.3.关于sql_mode:MySQL8.0版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有NO_AUTO_CREATE_USER。
[root@mysql~]# vi /etc/my.cnf
# mysql 8.0 parameter
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
character-set-server=utf8mb4
collation_server = utf8_general_ci
skip_ssl
default_authentication_plugin=mysql_native_password
3.部署MySQL-8.0
下载好MySQL-8.0.22 tar包，改包大小为1.1G左右
[root@mysql minstall]# ls -al
total 1151420
-rw-r--r-- 1 root root 1179054080 Jan 7 12:57 mysql-8.0.22-linux-glibc2.12-x86_64.tar
[root@mysql minstall]# du -sm mysql-8.0.22-linux-glibc2.12-x86_64.tar
1125 mysql-8.0.22-linux-glibc2.12-x86_64.tar
3.1.解压MySQL 8.0
解压MySQL-8.0.22,加压后会生成3个.xz结尾的压缩包
[root@mysql57-m minstall]# tar xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar
mysql-test-8.0.22-linux-glibc2.12-x86_64.tar.xz
mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
mysql-router-8.0.22-linux-glibc2.12-x86_64.tar.xz
将mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz拷贝到/usr/local 目录并继续解压并赋权
[root@mysqlminstall]#cp mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz /usr/local/
[root@mysql local]#xz -d mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
[root@mysql local]#tar xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar
[root@mysql local]#chown -R mysql.mysql mysql-8.0.22-linux-glibc2.12-x86_64
3.2 登录mysql 设置 global innodb_fast_shutdown干净关闭数据库这一步很重要
mysql> set global innodb_fast_shutdown =0;
Innodb_fast_shutdown关于InnoDB有三个值:
0表示在innodb关闭的时候，需要purge all, merge insert buffer,flush dirty pages。这种方式shutdown最慢，start最快。
1表示在innodb关闭的时候，它不需要purge all，merge insert buffer，只需要flush dirty page。
2表示在innodb关闭的时候，它不需要purge all，merge insert buffer，也不进行flush dirty page，只将log buffer里面的日志flush到log files，因此在进行崩溃恢复操作时它是最耗时的。
4.备份系统库
这里只备份了mysql5.7的系统库，如果数据库不是很大建议全库备份
#innobackupex --defaults-file=/etc/my.cnf --user=root --password=mysql123 --host=192.168.10.11 --databases="sys mysql performance_schema information_schema" /backup-mysql
5.关闭数据库
[root@mysql ~]# /etc/init.d/mysql.server stop
热备份完成后，可以在关闭数据库后进行一次冷备份
[root@mysql data]# cp -r mysql /backup-mysql/
[root@mysql data]# cp -r sys /backup-mysql/
[root@mysql data]# cp -r performance_schema/ /backup-mysql/
6.替换MySQL5.7目录
[root@mysql ~]# cd /usr/local/mysql
[root@mysql local]# mv mysql-5.7.31-linux-glibc2.12-x86_64 mysql-5.7.31-bakcup
删除mysql5.7 软链接
[root@mysql-slave01 local]# rm mysql
rm: remove symbolic link ‘mysql’? y
重新连接MySQL 8.0.22
[root@mysql local]# ln -s mysql-8.0.22-linux-glibc2.12-x86_64 mysql
[root@mysql local]# ls -al mysql
lrwxrwxrwx 1 root root 35 Jan 7 17:46 mysql -> mysql-8.0.22-linux-glibc2.12-x86_64
7.升级MySQL 至MySQL 8.0
在MySQL 8.0.16版本之前，需要手动的执行mysql_upgrade来完成该步骤的升级，
在MySQL 8.0.16版本及之后是由mysqld以In-Place方式直接升级到MySQL8.0.22
[root@mysql local]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[1] 31845
[root@mysql local]# 2021-01-07T10:24:03.135300Z mysqld_safe Logging to '/mysql/log/error.log'.
2021-01-07T10:24:03.171909Z mysqld_safe Starting mysqld daemon with databases from /mysql/data
7.1 查看3306端口是否开启
[root@mysql local]# ss -ln |grep 3306
tcp LISTEN 0 70 [::]:33060 [::]:*
7.2 查看错误日志
[root@mysql local]# tail -20 /mysql/log/error.log
2021-01-07T09:22:45.389084Z 0 [Note] Shutting down plugin 'binlog'
2021-01-07T09:22:45.389550Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
2021-01-07T10:24:03.547085Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2021-01-07T10:24:03.547327Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.22) starting as process 32124
2021-01-07T10:24:03.551310Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-01-07T10:24:03.551324Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2021-01-07T10:24:03.625074Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2021-01-07T10:24:03.625378Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-07T10:24:04.813246Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-07T10:24:05.327785Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-01-07T10:24:07.097821Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2021-01-07T10:24:08.158391Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80022' started.
2021-01-07T10:24:13.446048Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80022' completed.
2021-01-07T10:24:13.650879Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2021-01-07T10:24:13.737051Z 0 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql-slave01-relay-bin' to avoid this problem.
2021-01-07T10:24:13.757292Z 8 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2021-01-07T10:24:13.760434Z 8 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl@192.168.10.11:3306',replication started in log 'mysql-bin.000016' at position 154
2021-01-07T10:24:13.813370Z 0 [Warning] [MY-011302] [Server] Plugin mysqlx reported: 'Failed at SSL configuration: "SSL context is not usable without certificate and private key"'
2021-01-07T10:24:13.813662Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.22' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
7.3 登录mysql 查看信息
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| txgd01 |
| tsche02 |
| school3 |
| tx |
| uu |
+--------------------+
9 rows in set (0.01 sec)
至此，MySQL升级暂告一段落
版权声明：本文为Evils798原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。