Mysql命令行连接远程/本地数据库详解

可用于本人这个头像 · 发表于 2023-5-13 03:31:25

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命令行连接数据库内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/article/283294.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！