mysql从5.6.31升级到8.0.31，超详细

#查看用户权限，为用户迁移做准备

select concat(‘create user "’,user,‘“@”’,host,‘";’ )
from mysql.user
where user
not in (‘root’,‘mysql.infoschema’,‘mysql.session’,‘mysql.sys’)
;

show grants for 用户名;

1、备份导出数据库数据
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

mysqldump -h192.168.171.128 -uroot -p’123456’ test> /usr/local/data/test.sql

2、备份配置文件
cp /etc/my.cnf /etc/my.cnf_20230717

3、备份压缩数据库文件
zip -r /usr/local/mysql_20230715.zip /usr/local/mysql/

4、停止mysql

systemctl stop mysqld

5、卸载mysql

#查看当前 mysql 安装状况
rpm -qa | grep -i mysql
#或
yum list installed | grep mysql

#卸载
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
#或
chkconfig --del mysql

#删除 mysql 相关文件
find / -name mysql
rm -rf xxx

#删除 my.cnf

rm -rf /etc/my.cnf

6、删除依赖
#检查依赖
rpm -qa|grep libaio
rpm -qa|grep net-tools
#删除依赖

yum remove mysql-libs
或者
rpm -e mariadb-libs --nodeps

7、按顺序安装
rpm -ivh mysql-community-common-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.31-1.el7.x86_64.rpm

8、启动mysql

systemctl start mysqld 启动mysql服务

service mysqld status 查看mysql服务状态

9、获取mysql 临时密码
grep ‘temporary password’ /var/log/mysqld.log

10、登录mysql修改密码和用户权限
mysql -h192.168.171.128 -u root -p

alter user ‘root’@‘localhost’ identified by ‘123456’; 修改密码

#修改root用户权限
use mysql
update user set host=‘%’ where user=‘root’;
grant all privileges on . to ‘root’@‘%’;
flush privileges;

11、添加开机自动启动

systemctl disable mysqld.service

12、创建用户

CREATE USER ‘9x_db’@‘%’ IDENTIFIED BY ‘123456’;

GRANT ALL ON ga_xiafa_zfw.* TO ‘9x_db’@‘%’；授权ga_xiafa_zfw数据库给9x_db用户
GRANT ALL ON zfwqzk_db.* TO ‘9x_db’@‘%’；

flush privileges;

13、修改mysql配置文件 vim /etc/my.conf

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#设置数据库默认字符集
character_set_server=utf8

#开启数据库bin.log日志增量
[mysqld]
#mysqlbinlog config
server-id=1
log-bin=/var/lib/mysql/mysql-bin
binlog_format=row
expire_logs_days=15
max_binlog_size=500m

#设置数据库慢查询日志
slow-query-log = on
long_query_time = 2

14、重启数据库

15、创建数据库
create database ga_xiafa_zfw;
create database zfwqzk_db;
create database yubei_qingxi_db;

16、导入数据

source /usr/local/data/ga_xiafa_zfw.sql;
source /usr/local/data/yubei_qingxi_db.sql;
source /usr/local/data/zfwqzk_db.sql;

17、验证数据