MySQL_帳號管理

1.新增帳號

新增帳號

--新增使用者 test, 密碼 newpassword, 允許任何來源 %
CREATE USER 'test'@'%' IDENTIFIED BY 'newpassword';

--新增使用者 test, 密碼 newpassword, 只允許本機(localhost)
CREATE USER 'test'@'localhost' IDENTIFIED BY 'newpassword';

賦予權限

--所有權限
grant all privileges ON *.* TO 'test'@'%';

--db1 所有查詢權限
grant select ON 'db1'.* TO 'test'@'%';

--更新權限
flush privileges;

---

2.修改密碼

查詢帳號

#先找到user

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | bookstackuser |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

修改密碼

#修改密碼
mysql> alter user 'root'@'localhost' identified by 'sa1234Asdf';
#更新
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

---

3.忘記密碼(密碼回復)

1. 修改設定檔  /etc/my.cnf
2. 服務重啟登入
3. 修改密碼
4. 恢復設定檔 /etc/my.cnf
5. 重啟服務

修改設定檔 /etc/my.conf

#新增skip-grant-tables
[mysqld]
skip-grant-tables

服務重啟登入

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql

修改密碼

#修改密碼
mysql> alter user 'root'@'localhost' identified by 'newpassword'; 

#如果出現以下錯誤
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
## 先更新權限在修改密碼
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)


#更新權限
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

恢復設定檔 /etc/my.conf

#移除(註解) skip-grant-tables
[mysqld]
#skip-grant-tables

重啟服務

[root@localhost ~]# systemctl restart mysqld