CentOS7在线安装并配置Mysql8.x

最近在项目中甲方给到有网络的环境要求安装Mysql8，记录一下安装和配置步骤

环境：CentOS7

一、官网下载Mysql8的RPM包

官网下载链接：http://repo.mysql.com/

二、将下载的RPM文件上传到服务器任意位置，这里我们先上传到/root下

三、查看是否有已安装的mysql或mariadb，有的话卸载掉，没有的话就不管

rpm -qa | grep mariadb
rpm -qa | grep mysql
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

三、安装rpm并下载mysql

rpm -ivh mysql80-community-release-el7-7.noarch.rpm --nodeps
yum -y install mysql-community-server

四、启动mysql并查看、重置密码

systemctl restart mysqld
cat /var/log/mysqld.log | grep localhost

mysql -u root -p
输入刚才cat出来的初始密码

alter user 'root'@'localhost' identified by 'AbC111@qq.coM'

因为mysql8的安全策略比mysql57的要严格，所以密码尽可能复杂点。

五、设置允许远程连接mysql

use mysql;
update user set host='%' where user='root';
flush privileges;
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

六、查看修改mysql密码验证方式

默认mysql8是caching_sha2_password验证方式

use mysql;
select host, user, authentication_string, plugin from user;
alter user 'root'@'%' identified with mysql_native_password by 'AbC111@qq.coM';
flush privileges;

七、去除only_full_group_by并关闭X Plugin

默认是有only_full_group_by的，这会导致java起来的时候执行group by命令会报错从而导致启动失败

set @@GLOBAL.sql_mode=
'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

set sql_mode=
'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

MySQL启动之后监听了两个端口3306和33060，是为MySQL5.7.12 之后新增了X plugin
在/etc/my.cnf里面添加下面一句

mysqlx=0
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

八、重启mysql并设置mysql开机自启

systemctl restart mysqld
systemctl enable mysqld