【MySQL】Ubuntu22.04中MySQL-V8安装和修改root密码

1、安装

sudo apt install mysql-server

2、安全配置

$ sudo mysql_secure_installation Securing the MySQL server deployment.Enter password for user root: VALIDATE PASSWORD PLUGIN 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 plugin?Press y|Y for Yes, any other key for No: y	// 配置验证密码强度的插件There are three levels of password validation policy:	//密码验证策略有三个级别:// 长度大于8
LOW    Length >= 8	
// 长度大于8，密码由数字、大小写字母和特殊字符组成
MEDIUM Length >= 8, numeric, mixed case, and special characters	
// 长度大于8，密码由数字、大小写字母和特殊字符组成，并且任意连续4个及以上的字母不能是字典中的单词
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Using existing password for root.Estimated strength of the password: 50 
Change the password for root ? (Press y|Y for Yes, any other key for No) : n 
// 是否更改root密码，我在安装MySQL时，root密码够复杂，这里不再更改... skipping.
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) : y	// 不允许root用户通过网络连接数据库
Success.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 // 删除test数据库，一般在正式部署之前删除它- 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过程中没有提示设置密码，需要手动设置root用户密码

1）查看默认用户和密码
sudo cat /etc/mysql/debian.cnf

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

2）使用默认用户和密码登录

mysql -u debian-sys-maint -p

输入上面 password 对应的值

3）切换至mysql表

use mysql

4）查看策略

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

5）修改策略

set global validate_password.policy=LOW;

6）重置root密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'helloworld';

7）生效

FLUSH PRIVILEGES;

8）注意
低版本MySQL重置密码的SQL语句为：

UPDATE user SET authentication_string=PASSWORD('your_new_password') WHERE user='your_user';

使用 MySQL 8.0 或更高版本，应该使用以下语句：

ALTER USER 'your_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';

9）验证

mysql -u root -p

输入刚设置的密码，应该生效了