一、连接与断开服务器
为了连接服务器，当调用mysql时，通常需要提供一个MySQL用户名并且很可能需要一个 密码。如果服务器运行在登录服务器之外的其它机器上，还需要指定主机名。联系管理员以找出进行连接所使用的参数 (即，连接的主机、用户名和使用的密码)。知道正确的参数后，可以按照以下方式进行连接：
shell> mysql -h host -u user -p
Enter password: ********
host和user分别代表MySQL服务器运行的主机名和MySQL账户用户名。设置时替换为正确的值。******** 代表你的密码；当mysql显示Enter password:提示时输入它。
如果有效，你应该看见mysql>提示符后的一些介绍信息：
|
1
2
3
4
5
6
7
8
|
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:
|
1
2
3
4
5
6
7
|
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.
|
创建用户在其他主机上访问数据库：
|
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
|
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)
访问被拒绝！
|
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
|
[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)
|
更改用户口令：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[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
|
删除用户：
|
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
|
[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)
|
添加用户：
|
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
|
[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>
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1557699，如需转载请自行联系原作者
