Jason145
Posts: 1
Joined: Tue Jan 08, 2019 9:52 am

Raspberry Pi MySQL Remote Connection (error: 10061)

Thu Jan 10, 2019 10:22 am

I'm trying to connect to a MySQL server running on my Raspberry Pi 3 remotely (though on the same LAN).

Attempting to do so through HeidiSQL returns an error coded 10061. A bit of research suggests I have to edit the config to allow remote connections.

The config, however, does not contain a bind-adress x.x.x.x line (/etc/mysql/my.cnf) for my to comment out. Added it myself breaks the service, causing errors when trying to restart it. Checking other possible config file locations resulted in only empty files. kodi Additionally, comparing to the "default" config file, mine is a lot shorter, and has no actual statements, other than a few includes for other config files (it seems). These included files are all empty as well.

I'm now lost, and cannot figure out how to allow remote connections to my server. Any help is much appreciated :)
Last edited by Jason145 on Fri Jan 11, 2019 6:22 am, edited 1 time in total.

Andyroo
Posts: 1471
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Raspberry Pi MySQL Remote Connection (error: 10061)

Thu Jan 10, 2019 6:43 pm

Can you let us know:

1) Operating system version
2) How you installed SQL
3) If SQL works locally from the command line
4) Content of your config file
Need Pi spray - these things are breeding in my house...

bzt
Posts: 301
Joined: Sat Oct 14, 2017 9:57 pm

Re: Raspberry Pi MySQL Remote Connection (error: 10061)

Sun Jan 13, 2019 3:14 pm

Hi,

Step 1: check if mysql server is listening. You can use "netstat" for that. If not, you need to modify the config file (could listen on local unix sockets). It is extremely helpful to check the log files, I usually do "tail -f /var/log/mysqld/mysql.log" in another terminal. Try to connect to the server locally through loopback interface (with ip 127.0.0.1) using mysql-client.

Step 2: when you are positive that mysql server is ok, then check the network connections. Can you access the port remotely from the client machine? I usually do "telnet 192.168.x.y 3306" for that. You won't be able to use it in any meaningful way, but you will see if the tcp connection builds up. Using mysql-client is equally good. If there's an error, you should look into the firewall (on both the client machine and on the sql server) or the LAN router's configuration. Again, tail-ing log files are extremely helpful (/var/log/messages, /var/log/syslog etc.).

Step 3: as a last step, use your preferred mysql library to connect to the remote mysql from your application. If there's a problem with that, but the two steps above worked, then it must be a configuration error, usually a typo in the mysql connection string or a mis-spelled password etc.

If you're unable to solve the issue with these instructions, then I also suggest to post what Andyroo asked for. That could help a lot.

Cheers,
bzt

swampdog
Posts: 164
Joined: Fri Dec 04, 2015 11:22 am

Re: Raspberry Pi MySQL Remote Connection (error: 10061)

Thu Jan 24, 2019 5:26 am

cat<<EOF > z
CREATE USER '${A_USER}'@'localhost' IDENTIFIED BY '${A_PASS}';
CREATE USER '${A_USER}'@'' IDENTIFIED BY '${A_PASS}';
GRANT ALL PRIVILEGES ON *.* TO '${A_USER}'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO '${A_USER}'@'' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EOF

mysql -u "$R_USER" < z

where R_USER is 'root' and A_USER/A_PASS is your new user which is essentially a duplicate "root" account. Now connect using A_USER to secure it..

cat<<EOF > z
SET PASSWORD FOR '${R_USER}'@'localhost' = PASSWORD('${R_PASS}');
SET PASSWORD FOR '${R_USER}'@'::1' = PASSWORD('${R_PASS}');
DROP USER '${R_USER}'@'${h}';
DROP USER '${R_USER}'@'127.0.0.1';
DROP USER ''@'localhost';
DROP USER ''@'${h}';
DROP DATABASE IF EXISTS \`test\`;
FLUSH PRIVILEGES;
EOF

mysql" -u "$A_USER" -p"$A_PASS" < z

Now you can administer it remotely using 'mysql -u ${A_USER} -p${A_PASS} -h hostname (test it with workbench for instance).

eg:
[email protected]$ mysql -u admin -pblah -h c7srv
mysql> use msql;

mysql> select host,user,password from user;

+-----------+--------+
| host | user |
+-----------+--------+
| | admin |
| % | bacula |
| ::1 | root |
| localhost | admin |
| localhost | bacula |
| localhost | root |
+-----------+--------+
^^^I've omitted password from the above. Nothing in password field should be blank. Now create the user/database for your app (ie "bacula" above) and reduce privileges. Finally, if its an internet facing mysql then don't allow remote access at all - tunnel in with ssh but that's another topic!

User avatar
DougieLawson
Posts: 34762
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Raspberry Pi MySQL Remote Connection (error: 10061)

Thu Jan 24, 2019 7:21 am

Have you changed bind-address in mysql.cnf to 0.0.0.0?
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a hippy & doctor free zone.

fbe
Posts: 417
Joined: Thu Aug 17, 2017 9:08 pm

Re: Raspberry Pi MySQL Remote Connection (error: 10061)

Sun Jan 27, 2019 8:52 pm

Jason145 wrote:
Thu Jan 10, 2019 10:22 am
The config, however, does not contain a bind-adress x.x.x.x line (/etc/mysql/my.cnf) for my to comment out. Added it myself breaks the service, causing errors when trying to restart it. Checking other possible config file locations resulted in only empty files. kodi Additionally, comparing to the "default" config file, mine is a lot shorter, and has no actual statements, other than a few includes for other config files (it seems). These included files are all empty as well.
If you are using Raspbian Stretch, /etc/mysql/my.cnf should contain
# 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/
The file with the server bind-address is /etc/mysql/mariadb.conf.d/50-server.cnf

Repeat the setup on a spare drive, then try to make work remote access and copy the configuration files.

Return to “Other programming languages”