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 -pEnter 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 10Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.
创建用户在其他主机上访问数据库：
mysql> use mysql;Database changedmysql> 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> exitBye 连接用户访问:
[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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysql;Database changedmysql> 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 -poracleWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 21Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> ;;mysql用户从本地连接成功mysql> use mysql;Database changedmysql> 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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 20Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysqlDatabase changedmysql> update user set password=PASSWORD('oracle') where user='mysql';Query OK, 1 row affected (0.00 sec)Rows matched: 2 Changed: 1 Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> exitBye 删除用户：
[root@ogg ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 31Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysql;Database changedmysql> 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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 33Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysql;Database changedmysql> 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> exitByemysql用户在本地登录：[root@ogg ~]# mysql -u mysql -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 35Server version: 5.6.4-m7-log Source distributionCopyright (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 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>