MySQL升级-MySQL 5.7升级到8.0详细过程

1. 升级前准备

在升级MySQL之前，需要进行充分的准备工作，包括检查当前版本、备份数据、检查硬件环境等。更多学习教程www.fgedu.net.cn

# 检查当前MySQL版本
# /mysql/current/bin/mysql –version
mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper

# 检查硬件环境
# free -h
total used free shared buff/cache available
Mem: 64G 2.1G 60G 8.5M 1.8G 61G
Swap: 32G 0B 32G

# 检查磁盘空间
# df -h /mysql
Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 1.0T 100G 900G 10% /mysql

# 检查MySQL状态
# systemctl status mysql
● mysql.service – MySQL Server
Loaded: loaded (/etc/systemd/system/mysql.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2026-03-31 09:00:00 CST; 1h ago
Main PID: 12345 (mysqld)
Tasks: 38
Memory: 2.0G
CGroup: /system.slice/mysql.service
└─12345 /mysql/current/bin/mysqld –defaults-file=/mysql/current/my.cnf

生产环境建议：
– 确保有足够的磁盘空间用于备份和安装新版本
– 确保内存足够，MySQL 8.0对内存的使用可能会有所增加
– 安排在业务低峰期进行升级
– 提前通知相关业务方，做好升级计划
– 检查MySQL 8.0的系统要求，确保操作系统版本兼容

2. 数据备份

在升级之前，必须对MySQL数据进行备份，以防止升级过程中出现问题导致数据丢失。学习交流加群风哥微信: itpux-com

# 创建备份目录
# mkdir -p /backup/mysql/upgrade

# 执行备份
# /mysql/current/bin/mysqldump –all-databases –single-transaction –master-data=2 –flush-logs –triggers –routines –events -u root -p”fgedudb123″ > /backup/mysql/upgrade/all_databases-$(date +”%Y%m%d%H%M%S”).sql

# 检查备份文件
# ls -la /backup/mysql/upgrade/
total 51200
-rw-r–r– 1 root root 26214400 Mar 31 10:00 all_databases-20260331100000.sql

3. 下载新版本

从MySQL官方网站下载最新版本的MySQL 8.0。

# 下载MySQL 8.0.28
# cd /tmp
# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.28-el7-x86_64.tar.gz

# 解压源码包
# tar -zxvf mysql-8.0.28-el7-x86_64.tar.gz

# 检查解压结果
# ls -la mysql-8.0.28-el7-x86_64/
total 2048
drwxr-xr-x 10 root root 4096 Mar 31 10:05 mysql-8.0.28-el7-x86_64
-rw-r–r– 1 root root 1048576 Mar 31 10:05 mysql-8.0.28-el7-x86_64.tar.gz

4. 停止服务

在安装新版本之前，需要停止当前运行的MySQL服务。

# 停止MySQL服务
# systemctl stop mysql

# 检查服务状态
# systemctl status mysql
● mysql.service – MySQL Server
Loaded: loaded (/etc/systemd/system/mysql.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Mon 2026-03-31 10:10:00 CST; 1min ago
Process: 12345 ExecStop=/mysql/current/bin/mysqladmin -u root -p shutdown (code=exited, status=0/SUCCESS)
Main PID: 12345 (code=exited, status=0/SUCCESS)

5. 安装新版本

安装MySQL 8.0版本。

# 移动到安装目录
# mv /tmp/mysql-8.0.28-el7-x86_64 /mysql

# 创建新的符号链接
# rm -f /mysql/current
# ln -s /mysql/mysql-8.0.28-el7-x86_64 /mysql/current

# 检查安装结果
# ls -la /mysql/current/bin/
total 102400
drwxr-xr-x 2 root root 4096 Mar 31 10:15 .
drwxr-xr-x 10 root root 4096 Mar 31 10:15 ..
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysql
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqladmin
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqlbinlog
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqldump
-rwxr-xr-x 1 root root 40960000 Mar 31 10:15 mysqld
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqlimport

风哥提示：MySQL 8.0需要使用较新的glibc版本，确保操作系统满足要求。

6. 配置文件迁移

将旧版本的配置文件迁移到新版本，并根据MySQL 8.0的新特性进行优化。学习交流加群风哥QQ113257174

# 复制配置文件
# cp /mysql/current/my.cnf /mysql/current/my.cnf.bak

# 编辑配置文件，添加MySQL 8.0的新特性
# vi /mysql/current/my.cnf

# 配置文件内容
[mysqld]
basedir=/mysql/current
datadir=/mysql/data
port=3306
socket=/mysql/mysql.sock
user=mysql
bind-address=192.168.1.51

# 字符集设置
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 日志设置
log-error=/mysql/log/mysql-error.log
slow-query-log=1
slow-query-log-file=/mysql/log/mysql-slow.log
long-query-time=1

# 性能设置
max_connections=2000
innodb_buffer_pool_size=48G
innodb_log_file_size=2G
innodb_flush_method=O_DIRECT

# MySQL 8.0新特性：密码验证插件
default_authentication_plugin=caching_sha2_password

# MySQL 8.0新特性：持久化配置
persisted_globals_load=ON

# 安全设置
symbolic-links=0
skip-external-locking

[client]
socket=/mysql/mysql.sock

7. 启动服务

使用新版本的MySQL启动服务。

# 启动MySQL服务
# systemctl start mysql

# 检查服务状态
# systemctl status mysql
● mysql.service – MySQL Server
Loaded: loaded (/etc/systemd/system/mysql.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2026-03-31 10:20:00 CST; 1min ago
Main PID: 67890 (mysqld)
Tasks: 38
Memory: 2.5G
CGroup: /system.slice/mysql.service
└─67890 /mysql/current/bin/mysqld –defaults-file=/mysql/current/my.cnf

8. 升级验证

升级完成后，需要验证MySQL服务是否正常运行。

# 连接MySQL
# /mysql/current/bin/mysql -u root -p

mysql> use fgedudb;
Database changed

mysql> create table fgedu_users (
-> id int primary key auto_increment,
-> name varchar(50) not null,
-> age int,
-> email varchar(100)
-> ) engine=InnoDB default charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into fgedu_users (name, age, email) values (‘测试用户’, 25, ‘test@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from fgedu_users;
+—-+———–+—–+———————+
| id | name | age | email |
+—-+———–+—–+———————+
| 1 | 测试用户 | 25 | test@fgedu.net.cn |
+—-+———–+—–+———————+
1 row in set (0.00 sec)

mysql> status;
————–
/mysql/current/bin/mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server – GPL)

Connection id: 8
Current database: fgedudb
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 8.0.28 MySQL Community Server – GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 360 sec

Threads: 2 Questions: 15 Slow queries: 0 Opens: 131 Flush tables: 3 Open tables: 48 Queries per second avg: 0.004
————–

mysql> exit

9. 性能调优

MySQL 8.0引入了一些新的性能特性，需要进行相应的调优。更多学习教程公众号风哥教程itpux_com

# 编辑配置文件
# vi /mysql/current/my.cnf

# MySQL 8.0性能调优

[mysqld]

# 基本配置
basedir=/mysql/current
datadir=/mysql/data
port=3306
socket=/mysql/mysql.sock
user=mysql
bind-address=192.168.1.51

# 字符集设置
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 日志设置
log-error=/mysql/log/mysql-error.log
slow-query-log=1
slow-query-log-file=/mysql/log/mysql-slow.log
long-query-time=1
general-log=0
general-log-file=/mysql/log/mysql-general.log

# 性能设置
max_connections=2000
max_connect_errors=1000000
wait_timeout=300
interactive_timeout=300

# InnoDB设置
innodb_buffer_pool_size=48G
innodb_buffer_pool_instances=8
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_max_dirty_pages_pct=75
innodb_io_capacity=2000
innodb_io_capacity_max=4000

# MyISAM设置
key_buffer_size=256M
myisam_sort_buffer_size=128M

# 连接设置
max_allowed_packet=64M
net_buffer_length=16384

# 查询缓存设置
query_cache_type=0
query_cache_size=0

# MySQL 8.0新特性：密码验证插件
default_authentication_plugin=caching_sha2_password

# MySQL 8.0新特性：持久化配置
persisted_globals_load=ON

# MySQL 8.0新特性：并行复制
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4

# 安全设置
symbolic-links=0
skip-external-locking
skip-name-resolve

[client]
socket=/mysql/mysql.sock
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

生产环境建议：
– 根据服务器内存大小调整innodb_buffer_pool_size参数
– 启用并行复制以提高复制性能
– 合理设置连接池参数
– 定期监控MySQL的性能指标
– 利用MySQL 8.0的新特性，如持久化配置和密码验证插件

风哥提示：MySQL 8.0相比5.7版本有很多新特性，包括持久化配置、并行复制、密码验证插件等，建议详细阅读官方文档了解这些新特性。from:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html