正文之前
承接上文,树莓派上的Mysql准备好了。能正常运行而且还可以很健康的跑动。下面就试试能否远程连接数据库呢?找了不少法子,发现跟我的情况不太一样了,所以一开始放弃了,但是刚才准备下楼吃饭之前突然想起还有个法子没试过,索性直接来一发!结果?成了!!
正文
首先,介绍下我参考的博客:
上文中提到了
修改/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:~ zhangzhaobo$ 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' (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试试!!