MySQL登录命令详解

MySQL登录命令详解

一、登录命令语法格式

mysql -u用户名 [-h主机名] -p密码 [-P端口号] [-D数据库名] [-eMySQL命令] [-S socket文件名]

参数说明：

（1）【-u用户名】或者【–user=用户名】：指定用户登录的用户名；
（2）【-p密码（p小写）】或者【–password=密码】：输入登录密码；
（3）【-h主机名或ip地址】或者【–host=主机名ip地址】：指定登录的主机名；
（4）【-P端口号（P大写）】或者【–port=端口号】：指定登录的MySQL的端口号；
（5）【-D数据库名】或者【–database=数据库名】：指定登录的数据库名称；
（6）【-S socket文件名】或者【–socket=socket文件名】：指定登录时使用的socket文件名。
（7）【-e MySQL命令】或者【–execute= MySQL命令】：在不登录MySQL的情况下执行MySQL命令。

二、登录本地数据库

如果需要登录本地数据库，只需要指定用户名（-u）和密码（-p）即可，不需要指定主机名（-h），命令如下：

[root@bogon ~]# mysql -uroot -pWgx123456.
mysql: [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.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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服务器中创建登录账号并授予相应的访问权限。然后使用（-h）参数指定远程服务器的IP地址，使用（-P）参数指定远程服务器中MySQL的端口号。

1、先在IP地址为192.168.1.11的MySQL服务器中创建登录账户’zhang’@'192.168.1.%'并授予访问权限。命令如下：

mysql> select host,user from user;
+-------------+---------------+
| host        | user          |
+-------------+---------------+
| 192.168.1.% | zhang         |

2、在IP地址为192.168.1.12的机器中执行如下登录命令：

[root@bogon ~]# mysql -uzhang -h192.168.1.11 -pWgx123456. -P3306
mysql: [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 8
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 

四、登录指定的数据库

使用show database查看MySQL服务器中的数据库如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db              |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

然后退出MySQL，再使用如下的命令登录MySQL：

[root@bogon ~]# mysql -uroot -pWgx123456. -Dmy_db
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> select database();
+------------+
| database() |
+------------+
| my_db      |
+------------+
1 row in set (0.00 sec)

mysql> 

可以看出，登录到MySQL之后进入了 my_db数据库。

五、使用（-e）参数在不登录MySQL的情况下执行MySQL命令

[root@bogon ~]# mysql -uroot -p -e "use my_db;select * from stu where class='物流1班';"
Enter password: 
+------+-----------+--------+-----+------------+
| s_id | name      | gender | age | class      |
+------+-----------+--------+-----+------------+
|    1 | 张平      | 男     |  20 | 物流1班    |
|    2 | 王刚      | 男     |  21 | 物流1班    |
|    3 | 刘静      | 女     |  18 | 物流1班    |
|    4 | 张静静    | 女     |  21 | 物流1班    |
|    5 | 刘涛      | 男     |  19 | 物流1班    |
+------+-----------+--------+-----+------------+