mysql 命令dl_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 -p2 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.35MySQL 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 its10 affiliates. Other names may be trademarks of their respective11 owners.12

13 Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.14

15 mysql>quit16 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.35MySQL 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 its16 affiliates. Other names may be trademarks of their respective17 owners.18

19 Type 'help;' or '\h' for help. Type '\c' to clearthe 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.35MySQL 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 its14 affiliates. Other names may be trademarks of their respective15 owners.16

17 Type 'help;' or '\h' for help. Type '\c' to clearthe 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.cnf2 [mysqld]3 skip-grant //1，添加此行即可。意思是忽略授权。也就是说不用用户名密码了，只能就能登录mysql。

4 datadir=/data/mysql5 socket=/tmp/mysql.sock6 # Disabling symbolic-links is recommended to prevent assorted security risks7 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 formariadb according to the11 # 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 -uroot19 Welcome to the MySQL monitor. Commands end with ; or \g.20 Your MySQL connection id is 1

21 Server version: 5.6.35MySQL 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 its26 affiliates. Other names may be trademarks of their respective27 owners.28

29 Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.30

31 mysql> //登录成功

32

33 mysql> use mysql; //切换mysql库

34 Reading table information forcompletion of table and column names35 You can turn off this feature to get a quicker startup with -A36

37 Database changed38 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_ci42

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.00sec)55

56 mysql> update user set password=password('mysqldl991124') where user='root'; //更改密码为 mysqldl991124

57 Query OK, 4 rows affected (0.00sec)58 Rows matched: 4 Changed: 4 Warnings: 0

59

60 mysql>exit61 Bye62 //此时密码已经更改完成，但是需要，恢复配置文件重启并登录。操作如下

63

64 [root@ubuntu ~]# vim /etc/my.cnf65

66 [mysqld]67 skip-grant //删除此行

68 datadir=/data/mysql69 socket=/tmp/mysql.sock70 //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.35MySQL 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 its84 affiliates. Other names may be trademarks of their respective85 owners.86

87 Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.88

89 mysql>