CentOS7.6安装配置MySQL8.0并设置远程连接

首先,centos官方仓库中没有MySQL的软件包(MySQL被收购了,原作者继续开发mariadb)只有mariadb软件包。

以下所有操作都是在云服务器上面操作的,ssh登录是root用户,如果是普通用户需要sudo。

安装

更新系统

1
2
yum update
yum upgrade

添加MySQL源

首先安装下载工具,一般都已经安装好了

1
2
yum install wget curl
yum install yum-utils

下载MySQL源,2020年6月16日地址是这个:

1
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

安装源软件包

1
yum localinstall mysql80-community-release-el7-3.noarch.rpm

查看是否添加成功

1
yum repolist all | grep mysql

我的输出为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ yum repolist all | grep mysql                              
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community disabled
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community disabled
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community disabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 153
mysql-connectors-community-source MySQL Connectors Community - disabled
mysql-tools-community/x86_64 MySQL Tools Community enabled: 110
mysql-tools-community-source MySQL Tools Community - Sour disabled
mysql-tools-preview/x86_64 MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview - Source disabled
mysql55-community/x86_64 MySQL 5.5 Community Server disabled
mysql55-community-source MySQL 5.5 Community Server - disabled
mysql56-community/x86_64 MySQL 5.6 Community Server disabled
mysql56-community-source MySQL 5.6 Community Server - disabled
mysql57-community/x86_64 MySQL 5.7 Community Server disabled
mysql57-community-source MySQL 5.7 Community Server - disabled
mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 177
mysql80-community-source MySQL 8.0 Community Server - disabled

安装软件包

1
yum install mysql-community-server

腾讯云网速比较快,也可以去直接下载对应平台的安装包

配置

服务

首先启动MySQL服务:

1
systemctl start mysqld

输出为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-06-17 21:53:50 CST; 58s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 14139 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 14222 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─14222 /usr/sbin/mysqld

Jun 17 21:53:42 VM_16_5_centos systemd[1]: Starting MySQL Server...
Jun 17 21:53:50 VM_16_5_centos systemd[1]: Started MySQL Server.

初始化

从 MySQL 5.7 开始,mysql 安装之后,会默认随机生成一个临时密码,因此登录之后需要修改密码

查看生成的初始密码:

1
grep 'temporary password' /var/log/mysqld.log

输出为:

1
2020-06-17T13:53:45.361778Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: v/#9W&q2ro(r

登录MySQL

1
mysql -u root -p

输入密码就登录成功了

login

修改密码

最好先看一下本文最后的校验方式修改。

1
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

sql语句大小写都可以。

输出为:

1
Query OK, 0 rows affected (0.01 sec)

新密码设置完成,可以退出重新登录试一试。

开机启动

1
systemctl enable mysqld

设置远程登录

首先,我们看一下用户和主机信息:

1
select user,host from user;

我的输出为:

1
2
3
4
5
6
7
8
9
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

也就是说root用户只允许localhost登录,其他IP登录不了。

需要修改一下:

1
2
3
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

将host修改为%表示所有的IP都可以访问。

修改访问权限:

1
2
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)

对所有的数据库表只要是root用户访问,赋予所有权限。

如果显示无权操作可以使用root登录操作,如果是root用户可以先退出在登录进行操作(我是这样)。

刷新权限:

1
2
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改配置文件:

centos 是在/etc/my.cnf

1
2
[mysqld]
bind-address=0.0.0.0

明显的变化就是:

1
2
3
4
5
6
7
$ netstat -an | grep 3306
tcp6 0 0 :::33060 :::* LISTEN
tcp6 0 0 :::3306 :::* LISTEN

$ netstat -an | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp6 0 0 :::33060 :::* LISTEN

远程登录

1
mysql -h ip -u user_name -p -P 3308

ip可以为127.0.0.1(本地)。

测试:

remote

可以看出带端口的登录失败了。

设置密码校验方式

我们看一下root用户的密码状态:

1
2
use mysql;
select * from user;

可以看到一些异常:

error

有的客户端不支持sha2,所以我们修改为native_password模式。

1
2
3
alter user 'root'@'%' identified with mysql_native_password by 'root';

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

提示密码不符合策略需要。

我们先查看一下当前策略:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

关于 mysql 密码策略相关参数:

  • validate_password.length 固定密码的总长度;

  • validate_password.dictionary_file 指定密码验证的文件路径;

  • validate_password.mixed_case_count 整个密码中至少要包含大/小写字母的总个数;

  • validate_password.number_count 整个密码中至少要包含阿拉伯数字的个数;

  • validate_password.policy 指定密码的强度验证等级,默认为 MEDIUM;
    关于 validate_password_policy 的取值:

    • 0/LOW:只验证长度;
    • 1/MEDIUM:验证长度、数字、大小写、特殊字符;
    • 2/STRONG:验证长度、数字、大小写、特殊字符、字典文件;
  • validate_password.special_char_count 整个密码中至少要包含特殊字符的个数;

只要不符合个数,哪怕比规定的多都不符合。

我们修改一下:

1
2
mysql> set global validate_password.length=16;
Query OK, 0 rows affected (0.00 sec)

新的输出为:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 16 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
1
2
mysql> alter user 'root'@'%' identified with mysql_native_password by '16位密码';
Query OK, 0 rows affected (0.01 sec)

也可以减低policy政策来通过密码检测,不过远程登录还是安全一点好。

新的user输出:

最好在设置密码之前设置这个。


CentOS7.6安装配置MySQL8.0并设置远程连接
https://blog.jackeylea.com/mysql/installation-and-setup-remote-access-of-mysql/
作者
JackeyLea
发布于
2020年6月17日
许可协议