I have a RaspberryPi 2 with RaspbianOS on my local network (192.168.0.0/24). It has a static IP and so does my OSX work computer - 192.168.0.21, 192.168.0.10 respectively.
It took me two days of playing around with iptables to find Uncomplicated Firewall, which seems straight forward enough, but I can't get it to work nonetheless. I have a feeling something is overriding the user input rules, maybe something I have added to iptables?
I can't connect to MySQL server on Raspberry. SSH(22) works and so does HTTP(80), but 3306 won't budge. I've copied the content of my.cnf file and iptables -L output.
So my question here is how would I establish a connection between Raspberrys MySQL server and my work computer's client MySQLWorkbench without using SSL and SSH as simple as possible, so I could get back to working with PHP? I hope I didn't forget to pass along any other relevant information.
iptables -L
pi@raspberrypi ~ $ sudo iptables -L
Chain INPUT (policy DROP)
target prot opt source destination
ufw-before-logging-input all -- anywhere anywhere
ufw-before-input all -- anywhere anywhere
ufw-after-input all -- anywhere anywhere
ufw-after-logging-input all -- anywhere anywhere
ufw-reject-input all -- anywhere anywhere
ufw-track-input all -- anywhere anywhere
Chain FORWARD (policy DROP)
target prot opt source destination
ufw-before-logging-forward all -- anywhere anywhere
ufw-before-forward all -- anywhere anywhere
ufw-after-forward all -- anywhere anywhere
ufw-after-logging-forward all -- anywhere anywhere
ufw-reject-forward all -- anywhere anywhere
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
ufw-before-logging-output all -- anywhere anywhere
ufw-before-output all -- anywhere anywhere
ufw-after-output all -- anywhere anywhere
ufw-after-logging-output all -- anywhere anywhere
ufw-reject-output all -- anywhere anywhere
ufw-track-output all -- anywhere anywhere
Chain ufw-after-forward (1 references)
target prot opt source destination
Chain ufw-after-input (1 references)
target prot opt source destination
ufw-skip-to-policy-input udp -- anywhere anywhere udp dpt:netbios-ns
ufw-skip-to-policy-input udp -- anywhere anywhere udp dpt:netbios-dgm
ufw-skip-to-policy-input tcp -- anywhere anywhere tcp dpt:netbios-ssn
ufw-skip-to-policy-input tcp -- anywhere anywhere tcp dpt:microsoft-ds
ufw-skip-to-policy-input udp -- anywhere anywhere udp dpt:bootps
ufw-skip-to-policy-input udp -- anywhere anywhere udp dpt:bootpc
ufw-skip-to-policy-input all -- anywhere anywhere ADDRTYPE match dst-type BROADCAST
Chain ufw-after-logging-forward (1 references)
target prot opt source destination
LOG all -- anywhere anywhere LOG level warning prefix "[UFW BLOCK] "
Chain ufw-after-logging-input (1 references)
target prot opt source destination
LOG all -- anywhere anywhere LOG level warning prefix "[UFW BLOCK] "
Chain ufw-after-logging-output (1 references)
target prot opt source destination
LOG all -- anywhere anywhere LOG level warning prefix "[UFW ALLOW] "
Chain ufw-after-output (1 references)
target prot opt source destination
Chain ufw-before-forward (1 references)
target prot opt source destination
ufw-user-forward all -- anywhere anywhere
Chain ufw-before-input (1 references)
target prot opt source destination
ACCEPT all -- anywhere anywhere
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ufw-logging-deny all -- anywhere anywhere state INVALID
DROP all -- anywhere anywhere state INVALID
ACCEPT icmp -- anywhere anywhere icmp destination-unreachable
ACCEPT icmp -- anywhere anywhere icmp source-quench
ACCEPT icmp -- anywhere anywhere icmp time-exceeded
ACCEPT icmp -- anywhere anywhere icmp parameter-problem
ACCEPT icmp -- anywhere anywhere icmp echo-request
ACCEPT udp -- anywhere anywhere udp spt:bootps dpt:bootpc
ufw-not-local all -- anywhere anywhere
ACCEPT udp -- anywhere 224.0.0.251 udp dpt:mdns
ACCEPT udp -- anywhere 239.255.255.250 udp dpt:1900
ufw-user-input all -- anywhere anywhere
Chain ufw-before-logging-forward (1 references)
target prot opt source destination
LOG all -- anywhere anywhere limit: avg 3/min burst 10 LOG level warning prefix "[UFW AUDIT] "
Chain ufw-before-logging-input (1 references)
target prot opt source destination
LOG all -- anywhere anywhere limit: avg 3/min burst 10 LOG level warning prefix "[UFW AUDIT] "
Chain ufw-before-logging-output (1 references)
target prot opt source destination
LOG all -- anywhere anywhere limit: avg 3/min burst 10 LOG level warning prefix "[UFW AUDIT] "
Chain ufw-before-output (1 references)
target prot opt source destination
ACCEPT all -- anywhere anywhere
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ufw-user-output all -- anywhere anywhere
Chain ufw-logging-allow (0 references)
target prot opt source destination
LOG all -- anywhere anywhere LOG level warning prefix "[UFW ALLOW] "
Chain ufw-logging-deny (2 references)
target prot opt source destination
LOG all -- anywhere anywhere state INVALID LOG level warning prefix "[UFW AUDIT INVALID] "
LOG all -- anywhere anywhere LOG level warning prefix "[UFW BLOCK] "
Chain ufw-not-local (1 references)
target prot opt source destination
RETURN all -- anywhere anywhere ADDRTYPE match dst-type LOCAL
RETURN all -- anywhere anywhere ADDRTYPE match dst-type MULTICAST
RETURN all -- anywhere anywhere ADDRTYPE match dst-type BROADCAST
ufw-logging-deny all -- anywhere anywhere limit: avg 3/min burst 10
DROP all -- anywhere anywhere
Chain ufw-reject-forward (1 references)
target prot opt source destination
Chain ufw-reject-input (1 references)
target prot opt source destination
Chain ufw-reject-output (1 references)
target prot opt source destination
Chain ufw-skip-to-policy-forward (0 references)
target prot opt source destination
DROP all -- anywhere anywhere
Chain ufw-skip-to-policy-input (7 references)
target prot opt source destination
DROP all -- anywhere anywhere
Chain ufw-skip-to-policy-output (0 references)
target prot opt source destination
ACCEPT all -- anywhere anywhere
Chain ufw-track-input (1 references)
target prot opt source destination
Chain ufw-track-output (1 references)
target prot opt source destination
ACCEPT tcp -- anywhere anywhere state NEW
ACCEPT udp -- anywhere anywhere state NEW
Chain ufw-user-forward (1 references)
target prot opt source destination
Chain ufw-user-input (1 references)
target prot opt source destination
ACCEPT all -- 192.168.0.0/24 anywhere
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT udp -- anywhere anywhere udp dpt:mysql
Chain ufw-user-limit (0 references)
target prot opt source destination
LOG all -- anywhere anywhere limit: avg 3/min burst 5 LOG level warning prefix "[UFW LIMIT BLOCK] "
REJECT all -- anywhere anywhere reject-with icmp-port-unreachable
Chain ufw-user-limit-accept (0 references)
target prot opt source destination
ACCEPT all -- anywhere anywhere
Chain ufw-user-logging-forward (0 references)
target prot opt source destination
Chain ufw-user-logging-input (0 references)
target prot opt source destination
Chain ufw-user-logging-output (0 references)
target prot opt source destination
Chain ufw-user-output (1 references)
target prot opt source destination
And my.cnf:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# 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.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#ssl-ca=/etc/mysql/ca-cert.pem
#ssl-cert=/etc/mysql/client-cert.pem
#ssl-key=/etc/mysql/client-key.pem
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
log-warnings=1
#
# * 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.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#ssl-ca=/etc/mysql/ca-cert.pem
#ssl-cert=/etc/mysql/server-cert.pem
#ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
. . . .
Edit 1.0 Even after I clear iptables with a shell script ...
pi@raspberrypi ~ $ sudo iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
... the port stays inaccessible
So yeah, the problem was in MySQL configuration file /etc/mysql/my.conf.
line with 'bind-address = 127.0.0.1' has to be commented out