By Default MySQL only accept local connection to enable MySQL access from remote computer we need to make some changes. Edit /etc/mysql/my.cnf file and search for bind-address line and comment it out and save it.
|
1 |
# bind-address = 127.0.0.1 |
User Permission
Login to MySQL by issuing following command, you will be prompted for my your MySQL root password.
|
1 |
mysql -u root -p |
Enter MySQL root password that we set when you installed MySQL server on rpi, after successful login issue following command to give access to SQL server from remote IP address. (change IP address of your required one and password that you want to use).
|
1 |
GRANT ALL ON *.* TO root@'192.168.99.10' IDENTIFIED BY 'passw0rd'; |
Firewall Settings
Raspberry pi OS comes with built-in firewall and it blocks all incoming connections. Now configure iptables so it accept remote connection for MySQL traffic from our remote computer, run following commands and make sure you don’t get any errors.
|
1 2 |
iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT iptables -A INPUT -i wlan0 -s 192.168.99.10 -p tcp --destination-port 3306 -j ACCEPT |
To test the connection remotely try to connect your MySQL server from remote computer with credentials you added.
3 comments
Hi,
I’m a bit confused as to what ip address I should be putting in on these steps?
@Luke,
if you need to access your MySQL/MariaDB from any other computer then you need to enter ip address of that computer, but if you are just trying to setup PiHome then you can enter following:
mysql -u root -pCREATE USER 'pihome'@'localhost' IDENTIFIED BY 'pihome2018';
GRANT ALL PRIVILEGES ON *.* TO 'pihome'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;