Allow remote mysql access on linux (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.

Answer 1

You can do this via webmin too,

Create your user account for remote access

• Webmin > Servers > MySQL Database Server > User permissions

Allow the MySQL server to listen to remote requests

• Webmin > Servers > MySQL Database Server > MySQL Server Configuration

MySQL server listening address - set it to any

Restart MySQL using service mysql restart or directly from webmin.

Allowing MySQL to listen to any port is not a good idea , unless you are the only one who can access that network ,

Don't leave it like this afterwards , Its better to Allow certain hosts on certain domains , like your IP , Or simply just dont listen on all ports if its not required i.e when you are finished with your session.

You can also do this via /etc/mysql/my.cnf Just add a binding adress of your choice instead of localhost

Answer 2

I have got it to work, however not through webmin at all.

First I need to comment out the following line in /etc/mysql/my.cnf:

#bind-address       = 127.0.0.1

I guess instead of simply commenting it out, I can also change 127.0.0.1 to my local IP address. Many google results stop here, but this is not enough. The next step is to grant the local user privileges: On remote server, I need to run the following commands:

$ mysql -u root -p Enter password:

mysql> use mysql

mysql> GRANT ALL ON . to user@'localIP' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

Actually I have seen this when I was doing google search before I asked the question here, but I just ignored it because I thought I've done it. It turns out I not only need to grant privileges on server side but also need to do it for "local user".

Feel free to comment here if there's still something I missed out or you know how to do it throught webmin(I am still wondering what editting "host permissions" in webmin does).