Ubuntu 20.04 安装 MySQL 8.0 并且远程连接数据库(包括后续遇到的新坑)

最近在阿里云上部署了Ubuntu环境，装MySQL 8.0踩了一些坑，分享给大家。

(添加了一写后续的问题的记录)

保姆级教程,适合新手

---

先决条件

• 配置：阿里云ECS6
• 系统：Ubuntu 20.04
• 包括具有特权和防火墙的root用户或者可以使用sudo

---

第 1 步 - 安装 MySQL

在Ubuntu 18 以上默认都是MySql 8 不需要更改设置

要安装它，请使用以下命令更新服务器上的包索引apt：

sudo apt update

然后安装默认包：

sudo apt install mysql-server

这将安装 MySQL，但不会提示您设置密码或进行任何其他配置更改。因为这会使您的 MySQL 安装不安全，我们将在接下来解决这个问题。

---

第 2 步 - 配置 MySQL

对于全新安装，您需要运行包含的安全脚本。这会更改一些不太安全的默认选项，例如远程 root 登录和示例用户。在旧版本的 MySQL 上，您还需要手动初始化数据目录，但现在这是自动完成的。

运行安全脚本：

sudo mysql_secure_installation

这将带您完成一系列提示，您可以在其中对 MySQL 安装的安全选项进行一些更改。第一个提示会询问您是否要设置验证密码插件，该插件可用于测试您的 MySQL 密码的强度。无论您如何选择，下一个提示都是为 MySQL root 用户设置密码。输入并确认您选择的安全密码。

从那里，您可以按Y，然后ENTER接受所有后续问题的默认值。这将删除一些匿名用户和测试数据库，禁用远程 root 登录，并加载这些新规则，以便 MySQL 立即尊重您所做的更改。

user:~# 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: n
Please set the password for root here.

New password:  <你想要的数据库密码>

Re-enter new password:   <你的数据库密码>
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

 自定义设置

根据自己需求选择

• 删除匿名用户？（按y|Y表示是，任何其他键表示否）：y
• 禁止远程root登录？（按y|Y表示是，任何其他键表示否）：y
• 删除测试数据库并访问它？（按y|Y表示是，任何其他键表示否）：y
• 现在重新加载权限表？（按y|Y表示是，任何其他键表示否）：y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : n

 ... skipping.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

---

步骤 3 —（可选）调整用户身份验证和权限

在运行 MySQL 5.7（及更高版本）的 Ubuntu 系统中，root MySQL 用户auth_socket默认设置为使用插件而不是密码进行身份验证。在许多情况下，这允许更高的安全性和可用性，但是当您需要允许外部程序（例如，phpMyAdmin）访问用户时，它也会使事情复杂化。

为了使用密码以root身份连接到 MySQL ，您需要将其身份验证方法从 切换auth_socket到mysql_native_password。

请从终端打开 MySQL

sudo mysql

 接下来，使用以下命令检查每个 MySQL 用户帐户使用的身份验证方法：

SELECT user,authentication_string,plugin,host FROM mysql.user;

切换到mysql数据库

use mysql

您可以在此示例输出中看到root MySQL 用户现在使用密码进行身份验证。在您自己的服务器上确认后更改 "mysql" 数据库里的 "user" 表里的 "host" 项，将"localhost"改称"%"

访问权限说明：host默认都是localhost访问权限

localhost	本地连接
%	本地+远程连接

update user set host = '%' where user = 'root';

#刷新cache中配置
flush privileges;

 查看更改之前的修改

select host, user from user;

修改过后root用户的host应该是%  

 

 你也可以自定义root密码 

ALTER USER 'root'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'root'

退出mysql

exit

---

第 4 步 - 测试 MySQL

请检查其状态

无论您如何安装它，MySQL 都应该自动开始运行。要对此进行测试，请检查其状态。

systemctl status mysql

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-07-02 09:15:26 CST; 3min 28s ago
   Main PID: 48560 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 4483)
     Memory: 333.5M
     CGroup: /system.slice/mysql.service
             └─48560 /usr/sbin/mysqld

Jul 02 09:15:25 iZ2vc96ynokmnl2fdpcuf6Z systemd[1]: Starting MySQL Community Server...
Jul 02 09:15:26 iZ2vc96ynokmnl2fdpcuf6Z systemd[1]: Started MySQL Community Server.

如果出现问题 

如果出于某种原因，它没有自动启动，请使用以下命令启动并使其在系统启动时启动

systemctl enable mysql

 Synchronizing state of mysql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable mysql

对于额外的检查，您可以尝试使用该mysqladmin工具连接到数据库，该工具是一个允许您运行管理命令的客户端。例如，此命令表示以root ( -u root)身份连接到 MySQL ，提示输入密码 ( -p)，并返回版本。 

sudo mysqladmin -p -u root version

---

第 5 步 - 修改MySql远程连接配置

这里我遇到了坑

网上都是这样写的:

What IP address is MySQL listening on?

sudo netstat -plutn | grep 3306

If 127.0.0.1, then you will have to configure it to listen on all interfaces instead (127.0.0.1 and your droplet’s IP address). Edit /etc/mysql/my.cnf and set bind-address to 0.0.0.0:

bind-address = 0.0.0.0

Then, restart MySQL:

sudo service mysql restart

If you’re the only one who’s going to access MySQL externally, it’s recommended to keep it listening on 127.0.0.1 and using an SSH tunnel instead as it’s much more secure:

实际上,在Ubuntu >= 16,需要修改的目录为 

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

修改他 ！

vim /etc/mysql/mysql.conf.d/mysqld.cnf

vim的操作就不用讲了吧

输入 i 进入插入模式

找到他们，修改为0.0.0.0

bind-address            = 0.0.0.0

按下键盘esc

输入 :wq(保存退出) 回车

重启MySQL服务

sudo service mysql restart

或者

/etc/init.d/mysql restart

---

第 6 步 - 打开防火墙

使用 ss 命令检查开放端口

的SS命令可以用来显示哪些端口监听连接。它还显示它正在接受来自哪些网络的连接。

我们建议-ltn在命令中使用选项以查看简洁且相关的输出。让我们看一个关于我们的测试系统的例子。

sudo ss -ltn 

$ sudo ss -ltn 
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process      
LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:*                     
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*                     
LISTEN 0 70 127.0.0.1:33060 0.0.0.0:*                     
LISTEN 0 151 127.0.0.1:3306 0.0.0.0:*                     
LISTEN 0 5 [::1]:631 [::]:*                     
LISTEN 0 511 *:80

我们可以看到我们的服务器正在侦听端口 80、3306 和 33060 上的连接。这些是众所周知的与 HTTP 和 MySQL 相关的端口。 

检查 ufw 防火墙中打开了哪些端口

你应该记住一个重要的警告。在我们的本地系统上使用ss或者nmap localhost命令时，我们绕过了防火墙。实际上，这些命令显示处于侦听状态的端口，但这并不一定意味着端口对 Internet 开放，因为我们的防火墙可能拒绝连接。

使用以下命令检查 ufw 防火墙的状态。

sudo ufw status verbose

$ sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

从输出中，我们可以看到 ufw 拒绝传入连接。由于端口 80 和 3306 尚未添加为例外，因此 HTTP 和 MySQL 无法接收传入连接，尽管ss并nmap报告它们处于侦听状态。

让我们使用以下命令为这些端口添加例外。

sudo ufw allow 80/tcp
sudo ufw allow 3306/tcp

$ sudo ufw allow 80/tcp
Rule added
Rule added (v6)
$ sudo ufw allow 3306/tcp
Rule added
Rule added (v6)

我们可以再次检查 ufw 的状态，以查看端口现在是否已打开。

$ sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
80/tcp                     ALLOW IN    Anywhere                  
3306/tcp                   ALLOW IN    Anywhere                  
80/tcp (v6)                ALLOW IN    Anywhere (v6)             
3306/tcp (v6)              ALLOW IN    Anywhere (v6)

现在我们的两个端口在防火墙中打开并处于侦听状态。要了解有关 ufw firewall 的更多信息，包括命令示例，请查我们在 Linux上安装和使用 ufw firewall 的指南。 

注意:还需要配置阿里云服务器安全组规则，开放3306端口

简化版

[ Kthemis ] 大佬的简化版 Ubuntu20.04配置MySQL8.0_Kthemis的技术输出-CSDN博客

一、安装MySQL

$ sudo apt-get update #更新源
$ sudo apt-get install mysql-server #安装mysql
$ sudo mysql_secure_installation #按提示设置即可
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf #找到 bind-address 修改值为 0.0.0.0(如果需要远程访问)
$ sudo /etc/init.d/mysql restart #重启mysql

二、设置root账号为远程访问

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #修改加密规则
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码'; #使用mysql_native_password重新修改密码
mysql> UPDATE mysql.user SET host = '%' WHERE user = 'root'; #允许远程访问
mysql> FLUSH PRIVILEGES;

三、新增用户赋权并设置远程访问
 

mysql> CREATE USER 'sammy'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'%' WITH GRANT OPTION;

第 7 步 - 后续bug

7.1 连接得上却验证不通过

当我想把本地数据通过工具导入ECS的 8.0.26-0ubuntu0.20.04.2 (Ubuntu)中时

发现死活密码验证不成功,经过一番折腾后直接说结论

不使用root远程连接,创建新的MySQL用户顺利连上

创建用户

create user 'test1'@'localhost' identified by '‘密码';

flush privileges;刷新权限

其中localhost指本地才可连接

可以将其换成%指任意ip都能连接

也可以指定ip连接

修改密码

Alter user 'test1'@'localhost' identified by '新密码';

flush privileges;

授权

grant all privileges on *.* to 'test1'@'localhost' with grant option;

with gran option表示该用户可给其它用户赋予权限，但不可能超过该用户已有的权限

比如a用户有select,insert权限，也可给其它用户赋权，但它不可能给其它用户赋delete权限，除了select,insert以外的都不能

这句话可加可不加，视情况而定。

成了!重启MySQL生效!

7.2 无法重启MySQL

我兴致勃勃的

service mysql restart

然而

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

查看日志 mysqld.log 

直接说结论 /usr/lib/mysql 没有权限

chmod -R 777 /usr/lib/mysql #我全部给了,自己酌情而定

 再次重新启动!

service mysql restart

查看结果

systemctl status mysql.service

 好的 大功告成!

7.3 注意Linux下大小写敏感而Windows下不敏感!坑爹呢

网上说

来自  org.quartz.impl.jdbcjobstore.LockException - yshy - 博客园

喵喵的 最后发现不行，只有在数据库安装初始化之前，修改my.cnf才有效

通过lower_case_table_names在安装后使用新值重新初始化 MySQL 。

1. 停止 MySQL 服务：

   sudo service mysql stop

2. 删除 MySQL 数据目录：

   sudo rm -rf /var/lib/mysql

3. 重新创建 MySQL 数据目录（是的，仅删除其内容是不够的）：

   sudo mkdir /var/lib/mysql    
   sudo chown mysql:mysql /var/lib/mysql
   sudo chmod 700 /var/lib/mysql

4. 添加lower_case_table_names = 1到 中的[mysqld]部分/etc/mysql/mysql.conf.d/mysqld.cnf。
5. 使用以下命令重新初始化 MySQL --lower_case_table_names=1：

   sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

6. 启动 MySQL 服务：

   sudo service mysql start
   

 如果你还不行,建议重装 哈哈,看看大佬的博客

ubuntu下安装mysql大小写敏感问题_m0_48937226的博客-CSDN博客