CentOS7用yum安装MySQL8

0. 安装环境

CentOS7最小化安装

# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"

# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)

1. 配置YUM源

新建 vi /etc/yum.repos.d/mysql-community.repo，使用清华源，内容如下：

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=https://opentuna.cn/mysql/yum/mysql-connectors-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=https://opentuna.cn/mysql/yum/mysql-tools-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.6-community]
name=MySQL 5.6 Community Server
baseurl=https://opentuna.cn/mysql/yum/mysql-5.6-community-el7-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.7-community]
name=MySQL 5.7 Community Server
baseurl=https://opentuna.cn/mysql/yum/mysql-5.7-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-8.0-community]
name=MySQL 8.0 Community Server
baseurl=https://opentuna.cn/mysql/yum/mysql-8.0-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

2. 安装MySQL

# yum install -y mysql-community-server

3. 启动MySQL

# service mysqld start

4. 查看默认密码

加粗部分即为默认密码

# cat /var/log/mysqld.log | grep password
2021-07-25T12:29:02.180014Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: UsrXkeIG1A;0

5. 修改密码

5.1 登录MySQL

加粗部分即为默认密码

# mysql -p'UsrXkeIG1A;0'

5.2 修改root密码

加粗部分即为新密码，请牢记

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123' PASSWORD EXPIRE NEVER;

5.3 刷新权限

mysql> FLUSH PRIVILEGES;

5.4 退出MySQL

mysql> exit;

5.5 确认新密码

加粗部分即为新密码，能登录成功则新密码没问题

# mysql -pRoot@123

6. 创建新用户

安全起见，我们不允许root用户远程访问，创建新用户拥有CRUD权限

6.1 登录MySQL

mysql -p'Root@123'

6.2 创建新用户

加粗部分即为密码，请牢记

CREATE USER 'app'@'%' IDENTIFIED BY 'App@123456' PASSWORD EXPIRE NEVER;

6.3 为新用户授权

授予所有数据库的所有表的增加、修改、删除、查询、创建临时表、锁表、查看数据库的权限

GRANT INSERT,UPDATE,DELETE,SELECT,CREATE TEMPORARY TABLES,LOCK TABLES,SHOW DATABASES ON *.* TO 'app'@'%';

6.4 刷新权限

mysql> FLUSH PRIVILEGES;

6.5 退出MySQL

mysql> exit;

6.6 用新用户登录MySQL

加粗部分即为密码

# mysql -uapp -pApp@123456

6.7 确认新用户权限

1. 查看所有数据库，有权限

   mysql> show databases;

   Database

   information_schema

   mysql

   performance_schema

   sys

2. 创建数据库，没权限

   mysql> create database app;
   ERROR 1044 (42000): Access denied for user 'app'@'%' to database 'app'

3. 查询数据表，有权限

   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 host,user from user;

   host	user
   %	app
   localhost	mysql.infoschema
   localhost	mysql.session
   localhost	mysql.sys
   localhost	root

其他权限不在一一验证

6.8 验证新用户远程登录

SSH到另一台安装了MySQL Client的服务器

# mysql -h这里换成目标MySQL服务器的IP -uapp -pApp@123456

注意：目标MySQL服务器防火墙需要关闭或开放3306端口

• 关闭防火墙：systemctl stop firewalld && systemctl disable firewalld
• 开放3306端口：firewall-cmd --zone=public --add-port=3306/tcp --permanent && firewall-cmd --reload