设置mysql

1、配置mysql

<code>sudo mysql_secure_installation

mysql_secure_installation脚本设置的东西：移除MySQL的匿名用户、禁止root远程登录、删除test数据库和重新加载权限。询问是否禁止远程登录时，输入N，否则会导致后面用navicat远程连接mysql失败，重新加载权限表我选的Y，其余的问题直接回车，使用上面的这些选项可以提高MySQL的安全。

2、测试mysql

systemctl status mysql.service

#得到以下内容

● mysql.service - MySQL Community Server

Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)

Active: active (running) since Sat 2024-06-15 19:42:46 CST; 1h 13min ago

Process: 6917 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Main PID: 6925 (mysqld)

Status: "Server is operational"

Tasks: 39 (limit: 9323)

Memory: 370.8M (peak: 383.2M)

CPU: 40.731s

CGroup: /system.slice/mysql.service

└─6925 /usr/sbin/mysqld

6月 15 19:42:46 jxl systemd[1]: Starting mysql.service - MySQL Community Server...

6月 15 19:42:46 jxl systemd[1]: Started mysql.service - MySQL Community Server.

3、启动mysql服务

sudo service mysql start

或

sudo systemctl start mysql.service

 4、设置mysql服务开机自启动

sudo service mysql enable

或

sudo systemctl enable mysql.service

5、配置mysql远程登录

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

将bind-address = 127.0.0.1修改为bind-address = 0.0.0.0

 修改完后保存并重启mysql服务

<code>sudo systemctl restart mysql

6、登录mysql

此时无法用root用户登录，只能使用mysql初始化的账户密码登录

账户及密码位于/etc/mysql/debian.cnf

jxl@jxl:~$ mysql -udebian-sys-maint -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

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.

7、使用初始账户及密码登录进去之后，创建root用户

由于之前初始化时选择了密码规则，我选的是比较复杂的那种，现在设置密码比较麻烦，报错如下

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

报错原因：密码设置不符合规则

尝试删除规则，但失败

mysql> uninstall plugin validate_password;

ERROR 1305 (42000): PLUGIN validate_password does not exist

修改密码规则，但失败

mysql> SET GLOBAL validate_password_policy = 'LOW';

ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'

查看当前选择的密码规则

mysql> SHOW VARIABLES LIKE 'validate_password%';

+-------------------------------------------------+--------+code>

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

+-------------------------------------------------+--------+

8 rows in set (0.01 sec)

再次修改密码规则，成功

mysql> SET GLOBAL validate_password.policy = 'LOW';

Query OK, 0 rows affected (0.00 sec)

重新创建root用户，成功

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你自己的密码';

Query OK, 0 rows affected (0.01 sec)

刷新权限

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

安装破解navicat