MySQL 8.0以上用户操作相关

最新推荐文章于 2024-06-27 11:29:06 发布

原创最新推荐文章于 2024-06-27 11:29:06 发布 · 369 阅读

0 ·

CC 4.0 BY-SA版权

MySql 专栏收录该内容

4 篇文章

订阅专栏

以下适应于8.0以上的版本
新创建远程连接用户
命令行
mysql -uroot -p
use mysql
CREATE USER ‘errorbook’@’%’ IDENTIFIED BY ‘Lwie@129’;
GRANT ALL ON . TO ‘errorbook’@’%’;
flush privileges;

在MySQL 8.0.11中，caching_sha2_password是默认的身份验证插件，而不是以往的mysql_native_password。有关此更改对服务器操作的影响以及服务器与客户端和连接器的兼容性的信息

ALTER USER ‘errorbook’@’%’ IDENTIFIED WITH mysql_native_password BY ‘Lwie@129’;
flush privileges;

使用SQLyog for mysql连接公司的服务器数据库，报错：错误号码1129,Host ‘XXXXXX’ is blocked because of many connection errors

show global variables like ‘%max_connect_errors%’;

set global max_connect_errors = 1000;

flush hosts;

///////////////////////////////////////////////////////以下为操作过程////////////////////////
Microsoft Windows [版本 6.1.7601]
版权所有 © 2009 Microsoft Corporation。保留所有权利。

C:\Users\kingo>cd C:\Program Files\MySQL\MySQL Server 8.0\bin

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 321
Server version: 8.0.16 MySQL Community Server - GPL

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.

mysql> use mysql
Database changed
mysql> CREATE USER ‘errorbook’@’%’ IDENTIFIED BY ‘Lwie@129’;
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL ON . TO ‘errorbook’@’%’;
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER ‘errorbook’@’%’ IDENTIFIED WITH mysql_native_password BY ‘Lwie@129’;
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> set global max_connect_errors = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> flush hosts;
Query OK, 0 rows affected (0.02 sec)

mysql>