phpMyAdmin Remote Access

Question

I am trying to access phpMyAdmin from Ubuntu remotely, and not through localhost on the Ubuntu droplet (DigitalOcean). To do this, I need to change the setup of the config.ini.php in /etc/phpmyadmin.

When running the following code:

<?php
    $link = mysqli_connect("ip_address", "username", "password", "mysql")
    if (!$link) {
        echo "Error: Unable to connect to MySql" . PHP_EOL;    
        exit;
    }
    echo "Success: Connected to MySQL" . PHP_EOL;
    echo "Host Information: " . mysqli_get_host_info($link) . PHP_EOL;

    mysqli_close($link);
?>

I get "mysqli_connect(): (HY000/2002): Connection refused in ...."

Tips to configuration in config.ini.php so I can use the ip address to connect?

Have a look at the user permissions in MySQL (phpMyAdmin itself does not handle permissions) - probably, you don't have any user configured that is able to connect to your database server from the outside? After all, this is set on purpose, so you should not configure such a user if you don't really know what you are doing — Nico Haase, Feb 13 '18 at 12:57

mrmegatheman · Accepted Answer · 2018-02-13T16:21:15.843

I would comment if I could. Basically this is what you are after.

MySQL root access from all hosts

So you need to first grant privileges for table mysql to root or the username you are using for this case.

GRANT ALL PRIVILEGES ON mysql.* TO 'username'@'%' IDENTIFIED BY 'password';

Then you need edit the mysql config file

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Comment out #bind-address = 127.0.0.1 then save the config file.

Restart mysql service mysql restart

score 0 · Answer 2 · answered Feb 13 '18 at 12:36

0

First debug: Is Your digital ocean accessible by global adress? Second thing: You use mysqli_connect(); You pass the arguments as a "ip_address", "username", "password", "mysql" -> That means that you do not pass variables but literally string ip_adresss; This is for stack overflow purposes?

Third thing: In modern mysql versions ROOT user should have password and for mysql_server service should be enabled hosts others than localhosts. Try to debug that. Check the ports: 3306 by default

Fourth thing: Do not use mysqli_connect. Try PDO or Doctrine in Your project

answered Feb 13 '18 at 12:36

Błażej Krzakala

145
1
6

The mysqli_connect parameters are only for stack overflow purposes yes. I do have a public ip_address for the DigitalOcean droplet which I am using, and I do use the "root" username and password. So you recommend to use PDO or Doctrine instead of mysqli_connect in the php script? – haakoes Feb 13 '18 at 12:47
Yes, and also change user from root to else-named user with root privilledges. Than login via this user. 4.eg `myuser` Why? Mysqli server login via root elswhere than localhost is disabled and hard to overcome this build-in feature – Błażej Krzakala Feb 26 '18 at 14:21

score 0 · Answer 3 · answered Feb 15 '23 at 17:12

Make MySQL Server open to remote access connections

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find bind-address option, change to

bind-address = 0.0.0.0

Configure phpMyAdmin to work with remote connections

If you use phpMyAdmin v5+, first of all, you have to rename config.sample.inc.php to config.inc.php

mv config.sample.inc.php config.inc.php

If yoy have an old one - just edit config.inc.php

nano config.inc.php

Find $cfg['Servers'][...] array and add after

$i++;
$cfg['Servers'][$i]['host'] = 'xxx.xxx.xxx.xxx'; //Remote MySQL IP address
$cfg['Servers'][$i]['port'] = '3306'; //or your custom port

PROFIT. Go to your phpMyAdmin and look at login form select with choosing remote connections.

phpMyAdmin Remote Access

3 Answers3

Linked