Mysql学习之--数据库连接和用户管理
时间：2022-03-14 00:13
Mysql学习之--数据库连接和用户管理
shell> mysql -h host -u user -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25338 to server version: 5.1.2-alpha-standard Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer. mysql>
mysql> 提示符告诉你mysql准备为你输入命令。
一些MySQL安装允许用户以匿名（未命名）用户连接到本地主机上运行的服务器。如果你的机器是这种情况，你应该能不带任何选项地调用mysql与该服务器连接：
shell> mysql
成功地连接后，可以在mysql>提示下输入QUIT (或\q)随时退出：
mysql> QUIT
Bye
在Unix中，也可以按control-D键断开服务器。
二、用户管理
创建用户
[root@ogg ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
创建用户在其他主机上访问数据库：
mysql> use mysql; Database changed mysql> grant all privileges on *.* to ‘mysql‘@‘%‘ identified by ‘oracle‘ with grant option; Query OK, 0 rows affected (0.00 sec) mysql> desc user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Insert_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Update_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Delete_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Drop_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Reload_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Shutdown_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Process_priv | enum(‘N‘,‘Y‘) | NO | | N | | | File_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Grant_priv | enum(‘N‘,‘Y‘) | NO | | N | | | References_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Index_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_db_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Super_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tmp_table_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Lock_tables_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Execute_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Repl_slave_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Repl_client_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_user_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Event_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Trigger_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tablespace_priv | enum(‘N‘,‘Y‘) | NO | | N | | | ssl_type | enum(‘‘,‘ANY‘,‘X509‘,‘SPECIFIED‘) | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | +------------------------+-----------------------------------+------+-----+---------+-------+ 42 rows in set (0.00 sec) mysql> select user,password from user; +-------+-------------------------------------------+ | user | password | +-------+-------------------------------------------+ | root | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | | | root | | | root | | | | | | | | | mysql | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +-------+-------------------------------------------+ 7 rows in set (0.00 sec) mysql> exit Bye
连接用户访问:
[root@ogg ~]# mysql -u mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘mysql‘@‘localhost‘ (using password: YES)
访问被拒绝！
[root@ogg ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use mysql; Database changed mysql> desc user -> ; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Insert_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Update_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Delete_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Drop_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Reload_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Shutdown_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Process_priv | enum(‘N‘,‘Y‘) | NO | | N | | | File_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Grant_priv | enum(‘N‘,‘Y‘) | NO | | N | | | References_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Index_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_db_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Super_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tmp_table_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Lock_tables_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Execute_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Repl_slave_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Repl_client_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_user_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Event_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Trigger_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tablespace_priv | enum(‘N‘,‘Y‘) | NO | | N | | | ssl_type | enum(‘‘,‘ANY‘,‘X509‘,‘SPECIFIED‘) | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | +------------------------+-----------------------------------+------+-----+---------+-------+ 42 rows in set (0.00 sec) mysql> select user,Super_priv from user; +-------+------------+ | user | Super_priv | +-------+------------+ | root | Y | | root | Y | | root | Y | | root | Y | | | N | | | N | | mysql | Y | +-------+------------+ 7 rows in set (0.00 sec) 授权mysql用户从本地访问： mysql> grant all privileges on *.* to ‘mysql‘@‘localhost‘ identified by ‘oracle‘ ; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@ogg ~]# mysql -u mysql -poracle Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> ;;mysql用户从本地连接成功 mysql> use mysql; Database changed mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 29 rows in set (0.00 sec) mysql> desc host; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | Select_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Insert_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Update_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Delete_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Drop_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Grant_priv | enum(‘N‘,‘Y‘) | NO | | N | | | References_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Index_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tmp_table_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Lock_tables_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Execute_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Trigger_priv | enum(‘N‘,‘Y‘) | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 20 rows in set (0.00 sec)
更改用户口令：
[root@ogg ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use mysql Database changed mysql> update user set password=PASSWORD(‘oracle‘) where user=‘mysql‘; Query OK, 1 row affected (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye
删除用户：
[root@ogg ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use mysql; Database changed mysql> select user,password from user; +-------+-------------------------------------------+ | user | password | +-------+-------------------------------------------+ | root | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | | | root | | | root | | | | | | | | | mysql | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | mysql | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +-------+-------------------------------------------+ 8 rows in set (0.00 sec) mysql> delete from user where user=‘mysql‘; Query OK, 2 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user,password from user; +------+-------------------------------------------+ | user | password | +------+-------------------------------------------+ | root | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | | | root | | | root | | | | | | | | +------+-------------------------------------------+ 6 rows in set (0.00 sec)
添加用户：
[root@ogg ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use mysql; Database changed mysql> INSERT INTO user (Host,User,Password) -> values (‘localhost‘,‘mysql‘,password(‘oracle‘)); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> select user,password,Select_priv,Insert_priv,Update_priv,Delete_priv from user where user=‘mysql‘; +-------+-------------------------------------------+-------------+-------------+-------------+-------------+ | user | password | Select_priv | Insert_priv | Update_priv | Delete_priv | +-------+-------------------------------------------+-------------+-------------+-------------+-------------+ | mysql | *2447D497B9A6A15F2776055CB2D1E9F86758182F | N | N | N | N | +-------+-------------------------------------------+-------------+-------------+-------------+-------------+ 1 row in set (0.00 sec) mysql> grant all privileges on *.* to ‘mysql‘@‘localhost‘; Query OK, 0 rows affected (0.00 sec) mysql> select user,password,Select_priv,Insert_priv,Update_priv,Delete_priv from user where user=‘mysql‘; +-------+-------------------------------------------+-------------+-------------+-------------+-------------+ | user | password | Select_priv | Insert_priv | Update_priv | Delete_priv | +-------+-------------------------------------------+-------------+-------------+-------------+-------------+ | mysql | *2447D497B9A6A15F2776055CB2D1E9F86758182F | Y | Y | Y | Y | +-------+-------------------------------------------+-------------+-------------+-------------+-------------+ 1 row in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye mysql用户在本地登录： [root@ogg ~]# mysql -u mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
本文出自 “” 博客，请务必保留此出处
