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

SHOW GLOBAL VARIABLES LIKE 'PORT';

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>';

CREATE DATABASE <YourDatabase>;

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

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

FLUSH PRIVILEGES;

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 : '169.254.0.200'

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 = 127.0.0.1

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.

Leave a Reply

Your email address will not be published. Required fields are marked *