centos 7 mysql 创建用户_【CentOS 7MySQL常用操作4】，MySQL创建用户以及授权#180116

hellopasswd

MySQL创建用户以及授权

grant all on . to 'user1' identified by 'passwd';

grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.169.9.1' identified by 'passwd';

grant all on db1.* to 'user3'@'%' identified by 'passwd';

show grants;

创建使用指定登录用户

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p123

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> grant all on *.* to 'user1'@'127.0.0.1' identified by '123a';

Query OK, 0 rows affected (0.10 sec)

mysql> quit

Bye

登录用户

[root@localhost ~]# /usr/local/mysql/bin/mysql -uuser1 -p123a

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

需要指定IP，使用socket

[root@localhost ~]# /usr/local/mysql/bin/mysql -uuser1 -p123a -h127.0.0.1

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> quit

Bye

注意grant语句并不会记录在历史命令当中

创建使用socket登录用户

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p123

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> grant all on *.* to 'user1'@'localhost' identified by '123a';

Query OK, 0 rows affected (0.02 sec)

mysql> quit

Bye

登录用户

[root@localhost ~]# /usr/local/mysql/bin/mysql -uuser1 -p123a

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> quit

Bye

mysql> show grants;

+----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost |

+----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+----------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.02 sec)

mysql> show grants for user1@'127.0.0.1';

+-----------------------------------------------------------------------------------------------------------------------+

| Grants for user1@127.0.0.1 |

+-----------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*318A97B79A244462B853FC344402458373343CFF' |

+-----------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

show grants for user2@'192.168.133.1';

修改于 180116