MySQL Enable Remote Connections

Introduction

    This is something I've struggled with for about 5-6 hours now, all of the "helpful" websites have magic incantation command lines that are supposed to fix the problem, if they don't work you don't actually have any more knowledge to help you, so you just go looking for more magic to try.  Here I will attempt to improve this with a troubleshooting guide with some more details behind the commands used.

Possible Problems with Connecting to MySQL from another computer

• Network Hardware Failure
• MySQL Bind to an IP Address
• MySQL User Permissions
• Network Firewall (Not Complete)

I'm going to assume that the Network Hardware is working, which means that you can open a terminal from another computer. 

My configuration is an ODROID HC1 running Linux Ubuntu and MariaDB, I want to connect from my  Win10 Desktop with HeidiSQL. I'm using Putty to open a bash terminal to the HC1. Even if you are a Windows user I have to assume some knowledge with a navigating with a command line, primarily the 'ls' to list a directories contents and 'cd' to change to another directory, these commands should be enough. Optionally the 'mv' may be used to either move or rename a file. 

My main error is "Can't connect to MySQL server on <IP ADDRESS> (10061) 

I'm also assuming the MySQL (MariaDB) is already installed, but you can't connect remotely. You may of tried a bunch of things and have a very messed up environment and that is OK.

Troubleshooting tools to install

telnet client on both Ubuntu and Windows

• Ubuntu
• apt-get install telnet
• Windows 10
• Start button
• Type "Turn Windows" (Features On or Off)
• Check the box next to "Telnet Client"
• OK
• Restart

First steps are to make sure that it works on the Linux computer.

• Login to the remote Linux server with any terminal program (Win10 use Putty)
• >>mysql -u root -p
• password is blank by default, but if you set one use it.
• The default "host" is 'localhost' which is a unix socket
• SQL>select user, host, password from mysql.user;
• Don't forget the semi-colon at the end
• The password is hashed/encrypted but you can tell if it's set and if it's the same as another user.
• SQL>quit

At this point we have proven that the mysql server is installed and functioning and has at least a root user with localhost access. Now here is a tricky part mysql actually uses a unix socket to connect the client to the server, so there isn't any networking actually involved. 

• If any of the above steps failed maybe the mysql installation needs to be done/re-done

The easiest way to test if MySQL can be accessed outside of the local computer is with the telnet command. The following commands can be re-run at any stage of this article.

• telnet localhost 3306
• This uses the Unix Socket so it will work but doesn't prove anything. Although it does show you what you should see when you run the following command
• telnet <ip address> 3306
• If you see the same kind of thing you saw from the localhost version above, you can move on to the Network Firewall issues, or MySQL user permission issues

The next steps are to determine the configuration loaded by MySQL and where the values actually come from.

• >>mysqld --print-defaults
• One of issues was the bind-address was duplicated (see bold settings) the last one that is set is the value that is used. These all look like command line parameters, but they may come from a command line or from internal default values or from one of multiple configuration files. The challenge is figuring out which one.
• --bind-address=0.0.0.0 
• --character-set-server=utf8mb4 
• --collation-server=utf8mb4_unicode_ci 
• --character-set_server=utf8mb4 
• --collation_server=utf8mb4_unicode_ci 
• --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 
• --bind-address=127.0.0.1 
• --key_buffer_size=16M 
• --max_allowed_packet=16M 
• --thread_stack=192K 
• --thread_cache_size=8 
• --myisam-recover=BACKUP 
• --query_cache_limit=1M 
• --query_cache_size=16M 
• --log_error=/var/log/mysql/error.log 
• --expire_logs_days=10 
• --max_binlog_size=100M 
• --character-set-server=utf8mb4 
• --collation-server=utf8mb4_general_ci
• >>ps -ef | grep 'mysql'
• This command will show the values that were actually passed on the command line.
  
• >>mysqld --verbose --help | grep -A 1 "Default options"
• This will list the location of all configuration files that were read when mysql started.
• NOTE: In many cases (all?) one of the config files may "includedir" configuration files from other locations.
• The configuration files for my system were in this order
• /etc/my.cnf
• /etc/mysql/my.cnf
• /etc/mysql/mariadb.cnf
• /etc/mysql/conf.d/*.cnf
• /etc/mysql/mariadb.conf.d/*.cnf
• ~/.my.cnf

Now review the list from print-defaults and figure out where each value comes from. Reminder: use 'sudo' to elevate your permissions in order to edit these text files, 'vi' is a good editor if you are familiar with it. If you are usually a Windows user or otherwise aren't sure 'nano' is a little easier to use. 

Examples:

• sudo vi /etc/my.cnf
• Look up a cheatsheet if you don't know the commands
• OR
• sudo nano /etc/my.cnf
• Ctrl+O and <Enter> to save the change
• Ctrl+X to exit nano

Here are my results:

/etc/my.cnf --bind-address=0.0.0.0 

/etc/mysql/conf.d/my.cnf --character-set-server=utf8mb4 

/etc/mysql/conf.d/my.cnf --collation-server=utf8mb4_unicode_ci 

/etc/mysql/conf.d/my.cnf --character-set_server=utf8mb4 

/etc/mysql/conf.d/my.cnf --collation_server=utf8mb4_unicode_ci 

command line --user=mysql 

command line --pid-file=/var/run/mysqld/mysqld.pid 
command line --socket=/var/run/mysqld/mysqld.sock 
command line --port=3306 
command line --basedir=/usr 
command line --datadir=/var/lib/mysql 

/etc/mysql/mariadb.conf.d/50-server.cnf --tmpdir=/tmp 

/etc/mysql/mariadb.conf.d/50-server.cnf --lc-messages-dir=/usr/share/mysql 

/etc/mysql/mariadb.conf.d/50-server.cnf --skip-external-locking 

/etc/mysql/mariadb.conf.d/50-server.cnf --bind-address=127.0.0.1 

/etc/mysql/mariadb.conf.d/50-server.cnf --key_buffer_size=16M 

/etc/mysql/mariadb.conf.d/50-server.cnf --max_allowed_packet=16M 

/etc/mysql/mariadb.conf.d/50-server.cnf --thread_stack=192K 

/etc/mysql/mariadb.conf.d/50-server.cnf --thread_cache_size=8 

/etc/mysql/mariadb.conf.d/50-server.cnf --myisam-recover=BACKUP 

/etc/mysql/mariadb.conf.d/50-server.cnf --query_cache_limit=1M 

/etc/mysql/mariadb.conf.d/50-server.cnf --query_cache_size=16M 

/etc/mysql/mariadb.conf.d/50-server.cnf --log_error=/var/log/mysql/error.log 

/etc/mysql/mariadb.conf.d/50-server.cnf --expire_logs_days=10 

/etc/mysql/mariadb.conf.d/50-server.cnf --max_binlog_size=100M 

/etc/mysql/conf.d/my.cnf --character-set-server=utf8mb4 

/etc/mysql/conf.d/my.cnf --collation-server=utf8mb4_general_ci

If you determine you need to edit any of these files use the editor of your choice from above and if you aren't sure about the change you are making I suggest commenting out the line with a '#' as the first character and retyping the line that you want.

Once you modify a configuration files that you think might fix your problem do the following:

• >>sudo service mysql restart
• I'm using Ubuntu so your syntax for this may be different
• >>telnet <ip address> 3306
• Error or some text that includes "Trying" and the database name "MariaDB"
• >>mysql -u root -h <ip address> -p
• I used the blank password that I was using for localhost previously and now this command changed to a new error message for me:
• Access denied for user 'root'@'<ip address>' (using password: NO)
• if root has a password assigned for '%' (see the query we ran near the beginning) use that password to try and connect.

NOTE: I was able to connect remotely at this point, so the following isn't as well tested. 

At this point we have a "permission" error. If you already set a password for root for remote access you can use that. Although best practice would be to create a new mysql user with remote access permissions. 

• >>mysql -u root -p
• Connect to the mysql server locally to add a new user
• SQL>create user '<username>'@'%' identified by '<password>';
• Even better is to use an IP address or range instead of the % character above
• SQL>GRANT ALL PRIVILEGES ON *.* TO '<username>'@'%';
• Again if you specified an IP address or range use that instead of the %
• OPTIONAL SQL>grant grant option on *.* to '<username>'@'%';
• This is so that the user can grant other permissions to users like 'root'
• SQL>flush privileges;

I don't know enough about firewall's to really guide someone and my problem is solved and I feel that this improves the available information on this error.

I hope your day is better because of this post.