安装

[root@almalinux9 ~]# uname -a
Linux almalinux9 5.14.0-427.13.1.el9_4.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Apr 30 18:22:29 EDT 2024 x86_64 x86_64 x86_64 GNU/Linux

[root@almalinux9 ~]# wget https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
mysql84-community-rele 100%[============================>]  12.83K  --.-KB/s    in 0s      

2024-12-08 11:53:51 (180 MB/s) - ‘mysql84-community-release-el9-1.noarch.rpm’ saved [13139/13139]

[root@almalinux9 ~]# rpm -ivh mysql84-community-release-el9-1.noarch.rpm 
warning: mysql84-community-release-el9-1.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID a8d3785c: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql84-community-release-el9-1  ################################# [100%]

[root@almalinux9 ~]# dnf epolist|grep mysql
No such command: epolist. Please use /usr/bin/dnf --help
It could be a DNF plugin command, try: "dnf install 'dnf-command(epolist)'"

[root@almalinux9 ~]# dnf repolist|grep mysql
mysql-8.4-lts-community                 MySQL 8.4 LTS Community Server
mysql-connectors-community              MySQL Connectors Community
mysql-tools-8.4-lts-community           MySQL Tools 8.4 LTS Community

yum install mariadb-server mariadb -y

systemctl start mysqld
systemctl enable mysqld

获取初始密码并改密

[root@almalinux9 ~]# cat /var/log/mysqld.log | grep password
# 2024-12-08T03:59:48.258509Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: A#q/Ad!na0kz

# 登录
[root@almalinux9 ~]# mysql -uroot -p

# 改密
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345';
# 如果不改密执行命令会提示：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
# 密码简单提示：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 这个其实与validate_password_policy的值有关。
# validate_password_policy有以下取值：
# Policy Tests Performed
# 0 or LOW Length
# 1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
# 2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file
# 默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

# 修改更复杂的密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '更复杂的密码';

# 调整密码验证规则
mysql> set global validate_password.policy=0;
mysql> set global validate_password.length=1;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypswd';

设置登录密码

mysql_secure_installation
# 注意：初始化的mysql数据库管理员并没有任何密码，按下回车键即可

# 之后，设置root密码，一次设置，一次确认

mysql -uroot -p
# 验证，输入新密码，如果能进去，说明设置成功

修改密码（已知密码）

mysqladmin -uroot -poldpswd password newpswd

重置密码（忘记密码）

# 停止MariaDB服务：
[root@almalinux9 ~]# systemctl stop mariadb

# 启动MariaDB的安全模式，跳过权限表，以便可以登录到MySQL服务：
[root@almalinux9 ~]# mysqld_safe --skip-grant-tables &

# 登录到MariaDB：
[root@almalinux9 ~]# mysql -u root

# 查看存在的用户信息
MariaDB [(none)]> select User,Password from mysql.user where user="root";
+------+-------------------------------------------+
| User | Password                                  |
+------+-------------------------------------------+
| root | *88CB212DD2B555C6D18D06FA96AF943C8F19E25D |
+------+-------------------------------------------+
1 row in set (0.001 sec)

# 修改密码（测试环境为 root）
MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
Query OK, 0 rows affected (0.000 sec)

# 可以看到，root密文已经更新
MariaDB [(none)]> select User,Password from mysql.user where user="root";
+------+-------------------------------------------+
| User | Password                                  |
+------+-------------------------------------------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+-------------------------------------------+
1 row in set (0.001 sec)

# 退出
MariaDB [(none)]> exit
Bye

# 测试登录
[root@almalinux9 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 


# 清空缓存信息（没有此步骤，则可能会出现新旧密码都能登陆，从而影响正常使用）

ps aux | grep mysql
# 找mysql相关进程
# 通过 kill-9 xxx 结束对应进程，在启动 mysql

新建数据库和用户

# 创建数据库（设置默认编码）
MariaDB [(none)]> CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.000 sec)

# 创建用户，用户名为 myuser，密码为 mypswd
MariaDB [(none)]> CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypswd';
Query OK, 0 rows affected (0.001 sec)

# 使用 GRANT 语句将特定权限授予新用户，以便他们可以访问和操作 mydb 数据库
MariaDB [(none)]> GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.001 sec)

# 使用 FLUSH PRIVILEGES 语句来使权限更改生效
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

# 设置编码
MariaDB [(none)]> ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; 
Query OK, 1 row affected (0.001 sec)

# 也需要检查并可能更改表的字符集：
SHOW FULL COLUMNS FROM auth_permission;
# 果发现 name 列的字符集不是 utf8mb4，可以使用以下命令来更改它：
ALTER TABLE auth_permission MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;