mysql 8.0.12 创建新的数据库、用户并授权

转载地址：https://www.cnblogs.com/xzlive/p/9546964.html

mysql 8.0.12 创建新的数据库、用户并授权

Mysql安装成功后，默认的root用户密码为空，你可以使用以下命令来创建root用户的密码：

1	[root@host]# mysqladmin -u root password "new_password";

现在你可以通过以下命令来连接到Mysql服务器：

1 2	[root@host]# mysql -u root -p Enter password:*******

注意：在输入密码时，密码是不会显示了，你正确输入即可。

 

一、创建数据库

1 2	mysql> create database news character set utf8; Query OK, 0 rows affected (0.09 sec)

二、创建用户

1 2	mysql> create user 'news'@'39.15.16.14' identified by '123news'; Query OK, 0 rows affected (0.09 sec)

三、授权用户

1 2 3 4 5

mysql> grant all privileges on news.* to 'news'@'39.15.16.14'; Query OK, 0 rows affected (0.10 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

或者 这种方法 ：创建并授权用户，是二和三的合并。

1 2 3 4

mysql> grant all on asd.* to 'wanghz'@'192.168.1.%' identified by 'w123' Query OK, 0 rows affected (0.09 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec)

四、删除用户

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89

查看用户信息 mysql> select distinct * from (select user.Host,user.User,db.Db,user.Password,user.Drop_priv,user.Grant_priv,user.Alter_priv from db inner JOIN user on db.user=user.user) as it where user='wanghz'; +------+--------+-----------+-------------------------------------------+-----------+------------+------------+ | Host | User   | Db        | Password                                  | Drop_priv | Grant_priv | Alter_priv | +------+--------+-----------+-------------------------------------------+-----------+------------+------------+ | %    | wanghz | asd | *D98C88AFF81F2B8C5A0930313CD1250C6D81672E | N         | N          | N          | +------+--------+-----------+-------------------------------------------+-----------+------------+------------+ 1 row in set (0.00 sec)   mysql> select * from user where user='wanghz'\G; *************************** 1. row ***************************                   Host: %                   User: wanghz               Password: *D98C88AFF81F2B8C5A0930313CD1250C6D81672E            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: N       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string:       password_expired: N 1 row in set (0.00 sec)   ERROR: No query specified   mysql> select * from db where user='wanghz'\G; *************************** 1. row ***************************                  Host: %                    Db: asd                  User: wanghz           Select_priv: Y           Insert_priv: Y           Update_priv: Y           Delete_priv: Y           Create_priv: Y             Drop_priv: Y            Grant_priv: N       References_priv: Y            Index_priv: Y            Alter_priv: Y Create_tmp_table_priv: Y      Lock_tables_priv: Y      Create_view_priv: Y        Show_view_priv: Y   Create_routine_priv: Y    Alter_routine_priv: Y          Execute_priv: Y            Event_priv: Y          Trigger_priv: Y 1 row in set (0.00 sec)   ERROR: No query specified   mysql>

　删除用户

1 2 3 4

mysql> drop user 'wanghz'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)