MySQL安装及远程连接配置

MySQL 数据库
11

MySQL安装及远程连接配置

author: histonevon@zohomail.com

date: 09/11/2021

环境

  • 系统 Ubuntu Server 20.04 LTS

  • MySQL Server version 8.0.26-0ubuntu0.20.04.2 (Ubuntu)

安装MySQL

更新软件源

 sudo apt update

安装MySQL Server

 sudo apt install mysql-server

验证MySQL服务运行状况

 sudo systemctl status mysql

  • 如果输出以下内容则显示正在运行

 ● mysql.service - MySQL Community Server
      Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
      Active: active (running) since Sat 2021-09-11 02:48:50 UTC; 33s ago
    Main PID: 4133 (mysqld)
      Status: "Server is operational"
       Tasks: 38 (limit: 9448)
      Memory: 353.8M
      CGroup: /system.slice/mysql.service
              └─4133 /usr/sbin/mysqld
 ​
 Sep 11 02:48:50 hitiotdatabases systemd[1]: Starting MySQL Community Server...
 Sep 11 02:48:50 hitiotdatabases systemd[1]: Started MySQL Community Server.

安全配置

  • MySQL安装随附一个名为 mysql_secure_installation 的脚本，可轻松提高数据库服务器的安全性。

调用不带参数的安全配置脚本

 sudo mysql_secure_installation

  • 将会引导用户进行配置

 Securing the MySQL server deployment.
 ​
 Connecting to MySQL using a blank password.
 ​
 VALIDATE PASSWORD COMPONENT can be used to test passwords
 and improve security. It checks the strength of password
 and allows the users to set only those passwords which are
 secure enough. Would you like to setup VALIDATE PASSWORD component?
 ​
 Press y|Y for Yes, any other key for No: y

  • 密码验证策略分为三个级别：低，中和强。按下y如果你想设置的验证密码插件或任何其他键移动到下一个步骤：

 There are three levels of password validation policy:
 ​
 LOW Length >= 8
 MEDIUM Length >= 8, numeric, mixed case, and special characters
 STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
 ​
 Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

策略

级别

描述

LOW Length

0

仅验证长度>=8

MEDIUM Length

1

不仅验证长度>=8，且要求有数字、大/小写英文字母、特殊字符

STRONG Length

2

不仅验证长度>=8，且要求有数字、大/小写英文字母、特殊字符、字典文件

  • 在下一个提示符下，将要求您设置MySQL root用户的密码：

 Please set the password for root here.
 ​
 New password: 
 ​
 Re-enter new password:

  • 如果您设置了验证密码插件，该脚本将向您显示新密码的强度。键入y以确认密码：

 Estimated strength of the password: 50 
 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

  • 接下来，将要求删除匿名用户，限制root用户对本地计算机的访问，删除测试数据库并重新加载特权表。所有问题需要回答 y 。

在服务器本地以root身份登录

  • 在MySQL 8.0上，默认情况下，root用户通过 auth_socket 插件进行身份验证。

  • 该 auth_socket 插件对 localhost 通过Unix套接字文件从进行连接的用户进行身份验证。这意味着不能通过提供密码来以root用户身份进行身份验证。

  • 要以root用户身份登录到MySQL服务器，需要输入

 sudo mysql

  • 将提供MySQL Shell，如下所示：

 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 12
 Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)
 ​
 Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
 ​
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 ​
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 ​
 mysql>

配置远程登录MySQL Server

  • 推荐使用新建专用用户进行远程访问

创建用户

 CREATE USER 'administrator'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd';

  • mysql_native_password 使用密码登录

  • administrator 用户名，可自定义

  • passwd 密码，自定义，需要符合之前所设的安全策略，否则会出现：

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

  • 可以使用如下语句更改安全策略

 set global validate_password.policy=LOW; # 更改为最低安全策略

  • 使用以下语句查看安全策略

 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      |
 +--------------------------------------+--------+

  • 也可以先创建用户，再更改认证方式为 mysql_native_password

 create user 'administrator'@'%' identified by 'passwd';
 ALTER USER 'administrator'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd';

  • 使用以下语句查看用户认证方式

 use mysql;
 select host, user, authentication_string, plugin from user;

更改用户权限

 grant all privileges on *.* to 'administrator'@'%';

  • 刷新权限

 flush privileges;

配置可访问ip

  • 默认状态下Mysql不允许除本机外的主机访问（SSH除外）

  • 不进行配置可能会出现 10061 "Unknown error" 错误

  • 打开 /etc/mysql/mysql.conf.d/mysqld.cnf 文件（使用root权限，否则为只读文件）

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

  • 在 bind-address=127.0.0.1 前加 # 注释

 #
 # The MySQL database server configuration file.
 #
 # One can use all long options that the program supports.
 # Run program with --help to get a list of available options and with
 # --print-defaults to see which it would actually understand and use.
 #
 # For explanations see
 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
 ​
 # Here is entries for some specific programs
 # The following values assume you have at least 32M ram
 ​
 [mysqld]
 #
 # * Basic Settings
 #
 user        = mysql
 # pid-file  = /var/run/mysqld/mysqld.pid
 # socket    = /var/run/mysqld/mysqld.sock
 # port      = 3306
 # datadir   = /var/lib/mysql
 ​
 ​
 # If MySQL is running as a replication slave, this should be
 # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
 # tmpdir        = /tmp
 #
 # Instead of skip-networking the default is now to listen only on
 # localhost which is more compatible and is not less secure.
 # bind-address      = 127.0.0.1
 mysqlx-bind-address = 127.0.0.1
 #
 # * Fine Tuning
 #
 ...

  • 保存关闭文件

  • 重启mysql，可能需要填入系统认证信息

 service mysql restart

  • 此时使用远程连接即可成功

配置MySQL开机自启

 sudo update-rc.d mysql defaults