在 CentOS 7 下安装 MySQL 8 及配置

关键字

CentOS, MySQL, CentOS 7, MySQL 8

背景概述

MySQL 数据库软件的背景请参阅《在 CentOS 7 下安装 MariaDB 10.4.7》一文,它目前属于 Oracle 公司,其存储引擎 InnoDB 是最成熟的。Oracle 在 MySQL 的研发资源在近年也有增加的趋势,尤其是最新版更是有可观的进步,所以撰文以表肯定。

本文旨在指导你在 CentOS 7 下安装 MySQL 8。本安装过程应已经过笔者反复测试,以确保成功。

如果按本文安装时遇到任何问题,请留言或通过【桃花岛】官方联系我们。

软件依赖

  • CentOS 7
  • MySQL 8

具体步骤

查找最新版

浏览器打开官方网站 yum 仓库查看最新版,发现是 mysql80-community-release-el7-3.noarch.rpm,这里的 el7 是 Red Hat 公司 Enterprise Linux 的简写。常见的是 el7 和 el6, el5 旧到可以淘汰,el8 还未出现。

  • el7 软件包用于在 Red Hat 7.x, CentOS 7.x, and CloudLinux 7.x
  • el6 软件包用于在 Red Hat 6.x, CentOS 6.x, and CloudLinux 6.x

配置软件仓库

有两种选择:

  • 方式一:下载 rpm 配置文件,再使用 yum-config-manager 工具激活待安装版本
  • 方式二:编辑 .repo 仓库配置文件

方式一,下载 rpm 配置

yum -y install wget
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

此时已经下载配置了 rpm 配置,我们执行

yum repolist all | grep mysql

查看 MySQL rpm 配置中可用的版本,看到 mysql80-community

再执行

yum -y install yum-config-manager  
yum-config-manager --enable mysql80-community

返回

[root@localhost ~]# yum-config-manager --enable mysql80-community
Loaded plugins: fastestmirror
======================================================================================================== repo: mysql80-community =========================================================================================================
[mysql80-community]
async = True
bandwidth = 0
base_persistdir = /var/lib/yum/repos/x86_64/7
baseurl = http://repo.mysql.com/yum/mysql-8.0-community/el/7/x86_64/
cache = 0
cachedir = /var/cache/yum/x86_64/7/mysql80-community
check_config_file_age = True
compare_providers_priority = 80
cost = 1000
deltarpm_metadata_percentage = 100
deltarpm_percentage = 
enabled = True
enablegroups = True
exclude = 
failovermethod = priority
ftp_disable_epsv = False
gpgcadir = /var/lib/yum/repos/x86_64/7/mysql80-community/gpgcadir
gpgcakey = 
gpgcheck = True
gpgdir = /var/lib/yum/repos/x86_64/7/mysql80-community/gpgdir
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
hdrdir = /var/cache/yum/x86_64/7/mysql80-community/headers
http_caching = all
includepkgs = 
ip_resolve = 
keepalive = True
keepcache = False
mddownloadpolicy = sqlite
mdpolicy = group:small
mediaid = 
metadata_expire = 21600
metadata_expire_filter = read-only:present
metalink = 
minrate = 0
mirrorlist = 
mirrorlist_expire = 86400
name = MySQL 8.0 Community Server
old_base_cache_dir = 
password = 
persistdir = /var/lib/yum/repos/x86_64/7/mysql80-community
pkgdir = /var/cache/yum/x86_64/7/mysql80-community/packages
proxy = False
proxy_dict = 
proxy_password = 
proxy_username = 
repo_gpgcheck = False
retries = 10
skip_if_unavailable = False
ssl_check_cert_permissions = True
sslcacert = 
sslclientcert = 
sslclientkey = 
sslverify = True
throttle = 0
timeout = 30.0
ui_id = mysql80-community/x86_64
ui_repoid_vars = releasever,
   basearch
username = 

即表示配置成功。

方式二,编辑 .repo 配置文件(同样适用于旧版安装,如 5.7、5.6、5.5 )

 vim /etc/yum.repos.d/mysql-community.repo 

将下面的内容粘贴进去,保存,退出:

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

安装

接下来执行,下载二进制包有些大,耗时长,需要耐心等待:

yum -y install mysql-community-server

初步验证

运行

[root@localhost ~]# mysql -V

返回如下即表示 MySQL 新版已安装成功

mysql  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

添加本地 systemd 配置文件

如果想让 systemctl 运行本地配置文件,可创建 mysqld.service.d 目录,并添加 override.conf 配置如下:

mkdir /etc/systemd/system/mysqld.service.d
cd /etc/systemd/system/mysqld.service.d
vim override.conf

插入以下内容,并保存,退出

[Service]
PIDFile=/var/run/mysqld/mysqld-custom.pid
ExecStart=
ExecStart=/usr/sbin/mysqld --pid-file=/var/run/mysqld/mysqld-custom.pid $MYSQLD_OPTS

此时我们即可启动 mysqld 服务了

systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld

添加用户并授权

查看 root 初始密码,登录

如果不记得初始密码,可以用以下命令查看日志,其中包含初始化时 root 的临时密码

cat /var/log/mysqld.log

启动 MySQL 客户端,输入临时密码,查看所有用户,修改密码验证策略

[root@carbon ~]# mysql -u root -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT user, host FROM user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> SET global validate_password.policy=0;
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'PASSWORD'
授权 root 用户远程访问

MySQL 中用户是分主机的,’user’@’host’ 为一组,每组可以设置不同密码

CREATE USER 'root'@'%' INDENTIFIED BY 'admin';
ALTER USER 'root'@'%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;
创建远程新用户,授权表访问
CREATE DATABASE test;
CREATE USER 'mysql'@'%' INDENTIFIED BY 'admin';
CREATE USER 'mysql'@'localhost' INDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

此时,登录 mysql 只可查看到 information_schema 表,看不到 test 表

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON test.* TO 'mysql'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON test.* TO 'mysql'@'localhost';
FLUSH PRIVILEGES;

此时,登录 mysql 后可查看到 information_schema 和 test 表

设置密码验证方式
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
ALTER USER 'mysql'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
ALTER USER 'mysql'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
FLUSH PRIVILEGES;

因为默认的密码验证方式是 caching_sha2_password,很多 MySQL 客户端因为没有安装最新驱动导致连接不上,只有官方客户端及新版本 Navicat 有安装,PHP 中也需要额外安装。笔者认为这种不兼容性更新在一定程度上不是必要的,一劳永逸的方式是在配置文件 /etc/my.cnf 中设置 mysql_native_password

[mysqld]
default_authentication_plugin=mysql_native_password

问题汇总

问题:mysql 命令行修改密码时提示:

1819 (HY000): Your password does not satisfy the current policy requirements

解决:查看当前密码验证策略,如果是开发用,可以设置为最低要求,如果是生产环境,则需要慎重修改。

SHOW VARIABLES LIKE 'validate_password%';
SET global validate_password.policy=0;

相关文章

暂无

发表评论

电子邮件地址不会被公开。 必填项已用*标注