Troubleshoot Lost MySQL Remote Connections

By: Morpheus Data

There aren’t many MySQL databases that don’t need to support users’ remote connections. While many failed remote connections can be traced to a misconfigured my.cnf file, the many nuances of MySQL remote links make troubleshooting a dropped network connection anything but straightforward.

Some Linux administrators were rattled this week to learn of the discovery by Qualys of a bug in the GNU C Library (glibc) that could render affected systems vulnerable to a remote code execution attack. In a January 27, 2015, article, The Register’s Neil McAllister describes the dangers posed by Ghost to Linux and a handful of other OSes.

Ghost affects versions of glibc back to 2.2, which was released in 2000, but as threats go, this one appears to be pretty mild. For one thing, the routines involved are old and rarely used these days. Even when they are used, they aren’t called in a manner that the vulnerability could exploit. Still, Linux vendors DebianRed Hat, and Ubuntu have released patches for Ghost.

As Ars Technica’s Dan Goodin explains in a January 27, 2015, article, Ghost may affect MySQL servers, Secure Shell servers, form submission apps, and other mail servers in addition to the Exim server on which Qualys demonstrated the remote code execution attack. However, Qualys has confirmed that Ghost does not impact Apache, Cups, Dovecot, GnuPG, isc-dhcp, lighttpd, mariadb/mysql, nfs-utils, nginx, nodejs, openldap, openssh, postfix, proftpd, pure-ftpd, rsyslog, samba, sendmail, sysklogd, syslog-ng, tcp_wrappers, vsftpd, or xinetd.

Finding a solution to common MySQL remote-connection glitches

While protecting against Ghost may be as simple as applying a patch, managing remote connections on MySQL servers and clients can leave DBAs pounding their keyboards. ITworld’s Stephen Glasskeys writes in a December 19, 2014, post about the hoops he had to jump through to find the cause of a failed remote connection on a Linux MySQL server.

After using the ps command to list processes, Glasskeys found that the –skip-networking command was enabled, which tells MySQL not to listen for remote TCP/IP connections. Running KDE’s Find Files/Folders tool determined that rc.mysqld was the only script file containing the text “–skip-networking”.

Diagnosing the cause of failed remote connections on a MySQL server led to the file rc.mysqld. Source: ITworld

To restore remote connections, open rc.mysqld and comment out the command by placing a pound sign (#) at the beginning of the line. Then edit the MySQL configuration file /etc/my.cnf as follows, making sure bind-address is set to 0.0.0.0:

Edit the /etc/my.cnf file to ensure bind-address is set to 0.0.0.0. Source: ITworld

Finally, use the “iptables –list” command to make sure the Linux server is set to accept requests on MySQL’s port 3306, and the “iptables” command to enable them if it’s not. After you restart MySQL, you can test the remote connection using the credentials and other options as they appear in the my.cnf file on the Linux server.

When MySQL’s % wildcard operator leaves a remote connection hanging

Stack Overflow post from April 2013 describes a situation where MySQL’s % wildcard operator failed to allow a user “user@%” to connect remotely. Such remote connections require that MySQL’s bind port 3306 be present in each machine’s IP in my.cnf. Also, the user has to be created in both localhost and the % wildcard, and permissions granted on all databases. (You may also need to open port 3306, depending on your OS.)

Enable remote connections in the MySQL my.cnf file by adding each machine’s IP and creating users in localhost and the % wildcard. Source: ITworld

Diagnosing failed remote connections and other database glitches is facilitated by the point-and-click interface of the new Morpheus Virtual Appliance, which lets you manage heterogeneous MySQL, MongoDB, Redis, and ElasticSearch databases in a single dashboard. Morpheus is the first and only database-as-a-service (DBaaS) that supports SQL, NoSQL, and in-memory databases across public, private, and hybrid clouds.

With Morpheus, you can invoke a new database instance with one click, and each instance includes a free full replica set for failover and fault tolerance. You can administer your databases using your choice of tools. Visit the Morpheus site to create a free account.