正文之前

承接上文,树莓派上的Mysql准备好了。能正常运行而且还可以很健康的跑动。下面就试试能否远程连接数据库呢?找了不少法子,发现跟我的情况不太一样了,所以一开始放弃了,但是刚才准备下楼吃饭之前突然想起还有个法子没试过,索性直接来一发!结果?成了!!

正文

首先,介绍下我参考的博客:

树莓派安装mysql并开启远程访问(开启3306端口)

上文中提到了


修改/etc/mysql/my.cnf文件
找到下面这行,并用#注释掉,
bind-address = 127.0.0.1
或者修改为bind-address = 0.0.0.0


但是我的mysql很明显并非如此:

pi@raspberrypi:~ $ cat /etc/mysql/my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

我自行添加了一行bind-address = 0.0.0.0,但是没啥用,索性就丢弃了这种办法!

倒是让我找到了一个地方:

/etc/mysql/mariadb.conf.d

里面的50-server.cnf内容还是很多的,所以我在这里面改动了下:

root@raspberrypi:/etc/mysql/mariadb.conf.d# cat 50-server.cnf 
[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#Changed Here ZZB  bind-address     = 127.0.0.1
bind-address = 192.168.2.127

其他的不做改动。包括沸沸扬扬的my.cnf

请看上面代码的最后一行,找到标记的此处,我是按照老外的一个说法,把bind-address改成了mysql-server所在的主机的ip,而不是0.0.0.0反正我就这么更改了。是不是发挥了作用鬼知道。

然后在树莓派本地登录mysql(怎么登陆这就不好说了吧????这还用说?),输入下面命令:

mysql>grant all privileges on . to username@”%” identified by “password”;(username一般是root,password是新的密码)
mysql> FLUSH PRIVILEGES;

上面的内容可以让你指定的用户登录,在此之前你也可以创建一个专门用于网络登录的用户,随你的便,反正我设置的是pi这个用户,设置完毕之后记得刷新,并且重新启动mysql,当然,重启系统reboot是最好的了。防止意外么。要是配置文件每次开机加载呢?那岂不是GG?另外,你可以在mysql的命令行中查看某个用户的权限:

MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4D743C4208AD60ADC939F0AB9C5165DE94A9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show grants for 'pi'@'%'
    -> ;
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for pi@%                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'pi'@'%' IDENTIFIED BY PASSWORD '*4D743C4208AD60ADC429D939F0A165DE94A9' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

如果没有设置用户,就是当前的权限,设置了就会显示相应的了!可以看到我,我的pi是设置的%,代表的就是谁都可以访问我的这个mysql数据库叻。正式成为一个网络数据库咯!!

当然,如果你没有。那么还可以试试直接在数据库面改动。

>MariaDB [(none)]> 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
MariaDB [mysql]> select Host from user where user='pi';
+---------------+
| Host          |
+---------------+
| %             |
| 192.169.2.212 |
+---------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> 

上面是树莓派的,因为改过了。就先不动,我用Mac试试:

mysql> select Host from user where user='root';
+-----------+
| Host      |
+-----------+
| localhost |
+-----------+
1 row in set (0.00 sec)

mysql> update user set  Host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select Host from user where user='root';
+------+
| Host |
+------+
| %    |
+------+
1 row in set (0.00 sec)

mysql> 

ok我怀疑现在我的树莓派就可以连接到我的Mac上的数据库了。好吧,现实告诉我,想多了:

root@raspberrypi:/etc/mysql/mariadb.conf.d# mysql -h 192.168.2.212 -P 3306 -u root -p 
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.212' (111 "Connection refused")
root@raspberrypi:/etc/mysql/mariadb.conf.d# 

所以还是乖乖连接树莓派的吧!!

当然,有时候希望各干各事。那么创建个用户呗!链接给你准备好了:

MySQL创建用户与授权

在此之后,我们只要打开3306,也就是mysql的默认端口对外开放就ok!

使用 ufw软件来开启3306端口

  • 安装ufw
    • apt-get install ufw
  • 启用ufw
    • ufw enable
    • ufw default deny
  • 开启3306、22(ssh端口)端口
    • ufw allow 3306
    • ufw allow 22

如果你是VNC用户你就发现你的vnc已经挂了,嘿嘿🙄!!我就是,所以我去找了找,有收获:

CentOS 6.0 下 VNC 配置方法(带防火墙配置)

简单说就是开个5900、5901端口的事情:

pi@raspberrypi:~ sudo ufw allow 5900
Rule added
Rule added (v6)
pi@raspberrypi:~ sudo ufw allow 5901
Rule added
Rule added (v6)
pi@raspberrypi:~ $ sudo ufw allow 5902
Rule added
Rule added (v6)

之后你的VNC就如常开启了!

看下面!!!嘿嘿!!

HustWolf:~ zhangzhaobomysql -h 192.168.2.212 -P 3306 -u root -p 
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.212' (61)
HustWolf:~ zhangzhaobo mysql -h 192.168.2.212 -P 3306 -u pi -p 
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.212' (61)
HustWolf:~ zhangzhaobo$ mysql -h 192.168.2.127 -P 3306 -u pi -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.5.5-10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2017, 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> 

正文之后

之后你就好好使用吧!!待会吃完饭,就来用JDBC试试!!

文章来源于互联网:【寒假学Mysql】远程连接树莓派上的数据库成功!

发表评论