Tag: MariaDB

Connecting PowerBI to RaspberryPi (MariaDB Engine)

It’s been a little while since I had PowerBI working against a remote repository on my Raspberry Pi and I had some troubles recalling how to configure it. As a result, it was an excellent experience troubleshooting it. This also led to me realizing, there’s no guide to configuring PowerBI to MySQL(MariaDB) on a Raspberry Pi. There are a lot of similar posts, but the problem with most of scenarios I found online is that the MySQL DB was most frequently running locally with PowerBI, whereas in my circumstance it was remote.

The primary issue I ran into was seeing “Details: MySQL: Unable to connect to any of the specified MySQL hosts.” I was able to connect to my instance with phpmyadmin and also with MySQL workbench, so I felt I was able to connect with PowerBI here too. I installed MariaDB Workbench just to test and see if I could at least access my instance with that tool. It turned out I ran into a similar issue. The problem is twofold.

1. The bind-address for my MySQL instance was set, not allowing remote IP calls.
2. I needed a user with a correct domain to access the box with a SQL login.

To resolve the first issue, I had to find my config file. MariaDB is set up to import four different config files, the one I edited was /etc/mysql/mariadb.conf.d/50-server.cnf. A quick # in front of #bind-address=127.0.0.1 and a sudo /etc/init.d/mysql restart got the IP fixed up quick.

To resolve the second issue, I created a user with extra permissions. IMPORTANT NOTE: This is NOT best practice. My Pi is on a private network and does not host any secure data. So I’d recommend taking this as an example and working with it to fit your security needs.

I create a user with admin privileges that has access open to any IP. So I’ll be able to remote into the Pi from any device on that private network with that login now.

CREATE USER 'someuseraccount'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'someuseraccount'@'%' WITH GRANT OPTION;

In PowerBI, I went to Get Data -> MySQL DB -> put in my server as 192.168.1.155 -> the name of my Database -> and set the login for Database and used the user and password I had just created in the last step.

Now I’ve seen where other users had issues and it was due to a driver issue, so make sure you have your driver updated for MySQL.

Here is where I obtained the idea for the bind in step 1.

Here is where I obtained the idea for the user in step 2.

MySQL (Really, MariaDB) on the Raspberry Pi!

I’ve had a few people ask me lately about how I got MySQL running on my Raspberry Pi, so here’s my guide on how to go from almost 0 to 100. This guide assumes you understand the basics of Linux and Raspberry Pi’s. You’ve probably done a few different tutorials and you have tinkered with it before.

1. Download Raspbian.

Raspbian

2. Wipe the sd card and format it. (SDFormatter is a free tool you can use.)

3. Unzip the file.

4. Write the image to disk. (Win32 Disk Imager is a free tool you can use.)

5. Copy a wpa_supplicant.conf file and ssh.txt over to the boot partition of the sd card. You’ll need to edit the conf to contain your current wifi information, the below is a good example of a file you can modify and use. The ssh.text file is just a blank file with the name ssh so the Pi knows to enable SSH on boot.

country=US
ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

network={
    ssid="YOUR WIFI IS GONNA GO HERE"
    scan_ssid=1
    psk="YOUR PASSWORD IS GOING HERE AND IF YOU DON'T USE ONE THEN SHAME ON YOU"
    key_mgmt=WPA-PSK
}

6. SSH into the PI. (Putty is a free tool you can use.)

7. Run this on your Pi to get RDP working. It’s just a nice to have but you can skip this if you won’t use it, we won’t be using it in the tutorial.

#remove the following packages : xrdp, vnc4server, tightvncserver
sudo apt-get remove xrdp vnc4server tightvncserver

#install tightvncserver followed by xrdp
sudo apt-get install tightvncserver

sudo apt-get install xrdp

8. Now update the Pi so it’s current.

#update your system's package list by entering the following command:
sudo apt-get update

#Next, upgrade all your installed packages to their latest versions with the command:
sudo apt-get dist-upgrade

8. Get My-SQL.

sudo apt-get install mysql-server

9. Configure security for MySQL, this is going to ask you about creating a root user. Make sure you give it a password, MyPHPAdmin will NOT work if you don’t make your root user with a password. I made this mistake the first time and then installed phpmyadmin, this was where the root password got stored and I had to re-run the configuration for phpmyadmin by following the steps posted below: (Credit to Amigo Chan on askUbuntu) (For anyone googling this error, I saw errors such as “not enough privilege to view users. phpmyadmin” and “access denied for user ‘root’@’localhost’ TO DATABASE phpmyadmin”.)

sudo mysql_secure_installation

10. Install bindings for python for later use.

sudo apt-get install python-mysqldb

11. Create a mysql user.

sudo mysql -u root -p

GRANT ALL PRIVILEGES ON mydb.* TO 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD GOES HERE';

quit

12. Get apache2 up and running.

sudo apt-get install apache2 php5 libapache2-mod-php5

13. Get phpmyadmin up and running.

sudo apt-get install phpmyadmin

14. Configure apache2.

sudo nano /etc/apache2/apache2.conf

15. Restart apache2.

sudo /etc/init.d/apache2 restart

16. Now you need to go to a webbrowser and put in the IP of the Pi and /phpmyadmin at the end. Example(192.168.1.155/phpmyadmin)

And Voila! You should be in!

Additional resources used:
https://pimylifeup.com/raspberry-pi-mysql-phpmyadmin/