Ubuntu24.04安装mysql-server小计，解决mysql_secure_installation时不能重置密码的问题

Ubuntu24.04安装mysql-server小计，解决mysql_secure_installation时不能重置密码的问题

为什么要写这往篇文章？

一般情况下，我安装mysql都用源码编译，以此方便安装更多自定义插件，但这次只需要安装一台开发机，无需太多要求。机器上安装的是ubuntu24.04，本着省时省力的想法，用官方的apt安装。结果，，，，很久没有搞定重设密码问题。绕了一圈，终究搞定了，但花的时间也不少，因此，写个备忘录，以便后需。

安装

1. apt仓库方式安装

sudo apt update
sudo apt install mysql-server -y
sudo systemctl status mysql
sudo systemctl start mysql

2.设置账号

sudo mysql_secure_installation

按照提示完成以下步骤：

• 设置root用户密码
• 移除匿名用户
• 禁止root远程登录
• 移除测试数据库并重新加载权限表

执行过程需要输入 Y N Y Y，根据情况自行选择

root@fred-4:/home/fred-4# sudo mysql_secure_installationSecuring the MySQL server deployment.Connecting to MySQL using a blank password.
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.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.Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.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! 

注意：Skipping password set for root as authentication with auth_socket is used by default. 密码设置已被跳过。

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.
设置了匿名用户。。
那该怎么登录呢？

就是不要输登录用户直接进入：

$ mysql
ERROR 1045 (28000): Access denied for user 'my-ubuntu-user'@'localhost' (using password: NO)

完犊子，明明只输入了mysql ,执行的却是mysql -u ‘my-ubuntu-user’@‘localhost’

咋办？继续看吧

匿名登录方法

进入超级用户环境，再进mysql

$ sudo su
$ mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.37-0ubuntu0.24.04.1 (Ubuntu)Copyright (c) 2000, 2024, 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> 

OK，搞定

进去了，接下来要改密码

修改密码

mysql> select user, plugin,host from mysql.user;
+------------------+-----------------------+-----------+
| user             | plugin                | host      |
+------------------+-----------------------+-----------+
| root             | auth_socket           | localhost |
+------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

plugin auth_socket 要换掉
换成下面的

mysql> select user, plugin,host from mysql.user;
+------------------+-----------------------+-----------+
| user             | plugin                | host      |
+------------------+-----------------------+-----------+
| root             | mysql_native_password | localhost |
+------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

服了吧，报错了，这是密码强度不够

SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| 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      |
+-------------------------------------------------+--------+

validate_password.policy由于是内部测试机，这项改低一点，不然以前的项目都得改

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql>  set global validate_password.length=6;
Query OK, 0 rows affected (0.00 sec)

现在可以改简单密码了

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.08 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

查看plugin

mysql> select user, plugin,host from mysql.user;
+------------------+-----------------------+-----------+
| user             | plugin                | host      |
+------------------+-----------------------+-----------+
| root             | mysql_native_password | localhost |
+------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

搞定了

接下来可以exit退出超级用户登录了

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37-0ubuntu0.24.04.1 (Ubuntu)Copyright (c) 2000, 2024, 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

其它配置

$ sudo nano /etc/mysql/my.cnf

如下配置安需修改

GNU nano 7.2                                                    /etc/mysql/my.cnf                                                              
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# 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#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
port = 3307
mysqlx_port = 33070
default_authentication_plugin = mysql_native_password

重启，自启

 sudo systemctl restart mysqlsudo systemctl enable mysql

修改root用户，允许远程登录

mysql> update mysql.user set host = '%' where user='root' and host='localhost';
mysql> FLUSH PRIVILEGES;

新建用户

mysql> set global validate_password.policy=0;
mysql> set global validate_password.length=6;mysql> create user 'my'@'%' identified by '123456';
mysql> grant all privileges on *.* to 'my'@'%' with grant option;

回收权限

mysql> REVOKE privileges ON *.* FROM 'my'@'%';
1227 - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

root用户没有SYSTEM_USER权限。

mysql> grant SYSTEM_USER on *.*  to 'root';
mysql> flush privileges;

删除用户

mysql> DROP USER 'my'@'%';