3

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:

enter image description here

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.

6

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)

  • 1
    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. – Pepito Fernandez Jul 9 '16 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.