大家好,又见面了,我是你们的朋友全栈君。
新安装的 MySQL 只有一个 root 用户,默认不开启远程连接。下面以 Ubuntu 系统下的 MySQL 为例,说明如何远程连接 MySQL 数据库。
mysql -uroot -pyongdu
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hr |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql> select host,user from user where user = 'root';
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| localhost | root |
| myubuntu | root |
+-----------+------+
4 rows in set (0.00 sec)
mysql> quit
Bye
ubuntu@MyUbuntu:~$ mysql -uroot -pyongdu -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
-h
代表指定的 ip 主机,用本机的 ip 尝试连接 mysql -uroot -pyongdu -h 192.168.43.246
发现无法登陆,这是因为 root 只允许 localhost 和 127.0.0.1 进行登陆ubuntu@MyUbuntu:~$ mysql -uroot -pyongdu -h 192.168.43.246
ERROR 1045 (28000): Access denied for user 'root'@'192.168.43.246' (using password: YES)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
flush privileges;
,使上述设置生效mysql> select host,user from user where user = 'admin';
+------+-------+
| host | user |
+------+-------+
| % | admin |
+------+-------+
1 row in set (0.00 sec)
/etc/mysql/my.cnf bind-address = 127.0.0.1
在这个配置文件里也绑定了 ip,可用命令 sudo find / -name my.cnf
查找到该文件,将 bind-address 这一行注释掉,或者改为 0.0.0.0# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
sudo service mysql restart
ubuntu@MyUbuntu:~$ mysql -h 192.168.43.246 -uadmin -padmin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/182347.html原文链接:https://javaforall.cn