Raspberry Pi How to remote access MySQL on openHabian (RPI 4)

I wanted to use phpMyAdmin on a Synology to access a MySQL running on a RPI with openHabian. Here is my how-to:

Click to Read More

First connect on your openHabian using a ssh console.

Obviously, you need MySQL to be installed and configured:

sudo apt update

sudo apt upgrade

sudo apt install mariadb-server

sudo mysql_secure_installation

Then, double check that MySQL is running and listening on port 3306
netstat -plantu | grep 3306

If nothing is displayed by this command, MySQL is not listening on the port 3306.

Enter MySQL as root with the command:

sudo mysql -uroot -p

Check the port used by MySQL


Then, type the following MySQL commands to create an account and a database, and grant both local and remote access for this account on the database:

CREATE USER '<YourAccount>'@'localhost' IDENTIFIED BY '<YourPassword>';


GRANT ALL PRIVILEGES ON <YourDatabase>.* TO '<YourAccount>'@'localhost';

GRANT ALL PRIVILEGES ON *.* to '<YourAccount>'@'169.254.0.%' identified by '<YourAccountPassword>' WITH GRANT OPTION;


Here above, I do grant access from all machines in my local network with '169.254.0.%'. One can restrict access to one machine with its specific address, such as : ''

Now, edit 50-server.cnf and configure MySQL to not listen anymore on its local IP only (simply comment the line bind-address) :

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

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

Finally, restart MySQL for the changes above to be applied:

sudo service mysqld restart

You can now edit the config of phpMyAdmin to access the MySQL on your RPI. If it is running on Synology, look here.

Synology How to Add Multiple Hosts in phpMyAdmin on Synology

On Google, one can easily find how to add servers in the list presented on the login page of phpMyAdmin. But thes results don't apply if you are using the package 'phpMyAdmin' for Synology. With that package, one must edit the synology_server_choice.json file.

Click to Read More

If you are connected on your NAS via a SSH console, the file to be edited is located in /var/services/web/phpMyAdmin/synology_server_choice.json.

But you should also be able to access it from a Windows PC on \\<YourNAS>\web\phpMyAdmin\synology_server_choice.json

To add a server, simply duplicate the first statement of the json file, separated with a comma:

{"verbose":"Server 1","auth_type":"cookie","host":"localhost","connect_type":"socket","socket":"\/run\/mysqld\/mysqld10.sock","compress":false,"AllowNoPassword":false},
{"verbose":"Server 2","auth_type":"cookie","host":"","connect_type":"socket","socket":"\/run\/mysqld\/mysqld10.sock","compress":false,"AllowNoPassword":false},
{"verbose":"Server 3","auth_type":"cookie","host":"","connect_type":"socket","socket":"\/run\/mysqld\/mysqld10.sock","compress":false,"AllowNoPassword":false}

Et voilĂ .