allow remote mysql access (through webmin or shell)

Question

Spec: Ubuntu 14.04 webmin/virtualmin 1.791

I am using following code to test remote mysql database connection:

<?php

$db_host = "123.456.789";
$db_name = "database";
$db_user = "user";
$db_pass = "password";
$db_table_prefix = "prefix_";



GLOBAL $errors;
GLOBAL $successes;

$errors = array();
$successes = array();

$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);
GLOBAL $mysqli;

if(mysqli_connect_errno()) {
    echo "Conn Error = " . mysqli_connect_error();
    exit();
}

?>

I keep getting this error:

No connection could be made because the target machine actively refused it.

Research shows this means the server is "not listening". Before I ran the above script I've already tried to allow remote mysql access through webmin gui. What I did is editting "database manage->host permissions" and make it as follows:

This was supposed to allow remote mysql access but it doesn't work. Also I read from somewhere else that to allow remote mysql access I need to edit /etc/mysql/my.cnf; I have thought that after I edit the "host permissions" in webmin this file would be changed, but it was not. On the other hand, I couldn't find the lines I was supposed to edit in my.cnf, so I am stuck here.

Any help is appreciated.

Solution

Login mysql mysql -u yourname -p yourpassword,then follow like this

mysql>use mysql;
mysql>select host,user from user;

It may give the following result:

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| 127.0.0.1 | root             |
| ::1       | root             |
| localhost | debian-sys-maint |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

If you see that, it means you can only connect mysql in localhost,so you need do the following steps:

mysql>GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)
mysql>flush privileges;
Query OK, 0 rows affected (0.00 sec)

"%"means any host can remotely log on to the server,if you want to restrict access to only a machine,you need to change the "%" to the IP address of the machine you want to allow to connect.

If it works,then you select host,user from user;,you will get the following info:

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| 127.0.0.1 | root             |
| ::1       | root             |
| localhost | debian-sys-maint |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

Exit mysql,edit /etc/mysql/my.cnf,find

# 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

annotate it or you can delete it(not recommended),restart your mysql server,usingservice mysql restart,if you do this like me,you may solve the problem.

It works well in my computer(ubuntu 14.04+mysql 5.5)

Comments

[Pepito Fernandez]

In my case, /etc/mysql/my.cnf was empty, with the exception of two lines (!include) references. I went to the second reference (mysqld.cnf) where I found the bind-address section. Works like a charm.