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

吾爱主题阅读：203 2023-05-06 14:45:00 评论：0

Mysql 命令行连接本地数据库

MySQL登录

mysql -uroot -p密码
mysql -hip -uroot -p连接目标的密码
mysql --host=ip --user=root --password=连接目标的密码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 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=连接目标的密码

1 2 3 4 ┌──(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 数据库没有密码最好创建一个一密码

1 2	`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"改为"%"

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 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 服务即可

1 2 3 4 5 6 7 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服务器的话。

1 2	`/myuser mypassword 为对应的用户迷宫和密码 /` `GRANT` `ALL` `PRIVILEGES` `ON` `.` `TO` `'myuser'` `@` `'%'` `IDENTIFIED` `BY` `'mypassword'` `WITH` `GRANT` `OPTION` `;`

如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码

1 2 3 /*例如 */ 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;

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

取消授权

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 # 查看授权的所有用户 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)