How to Build a database server with a Raspberry PI

Starting with Problems

Are you ready for the next chapter in our adventure? We’re building a highly available, WordPress website out of Raspberry Pis. Today, we’re going to build the backend, a MySQL database server with an external USB 3.0, 64TB thumb drive. The Pi is a Raspberry 4 with 2Gb of RAM. First, create the SD card and get booted. Follow my newbie guide if you need to! Database servers do a tremendous number of reads and writes to their hard drives. My Raspberry has an SD card for a hard drive! Bad combination. Once I get my Raspberry up-and-running, I’m going to move the entire OS to an external USB thumb drive and then install MySQL.

Format the USB drive

I installed the Raspbian Lite image on this install, knowing full well that this would be a dedicated Raspberry database server. This tactic saved me a couple of GB of files to copy over to the USB drive. So, I plugged in my 64GB USB, booted up, and logged in. A quick lsblk, lets us know what we’ve got:

pi@dbase1:~ $ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 1 59.8G 0 disk
├─sda1 8:1 1 256M 0 part
└─sda2 8:2 1 6.6G 0 part
mmcblk0 179:0 0 14.9G 0 disk
├─mmcblk0p1 179:1 0 256M 0 part /boot
└─mmcblk0p2 179:2 0 14.6G 0 part /

Looks like the USB drive is recognized, but there’s some old partitions on it. Let’s wipe it and write a new partition table.

Choose a Partitioning Standard

To do this, we first need to specify the partitioning standard we wish to use. GPT is the more modern partitioning standard, while the MBR standard offers wider support among operating systems. If you do not have any special requirements, it is probably better to use GPT at this point.

To choose the GPT standard, pass in the disk you identified like this:

sudo parted /dev/sda mklabel gpt

Create the New Partition

Once the format is selected, you can create a partition spanning the entire drive by typing:

sudo parted -a opt /dev/sda mkpart primary ext4 0% 100%

If we check lsblk, we should see the new partition available:

pi@dbase1:~# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda            8:0  1 59.8G 0 disk
└─sda1         8:1  1 59.8G 0 part 
mmcblk0      179:0  0 14.9G 0 disk
├─mmcblk0p1  179:1  0  256M 0 part /boot
└─mmcblk0p2  179:2  0 14.6G 0 part /

From here, check out my post on moving your root to the USB partition, and then we’ll continue with the database installation…

Install mysql on Raspberry

pi@dbase1:~# sudo apt install mysql-server
Reading package lists… Done
Building dependency tree
Reading state information… Done
Package mysql-server is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
However the following packages replace it:
mariadb-server-10.0
E: Package 'mysql-server' has no installation candidate

Hmm. Can’t install MySQL? But it leaves us with a hint. The MySQL developers and Oracle parted ways and changed a lot of their licensing. It’s a long story that you can Google, but we’re more interested in getting our DB server online! Check the second to last line and go forward:

pi@dbase1:~# sudo apt install mariadb-server-10.0
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following additional packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libdbd-mysql-perl libdbi-perl libencode-locale-perl libfcgi-perl
libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
liblwp-mediatypes-perl libmariadb3 libncurses5 li...

Looks good! Check it out:

pi@dbase1:~# systemctl status mysql
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql; generated)
Active: active (running) since Thu 2020-04-30 12:39:29 EDT; 1min 12s ago
Docs: man:systemd-sysv-generator(8)
Tasks: 26 (limit: 4915)
Memory: 44.4M
CGroup: /system.slice/mysql.service
├─1273 /bin/bash /usr/bin/mysqld_safe
├─1418 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/arm-linux-gnueabihf/mariadb18
└─1419 logger -t mysqld -p daemon error
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: information_schema
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: mysql
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: performance_schema
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: Phase 5/6: Checking and upgrading tables
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: Processing databases
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: information_schema
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: performance_schema
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: Phase 6/6: Running 'FLUSH PRIVILEGES'
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[1470]: OK
Apr 30 12:39:45 dbase1 /etc/mysql/debian-start[2012]: Triggering myisam-recover for all MyISAM tables and aria-recover for all
lines 1-21/21 (END)

Hold your BINGO cards, folks! We have a winner! … well, at least a working database server. AND, as an added bonus, the database is reading and writing to your USB device–not to an SD card, which means speed and reliability. Now we can move on to creating some db server accounts.

Configure the database

It is just as simple! There’s a script you can run that helps you set the initial password and then asks a couple of questions to customize things. Here’s a screengrab. You can see my answers. Hint: avoid special characters in your MySQL passwords for the time being. Letters and numbers are good enough for now.

pi@dbase1:~ $ sudo mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
… Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
… skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
Dropping test database…
… Success!
Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
pi@dbase1:~ $

Create the WordPress user

Now that we’ve got the root user’s password changed, we can create a user for the WordPress connection. Log in to the database as root with the password you just set. Remember what you type in here, as you’ll need it to configure WordPress.

root@dbase1:~# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database howtodb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON howtodb.* TO "howtouser"@"%" IDENTIFIED BY 'howtopass’';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
root@dbase1:~# 

And that’s it!

The WordPress installation will go the rest of the database administration. You can, of course, name your database whatever you like and the username and password are completely up to you. The “%” you see in the above statements is a wildcard. It means that “howtouser” will be allowed to access this database from any server. If you have port forwarding set up on your firewall, that would greatly expand this access. I don’t allow port 3306 in from outside, so this should be secure enough.

Next! How to Install WordPress on a Raspberry!

Author: John

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.