Ubuntu – Connect to MySQL in VirtualBox Ubuntu

MySQLPHPUbuntu

I have this code on my PC (Windows), calling a database from an Ubuntu Virtual Machine with host-only network. I'm using VirtualBox for the Ubuntu Machine

$ipHost = "xxx.xxx.x.xxx";
$nombreUsuario = "rootpi";
$passUsuario = "rootpi";
$bddNombre = "tienda";

$conexion = new mysqli($ipHost, $nombreUsuario, $passUsuario, $bddNombre);

if ($conexion->connect_error) {
    die("Conexión fallida: " .$conexion->connect_error);
}

$sql = "SELECT id, name, address, image FROM people";
$result = $conexion->query($sql);

if ($result->num_rows > 0 ) {
    //Stuff...
}

This code works fine. The connection is made and shows the result on my PHP page in Apache server. My main objective is to pass the files to my Ubuntu 14.04 virtual machine in the /var/www/html directory using WinSCP and call them from there.

The only line I think I need to change is the $ipHost variable from the ip to localhost:

$ipHost = "xxx.xxx.x.xxx"; -> $ipHost = "localhost";

But it does not work. I tried to echo after:

$conexion = new mysqli($ipHost, $nombreUsuario, $passUsuario, $bddNombre);

But it does not show the echo after it.

I have MySQL installed in Ubuntu, also the php5 php5-mcrypt package.

My PHP versions are:

Windows: PHP Version 5.6.3 – Ubuntu: PHP Version 5.5.9-1ubuntu4.5

Best Answer

So you are basically saying that when you connect via IP address to the Ubuntu MySQL you can connect, but when you are on localhost you can’t correct?

This could be an issue with MySQL grants being tied to the IP address. But unclear. I would do the following to debug.

First, the basics.

Before anything, you say you have php5 and php5-mcrypt installed. But what about php5-mysql? Something as simple as that could cause you headaches. Just run this line:

sudo aptitude install php5-mysql

And once that is installed, restart Apache like this:

sudo service apache2 restart

Now if that clears things up, it was a simple software install issue. Anything else past that should be covered by the next two items.

Next, check how MySQL networking is bound.

If you have nmap install on you Ubuntu system, login to the command line on Ubuntu and run this command:

nmap localhost -p 3306

If you don’t have nmap installed, just run the following command to install it; it’s a nice simple network debugging tool all setups should have installed as a default:

sudo aptitude install nmap

That will tell you if the MySQL port (3306) on your machine is open or not. For example I just did that on a test Ubuntu 12.04 setup I have and this is the response I get:

Starting Nmap 5.21 ( http://nmap.org ) at 2014-11-30 01:29 EST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000070s latency).
PORT     STATE SERVICE
3306/tcp open  mysql

That tells me that the MySL port (3306) on my localhost is open. If that shows closed, that could be an issue and I would open up the my.cnf on the Ubuntu system and look for bind-address. I like to use nano so this is the command I would use:

sudo nano /etc/mysql/my.cnf

And on a non-networked install of MySQL the bind-address setting would be as follows:

bind-address            = 127.0.0.1

That would force the binding to be only localhost (127.0.0.1). But if you say you can connect to the MySQL database from your Windows host machine, that could be set to a VirtualBox IP address like this; using a pseudo-example with a fake IP address:

bind-address            = 123.456.789.0

And that would definitely allow the Windows host to connect but—and this is critical—but it would only allow connections via 123.456.789.0.

So the better way to allow Windows as well as Ubuntu use the MySQL server on that VirtualBox setup is to comment out the bind-address line like this:

# bind-address            = 123.456.789.0

And then restart MySQL like this:

sudo service mysql restart

With that done your MySQL will be accessible within Ubuntu via localhost as well as on your Windows machine via an IP connection. It’s basically allowing any network connection in the world to connect to that machine. Which is not a good thing for a real world production server, but for a localized test/development setup it should be fine.

Now, check how MySQL grants are setup for the database user.

Now if that first part basically showed you that you successfully have networking between Ubuntu and Windows worlds but still can’t connect, the next issue could be tied to MySQL GRANTS which are basically user permissions in MySQL.

To check your user’s grants you first should login to MySQL as a privileged user and run this command to connect to the mysql core database:

CONNECT `mysql`;

And then run this command to see what users/hosts you have setup:

SELECT `user`, `host` from `user`;

The output on a basic MySQL install should be something like this if you only have root setup:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+

Of course your list might be different than mine, so keep that in mind. But the key is you should see something like this. Note how root has three different entries in user: One for 127.0.0.1 (IPv4 address), ::1 (IPv6 address) and localhost (human readable hostname). That is the key here.

The thing is if your user is bound only to the networked address—such as 123.456.789.0—then there is no way that user would be able to connect via localhost. So you would need to setup a separate grant for localhost.

Doing something like that is fairly easy. Some people like to toss all grants to a user in a development environment like this:

GRANT ALL PRIVILEGES ON `some_database`.* TO 'some_user'@'localhost';

But I don’t like that for many reasons. I prefer to use a fairly safe, secure and standard set of grants for MySQL users that allows them to do what they have to in their database but disallows root privileges like this:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `some_database`.* TO 'some_user'@'localhost';

Of course change, some_database and some_user to match your specific setup. But after doing one of those GRANT commands, you need to tell MySQL to reload the privileges like this:

FLUSH PRIVILEGES;

And now if you run SHOW GRANTS on that user you can see all their rights as you have set them:

SHOW GRANTS FOR 'some_user'@'localhost';

Those are basically the two things I would look for and act on when I am debugging a MySQL networking/connection issue like this.

Finally, test the connection from the command line.

And now to test the connection from the Ubuntu 14.04 command line, just enter the following command:

mysql -u some_user -p

Of course change some_user to match your user and then enter your password. You should now be connected to MySQL via that user. If you can successfully connect via the command line, then you know the MySQL networking/connection stuff works well. So anything past that could be related to your PHP coding itself. And that is really something outside of the scope of a simple answer on this site; you need to debug your local PHP code yourself to solve that.

Related Question