设置mysql

1、配置mysql

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

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%';
+-------------------------------------------------+--------+
| 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

略，有需要的私我，我再更新整理出来

navicat连接本地mysql服务

1、打开navicat：连接-->MySQL

连接名：随便

主机：本机IP

端口：默认3306

用户名：root

密码：刚刚创建root用户时设置的密码

2、测试连接，成功

3、确定-->确定，就可以看见自己创建的数据库了

双击连接名，即可激活数据库