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
andphp5-mcrypt
installed. But what aboutphp5-mysql
? Something as simple as that could cause you headaches. Just run this line:And once that is installed, restart Apache like this:
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: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: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:
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 themy.cnf
on the Ubuntu system and look forbind-address
. I like to usenano
so this is the command I would use:And on a non-networked install of MySQL the
bind-address
setting would be as follows: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: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:And then restart MySQL like this:
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:And then run this command to see what users/hosts you have setup:
The output on a basic MySQL install should be something like this if you only have
root
setup: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 inuser
: One for127.0.0.1
(IPv4 address),::1
(IPv6 address) andlocalhost
(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 vialocalhost
. So you would need to setup a separate grant forlocalhost
.Doing something like that is fairly easy. Some people like to toss all grants to a user in a development environment like this:
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:
Of course change,
some_database
andsome_user
to match your specific setup. But after doing one of thoseGRANT
commands, you need to tell MySQL to reload the privileges like this:And now if you run
SHOW GRANTS
on that user you can see all their rights as you have set them: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:
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.