mysql5.7升级至8.0,压缩包方式

冗焉

1205人浏览 · 2025-03-26 14:06:52

冗焉 · 2025-03-26 14:06:52 发布

mysql5.7升级至8.0,压缩包方式

一、MySQL8.0相较于MySQL5.7新特性
二、升级前准备
三、升级
四、my.cnf
五、后续操作
- 1. mysql_native_password 密码策略过时,官方建议更换
- 2.试行后删除备份
六、其他
- 1. 错误
- 2. 还原

目标服务器: CentOS Stream release 8 x86_64
自定义mysql位置: /home/soft/mysql
自定义mysql配置路径: /etc/my.cnf

cat /etc/redhat-release
cat /proc/version

一、MySQL8.0相较于MySQL5.7新特性

前几条会影响升级,注意更正

sql_mode参数默认值变化。需移除NO_AUTO_CREATE_USER
配置参数变化如:
- expire-logs-days=3 -> binlog_expire_logs_seconds=259200 ,(3天对应的秒数)
- innodb_log_file_size= 1G 和 innodb_log_files_in_group =2 ->
  innodb_redo_log_capacity=2147483648 ,(2G对应字节数)
- binlog_format -> binlog_row_image = FULL # 根据需求选择 FULL/MINIMAL/NOBLOB
MySQL 8.0 Group by默认不排序，切换后可能会导致业务返回的结果不对。
Datetime类型字段的值，不能等于空字符串，否则直接报错。
默认密码策略变更。
默认字符集由latin1变为utf8mb4。
MyISAM系统表全部换成InnoDB表。
JSON特性增强。
新增角色管理。
支持不可见索引，支持直方图。
支持窗口函数，支持Hash join。

二、升级前准备

1. Mysql-shell 检查工具兼容性

Mysql-shell 下载地址：https://dev.mysql.com/downloads/shell
注意架构,这里下载的是mysql-shell-8.0.41-linux-glibc2.28-x86-64bit.tar.gz
压缩包复制到服务器,解压

cd /home/soft/
tar -xf mysql-shell-8.0.41-linux-glibc2.28-x86-64bit.tar.gz
cd mysql-shell-8.0.41-linux-glibc2.28-x86-64bit/bin
#注意sock位置 可用cat /etc/my.cnf | grep socket 查看
./mysqlsh -uroot -p -S /home/soft/mysql/mysql.sock --verbose -e "util.checkForServerUpgrade()"

2. 下载mysql8.0

MySQL8.0官网下载

核心组件下载地址(mysql-8.0.41-linux-glibc2.28-x86_64)

3. 逻辑备份(可选,不建议操作)

备份也没用,纯心理安慰

#这里只备份了用户数据库,且指定了sock路径,备份期间完全锁定所有表,备份之前刷新二进制日志
/home/soft/mysql/bin/mysqldump -u root -p --socket=/home/soft/mysql/mysql.sock --lock-all-tables --flush-logs --databases eladmin file_manager tony_python > backup.sql

三、升级

# 3.1 停止数据库服务
service mysql stop
# 或者直接杀进程
ps aux | grep mysqld
kill  进程号

# 3.2 备份配置文件及物理文件
mv /etc/my.cnf /etc/my.cnf5
mv  /home/soft/mysql  /home/soft/mysql5

# 3.3 创建新的配置文件,见第四点
vi  /etc/my.cnf

# 3.4 解压,重命名mysql8
tar -xvf /home/soft/mysql-8.0.41-linux-glibc2.28-x86_64.tar.xz
mv  /home/soft/mysql-8.0.41-linux-glibc2.28-x86_64 /home/soft/mysql

# 3.5 复制原先的数据文件和日志,并修改权限
cp -r /home/soft/mysql5/data /home/soft/mysql/data
cp -r /home/soft/mysql5/log/ /home/soft/mysql/log/
chown -R mysql:mysql /home/soft/mysql
chmod -R 775 /home/soft/mysql

# 3.6 初次启动强制升级
/home/soft/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --upgrade=FORCE

# 过程中可新建中断查看日志
tail -n 100 /home/soft/mysql/log/error.log

# 3.7 添加mysql服务(可选)
ps aux | grep mysqld
kill 
cp -n /home/soft/mysql5/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
service mysql start

四、my.cnf

[mysqld]
# mysql8.4 使用mysql_native_password=ON
default-authentication-plugin=mysql_native_password
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
port = 3306
basedir = /home/soft/mysql
datadir = /home/soft/mysql/data
pid-file = /home/soft/mysql/mysql.pid
socket= /home/soft/mysql/mysql.sock
log-error = /home/soft/mysql/log/error.log
slow_query_log = 1
slow_query_log_file = /home/soft/mysql/log/slow.log
explicit_defaults_for_timestamp = true
user = mysql
bind-address = 0.0.0.0
server-id = 1
init-connect = 'SET NAMES utf8'
# 原先为 utf8
character-set-server = utf8mb4
skip-name-resolve
back_log = 100
log_bin_trust_function_creators = 1

max_connections = 300
max_connect_errors = 10000
open_files_limit = 20000
table_open_cache = 5000
max_allowed_packet = 250M
binlog_cache_size =50M
max_heap_table_size = 1G
tmp_table_size =2G
read_buffer_size = 326M
sort_buffer_size = 32M
join_buffer_size = 32M
thread_cache_size = 100
ft_min_word_len = 4
log_bin = mysql-bin
# 原先为binlog_format=MIXED
binlog_row_image=FULL
# 原先为expire-logs-days=3
binlog_expire_logs_seconds =259200
long_query_time = 5
performance_schema = 0

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 20000
innodb_buffer_pool_size = 45G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
# 原先为 innodb_log_file_size= 1G 和 innodb_log_files_in_group =2
innodb_redo_log_capacity=2147483648
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 3000
interactive_timeout = 3600
wait_timeout = 3600

[mysqldump]
quick
max_allowed_packet = 200M

五、后续操作

1. mysql_native_password 密码策略过时,官方建议更换

# 更新自定义用户即可
SELECT CONCAT('\'', user, '\'@\'', host, '\'') AS user, plugin FROM mysql.user;
ALTER USER 用户 IDENTIFIED WITH caching_sha2_password BY '密码';

# 刷新授权
FLUSH PRIVILEGES;

可以删除配置文件中的相关配置,重启服务
如default_authentication_plugin=mysql_native_password
或mysql_native_password=ON