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

Mysql 命令行 连接本地数据库

  • MySQL登录
    1. mysql -uroot -p密码
    2. mysql -hip -uroot -p连接目标的密码
    3. mysql –host=ip –user=root –password=连接目标的密码
C:\Users\Administrator>mysql -h 127.0.0.1 -uroot --port=3306 -pEnter password: *****Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitByeC:\Users\Administrator>mysql -h 127.0.0.1 -uroot --port=3308 -pEnter password: *****Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 11Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exit ByeC:\Users\Administrator>mysql -h 127.0.0.1 -uroot --port=3307 -pEnter password: *****Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 16Server version: 8.0.13 MySQL Community Server - GPLCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitBye

Mysql 命令行 连接远程数据库

  • 连接 远程的数据库
  • mysql –host=ip –user=root –password=连接目标的密码
┌──(root㉿kali)-[~]└─# mysql -h 69.45.123.1 -uroot --port=3307 -pEnter 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 -P3306Enter password: *****Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysql; # 使用数据库Database changedmysql> 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: 0Changed: 0Warnings: 0mysql> flush privileges; # 刷新一下 mysql的缓存Query OK, 0 rows affected (0.00 sec)
  • 执行操作之后 重启服务器
  • 如果有需要 改回来 在使用 数据库之后 把 “mysql” 数据库里的 “user” 表里的 “host” 项 从”%“改为”localhost” 之后刷新一下缓存之后 重启 mysql 服务 即可
mysql> use mysql; # 使用数据库Database changedmysql> update user set host = 'localhost' where user = 'root' and host='%'; Query OK, 0 rows affected (0.00 sec)Rows matched: 0Changed: 0Warnings: 0mysql> 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 服务