Shulou(Shulou.com)06/01 Report--

The following mainly brings you what are the main functions of managing MySQL users. I hope these words can bring you practical use, which is also the main purpose of this article of editing and managing MySQL users. All right, don't talk too much nonsense, let's just read the following.

1. MySQL user management

12345 user names required hostnames; host:IP, hostname, NETWORK,% (any long character), _ (any single character) skip_name_resolve= {ON | OFF} Skip hostname resolution [root@node2 ~] # vim / etc/my.cnf skip_name_resolve=ON

two。 View users

Example:

1MariaDB [mysql] > SELECT User,Host,Password FROM user

3. Create a user

CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD]' password'] [, 'user'@'host' [IDENTIFIED BY [PASSWORD]' password'].]

Example:

1MariaDB [(none)] > CREATE USER 'tom'@'127.0.0.1' IDENTIFIED BY' liumanlin', 'jerry'@'%' IDENTIFIED BY' liumanlin'

4. Rename: RENAME USER

RENAME USER old_user TO new_user [, old_user TO new_user]...

Example:

1MariaDB [mysql] > RENAME USER 'tom'@'127.0.0.1' TO' jerry'@'172.18.%.%'

5. Delete user

DROP USER 'user'@'host' [,' user'@'host']...

Example:

12MariaDB [mysql] > DROP USER 'jerry'@'%';MariaDB [mysql] > DROP USER' @ 'localhost'

6. Have MySQL reload the authorization list

FLUSH PRIVILEGES

Example:

1MariaDB [mysql] > FLUSH PRIVILEGES

7. Modify a user's password

(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD (' cleartext password'); PASSWORD is the built-in encryption function of MySQL

Example:

12MariaDB [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'

Example:

12MariaDB [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'

Example:

1 [root@node2] # mysqladmin-h227.0.0.1-uroot-p password 'liumanlin'

8. The solution to forgetting the administrator password

(1) when starting the mysqld process, use the-- skip-grant-tables and-- skip-networking options

Example:

CentOS 7:

1234 [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) modify the administrator password through the UPDATE command

Example:

123MariaDB [mysql] > UPDATE user SET Password=PASSWORD ('liumanlin') WHERE User='root' AND Host='127.0.0.1'; [root@node2 ~] # vim / usr/lib/systemd/system/mariadb.serviceExecStart=/usr/bin/mysqld_safe-- basedir=/usr

(3) start the mysqld process in normal way

Example:

12 [root@node2 ~] # systemctl daemon-reload [root@node2 ~] # systemctl restart mariadb.service

9. Authorization: GRANT

12345GRANT priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_levelTO user_specification [, user_specification]... [REQUIRE {NONE | ssl_option [[AND] ssl_option]...}] [WITH with_option.]

Object_type (object type):

TABLE

| | FUNCTION |

| | PROCEDURE |

Priv_level:

*

| | *. * (all tables of all libraries) |

| | db_name.* (specify all tables of the library) |

| | db_name.tbl_name (specify the specified table of the library) |

| | tbl_name (specified table) |

| | db_name.routine_name (specify the specified function of the library) |

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 |

Example 1:

12345678910MariaDB [mysql] > GRANT CREATE ON hidb.* TO 'jerry'@'172.18.%.%'; [root@node2 ~] # mysql-ujerry-h272.18.67.12-pMariaDB [(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' (not authorized to create an index, using the following methods) 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); (authorization succeeded)

Example 2:

12345678MariaDB [mysql] > CREATE USER 'tom'@'172.18.%.%' IDENTIFIED BY' liumanlin'; [root@node2 ~] # mysql-utom-h272.18.67.12-p (logon normally) MariaDB [mysql] > GRANT ALL ON hidb.* TO 'tom'@'172.18.%.%' REQUIRE SSL; (login with ssl authorization) MariaDB [mysql] > SHOW GRANTS FOR' tom'@'172.18.%.%';MariaDB [mysql] > FLUSH PRIVILEGES [root@node2 ~] # mysql-utom-h272.18.67.12-pEnter password:ERROR 1045 (28000): Access denied for user 'tom'@'172.18.67.12' (using password: YES) (unable to connect, please specify ssl certificate)

Example 3:

123456789101112MariaDB [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. View Licensing: SHOW GRANTS

SHOW GRANTS [FOR 'user'@'host']

Example:

1MariaDB [mysql] > SHOW GRANTS FOR 'tom'@'172.18.%.%'

11. Deregistration: REVOKE

12345REVOKE priv_type [(column_list)] [, priv_type [(column_list)]... ON [object_type] priv_levelFROM 'user'@'host' [,' user'@'host']... REVOKE ALL PRIVILEGES, GRANT OPTIONFROM user [, user].

Example:

123MariaDB [mysql] > REVOKE CREATE VIEW ON hidb.* FROM 'tom'@'172.18.%.%';MariaDB [mysql] > SHOW GRANTS FOR' tom'@'172.18.%.%';MariaDB [mysql] > FLUSH PRIVILEGES

For the above about the main functions of managing MySQL users, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.