当初次安装完mysql后，可以免口令直接登陆mysql。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[root@plinuxos ~]
# /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
更改口令
1、更改PATH，增加MySQL路径
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@plinuxos ~]
# export PATH=$PATH:/usr/local/mysql/bin/
[root@plinuxos ~]
# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 2
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
这样，可以直接通过mysql命令登陆，而不再需要写绝对路径。如果要想永久生效，必须要添加到profile配置中。
2、设置密码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[root@plinuxos ~]
# mysqladmin -uroot password '123456'
Warning: Using a password on the
command
line interface can be insecure.
[root@plinuxos ~]
# mysql -uroot
ERROR 1045 (28000): Access denied
for
user
'root'
@
'localhost'
(using password: NO)
[root@plinuxos ~]
# mysql -uroot -p123456
Warning: Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 5
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
3、修改密码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[root@plinuxos ~]
# mysqladmin -uroot -p123456 password 'abcdefg'
Warning: Using a password on the
command
line interface can be insecure.
[root@plinuxos ~]
# mysql -uroot -pabcdefg
Warning: Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 7
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
4、忘记密码时，重置密码
|
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
|
[root@plinuxos ~]
# vi /etc/my.cnf
[mysqld]
skip-grant
##新增该行，跳过授权
......
[root@plinuxos ~]
# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@plinuxos ~]
# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> use mysql;
Reading table information
for
completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user
set
password=password(
'88888888'
) where user=
'root'
;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql>
select
password from user where user=
'root'
;
+-------------------------------------------+
| password |
+-------------------------------------------+
| *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B |
| *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B |
| *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B |
| *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B |
+-------------------------------------------+
4 rows
in
set
(0.00 sec)
mysql>
exit
;
Bye
[root@plinuxos ~]
# mysql -uroot -p88888888
Warning: Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 2
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
连接MySQL
1、远程连接MySQL数据库
|
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
|
[root@plinuxos ~]
# mysql -uroot -p88888888
Warning: Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 3
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
exit
;
Bye
[root@plinuxos ~]
# mysql -uroot -p88888888 -h127.0.0.1 -P3306
Warning: Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 4
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
2、使用socket连接数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@plinuxos ~]
# mysql -uroot -p88888888 -S/tmp/mysql.sock
Warning: Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 5
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
3、数据库外快速执行命令
|
1
2
3
4
5
6
7
8
9
10
|
[root@plinuxos ~]
# mysql -uroot -p88888888 -e "show databases"
Warning: Using a password on the
command
line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
test
|
+--------------------+
|
常用命令
|
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
|
mysql> show databases;
##显示数据库
mysql> use mysql;
##切换数据库
mysql> show tables;
##显示表
mysql> desc columns_priv;
##查看columns_priv表
mysql> show create table columns_priv\G;
##查看columns_priv的创建语句
mysql>
select
user();
##查看当前登陆用户
mysql>
select
database();
##查看当前数据库
mysql> create database db1;
##创建数据库db1
mysql> use db1;create table t1(`
id
` int(4),`name` char(40));
##创建数据库和表
mysql> drop table t1;
##删除表
mysql>
select
version();
##查看mysql版本
mysql> show status;
##查看数据库状态
mysql> show variables;
##查看参数
mysql> show variables like
'max_connect%'
;
##模糊查询参数
mysql>
set
global max_connect_errors=1000;
##修改参数的值，若要永久生效，在/etc/my.cnf配置
mysql> show processlist;
##查看队列
mysql> show full processlist;
##查看完整的队列信息
本文转自Grodd51CTO博客，原文链接：http://blog.51cto.com/juispan/1958191，如需转载请自行联系原作者
|
