Running MariaDB on a Raspberry Pi

This is a quick rundown for anyone that needs it. I did this to be able to practice accessing and using a database on an external machine that I own and control.

If anyone has any recommendations to simplify or enhance this process, let me know or put it in a comment. If anything is incorrect here (I typed this up in a hurry) let me know so I can correct it ASAP.

Needs:

-         Personal Computer ("PC")

-         Raspberry Pi (mine’s a years-old Pi2)

-         Memory card: MicroSD Card >=1GB (maybe 2-4 just to be safe, Mine's 8GB)

-         An adapter to read the memory card on your PC

-         MicroUSB cable (for power)

-         USB AC adapter (for power)

-         HDMI cable

-         Any screen or monitor you can plug the HDMI cable into

-         USB keyboard and mouse

-         Either a WiFi dongle (pretty sure I got mine as part of a kit from Adafruit) or a plain old ethernet cable (eventually you might like to connect with both; they’re both cheap and this gives you the option to have your Pi wherever you are OR plug it right into your router.)

Parts/Instructions I don't discuss here:

- the 3.5in TFTPlus touchscreen from Adafruit I added. It’s fancy and fun but 1000% unnecessary.

- I also used a Bluetooth mini keyboard because having multiple full-sized keyboards and mice in a smaller home workspace can get a little crowded and confusing. Also, any kind of wireless keyboard/mouse combo only uses one of the Pis 4 USB ports.

- I never connect my Pi directly to my PC with an ethernet cable if I can help it, because I did that once a long time ago and all hell broke loose.

Instructions:

1)     Download Raspberry Pi Imager

Raspberry Pi OS – Raspberry Pi

2)     Connect your memory card to your PC.

3)     Use Raspberry Pi Imager to load Raspberry Pi OS on on your memory card.

This took about ~11 minutes on my computer.

4)     Remove the memory card from your PC and insert it into your Pi.

5)     Plug the HDMI cable into your Pi and your monitor.

6)     Plug your mouse and keyboard in to the Pi

7)     Ethernet cable to router, or plug in the wireless dongle into one of the USB ports

8)     Plug in the MicroUSB cable to the Pi and the other end into any kind of USB power adapter.

My Pi will takes about <1 min to start up.

9)     Set your initial settings: language, time zone, keyboard type, etc.

10)  For Wireless (how I did my initial set-up) put in your network info.

Not gonna lie: I have enough passwords to remember, so I tried to keep passwords as simple as possible for this AND THIS ALONE.

11)  Once the Pi is online, it will need to do some more updates.

Mine took between 14:-18 minutes different times.

At this point, your Pi is up an running. You can play with it and do whatever kooky stuff you want.

The worst things that could happen are:

A)     you install malware that mines bitcoin or participates in DDoS attacks or harvests files from any unsecured machines on your LAN.

You shouldn’t have to worry about this if you stick to apt-get on your Pi, which only installs official signed packages by the RPi foundation.

B)     You somehow permanently break your $40 computer.

This is exceedingly hard to do with just commands and software, but between junior Marines and having kids, I know that anything’s possible.

C)     You mess something up to a point you can’t easily undo/fix it.

So, you turn it off, take the memory card out of the Pi and repeat steps 2-11, which will take another ~35 min.

The following is to add SQL to your Pi, or more specifically MariaDB.

This is what Microsoft Learn has to say about MariaDB (I'm working on Azure DB stuff):

“MariaDB is a newer database management system, created by the original developers of MySQL. The database engine has since been rewritten and optimized to improve performance. MariaDB offers compatibility with Oracle Database (another popular commercial database management system). One notable feature of MariaDB is its built-in support for temporal data. A table can hold several versions of data, enabling an application to query the data as it appeared at some point in the past.”

So, it does the job, recognizes most SQL commands and it works for Pi. K.I.S.S.

12)  Open the terminal on your Pi’s OS. (it’s the little black box icon at the top of the screen with the >_ on it.)

13)  Type in:

sudo apt-get install mariadb-server python-mysqldb

14)  Once that’s done, you can clear out all the built-in test settings:

sudo mysql_secure_installation

You'll get a series of prompts for y/n responses. Read through them, but you'll likely just want 'y's across the board.

NOTE: initially it may ask for a password. It will also provide instructions that if you haven’t set a password yet, the password will be blank. If you haven’t set a password yet leave the password blank and hit enter. When you set a new password, I’d recommend you keep it simple and use the same password as your Pi itself, but you do you.

15)  Now that all this is in, you can do launch the MariaDB and use SQL (mysql) on your Pi:

sudo mysql

If you need help, use \h. If you want to get out of the DB, use \q to quit. Remember to use a semicolon ';' after commands.

At this point, you can just play around with SQL (MySQL, MariaDB) on your Pi, BUT if you want to do it all from your PC, you can use the command prompt or PowerShell.

16)  From your Pi’s terminal, type in:

ifconfig

You should see your Pi’s IP address (192.168.1.XXX). Remember if you switch from wired to wireless, this will likely change.

UPDATE 2: ALTERNATIVE TO "ifconfig"

If you have trouble picking through everything ifconfig spits out, or just want something a little cleaner, use:

hostname -I

17)  Turn on SSH (“secure shell”) on your Pi. You can do this with commands or by just using the GUI:

Click the RPi Icon in the top left corner>Preferences>Raspberry Pi Configuration>Interfaces> SSH: Enable

18)  Over on your PC, open PowerShell or the command prompt, and type in (using your RPi’s IP address):

ssh [email protected]

It will ask for your Pi’s password. Again, don’t trip out if you don’t see characters as you type. Just type it in. If all goes well, you’ll get the same prompt from your RPi’s terminal:

pi@raspberrypi:~ $

19)  Repeat step #15 to start up MariaDB

That’s it. There’s a variety of resources I tried to use, but honestly Raspberry Pi Foundation themselves have some of the most clear-cut instructions. A lot of the articles I picked through for this were years old and some of the commands and instructions didn’t quite work. Sometimes its your fault (PEBMAC, right?) but a lot of times, people are using their own weird stuff in weird ways. On that note, I AM NOT AN EXPERT ON THIS STUFF. I learn through doing coupled with repetition. This took me a day-ish to work out (not counting the addition of the touchscreen which was an absolute nightmare and the original reason I stopped playing with Raspberry Pis in the first place.) What isn’t listed here were the countless times I did something dumb, threw up my hands and cursed my maker…then reimaged my Pi and got back to work. Don't get discouraged. Don't be a punk.

I have more work to do, but I hope this gives you a resources and spares you from unintentionally obliterating a perfectly good PC by using it as a DB server. Or you can just work solely in the cloud, which is fine by me and cloud service providers, too.

***

UPDATE 1: Finding Your Pi's New IP Address Remotely if/when it Restarts and Reconnects to Your Network

Something that has come up once or twice is the Pi's IP address changing. Usually this is the result of it losing power, then reconnecting to the network. Not a problem if it's sitting next to you at your workstation: just repeat Step 16. But if you don't feel like getting up, or even tinkering with the Pi directly there are obviously other ways. One way is to go into your router's interface and find the Pi. Because I detest opening even a single extra browser window or tab, a way I was doing it was by, again, using PowerShell:

$Subnet = "192.168.1."

1..254|ForEach-Object{

  Start-Process -WindowStyle Hidden ping.exe -Argumentlist "-n 1 -l 0 -f -i 2 -w 1 -4 $SubNet$_"

}

$Computers =(arp.exe -a | Select-String "$SubNet.*dynam") -replace ' +',','|

 ConvertFrom-Csv -Header Computername,IPv4,MAC,x,Vendor|

          Select Computername,IPv4,MAC

ForEach ($Computer in $Computers){

 nslookup $Computer.IPv4|Select-String -Pattern "^Name:\s+([^\.]+).*$"|

  ForEach-Object{

   $Computer.Computername = $_.Matches.Groups[1].Value

  }

}

$Computers

#$Computers | Out-Gridview

The above looks ugly courtesy of LinkedIn's editor, It's functional, but can be cleaned up and saved in Notepad++. Using this will spit out a nice, pretty table of devices on your network using IPv4, including your Pi. This is a modified bit of code from a user on StackOverflow, user6811411. That individual also had a couple lines that created a new CSV file to save this info in, which I removed. I'll say this again, I'm not a computer science or engineering professor or instructor, but make sure you're paying attention to what you're doing and why it works.

There is a somewhat less obnoxious way to find out your Pi's IP address, but it requires you to know it's MAC Address. This is also listed in the results of running ifconfig in Step 16: it's listed after the word ether, something like a1:23:bc:4d:5e:f6. The curriculum developer in me would use a label maker to put this on a sticker; the combat vehicle commander in me would write it directly on my desk with a red grease pencil. Armed with this knowledge, you can instead type in:

arp -a

...and look for your Pi's MAC address and you'll see the corresponding IP address. This also helps for if you're an overachiever who has connected multiple Raspberry Pis to your network that haven't been individually named and might all show up as "raspberrypi".