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.
