wsl2（Ubuntu-22.04）配置mysql8.0

最新推荐文章于 2025-04-04 21:11:21 发布

菜梨子不想这么菜！

最新推荐文章于 2025-04-04 21:11:21 发布

阅读量4.9k

点赞数 22

分类专栏：数据库文章标签： ubuntu linux 运维

原文链接：https://blog.csdn.net/gandongusa/article/details/122964656

版权

数据库专栏收录该内容

4 篇文章

订阅专栏

这篇教程详细介绍了在Ubuntu 22.04上安装MySQL8.0的过程，包括使用sudo apt install命令快速安装，启动MySQL服务，通过service命令查看和管理服务状态。同时，教程还解决了root用户无法本地登录的问题，通过修改认证方式和密码，以及更新root用户的host权限为%，实现了远程访问。最后，提到了卸载MySQL的步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

该教程转载原地址：https://blog.csdn.net/gandongusa/article/details/122964656
原博主为ubuntu-20.04版本，本博主为ubuntu-22.04版本，因此本方法具备通用性。

1. MySQL8.0安装

因为ubuntu内置了最新的mysql8.0版本，因此快捷安装就直接这么整就行了，如果需要安装mysql5.7可以去看看官网的二进制安装。
如果需要在docker中安装可以去看docker相关拉镜像操作。

sudo apt install mysql-server

1.1 启动MySQL服务

WSL子系统Ubuntu中不包含systemctl命令，使用service命令

# 查看MySQL服务运行状态
 sudo service mysql status
 * MySQL is stopped.
 
 #启动MySQL服务
 sudo service mysql start
 * Starting MySQL database server mysqld 
 * su: warning: cannot change directory to /nonexistent: No such file or directory   [ OK ]

查看MySQL是否启动成功

# 查看MySQL服务运行状态
ps -ef | grep mysql
 
mysql    13613   120  0 15:49 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql    13760 13613  0 15:49 ?        00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=ZHOU-PC.pid
zhou     13828   121  0 15:51 pts/0    00:00:00 grep --color=auto mysql

sudo service mysql status

 * /usr/bin/mysqladmin  Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((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.

Server version          8.0.28-0ubuntu0.20.04.3
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 hours 1 min 31 sec

Threads: 2  Questions: 8  Slow queries: 0  Opens: 117  Flush tables: 3  Open tables: 36  Queries per second avg: 0.000

1.2 MySQL登录

mysql -u root -p

Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

1.2.1 第一种登录方法

root用户没有设置密码，不能从本地登录，可以使用sudo命令进入，此时不需要输入密码即可进入

sudo mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.28-0ubuntu0.20.04.3 (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>

1.2.2 第二种登录方法

MySQL在安装时会创建很多默认用户，其中就包含一个 debian-sys-maint，并且创建了该用户的随机密码，存储该用户信息的文件位于 /etc/mysql/debian.cnf文件中。

tree /etc/mysql
.
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian-start
├── debian.cnf
├── my.cnf -> /etc/alternatives/my.cnf
├── my.cnf.fallback
├── mysql.cnf
└── mysql.conf.d
    ├── mysql.cnf
    └── mysqld.cnf

sudo cat debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = YEd1uhCL4DsN5NBD
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = YEd1uhCL4DsN5NBD
socket   = /var/run/mysqld/mysqld.sock

可以利用debian-sys-main用户登录MySQL

mysql -u debian-sys-maint -pYEd1uhCL4DsN5NBD

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28-0ubuntu0.20.04.3 (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>

1.3 更改root用户密码

MySQL中用户相关信息存储于mysql数据库中，需要现转换到mysql数据库，分别用到如下命令
show databases; use database_name; show tables;

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

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> show tables;

+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.00 sec)

存储用户相关信息的内容位于user表格中，可以查看表格结构

mysql> desc user;

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)

mysql> select user,host,plugin from user;

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)bash

上面的localhost说明只允许本地登录

MySQL8.0 中用户默认认证方式为 caching_sha2_password，虽然安全性有保障，但是对于使用本地或者远程连接都造成了一定困扰。因此可以将用户认证方式改为MySQL5.X常用的mysql_native_password方式，只有此种方式才能在MySQL8.0中更改用户密码。

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '0';
Query OK, 0 rows affected (0.01 sec)

1.4 刷新缓存

输入flush privileges,刷新数据库。否则会保留在缓存中。

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

1.5 重启MySQL服务

退出mysql环境可以通过Ctrl+D或者输入quit退出回到Ubuntu环境。

mysql> ^DBye

mysql> quit
Bye

重新进入mysql环境就可以不用sudo在前面，直接使用mysql -u root -p即可
（由于本博主是在xshell中连接的，这里不sudo还是进不去，因为内置ban了su root，这里如果大家有问题留意下，不影响后面数据库连接的，就是进不去=3=）

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.28-0ubuntu0.20.04.3 (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> ;

重复上面的操作可以查看root用户认证方式

mysql> select user,host,plugin from user;

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

此时的root用户认证已经改为mysql_native_password方式。

1.6 设置root用户的远程访问

此时root用户的host属性仍然是localhost，也就是只能从本地访问，因此可以将root用户的访问权限由本地改为本地和外部都可以访问，将host的值由localhost改为 %。

mysql> update user set user.host='%' where user.user='root';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

输入flush privileges,刷新数据库。否则会保留在缓存中。

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,plugin from user;

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

此时root用户的host访问权限已经改为本地和外部都可以。
重启MySQL服务，即可实现远程连接。
WSL中的Ubuntu子系统访问可以直接使用127.0.0.1或localhost进行访问。

1.7 修改MySQL配置文件实现真正的远程访问

此时使用127.0.0.1或者localhost连接都没有问题，但是在外部一旦换成Ubuntu真正的IP地址访问就会报错。
在这里插入图片描述
这时还需要修改MySQL配置文件中的相关配置项 /etc/mysql//mysql.conf.d/mysqld.cnf

在这里插入图片描述
修改 bind-address = 0.0.0.0 。
重启MySQL后，再次通过IP地址远程连接。

在这里插入图片描述

2 MySQL8.0卸载

 sudo apt purge mysql-*
 sudo rm -rf /etc/mysql/ /var/lib/mysql
 sudo apt autoremove
 sudo apt autoclean