# MySQL_帳號管理

### 1.新增帳號

#### 新增帳號

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

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

#### 賦予權限

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

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

--更新權限
flush privileges;
```

---

### 2.修改密碼

#### 查詢帳號

```shell
#先找到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)

```

#### 修改密碼

```shell
#修改密碼
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

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

```

#### 服務重啟登入

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

#### 修改密碼

```shell
#修改密碼
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

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

```

#### 重啟服務

```shell
[root@localhost ~]# systemctl restart mysqld
```