Installing MariaDB or Mysql on a Rapsberry Pi

In this guide we will install MariaDB or Mysql. Secure the installation and create our first database and user. Finally we’ll go through how to tweak the installation.

In this guide we will install MariaDB or Mysql. Secure the installation and create our first database and user. Finally we’ll go through how to tweak the installation.

Installing MariaDB

Personally I strongly prefer MariaDB over MySQL. The performance is better then MySQL and it’s more open source. There are a few differences but nothing you will notice during normal daily use. Here is a guide If you’re interested in detailed differences between MariaDB and Mysql.

Let’s start by installing MariDB with the following command.

sudo apt install -y mariadb-server-10.3

Alternative, install mysql

If you don’t want to get out of your comfort zone use mysql. Keep in mind the rest of the setup steps might vary a little bit. Nothing you can’t figure out yourself 😉.

sudo apt install -y mysql-server

After it’s done check the version with mysql --version. It should be ‘mysql Ver 15.1 Distrib 10.3.25-MariaDB‘ or higher.

Securing the installation

The biggest thing we need is a root password. We will do that and some other things.

sudo mysql_secure_installation

This will give the following steps.

  1. Set a long safe password.
  2. Remove anonymous users Yes
  3. Disallow root login remotely Yes
  4. Remove test database and access to it Yes
  5. Reload privilege tables now Yes

Creating a database and user

To create a database we are going to login to MySQL with the following command.
Because we use sudo and root we will not need to enter a password.

sudo mysql -uroot

We are going to create a database and a user and connect them.

CREATE DATABASE raspimain_db;

Next we create a user, be sure to replace the password!

CREATE USER 'raspimain_user'@'localhost' IDENTIFIED BY '%%SAFE_PASSWORD%%';

Then we need to connect the user to that database.

GRANT ALL PRIVILEGES ON `raspimain_db`.* TO `raspimain_user`@`localhost`;

Next we 2 commands are pretty self explanatory.

FLUSH PRIVILEGES;
EXIT;

To test if it worked login in to mysql with that user.

mysql -u raspimain_user raspimain_db -p

This time use the password you used to create the user. Check if the raspimain_db is in the list of databases.

SHOW DATABASES;

If it is use EXIT; to exit the mysql promt.

Tweak a few settings

If you want to do custom changes I recommend doing them in a separate config file. Currently the only tweak I would want is to increase the max_allowed_packet. This allows bigger packets to be send to mysql. Some plugins recommend it.

So we open our new custom config: sudo nano /etc/mysql/mariadb.conf.d/99-custom-open-ip.cnf

And add the setting:

[mysqld]
max_allowed_packet=64M

After saving we reload mysql: sudo service mysql restart

And check the value with this command:
sudo mysql -uroot -e "SHOW VARIABLES LIKE 'max_allowed_packet';"

The output should look something like this:

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+

Other settings can be added in the same way.

Opening a Raspberry Pi to the outside world

To be able to reach a website we will need to open the Raspberry Pi to the big outside world.

So you have setup your Raspberry Pi got a website running on it, but you can only reach it from the local network.
In this guide we will open the Pi so the whole internet can reach it.

This part is a bit tricky. Because it’s depending on third parties. And they all work a bit different. The big stokes are the same for everyone but the how will differ a bit. This step can also be done later.

Opening the router to the outside world.

You need to login into your modem which your ISP provided to you. How to do that might differ per modem. But usually it will be an ip which start with 192.168.x.x To help you get started. A few pointers.

  • Inspect the modem. Look for passwords and model type.
  • Google the modem type, this will help with the ip and maybe the default login credentials.
  • Some still have default passwords, instead of random generated.
  • Call your ISP, it’s there modem they should be able to help.

Once you are in the modem settings we are going to do a thing called port-forwarding. I had a lot of problems figuring this out. Because of that I’m going to refer you else where. Keep in mind the following.

  • You will need to forward it to the hostname or the internal IP used by the Pi. If you need the IP see below to give a static IP. The hostname you can get with hostname.
  • The ports we are talking about should be 80 for http and 443 for https.
  • If you want too login to ssh remote secure your remote and forward port 22 for ssh.
  • You might need something called “TCP”
  • I needed to fiddle with a setting “DMZ Host”network
  • If you are struggling google port-forwarding with your modem

As I said this is really not my piece of pi, so here is a better guide: HowToGeek guide

Adding a domain name

First you will need to register a domain name. There are a lot of these registrars as they are called. I’m not going to recommend any because I registered all my domains at a Dutch company. One registered you will need to add a DNS A-record that will point to your home IP.

You can get your home network IP with the following command:

wget http://ipinfo.io/ip --timeout=3 --tries=1  -qO -

To test if your url is working run: ping example.com -c 5
It should return something like this, with your home IP:

5 packets transmitted, 5 received, 0% packet loss, time 1000ms

This blogs blueprint

How is this blog build. What theme, plugins and such.

Update dec-2019:

This article is now completely outdated.

Original:

I’ve had the idea for a blog for years. But actually bloging keeping it up is hard. So here is another go. This blog had no restrictions for me. So when setting this up I did some things I would(/could) never do anywhere else.
The biggest sin I commit is not having a development environment.
So how I this blog (being?) build:

Continue reading “This blogs blueprint”