目錄

• Mysql 命令行 連接本地數據庫
• Mysql 命令行 連接遠程數據庫
• 總結

Mysql 命令行 連接本地數據庫

MySQL登錄

• mysql -uroot -p密碼
• mysql -hip -uroot -p連接目標的密碼
• mysql --host=ip --user=root --password=連接目標的密碼

C:\Users\Administrator>mysql -h 127.0.0.1 -uroot --port=3306 -p  
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.17 MySQL Community Server (GPL)      
Copyright (c) 2000, 2018, 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
C:\Users\Administrator>mysql -h 127.0.0.1 -uroot --port=3308 -p  
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.61 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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
C:\Users\Administrator>mysql -h 127.0.0.1 -uroot --port=3307 -p  
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, 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

Mysql 命令行 連接遠程數據庫

連接 遠程的數據庫

mysql --host=ip --user=root --password=連接目標的密碼

┌──(root?kali)-[~]
└─# mysql -h 69.45.123.1 -uroot --port=3307 -p
Enter password: 
ERROR 1130 (HY000): Host "69.45.123.128" is not allowed to connect to this MySQL server

有兩個方法

如果 你的 mysql 數據庫沒有 密碼 最好創建一個一密碼

update mysql.user set authentication_string=password("新密碼") where user="用戶名" and Host ="localhost";
update mysql.user set authentication_string=password("admin") where user="用戶名" and Host ="localhost";

1.改表法

是因為 root 帳號不允許從遠程登陸，只能在localhost。這個時候只要在localhost的那臺電腦，登入mysql后，更改 “mysql” 數據庫里的 “user” 表里的 “host” 項，從"localhost"改為"%"

C:\Users\Administrator>mysql -uroot -p -P3306
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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; # 使用數據庫
Database changed
mysql> select user,password,host from user where user="root"; # 先查詢下 有權限的 用戶 的 host 是什么
+------+-------------------------------------------+-----------+
| user | password  | host      |
+------+-------------------------------------------+-----------+
| root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | localhost |
| root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | 127.0.0.1 |
| root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | ::1       |
+------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)
# 修改 "mysql" 數據庫里的 "user" 表里的 "host" 項，從"localhost"改為"%"
mysql> update user set host = "%" where user = "root" and host="localhost"; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> flush privileges; # 刷新一下 mysql的緩存
Query OK, 0 rows affected (0.00 sec)

執行操作之后 重啟服務器

如果有需要 改回來 在使用 數據庫之后 把 “mysql” 數據庫里的 “user” 表里的 “host” 項 從"%“改為"localhost” 之后刷新一下緩存之后 重啟 mysql 服務 即可

mysql> use mysql; # 使用數據庫
Database changed
mysql> update user set host = "localhost" where user = "root" and host="%"; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> flush privileges; # 刷新一下 mysql的緩存
Query OK, 0 rows affected (0.00 sec)

2. 授權法。例如，你想 某個用戶名 比如 myuser 和對應的密碼 從任何主機連接到mysql服務器的話。

/*myuser mypassword 為對應的 用戶迷宮和密碼 */
GRANT ALL PRIVILEGES ON *.* TO "myuser"@"%" IDENTIFIED BY "mypassword" WITH GRANT OPTION;

如果你想允許用戶myuser從ip為192.168.1.3的主機連接到mysql服務器，并使用mypassword作為密碼

/*例如 */
GRANT ALL PRIVILEGES ON *.* TO "root"@"192.168.1.3" IDENTIFIED BY "mypassword" WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO "root"@"192.168.1.3" IDENTIFIED BY "1235" WITH GRANT OPTION;

mysql> flush privileges; # 刷新一下 mysql的緩存
Query OK, 0 rows affected (0.00 sec)

取消授權

# 查看授權的所有用戶
mysql> SELECT DISTINCT CONCAT("User: """,user,"""@""",host,""";") AS query FROM mysql.user;  
+---------------------------+
| query     |
+---------------------------+
| User: "root"@"127.0.0.1"; |
| User: "root"@"::1";       |
| User: ""@"localhost";     |
| User: "root"@"localhost"; |
+---------------------------+
4 rows in set (0.00 sec)

# revoke all on *.* from "username"@"%";  username為指定的用戶，%為任意登錄的地址。
mysql> revoke all on *.* from "root"@"192.168.1.3";     
Query OK, 0 rows affected (0.00 sec)

# 然后再次 
mysql> flush privileges; # 刷新一下 mysql的緩存
Query OK, 0 rows affected (0.00 sec)

然后 重啟 mysql 服務

總結

到此這篇關于Mysql命令行連接遠程/本地數據庫的文章就介紹到這了,更多相關Mysql命令行連接數據庫內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！