MySQL5.7升级8

服务器为CentOS7、MySQL5.7.27-log

下载地址https://downloads.mysql.com/archives/community/
(mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz)
先搭建mysql8，然后导入库

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

# 解压安装包
xz -d mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar

# 文件夹重命名为mysql8 
mv mysql-8.0.26-linux-glibc2.12-x86_64 mysql8

# 更改文件夹所属
chown -R jss.mysql /usr/local/mysql8/

配置文件my.cnf

/usr/local/mysql/my.cnf

#查找my.cnf
locate my.cnf

1、查看是否使用了指定目录的my.cnf

ps -ef|grep mysql

没有my.cnf则使用了默认路径的
2、查看mysql默认读取my.cnf的目录

[root@api-db etc]# mysql --help|grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 

顺序排前的优先

COLLATION ‘utf8_unicode_ci’ is not valid for CHARACTER SET ‘utf8mb4’

原因：数据库编码不一致

vi /etc/my.cnf
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysql.pid
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
user = jss
log-error = /usr/local/mysql/data/mysql.log
slow_query_log = ON
long_query_time = 1
server-id = 1 
log-bin = mysql-bin
binlog-format=ROW
max_connections=1000
log_bin_trust_function_creators=1
character-set-client-handshake = FALSE
character-set-server = UTF8MB4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
lower_case_table_names = 0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default_authentication_plugin=mysql_native_password

bulk_insert_buffer_size = 100M

mysqld: File ‘/usr/local/mysql/logs/mysql-bin.index’ not found (OS errno 13 - Permission denied)

chown -R jss:mysql /usr/local/mysql/

在MySQL目录下的bin目录下执行命令：

mysqld --initialize --console
#在data/mysql.log日志中有临时密码
#登录更改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pwd';
show databases;

Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

ln -s /usr/local/mysql/data/mysql.sock /tmp/mysql.sock

导出数据库

mysqldump -uroot -h 127.0.0.1 -p zabbix > /opt/zabbix.sql

导入数据库

mysql> create database zabbix;      # 创建数据库
mysql> use zabbix;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /opt/zabbix.sql  # 导入备份数据库

本地可以访问，远程无法连接mysql8，修改

create user root@'%' identified by 'pwd';
grant all privileges on*.* to root@'%'with grant option;
flush privileges;

缓存不足

Caused by: java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size
默认为256kb，my.cnf增加

sort_buffer_size=4MB