Connect to MySQL on Raspberry Pi from Windows PC

While experimenting with mysql on Raspberry Pi, I often stumble upon few steps. Therefore I decided to write a post about creating the remote connection on mysql on raspberry pi. This post is about how to login to mysql on raspberry pi from windows computer. The steps are as they worked for me and may be different for you depending on version on your raspberry pi and your mariadb.

I have Raspberry Pi 4, 2GB RAM with the raspberry Pi os version February 2021

Mariadb on raspberry Pi Version : 10.3.27-MariaDB-0+deb10u1 |

How to connect to mariadb (free version of mysql) Server running on Raspberry Pi from Visual Studio Application

Install MySQL on Raspberry Pi

Since MySQL is a proprietary ORACLE product, there is no way you can install the MySQL on raspberry pi, however, we can use the free version of mysql which is called MariaDB on Raspberry Pi. Exactly the same functionality for all basic rdbms applications

Install Mariadb On Raspberry Pi

To install mariadb on raspberry Pi, run following series of commands. The update and upgrade are optional if you’ve recently done them on your raspberry Pi. So Open the terminal of Raspberry Pi, and use these commands to get mariadb installed on your raspberry pi

sudo mysql -u root -p
sudo apt update
sudo apt upgrade
sudo apt install mariadb-server
sudo mysql_secure_installation
sudo mysql -u root -p

Allow remote connection to Mariadb On Raspberry Pi

By default, MySQL or Mariadb on raspberry pi is not configured to accept remote connections. You can enable remote connections by modifying the configuration file: For me, in the raspberry pi, the configuration file WAS NOT LOCATED on this address

sudo nano /etc/mysql/my.cnf

Instead I found it at

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

So you may need to experiment with your raspberry pi and the version of mariadb to see where you find this file

Here, find the bind-address line which should be currently pointing to localhost address which is 127.0.0.1. In order to login from external source, you’ll have 2 options.

1. To access from A specific computer ONLY, you need to put address of that computer
2. To access from Any computer on LAN, change this address to 0.0.0.0 In my case, I did this.

Now,

Restart the MySQL service

sudo service mysql restart

Setup MySQL permissions

Connect to your MySQL instance as root:

mysql -p -u root

Remember, you can’t access mysql remotely using root user, so don’t try with it and simply create a new user which should be created at both localhost and at ‘%’ just like below

Now, Create a user:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

The apostrophes ( ‘ ) in the syntax are required

After this,

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
flush privileges;

Login to Mariadb on Raspberry Pi from Windows Computer

Now that we have created a user to login to our database remotely, its time to test this connection. To do this, you’ll need to install some database client on windows machine just like dbeaver

Once you install dbeaver, you can login to your database by specifying the server address as IP_OF_YOUR_RPI:3306

3306 is the default port number of mariadb server on raspberry pi and then you can see the data through dbeaver