HeidiSQL can’t connect to MySQL
I installed XAMPP and HeidiSQL (the latter running under Wine) on my local Xubuntu 14.04 (LTS) system sometime in the last year or two. I’d used both before when I used to use Windows, and have had no problem using HeidiSQL to connect to external MySQL servers. However, I have had no success connecting to my local installation of MySQL. Not having time to deal with the issue (what seemingly inexplicable computer issue is ever resolved in a matter of minutes?!) I just left it and used phpMyAdmin that comes bundled with XAMPP, which is up to the job but which is a pain to use once you’re used to a native application like HeidiSQL.
Well, yesterday was the day to get to the bottom of the problem, and predictably it took me several hours to figure out.
I found a number of articles that referred to user privileges, client bugs, binding MySQL to the localhost IP address, some guy who was doing it wrong, another guy who must have been using machine translation to try to get his point across (I don’t envy the poor guy), and a guy who seemed to have the same problem as me (but didn’t).
I found the clue that solved my problem in the Navicat knowledge base (“2003 – Can’t connect to MySQL server on xxx (10061)“) that linked to the MySQL reference manual (“B.5.2.2 Can’t connect to [local] MySQL server“). Looking at the default XAMPP MySQL configuration file at /opt/lampp/etc/my.cnf I noticed the following:
# commented in by xampp security #skip-networking skip-networking
I’m guessing that the XAMPP-provided script that I recall XAMPP suggesting you run after installation to plug a few security holes put that there. After I once again commented out “skip-networking” and restarted MySQL, HeidiSQL was able to connect. Considering I generally only run the local MySQL server for brief periods I don’t expect this to be a major security issue.
I am a happy camper once again. Hope this helps you too.