溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下文主要給大家帶來管理MySQL用戶主要有哪些功能，希望這些文字能夠帶給大家實際用處，這也是我編輯管理MySQL用戶主要有哪些功能這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。
1. MySQL用戶管理
1 2 3 4 5 | 'user'@'host'; host:IP、主機名、NETWORK、%(任意長字符),_(任意單個字符) skip_name_resolve={ON|OFF} 跳過主機名解析 [root@node2 ~]# vim /etc/my.cnf skip_name_resolve=ON |
2.查看用戶
示例：
1 | MariaDB [mysql]> SELECT User,Host,Password FROM user; |
3.創建用戶
CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]
示例：
1 | MariaDB [(none)]> CREATE USER 'tom'@'127.0.0.1' IDENTIFIED BY 'liumanlin' , 'jerry'@'%' IDENTIFIED BY 'liumanlin'; |
4.重命名：RENAME USER
RENAME USER old_user TO new_user[, old_user TO new_user] ...
示例：
1 | MariaDB [mysql]> RENAME USER 'tom'@'127.0.0.1' TO 'jerry'@'172.18.%.%'; |
5.刪除用戶
DROP USER 'user'@'host' [, 'user'@'host'] ...
示例：
1 2 | MariaDB [mysql]> DROP USER 'jerry'@'%'; MariaDB [mysql]> DROP USER ''@'localhost'; |
6.讓MySQL重新加載授權列表
FLUSH PRIVILEGES;
示例：
1 | MariaDB [mysql]> FLUSH PRIVILEGES; |
7.修改用戶密碼
(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password'); PASSWORD是MySQL內建加密函數
示例：
1 2 | MariaDB [mysql]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('liumanlin'); MariaDB [mysql]> FLUSH PRIVILEGES; |
(2) UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='USERNAME' AND Host='HOST';
示例：
1 2 | MariaDB [mysql]> UPDATE user SET Password=PASSWORD('liumanlin') WHERE User='root' AND Host='127.0.0.1'; MariaDB [mysql]> FLUSH PRIVILEGES; |
(3) mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'
示例：
1 | [root@node2 ~]# mysqladmin -h227.0.0.1 -uroot -p password 'liumanlin'; |
8.忘記管理員密碼的解決辦法
(1) 啟動mysqld進程時，使用--skip-grant-tables和--skip-networking選項
示例：
CentOS 7：
1 2 3 4 | [root@node2 ~]# vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking [root@node2 ~]# systemctl daemon-reload [root@node2 ~]# systemctl restart mariadb.service |
CentOS 6：
1 | [root@node2 ~]# vim /etc/init.d/mysqld 同理 |
(2) 通過UPDATE命令修改管理員密碼
示例：
1 2 3 | MariaDB [mysql]> UPDATE user SET Password=PASSWORD('liumanlin') WHERE User='root' AND Host='127.0.0.1'; [root@node2 ~]# vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr |
(3) 以正常方式啟動mysqld進程；
示例：
1 2 | [root@node2 ~]# systemctl daemon-reload [root@node2 ~]# systemctl restart mariadb.service |
9.授權：GRANT
1 2 3 4 5 | GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...] |
object_type(對象類型):
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*(所有庫的所有表)
| db_name.*(指定庫的所有表)
| db_name.tbl_name(指定庫的指定表)
| tbl_name(指定表)
| db_name.routine_name(指定庫的指定函數)
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
示例1：
1 2 3 4 5 6 7 8 9 10 | MariaDB [mysql]> GRANT CREATE ON hidb.* TO 'jerry'@'172.18.%.%'; [root@node2 ~]# mysql -ujerry -h272.18.67.12 -p MariaDB [(none)]> CREATE DATABASE hidb; MariaDB [(none)]> use hidb; MariaDB [hidb]> CREATE TABLE tbl1 (name CHAR(20)); MariaDB [hidb]> CREATE INDEX test ON tbl1(name); ERROR 1142 (42000): INDEX command denied to user 'jerry'@'172.18.67.12' for table 'tbl1' (無權創建索引，用以下方法) MariaDB [mysql]> GRANT INDEX ON hidb.* TO 'jerry'@'172.18.%.%'; MariaDB [mysql]> SHOW GRANTS FOR 'jerry'@'172.18.%.%'; MariaDB [hidb]> CREATE INDEX test ON tbl1(name); (授權成功) |
示例2：
1 2 3 4 5 6 7 8 | MariaDB [mysql]> CREATE USER 'tom'@'172.18.%.%' IDENTIFIED BY 'liumanlin'; [root@node2 ~]# mysql -utom -h272.18.67.12 -p (可正常登錄) MariaDB [mysql]> GRANT ALL ON hidb.* TO 'tom'@'172.18.%.%' REQUIRE SSL; (使用ssl授權登錄) MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%'; MariaDB [mysql]> FLUSH PRIVILEGES; [root@node2 ~]# mysql -utom -h272.18.67.12 -p Enter password: ERROR 1045 (28000): Access denied for user 'tom'@'172.18.67.12' (using password: YES) (無法連接，需指明ssl證書) |
示例3：
1 2 3 4 5 6 7 8 9 10 11 12 | MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+ |
10.查看授權：SHOW GRANTS
SHOW GRANTS [FOR 'user'@'host']
示例：
1 | MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%'; |
11.取消授權：REVOKE
1 2 3 4 5 | REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM 'user'@'host' [, 'user'@'host'] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... |
示例：
1 2 3 | MariaDB [mysql]> REVOKE CREATE VIEW ON hidb.* FROM 'tom'@'172.18.%.%'; MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%'; MariaDB [mysql]> FLUSH PRIVILEGES; |
對于以上關于管理MySQL用戶主要有哪些功能，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
