更新时间:2022-09-27 22:21:43
当初次安装完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,如需转载请自行联系原作者
|