mysql select skip_MySQL的skip-grant-tables

MySQL在Windows、Linux平台的安装，可以参考，

Windows下安装的MySQL 5.7，可以用如下指令，进行初始化、服务注册、启动服务，

C:\bisal\mysql\bin>mysqld --initialize --user=mysql --console2021-01-12T11:46:53.608737Z 0 [Warning] InnoDB: New log files created, LSN=457902021-01-12T11:46:53.889730Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2021-01-12T11:46:53.981412Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ddb683f6-54cb-11eb-ac61-0250f2000002.2021-01-12T11:46:53.996569Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2021-01-12T11:46:56.054507Z 0 [Warning] CA certificate ca.pem is self signed.2021-01-12T11:46:56.563731Z 1 [Note] A temporary password is generated for root@localhost: Bgbo>f4-Uv1jC:\bisal\mysql\bin>mysqld installService successfully installed.C:\bisal\mysql\bin>net start mysqlMySQL 服务正在启动 .MySQL 服务已经启动成功。

此时data路径下，就会出现这些数据文件，

看到一些教程，说在首次配置时，可以在配置文件my.ini中设置skip-grant-tables参数，

skip-grant-tables

顾名思义，该命令作用是跳过授权表，就是说谁都能进入MySQL看到所有数据表，输入任意字符账号密码都可以，当忘记账号密码时可以使用改命令修改密码，但是要随用随关，重启mysql，不然服务器上会有很大的风险。

输入登陆的指令，但是可任意输入密码，都可以登录，

C:\bisal\mysql\bin>mysql -u root -pEnter password: ******Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.32 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

查看用户，显示的是skip-grants user，说明确实skip-grants参数起了作用，

mysql> select current_user;+-----------------------------------+| current_user |+-----------------------------------+| skip-grants user@skip-grants host |+-----------------------------------+1 row in set (0.00 sec)

登陆后，可以update更新root的密码，注意5.7的user存储密码的字段名称是authentication_string，

mysql> update mysql.user set authentication_string=password('mysql') where user='root' and host='localhost';Query OK, 1 row affected, 1 warning (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 1

密码用password函数，进行了加密，

mysql> select password('mysql') from dual;+-------------------------------------------+| password('mysql') |+-------------------------------------------+| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |+-------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> select host, user, authentication_string from user;+-----------+---------------+-------------------------------------------+| host | user | authentication_string |+-----------+---------------+-------------------------------------------+| localhost | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |+-----------+---------------+-------------------------------------------+3 rows in set (0.00 sec)

当前模式下，而且支持用mysql直接进行登录，

C:\bisal\mysql\bin>mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.32 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

但是这种跳过授权表的操作，毕竟不安全，相当于开了后门，现在将skip-grant-tables注释，

# skip-grant-tables

然后在Windows的服务窗口重启MySQL服务，cmd-services.msc，

此时使用mysql，会提示1045，

C:\bisal\mysql\bin>mysqlERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

用mysql -u -root -p登录，同样提示1045的错误，

C:\bisal\mysql\bin>mysql -u -root -pEnter password: ************ERROR 1045 (28000): Access denied for user '-root'@'localhost' (using password: YES)

可以指定-h登录，

C:\bisal\mysql\bin>mysql -u root -h localhost -pEnter password: *****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.32 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

他会提示使用ALTER USRE重置密码，

mysql> use mysqlERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.mysql> alter user 'root'@'localhost' identified by 'mysql' password expire never;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

此时登陆的用户，不再是skip-grants，

mysql> select current_user;+----------------+| current_user |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)

MySQL我算小白，但是发现其实一些很小的知识点，例如登录，还是蕴涵了很多的原理，理解他的原理，多多实践，可能才会更深入的了解MySQL，当然这个过程，可能是很艰辛，还是要量变引起质变，各位共勉了。

参考，

https://blog.csdn.net/ibsfn/article/details/88963040

https://www.cnblogs.com/zhoushiya/p/12107201.html

https://www.cnblogs.com/jet-angle/p/11906671.html

近期更新的文章：

文章分类和索引：