溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
MySQL用戶賬號包括：用戶名@主機名
用戶名：16個字符以內
主機：主機有以下幾種表現方式
主機名： mysql
IP地址: 172.16.90.111
網絡地址：172.16.0.0/255.255.0.0
通配符：%，_ %：任意字符 _：任意一個
172.16.%.% %.magedu.com
權限級別：全局級別、庫級別、表級別、列級別、存儲過程和存儲函數級別
全局級別：SELECT * FROM db\G; 查詢全局庫級別的權限
**************************************************************************
CREATE USER username@host [IDENTIFIED BY 'password'] 創建用戶
DROP USER 'username'@'host'; 刪除用戶
RENAME USER old_name TO new_name; 重命名用戶
SHOW GRANTS FOR 'username'@'host'; 查看用戶權限列表
FLUSH PRIVILEGES ; 刷新權限列表
GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; 給用戶增加權限
REVOKE SELECT ON db.* FROM 'username'@'host'; 取消用戶的SELECT權限
**************************************************************************
**************************************************************************
MySQL數據庫ROOT用戶密碼忘記解決方案步驟：
第一：先關閉mysqld進程，并修改配置文件/etc/my.cnf
[root@lamp ~]# service mysqld stop #先停止mysqld進程
Shutting down MySQL.. [ OK ]
[root@lamp ~]# vim /etc/init.d/mysqld #修改mysqld的啟動腳本，修改內容如下紅色框內，保存退出
[root@lamp ~]# service mysqld start #啟動mysqld進程
Starting MySQL.. [ OK ]
[root@lamp ~]# mysql #此時登錄mysql即可不需要用戶名和密碼
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> USE mysql
Database changed
mysql> SELECT User,Host,Password FROM user; #查詢user表的三個字段，是需要密碼登錄的
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *A198E6EEE923DA319BBF86C99624479A198E6EEE9 |
| root | lamp | *A198E6EEE9823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *A198E6EEE9DA319BBF86C99624479A198E6EEE9 |
| root | ::1 | *A198E6EEE93DA319BBF86C99624479A198E6EEE9 |
| test | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> UPDATE user SET Password=PASSWORD('redhat') WHERE User='root'; #此時由于跳過
了grant權限列表，所以只能通過修改user表的Password字段的值來修改用戶密碼。
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0
mysql> SELECT User,Host,Password FROM user; #查詢user表的三個字段，是需要密碼登錄的
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | lamp | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | ::1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| test | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>\q
[root@lamp ~]# service mysqld stop #停止mysqld進程
Shutting down MySQL. [ OK ]
[root@lamp ~]# vim /etc/init.d/mysqld #修改啟動腳本，把之前修改的內容去掉后保存退出
[root@lamp ~]# service mysqld start #啟動mysqld進程
Starting MySQL.. [ OK ]
[root@lamp ~]# mysql #此時直接登錄mysql提示輸入用戶及密碼
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@lamp ~]# mysql -uroot -p #指定通過root賬號登錄 -p指定需要輸入密碼登錄
Enter password: 輸入正確的密碼后登錄mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> #至此mysql數據庫密碼遺忘的問題便解決了。
**************************************************************************
下表為權限對應的作用范圍：
創建用戶 CREATE USER：CREATE USER username@host [IDENTIFIED BY 'password']創建用戶，并通過IDENTIFIED BY 'password'，設定密碼。
Usage:
mysql> CREATE USER test@localhost IDENTIFIED BY 'test'; #創建一個用戶test本地數據庫賬號，
密碼為test。
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES ; #刷新權限列表
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR test@localhost\G; #查看數據庫賬戶test@localhost的權限列表
*************************** 1. row ***************************
Grants for test@localhost: GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
1 row in set (0.00 sec)
重新打開另一個客戶端，登錄以test賬號登錄mysql
[root@lamp ~]# mysql -utest -p #登錄mysql數據庫以test用戶，輸入用戶密碼
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> SHOW DATABASES; #登錄成功，查看該賬號下的數據庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
權限設定GRANT：GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; #指定權限PRIVILEGES ON指定對象名稱db.* object_type指定對象類型 TO username@host 指定用戶。
object_type對象類型有：TABLE（表） FUNCTION（函數） PROCEDURE(程序、庫)
Usage：GRANT EXECUTE ON FUNCTION db.abc TO 'username'@'host'; #授權給username@host用戶,對
db數據庫的abc函數有執行權限。
GRANT UPDATE(Age) ON db.testtb TO 'username'@'host'; #授權給username@host用戶對db數據庫的，testtb表的Age字段具有UPDATE權限。
取消權限設定REVOKE: REVOKE PRIVILEGES ON db.* FROM 'username'@'host'; #指定需取消什么
什么權限PRIVILEGES ON指定對象名稱db.* FROM 'username'@'host'; 指定取消哪個用戶的權限。
Usage： REVOKE SELECT ON db.* FROM 'testuser'@'%'; #取消testuser用戶對db數據庫的所有表
的SELECT權限。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
