How to access a MySQL database webservice over LAN

Question

Closed. This question needs debugging details. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 6 years ago.

Improve this question

I have three PCs that I connect over LAN.

• The first PC to database;
• The second to web service CodeIgniter RESTful;
• The third to client.

Now how can I connect web service CodeIgniter server to database, I've set the IP on aplication/config/database.php,

$db['default']['hostname'] = 'localhost';

I've changed localhost with the IP that is used by the first pc:

$db['default']['hostname'] = '192.168.1.10';

But, the result :

A database error occurred
Unable to connect to your database server using the provided settings.

Answer 1

A database error occurred
Unable to connect to your database server using the provided settings.

If this is MySQL, networking of the database server is not enabled by default. To enable networking in MySQL you need to find the active MySQL configuration file named my.cnf. And edit it.

I am going to explain how to do this on Ubuntu 12.04, but the instructions are similar for most any Linux install.

Enable MySQL Networking

First, open the file using an editor like nano. You might need to run the command via sudo:

sudo nano /etc/mysql/my.cnf

Then look for the area in your configuration file with the bind-address option:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

Now change that bind-address setting to the following:

bind-address            = 0.0.0.0

With that done save the file & restart MySQL like this:

sudo service mysql restart

And now your MySQL database will be able to take non-localhost connections from remote machines.

Make Sure the MySQL Port 3306 is Open

That said, even with networking enabled, you should still check if you are able to connect to the remote machine from the command line using a networking tool such as nmap. You might have a firewall on 192.168.1.10 blocking MySQL port 3306 so you need to check if it is open or closed like this:

nmap 192.168.1.10 -p3306

And if port 3306 is open, this will be the response; note the open under STATE:

Starting Nmap 6.40 ( http://nmap.org ) at 2014-06-10 10:34 EDT
Nmap scan report for 192.168.1.10
Host is up (0.0035s latency).
PORT     STATE SERVICE
3306/tcp open  mysql

But if port 3306 is closed, you will get this; note the closed under STATE:

Starting Nmap 6.40 ( http://nmap.org ) at 2014-06-10 10:34 EDT
Nmap scan report for 192.168.1.10
Host is up (0.0035s latency).
PORT     STATE  SERVICE
3306/tcp closed mysql

Check Your MySQL User Grants

Now with that all done, your MySQL database server should be accessible on the network. But that said, you need to make sure your database user you are using is allowed to connect from a remote machine on your LAN. So login to MySQL & run this command to see what grants the user might have:

SELECT user, host FROM `mysql`.`user`;

This will show you a list of users & hosts connected to those users in MySQL. The thing is most users are only granted access privileges to localhost or 127.0.0.1. Some are granted the wildcard host of %. You need to look at that list and see if the user you want to use has a wildcard host (%) or a specific IP address connected to them. You can check a user’s GRANTs by running a line like this; of course change [your_database_user] and [hostname] to match your settings:

SHOW GRANTS FOR '[your_database_user]'@'[hostname]';

That should show you a list of available GRANTs for a user on a specific host. If they have GRANTs that would allow remote access—such as using wildcard % host—then you are all set. If they don’t have any GRANTs you can run the following MySQL commands; of course change [your_database] and [your_database_user] to match your settings:

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 `[your_database]`.* TO '[your_database_user]'@'192.168.0.0/255.255.0.0';
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 `[your_database]`.* TO '[your_database_user]'@'10.0.0.0/255.0.0.0';
FLUSH PRIVILEGES;

Both GRANT lines apply a fairly standard set of access rights to a database. The first line applies them to any connection in the LAN network range of 192.168.x.x. The second line applies them to any connection in the LAN network range of 10.x.x.x. I like to do that to cover all bases on internal networks. The last FLUSH PRIVILEGES; line basically tells MySQL to reload the user privileges tables which allow those grants to take effect.