Can’t Connect: HeidiSQL + VirtualBox + mySQL + Ubuntu

After creating a new mySQL server within an Ubuntu guest OS running on VirtualBox, (how is that for a mouthful?) I wanted to connect to the server with HeidiSQL, my toolbox of choice when dealing with basic SQL operations. I expect that I would have the same problems attempting to connect with similar client software, such as mySQL Workbench or PHPmyAdmin.

The symptoms of the problem showed up in an error message, generated by HeidiSQL:

Connect Error (2003) Can‘t connect to MySQL server on ‘192.168.219.107‘ (10061)

So, more or less in order, I did the following:
1. Create a mySQL user with an identical name and password as the Ubuntu user account. To connect to mySQL, you need to have two valid accounts: a.) a valid Linux user account, and b.) a valid mySQL user account. On Ubuntu, these need to be same….because you only get one chance to pass through a name and password when logging into a server from HeidiSQL. This is done by logging into the mySQL client on the Ubuntu box, and performing the following:

GRANT ALL PRIVILEGES ON *.* TO ‘username‘@’localhost’ IDENTIFIED BY ‘password‘ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO ‘username‘@’%’ IDENTIFIED BY ‘password‘ WITH GRANT OPTION;

Exit the mySQL client, and then reload the grant tables, using the mySQL root user and password

mysqladmin -u root -prootpassword reload

Note that the both the user names and password are case-sensitive, and the mySQL user’s name and password must be identical to the ubuntu user’s name and password for Heidi to be able to log in.

2. In VirtualBox, be sure that the network settings for your network card are for a bridged adapter. What this does is it makes the VM act like another machine on your current network. The VirtualBox default is to create its own network and subnet which are inaccessible from the host machine. (but see note #1 below)

3. Once you’ve taken care of the above items, restart the VM, and log in at the console. You need to edit the mySQL configuration file to reflect the new IP address of the VM. Log into the VM. Usually the opening screen will show the current IP address, in the line:

IP address for eth0: 192.168.219.107.

If you miss it or need to find it again, just run

ifconfig | grep “inet addr”

This will probably show at least two lines. The first one is the current IP address.

inet addr: 192168.219.107 Bcast:192.168.219.255 Mask:255.255.2550

inet addr: 128.0.0.1 Mask 255.0.0.0

Now that you know the address, you can add it to mySQL configuration file:

sudo nano /etc/mysql/my.cnf

Scroll down in this file until you find an entry for the bind address. This will look something like:

bind-address = 127.0.0.1

Change this so it it listens on the address of your VM. I just comment out the bind-address line, and add the new line:

#bind-address = 127.0.0.1

bind-address = 192.168.219.107

Restart the machine.
sudo shutdown -r now

This reloads everything, including the mySQL daemon so that it listens on the new IP address.

That’s it. You should be able to log in with Heidi now.

Notes:
1. You could probably leave the default networking setting in place, (it is NAT), and the VM will be assigned a NAT address along the lines of 10.0.2.15. You’d still probably want to edit the mySQL file to reflect this address, and you might also want to make sure that you forward the mySQL port 3306. I just haven’t tested this, so that I can’t vouch that it will work.

2. Of course, in a production situation, you’ll want to choose a fixed IP address for the network address. This might involve your main router and firewall, so you may need to discuss this with the sys admins.

Anyway, now that I’ve actually got this thing running…I’ve got to get back to mySQL!

tfnp

Tech for Non-Profits

Can’t Connect: HeidiSQL + VirtualBox + mySQL + Ubuntu

Leave a Reply Cancel reply

Share this:

Like this:

Related

Leave a Reply Cancel reply