MySQL5.7升级到8.0过程详解

MySQL5.7升级到8.0过程详解

1. 下载安装包

官网下载对应版本的tar包，可通过wget下载或者本地下载后上传。
下载地址：
https://downloads.mysql.com/archives/community/
选择mysql--linux-glibcx86_64.tar.xz

执行以下步骤解压tar包：

安装包上传至原安装包目录下 我的是/usr/local/

cd /usr/local/

解压安装包

xz -d mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar

文件夹重命名为mysql8

mv mysql-8.0.19-linux-glibc2.12-x86_64 mysql8

更改文件夹所属

chown -R mysql.mysql /usr/local/mysql8/

2 更改配置文件my.cnf

最后几个for8.0的参数要格外注意

[mysqld]
user = mysql
datadir = /data/mysql/data
port = 3306

socket = /data/mysql/tmp/mysql.sock
pid-file = /data/mysql/tmp/mysqld.pid
tmpdir = /data/mysql/tmp
skip_name_resolve = 1
max_connections = 2000
group_concat_max_len = 1024000
lower_case_table_names = 1
log_timestamps=SYSTEM
max_allowed_packet = 32M
binlog_cache_size = 4M
sort_buffer_size = 2M
read_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 96M
max_heap_table_size = 96M
max_length_for_sort_data = 8096
default_time_zone = ‘+8:00’

#logs
server-id = 1003306
log-error = /data/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log-bin = /data/mysql/logs/binlog
binlog_format = row
log_bin_trust_function_creators = 1
gtid_mode = ON
enforce_gtid_consistency = ON

#for8.0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
character-set-server = utf8
collation_server = utf8_general_ci
basedir = /usr/local/mysql8
skip_ssl
default_authentication_plugin=mysql_native_password

3 执行升级程序

进入原5.7 mysql命令行 正确关闭数据库

mysql> select version();
±-----------+
| version() |
±-----------+
| 5.7.23-log |
±-----------+
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.00 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> exit
Bye

退出至终端 用mysql8.0.19客户端直接启动

[root@centos ~]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[1] 23333
[root@centos ~]# 2020-05-20T07:07:02.337626Z mysqld_safe Logging to ‘/data/mysql/logs/error.log’.
2020-05-20T07:07:02.366244Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

可观察下错误日志看是否报错 然后重新登录测试

[root@centos ~]# mysql -uroot -p123456
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 17
Server version: 8.0.19 MySQL Community Server - GPL

Copyright © 2000, 2018, 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.19 |
±----------+
1 row in set (0.00 sec)

4 环境变量修改

因basedir由/usr/local/mysql变成了/usr/local/mysql8，故相关环境变量推荐修改下。可按照以下步骤来操作验证：

修改PATH变量

vi /etc/profile

将PATH中的/usr/local/mysql/bin改为/usr/local/mysql8/bin

生效验证

[root@centos ~]# source /etc/profile
[root@centos ~]# which mysql
/usr/local/mysql8/bin/mysql
[root@centos ~]# mysql -V
mysql Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)