I've gone through a lot of tutorials and questions and I still can't get it to work.
I've been to:
I installed MariaDB on Ubuntu 16.04. Then set up two users, one of which is intended for public use so I can post it here.
The users are added as:
CREATE USER 'anon'@'%' IDENTIFIED BY '';
Does local connections work?
Yes, I can connect as the users via ssh on the server:
mysql -u anon
Did you verify the users were added correctly?
I think so:
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
+------+------+
| User | Host |
+------+------+
| anon | % |
| user | % |
+------+------+
2 rows in set (0.01 sec)
Have you unblocked the firewall?
One may need to unblock the firewall:
[user]@popfreq:/etc/mysql$ firewall-cmd --add-port=3306/tcp
The program 'firewall-cmd' is currently not installed. You can install it by typing:
sudo apt install firewalld
Firewall isn't installed.
Did you check the my.cnf file for correct settings?
Incorrect settings in my.cnf
([user]@popfreq:/etc/mysql
) can cause it to refuse connections. These are skip-networking
and bind-address
. My file looks like this:
# 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/
It doesn't have the offending lines at all.
Did you check the other config files?
Yes. They did not have the offending lines either.
Does telnet work?
No.
mint@mint-VirtualBox ~ $ telnet 128.199.203.208 3306
Trying 128.199.203.208...
telnet: Unable to connect to remote host: Connection refused
Not sure what this means or how to fix.
What interface is the server using?
Local only it seems:
[user]@popfreq:/etc/mysql/mariadb.conf.d$ sudo netstat -ntlup | grep mysql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 16884/mysqld
Did you remember to restart? Yes. I restarted using this between all attempts:
sudo service mysql restart
В моем случае решением проблемы было то, что в конфигурационных файлах my.cnf
раздела [mysqld] вообще не было. Добавление решило проблему:
[mysqld]
bind-address = ::
Не знаю, почему он не был добавлен по умолчанию. Обратите внимание, что причина использования :
по сравнению с 0.0.0.0
заключается в том, что :
работает и для IPv6 ( упоминается в mySQL manual, но не для mariaDB manual).
Это также исправило telnet:
mint@mint-VirtualBox ~ $ telnet 128.199.203.208 3306
Trying 128.199.203.208...
Connected to 128.199.203.208.
И теперь сетевой вывод:
[user]@popfreq:/etc/mysql$ sudo netstat -ntlup | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 17609/mysqld
Надеюсь, это поможет кому-то другому.
.После того, как возникла та же проблема (в Debian Stretch) и я уже безуспешно опробовал все упомянутые здесь решения, я наконец нашел это , откуда я просто публикую важную часть :
Отредактируйте /etc/mysql/mariadb.conf.d/50-server.cnf, прокомментируйте адрес привязки и добавьте оператор sql-mode:
[...]
# 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
sql-mode="NO_ENGINE_SUBSTITUTION"
[...]
и - это сработало.