Mysql5.7 用户授权和密码修改

Linux运维技术之路

发布于 2022-06-07 09:00:44

1.7K0

一、修改密码方法：

方法1：

set global validate_password_policy=LOW;
alter user 'root'@'localhost' identified by 'test';

方法2：

set global validate_password_policy=LOW;
set password for 'root'@'localhost'=password('test');

方法3：

set global validate_password_policy=LOW;
update mysql.user set authentication_string=password('test') where user='root' and Host = 'localhost';

刷新权限

flush privileges;

查询用户，主机名，加密字符串

select user,host,authentication_string from mysql.user;

二、用户授权

create database db_test;
create user 'demo'@'%' identified by 'test';
grant select,insert,delete,update on db_test.* to 'demo'@'%' ;
select user,host,authentication_string from mysql.user;
flush prvileges;

三、数据库开启主从后，从库为了防止别人误修改文件，开启只读模式，导致密码不能正确修改

mysql> update mysql.user set authentication_string=password('qaws213gt') where user='itdemo' and Host = '%';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

查询是否开启只读模式 read_only 为1 ，开启

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

解决方法：关闭只读模式

mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update mysql.user set authentication_string=password('demo') where user='itdemo' and Host = '%';
Query OK, 1 row affected, 1 warning (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)