③:MySQL管理員常用命令總結

2022-01-31 程式設計師路人

大家好，我是路人。

這是 mysql 系列第 3 篇文章。

環境：mysql5.7.25，cmd 命令中進行演示。

在玩 mysql 的過程中，經常遇到有很多朋友在雲上面玩 mysql 的時候，說我創建了一個用戶為什麼不能登錄？為什麼沒有權限？等等各種問題，本文看完之後，這些都不是問題了。

本文主要內容Mysql 權限工作原理

mysql 是如何來識別一個用戶的呢？

mysql 為了安全性考慮，採用主機名+用戶名來判斷一個用戶的身份，因為在網際網路中很難通過用戶名來判斷一個用戶的身份，但是我們可以通過 ip 或者主機名判斷一臺機器，某個用戶通過這個機器過來的，我們可以識別為一個用戶，所以mysql 中採用用戶名+主機名來識別用戶的身份。當一個用戶對 mysql 發送指令的時候，mysql 就是通過用戶名和來源（主機）來斷定用戶的權限。

Mysql 權限驗證分為 2 個階段：

階段 1：連接資料庫，此時 mysql 會根據你的用戶名及你的來源（ip 或者主機名稱）判斷是否有權限連接階段 2：對 mysql 伺服器發起請求操作，如 create table、select、delete、update、create index 等操作，此時 mysql 會判斷你是否有權限操作這些指令權限生效時間

用戶及權限信息放在庫名為 mysql 的庫中，mysql 啟動時，這些內容被讀進內存並且從此時生效，所以如果通過直接操作這些表來修改用戶及權限信息的，需要重啟mysql或者執行flush privileges;才可以生效。

用戶登錄之後，mysql 會和當前用戶之間創建一個連接，此時用戶相關的權限信息都保存在這個連接中，存放在內存中，此時如果有其他地方修改了當前用戶的權限，這些變更的權限會在下一次登錄時才會生效。

查看 mysql 中所有用戶

用戶信息在mysql.user表中，如下：

mysql> use mysql;
Database changed
mysql> select user,host from user;
++----+
| user          | host         |
++----+
| test4         | 127.0.0.%    |
| test4         | 192.168.11.% |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
| test2         | localhost    |
++----+
6 rows in set (0.00 sec)
創建用戶
語法：
create user 用戶名[@主機名] [identified by '密碼'];
說明：
主機名默認值為%，表示這個用戶可以從任何主機連接 mysql 伺服器
示例 1：不指定主機名
不指定主機名時，表示這個用戶可以從任何主機連接 mysql 伺服器
mysql> use mysql;
Database changed

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

mysql> create user test1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> exit
Bye

C:\Users\Think>mysql -utest1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.
上面創建了用戶名為test1無密碼的用戶，沒有指定主機，可以看出 host 的默認值為%，表示test1可以從任何機器登錄到 mysql 中。
用戶創建之後可以在mysql庫中通過 select user,host from user;查看到。
其他示例
create user 'test2'@'localhost' identified by '123';
說明：test2 的主機為 localhost 表示本機，此用戶只能登陸本機的 mysql
create user 'test3'@% identified by '123';
說明：test3 可以從任何機器連接到 mysql 伺服器
create user 'test4'@'192.168.11.%' identified by '123';
說明：test4 可以從 192.168.11 段的機器連接 mysql
修改密碼【3 種方式】
方式 1：通過管理員修改密碼
SET PASSWORD FOR '用戶名'@'主機' = PASSWORD('密碼');
方式 2：create user 用戶名[@主機名][identified by '密碼'];
set password = password('密碼');
方式 3：通過修改 mysql.user 表修改密碼
use mysql;
update user set authentication_string = password('321') where user = 'test1' and host = '%';
flush privileges;
注意：
通過表的方式修改之後，需要執行flush privileges;才能對用戶生效。
5.7 中 user 表中的 authentication_string 欄位表示密碼，老的一些版本中密碼欄位是 password。
給用戶授權
創建用戶之後，需要給用戶授權，才有意義。
語法：
grant privileges ON database.table TO 'username'[@'host'] [with grant option]
grant 命令說明：
priveleges (權限列表)，可以是all，表示所有權限，也可以是select、update等權限，多個權限之間用逗號分開。ON 用來指定權限針對哪些庫和表，格式為資料庫.表名 ，點號前面用來指定資料庫名，點號後面用來指定表名，*.* 表示所有資料庫所有表。TO 表示將權限賦予某個用戶, 格式為username@host，@前面為用戶名，@後面接限制的主機，可以是 IP、IP 段、域名以及%，%表示任何地方。WITH GRANT OPTION 這個選項表示該用戶可以將自己擁有的權限授權給別人。注意：經常有人在創建操作用戶的時候不指定 WITH GRANT OPTION 選項導致後來該用戶不能使用 GRANT 命令創建用戶或者給其它用戶授權。備註：可以使用 GRANT 重複給用戶添加權限，權限疊加，比如你先給用戶添加一個 select 權限，然後又給用戶添加一個 insert 權限，那麼該用戶就同時擁有了 select 和 insert 權限。
示例：
grant all on *.* to 'test1'@『%』;
說明：給 test1 授權可以操作所有庫所有權限，相當於 dba
grant select on seata.* to 'test1'@'%';
說明：test1 可以對 seata 庫中所有的表執行 select
grant select,update on seata.* to 'test1'@'%';
說明：test1 可以對 seata 庫中所有的表執行 select、update
grant select(user,host) on mysql.user to 'test1'@'localhost';
說明：test1 用戶只能查詢 mysql.user 表的 user,host 欄位
查看用戶有哪些權限
show grants for '用戶名'[@'主機']
主機可以省略，默認值為%，示例：
mysql> show grants for 'test1'@'localhost';
+---+
| Grants for test1@localhost                                         |
+---+
| GRANT USAGE ON *.* TO 'test1'@'localhost'                          |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |
+---+
2 rows in set (0.00 sec)
show grants;
查看當前用戶的權限，如：
mysql> show grants;
+----+
| Grants for root@localhost                                           |
+----+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'localhost'              |
| GRANT DELETE ON `seata`.* TO 'root'@'localhost'                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+----+
4 rows in set (0.00 sec)
撤銷用戶的權限
語法
revoke privileges ON database.table FROM '用戶名'[@'主機'];
可以先通過show grants命令查詢一下用戶對於的權限，然後使用revoke命令撤銷用戶對應的權限，示例：
mysql> show grants for 'test1'@'localhost';
+---+
| Grants for test1@localhost                                         |
+---+
| GRANT USAGE ON *.* TO 'test1'@'localhost'                          |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |
+---+
2 rows in set (0.00 sec)

mysql> revoke select(host) on mysql.user from test1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test1'@'localhost';
+--+
| Grants for test1@localhost                                   |
+--+
| GRANT USAGE ON *.* TO 'test1'@'localhost'                    |
| GRANT SELECT (user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--+
2 rows in set (0.00 sec)
上面我們先通過grants命令查看 test1 的權限，然後調用 revoke 命令撤銷對mysql.user表host欄位的查詢權限，最後又通過 grants 命令查看了 test1 的權限，和預期結果一致。
刪除用戶【2 種方式】
方式 1：
drop user '用戶名'[@『主機』]，示例：
mysql> drop user test1@localhost;
Query OK, 0 rows affected (0.00 sec)
drop 的方式刪除用戶之後，用戶下次登錄就會起效。
方式 2：
通過刪除 mysql.user 表數據的方式刪除，如下：
delete from user where user='用戶名' and host='主機';
flush privileges;
注意通過表的方式刪除的，需要調用flush privileges;刷新權限信息（權限啟動的時候在內存中保存著，通過表的方式修改之後需要刷新一下）。
授權原則說明只授予能滿足需要的最小權限，防止用戶幹壞事，比如用戶只是需要查詢，那就只給 select 權限就可以了，不要給用戶賦予 update、insert 或者 delete 權限創建用戶的時候限制用戶的登錄主機，一般是限制成指定 IP 或者內網 IP 段初始化資料庫的時候刪除沒有密碼的用戶，安裝完資料庫的時候會自動創建一些用戶，這些用戶默認沒有密碼總結通過命令的方式操作用戶和權限不需要刷新，下次登錄自動生效通過操作 mysql 庫中表的方式修改、用戶信息，需要調用flush privileges;刷新一下，下次登錄自動生效本文中講到的一些指令中帶主機的，主機都可以省略，默認值為%，表示所有機器mysql 中用戶和權限的信息在庫名為 mysql 的庫中
mysql 系列大概有 20 多篇，喜歡的請關注一下！
最新資料

相關焦點

  • MySQL 常用命令手冊
    點擊關註上方「SQL資料庫開發」，常用功能命令 –add-drop-table 在每個create語句之前增加一個drop table   4.導入資料庫   1A:常用source 命令   2 3進入mysql資料庫控制臺，   4 5如mysql -u root -p   6 7mysql>use 資料庫   8 9然後使用source命令，後面參數為腳本文件
  • MySQL/MsSQL/Oracle提權總結
    3.掌握mysql資料庫的帳戶，從擁有對mysql的insert和delete權限，以創建和拋棄函數。4.擁有可以將udf.dll寫入相應目錄的權限。③操作步驟1.最常見的是直接使用udf.php此類的工具來執行udf提權，具體如下：連接到mysql以後，先導出udf.dll到 c:\windows\system32 目錄下。
  • MySQL - mysqldump常用命令 - linux運維菜
    導出數據1、備份全部資料庫的數據和表結構mysqldump -uroot -ppassword -A >all.sql2、只備份表結構，不備份數據mysqldump -uroot -ppassword -A -d > database.sql3、只備份資料庫，不備份表結構mysqldump -uroot -ppassword -A -t > data.sql
  • 你可能不知道 Mysql的常用命令收集
    首頁 > 語言 > 關鍵詞 > 資料庫最新資訊 > 正文 你可能不知道 Mysql的常用命令收集
  • linux常用命令總結
    Linux常用命令查詢一文件目錄操作二 查看文件內容三 文本內容處理命令『u』鍵恢復（命令模式下）常用操作：① 刪除光標所在的行1：進入命令模式2：dd②對一般文本進行編輯判斷伺服器的健康狀態查看系統所有的進程殺死進程① ps命令作用：查看系統中的進程信息語法：ps [-auxle]常用選項
  • Mysql 常用命令操作
    無論開發還是測試人員，在工作中經常涉及資料庫交互，需要去操作資料庫，這裡簡單的講下，並記錄一些常用的
  • MySQL資料庫常用命令詳解
    （1）登錄MySQL資料庫用SSH客戶端連接CentOS伺服器，打開終端命令輸入窗口，在終端輸入窗口輸入命令：mysql -uroot –p 該命令用root帳號以密碼方式登錄MySQL，回車後提示輸入密碼
  • MySQL的安裝與配置——詳細教程
    免安裝版的Mysql　　MySQL關是一種關係資料庫管理系統，所使用的 SQL 語言是用於訪問資料庫的最常用的標準化語言，其特點為體積小
  • 系統管理員常用的Powershell命令
    其中一個300級別的講座介紹了一些系統管理常用的命令。這些命令，豆子絕大部分都很熟悉，這裡再溫故知新一下。作為一個系統管理員，一般都對長篇大論的腳本，各種參數，循環，判斷語句，輸入輸出等等是敬而遠之的~但是短小精悍的命令行語句還是可以試試的。開始之前，先說兩個最最基本的命令。Get-Command 和 help。
  • Mysql資料庫備份和還原常用的命令
    備份MySQL資料庫的命令mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql備份MySQL資料庫為帶刪除表的格式備份MySQL資料庫為帶刪除表的格式，能夠讓該備份覆蓋已有資料庫而不需要手動刪除原有資料庫
  • MySQL 常用語法總結
    可以實現上述改變的命令是alter，其基本語法如下：　　alter table table_name alter_spec [， alter_spec ...]大多數資料庫對象可以下面的命令刪除：　　drop object_name　　mysql> DROP TABLE tb1;　　5．執行查詢　　查詢是使用最多的SQL命令。查詢資料庫需要憑藉結構、索引和欄位類型等因素。大多數資料庫含有一個優化器(optimizer)，把用戶的查詢語句轉換成可選的形式，以提高查詢效率。
  • MySQL 常用命令速查表:日常開發、求職面試必備良方!
    For server side help, type 'help contents'使用 help contents 命令可以獲取 MySQL 伺服器相關的命令參考：mysql> help contents;You asked for help about help category: "Contents"For
  • MySQL 常用show命令
    參考來源：網絡  責編 | 檸檸精品課：522字 |2 分鐘閱讀MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中對show命令的使用還容易產生混淆，本文匯集了show命令的眾多用法。
  • mysql 常用命令一
    【資料庫連接】 mysql -h ip -u name -ppassword -P port -D database_name>【資料庫SHOW命令】 顯示mysql中所有資料庫的名稱 show databases;#顯示mysql中的相關 show tables [ from db_name
  • mysql常用命令,保存一下,以後查詢方便了
    -u root -password 1234563、修改root用戶密碼為 abc123mysqladmin -u root -p123456 password abc1234、如果想去掉密碼：mysqladmin
  • ③:MySQL中 DDL 常見操作命令匯總
    對 mysql 感興趣的，加一下我的微信 itsoku，拉你入群交流技術。這是 Mysql 系列第 4 篇。環境：mysql5.7.25，cmd 命令中進行演示。mysql> drop table IF EXISTS test9;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>mysql> create table test9(    ->    a int not null comment '欄位a',    ->
  • 從零開始學MYSQL - MYSQL安裝
    ，第一反應是進入power shell的管理員模式：PS C:\Windows\system32> mysql -uroot -pmysql : 無法將「mysql」項識別為 cmdlet、函數、腳本文件或可運行程序的名稱。
  • MySQL EXPLAIN 命令詳解
    如果作用在表上，那麼此命令等同於DESC 表命令。UPDATE和DELETE 命令也需要進行性能改進，當這些命令不是直接在表的主碼上運行時，為了確保最優化的索引使用率，需要把它們改寫成SELECT 語句(以便對它們執行EXPLAIN 命令)。
  • MySQL如何完成一次查詢?
    那麼從發出一條sql指令到返回結果mysql都做了什麼事情呢？mysql完成一次查詢過程是比較複雜的，在說明查詢過程前先介紹一下它的基礎概念和結構原理來幫助理解。下面從四個方面介紹，分別是mysql語句，mysql結構原理，mysql查詢過程，最後設置幾個有趣問題。
  • Mysql常用語句,供小白學習使用
    作者:L_先生原文連結：https://tinyurl.com/yc2cuexeMysql最常用的命令1、顯示資料庫列表