【寒假学Mysql】远程连接树莓派上的数据库成功！

承接上文，树莓派上的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:~ 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试试！！