Ubuntu 20.04 安装 mysql 并配置远程访问

---

一、使用 apt 安装 mysql 服务

# 更新软件包列表
apt update

# 安装mysql服务
apt install mysql-server

# 使用mysqladmin工具查看mysql版本
mysqladmin --version

# 启动mysql服务
systemctl start mysql

# 查看mysql服务运行状态
systemctl status mysql

二、初始化 mysql 数据库管理员用户密码

首先通过在root用户下执行 mysql 命令进入mysql命令行界面，然后执行SQL语句 alter user 'root'@'localhost' indentified with mysql_native_password by '密码'; 指定数据库管理员密码。

root@iZwz9fsfltolu74amg1v0rZ:/home/atreus# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> alter user 'root'@'localhost' indentified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
root@iZwz9fsfltolu74amg1v0rZ:/home/atreus# 

管理员密码初始化完成后就能通过 mysql -u root -p 命令以root用户连接到数据库了。

root@iZwz9fsfltolu74amg1v0rZ:/home/atreus# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> 

三、配置远程访问

首先通过 vim /etc/mysql/mysql.conf.d/mysqld.cnf 命令打开mysql配置文件，找到 bind-address 将其修改为 0.0.0.0。修改完成后通过 systemctl restart mysql 重启mysql服务。

通过 mysql -u root -p 命令连接到数据库，然后依次执行以下SQL语句：

# 切换数据库
use mysql;

# 查看状态
select host, user, plugin from user;

# 允许所有主机以root用户访问数据库
update user set host = '%' where user = 'root';

# 刷新权限数据
flush privileges;

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, plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
root@iZwz9fsfltolu74amg1v0rZ:/home/atreus# 

在本机测试远程连接：

atreus@MacBook-Pro % mysql -h 120.78.188.0 -u root -p        
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.33-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show databases;
+---------------------+
| Database            |
+---------------------+
| README_TO_RECOVER_A |
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
5 rows in set (0.06 sec)

mysql> quit
Bye
atreus@MacBook-Pro % 

---

参考：https://blog.csdn.net/weixin_38924500/article/details/106261971