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: information_schema Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: mysql Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: performance_schema Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: Phase 5/6: Checking and upgrading tables Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: Processing databases Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: information_schema Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: performance_schema Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: Phase 6/6: Running 'FLUSH PRIVILEGES' Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: OK Apr 30 12:39:45 dbase1 /etc/mysql/debian-start: 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.