MySQL常用操作

使用Linux首次登录需要把mysql加入环境变量$PATH

1 [root@ubuntu ~]# export PATH=$PATH:/usr/local/mysql/bin/
2     #说明：此时可以使用命令 # mysql -uroot就可以登录mysql。但是需要添加到配置文件中重启才会永久生效。配置如下：
3 
4 [root@ubuntu ~]# vi /etc/profile    //添加至配置文件中
5 .........................
6 export PATH=$PATH:/usr/local/mysql/bin/
7 .........................
8 [root@ubuntu ~]# source /etc/profile    //保存退出之后，要刷新才会生效

登录mysql

 1 [root@ubuntu ~]# mysql -uroot -p    
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 7
 5 Server version: 5.6.35 MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> quit
16 Bye

说明： -u 指定的是用户，-p指定的是密码。首次登录密码默认为空，直接回车即可。

设置密码并登录

 1 [root@ubuntu ~]# mysqladmin -uroot password 'mysqldl991124'    // 设置密码
 2 Warning: Using a password on the command line interface can be insecure.
 3 
 4 [root@ubuntu ~]# mysql -uroot    //设置密码完成之后，不指定密码是登录不成功的，需要-p指定密码。
 5 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 6 
 7 [root@ubuntu ~]# mysql -uroot -p'mysqldl991124'    //指定密码即可登录。
 8 Warning: Using a password on the command line interface can be insecure.
 9 Welcome to the MySQL monitor.  Commands end with ; or \g.
10 Your MySQL connection id is 11
11 Server version: 5.6.35 MySQL Community Server (GPL)
12 
13 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
14 
15 Oracle is a registered trademark of Oracle Corporation and/or its
16 affiliates. Other names may be trademarks of their respective
17 owners.
18 
19 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
20 
21 mysql>

说明：登录时，也可以使用 #mysql -uroot -p 此时不输入密码直接回车，在下一行中也会提示输入密码。这两种方式都可以登录。 
注意：的是mysql每执行一段命令，命令的结尾要加上;才会生效。

更改密码并登录

1，当知道原密码时，进行密码更改

 1 [root@ubuntu ~]# mysqladmin -uroot -p'mysqldl991124' password '1234567'
 2 Warning: Using a password on the command line interface can be insecure.
 3 //说明：第一个''是原密码，第二个''是更改后的密码。需要输入正确的原密码才能更改成功。
 4 
 5 [root@ubuntu ~]# mysql -uroot -p'1234567'    //指定更改后的密码并登录
 6 Warning: Using a password on the command line interface can be insecure.
 7 Welcome to the MySQL monitor.  Commands end with ; or \g.
 8 Your MySQL connection id is 15
 9 Server version: 5.6.35 MySQL Community Server (GPL)
10 
11 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
12 
13 Oracle is a registered trademark of Oracle Corporation and/or its
14 affiliates. Other names may be trademarks of their respective
15 owners.
16 
17 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18 
19 mysql>                 //成功登录

2，当忘记原密码时，进行密码更改

步骤：

1，编辑 vim /etc/my.cnf 添加配置文件 skip-grant ； 
2，重启mysql # /etc/init.d/mysqld restart ； 
3，此时不用输入密码登录到mysql # mysql -uroot ； 
4，在mysql中修改密码并退出 mysql> update user set password=password(‘mysqldl991124’) where user=’root’; ； 
5，在删除/etc/my.cnf中添加的配置文件； 
6，重启mysql # /etc/init.d/mysqld restart； 
7，此时就可以使用更改后的密码登录了！！！

具体操作如下：

 1 [root@ubuntu ~]# vim /etc/my.cnf   
 2 [mysqld]
 3 skip-grant        //1，添加此行即可。意思是忽略授权。也就是说不用用户名密码了，只能就能登录mysql。
 4 datadir=/data/mysql
 5 socket=/tmp/mysql.sock
 6 # Disabling symbolic-links is recommended to prevent assorted security risks
 7 symbolic-links=0
 8 # Settings user and group are ignored when systemd is used.
 9 # If you need to run mysqld under a different user or group,
10 # customize your systemd unit file for mariadb according to the
11 # instructions in http://fedoraproject.org/wiki/Systemd
12 
13 [root@ubuntu ~]# /etc/init.d/mysqld restart          //2，重新启动
14 Shutting down MySQL.. SUCCESS! 
15 Starting MySQL....... SUCCESS! 
16 // 说明： 完成该操作之后就可以无需密码登录mysql了，所以此时mysql安全性很差，平时配置文件中一定不要添加该参数。
17 
18 [root@ubuntu ~]# mysql -uroot
19 Welcome to the MySQL monitor.  Commands end with ; or \g.
20 Your MySQL connection id is 1
21 Server version: 5.6.35 MySQL Community Server (GPL)
22 
23 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
24 
25 Oracle is a registered trademark of Oracle Corporation and/or its
26 affiliates. Other names may be trademarks of their respective
27 owners.
28 
29 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
30 
31 mysql>        //登录成功
32 
33 mysql> use mysql;    //切换mysql库
34 Reading table information for completion of table and column names
35 You can turn off this feature to get a quicker startup with -A
36 
37 Database changed
38 mysql> select * from user    //该表中存放的是用户相关信息（密码、授权…）
39     -> ;
40 +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
41 | Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_ci
42 
43 ...................................
44 
45 mysql> select password from user where user='root';    //查看root用户的密码
46 +-------------------------------------------+
47 | password                                  |
48 +-------------------------------------------+
49 | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |        //此密码是使用password生成的
50 |                                           |
51 |                                           |
52 |                                           |
53 +-------------------------------------------+
54 4 rows in set (0.00 sec)
55 
56 mysql> update user set password=password('mysqldl991124') where user='root';    //更改密码为 mysqldl991124
57 Query OK, 4 rows affected (0.00 sec)
58 Rows matched: 4  Changed: 4  Warnings: 0
59 
60 mysql> exit
61 Bye
62 //此时密码已经更改完成，但是需要，恢复配置文件重启并登录。操作如下
63 
64 [root@ubuntu ~]# vim /etc/my.cnf
65 
66 [mysqld]
67 skip-grant    //删除此行
68 datadir=/data/mysql
69 socket=/tmp/mysql.sock
70     //wq保存退出
71 [root@ubuntu ~]# /etc/init.d/mysqld restart     //重新启动mysql
72 Shutting down MySQL.. SUCCESS! 
73 Starting MySQL.. SUCCESS! 
74 
75 [root@ubuntu ~]# mysql -uroot -p'mysqldl991124'    //使用更改后的密码登录成功！
76 Warning: Using a password on the command line interface can be insecure.
77 Welcome to the MySQL monitor.  Commands end with ; or \g.
78 Your MySQL connection id is 1
79 Server version: 5.6.35 MySQL Community Server (GPL)
80 
81 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
82 
83 Oracle is a registered trademark of Oracle Corporation and/or its
84 affiliates. Other names may be trademarks of their respective
85 owners.
86 
87 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
88 
89 mysql>

 

posted @ 2018-01-11 09:38  gyhuminyan  阅读(229)  评论(0)  编辑  收藏  举报