centos8安装MySQL8

依赖安装

sudo yum -y install libaio
sudo yum -y install ncurses-compat-libs
sudo ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5

用户准备

sudo groupadd mysql # 创建MySQL组
sudo useradd -r -g mysql -s /bin/false mysql # 添加mysql用户
sudo usermod -G mysql xxxx #把当前登录用户加入mysql组

下载

官网下载地址

解压

tar -xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
sudo mv mysql-8.0.22-linux-glibc2.12-x86_64/ mysql-8.0.22

创建mysql-files

sudo mkdir /usr/local/software/oracle/mysql-8.0.22/mysql-files
sudo chown mysql:mysql /usr/local/software/oracle/mysql-8.0.22/mysql-files
sudo chmod 750 /usr/local/software/oracle/mysql-8.0.22/mysql-files

创建data

sudo mkdir /usr/local/software/oracle/mysql-8.0.22/data
sudo chown mysql:mysql /usr/local/software/oracle/mysql-8.0.22/data/
sudo chmod 777 /usr/local/software/oracle/mysql-8.0.22/data

创建my.cnf

sudo touch /usr/local/software/oracle/mysql-8.0.22/my.cnf
sudo chown root:root /usr/local/software/oracle/mysql-8.0.22/my.cnf
sudo chmod 644 /usr/local/software/oracle/mysql-8.0.22/my.cnf

my.cnf内容如下

[mysqld]
# 设置3306端口
port=3306
  
# 设置mysql的安装目录
basedir=/usr/local/software/oracle/mysql-8.0.22
  
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/software/oracle/mysql-8.0.22/data 
  
# soket文件，本地连接时使用
socket=/usr/local/software/oracle/mysql-8.0.22/data/mysql.sock

# 错误日志
log-error=/usr/local/software/oracle/mysql-8.0.22/data/mysqld_error.log
 
# pid文件
pid-file=/usr/local/software/oracle/mysql-8.0.22/data/mysqld.pid
 
# 创建导入导出操作安全目录
secure_file_priv=/usr/local/software/oracle/mysql-8.0.22/mysql-files
 
# 允许最大连接数
max_connections=200
  
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
  
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
  
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
  
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
default-time-zone='+08:00' 
# 慢查询配置
slow_query_log=ON
slow_query_log_file =/usr/local/software/oracle/mysql-8.0.22/data/slow.log
# 单位时秒
long_query_time=2   

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
  
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

data目录中建立错误日志

touch /usr/local/software/oracle/mysql-8.0.22/data/mysqld_error.log
chown -R mysql /usr/local/software/oracle/mysql-8.0.22/data/mysqld_error.log
chgrp -R mysql /usr/local/software/oracle/mysql-8.0.22/data/mysqld_error.log

修改mysql.server

sudo vim /usr/local/software/oracle/mysql-8.0.22/support-files/mysql.server

# basedir=
basedir=/usr/local/software/oracle/mysql-8.0.22/
# datadir=
datadir=/usr/local/software/oracle/mysql-8.0.22/data

初始化mysql

sudo /usr/local/software/oracle/mysql-8.0.22/bin/mysqld --defaults-file=/usr/local/software/oracle/mysql-8.0.22/my.cnf --initialize --console

初始化完成后会给你一个root的默认密码一定要记住，等会登陆要用他。

配置环境变量、以及开机自启

环境变量

执行：vim /etc/profile，追加如下内容

export PATH=$PATH:/usr/local/software/oracle/mysql-8.0.22/bin

执行：source /etc/profile

开机自启

cp /usr/local/software/oracle/mysql-8.0.22/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on

启动mysql

systemctl start  mysqld.service

然后就可以使用mysql -u root -p 登陆了，这里要用到初始化时给你的密码了

重置用户的密码

alter user 'root'@'localhost'IDENTIFIED BY 'root';

允许外部访问

如果你是配置线上环境，建议不要直接开放root账号的允许外部访问，你可以新建立一个用户然后再开放，我这里是因为我配置的是本地所以无所谓。

use mysql;
update user set host='%' where user ='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql8.0 引入了新特性 caching_sha2_password

mysql8.0 引入了新特性 caching_sha2_password；这种密码加密方式Navicat 12以下客户端不支持；

Navicat 12以下客户端支持的是mysql_native_password 这种加密方式；

解决方案：新建立一个用户，并指定加密方式为mysql_native_password，同样不建议直接修改root的加密方式。

1.创建用户
create user 'native'@'%' identified by '‘native';
2.赋值权限
grant all privileges on *.* to 'native'@'%' with grant option;
3.修改该用户的密码策略
ALTER USER 'native'@'%' IDENTIFIED WITH mysql_native_password BY 'native';
4.刷新
FLUSH PRIVILEGES;

错误

每个错误报错信息可能一样，但是产生的原因会偶尔会不一致，这里是我自己配置的时候遇到的问题以及解决方法。

1.启动报错

[root@centos701 mysql]# mysql -u root -p 
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

解决： ln -s /usr/local/software/oracle/mysql-8.0.22/data/mysql.sock /tmp/mysql.sock

2.mysql启动失败，报没有pid错误

解决：首先确认你mysql服务是否启动了，如果启动了还有问题，修改 /usr/local/software/oracle/mysql-8.0.22/data的权限：sudo chmod 775 /usr/local/software/oracle/mysql-8.0.22/data

3.报错’/usr/local/software/oracle/mysql-8.0.22my.cnf’ is ignored

'/usr/local/software/oracle/mysql-8.0.22my.cnf' is ignored

解决：sudo chmod 644 /usr/local/software/oracle/mysql-8.0.22/my.cnf