Mysql学习之--数据库连接和用户管理
一、连接与断开服务器
为了连接服务器，当调用mysql时，通常需要提供一个MySQL用户名并且很可能需要一个 密码。如果服务器运行在登录服务器之外的其它机器上，还需要指定主机名。联系管理员以找出进行连接所使用的参数 (即，连接的主机、用户名和使用的密码)。知道正确的参数后，可以按照以下方式进行连接：
shell> mysql -h host -u user -p
Enter password: ********
host和user分别代表MySQL服务器运行的主机名和MySQL账户用户名。设置时替换为正确的值。******** 代表你的密码；当mysql显示Enter password:提示时输入它。
如果有效，你应该看见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>
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1557699