WordPress On Raspberry Pi – Part 2 – Database

Data Center Servers Room
Data Center Servers Room

In the first post of this series, we installed a number of softwares and packages including:

  • Apache,
  • MariaBD,
  • WordPress and,
  • phpMyAdmin

Then we created the basic required configuration to access Apache’s home page. Furthermore we made sure php is working properly. Finally we enabled WordPress on Apache.

However, to operate a blog, we miss one crucial component: a database. Sure we don’t need a datacenter but still that’s something !

In this post I’m going to focus on setting up a database for WordPress in MariaDB.

1. MariaDB First Steps

The very first thing we must do relates to security. You may have picked up a pattern here … Yes, security is indeed very important !

Out of the box, MariaDB defines a root user who can access the database without a password. This is obviously very wrong. Besides, nothing stops root from accessing the database through the network (compared to only locally to the Raspberry Pi). Same thing: very wrong.

Fortunately MariaDB comes with a useful program called mysql_secure_installation.

As a reminder, Oracle acquired MySQL a while ago. MariaDB is an open source fork of MySQL, created by the original developers of MySQL. Thus, this shouldn’t come as a shock, but there are still many references to MySQL when working with MariaDB.

Anyway, simply launch that command and read carefully before answering the questions.

Hints: Y is usually the right answer and you’ll be asked to change the root user password).

$ sudo mysql
> exit
$ sudo mysql_secure_installation
$ sudo mysql -u root -p
> SHOW DATABASES;
> USE mysql;
> SHOW TABLES;
> SELECT host, user, password FROM user;

In case it’s not obvious, the > represents MariaDB’s prompt for you to enter commands (compared to the shell prompt $). Likewise you may have noticed SQL commands must end with a ; character.

Once done, the root user can now only access the database from localhost. Furthermore, if you’ve followed my security guidelines, a firewall forbids any connection from the outside anyway (belt and suspenders).

Reference document:

2. Create A Dedicated User

It isn’t advisable to let WordPress use root access. Instead we are going to create a new MariaDB user with all privileges granted only on WordPress’ database:

> CREATE USER IF NOT EXISTS 'wordpress'@'localhost' IDENTIFIED BY '{wordpress MariaDB user password}';
> SELECT host, user, password FROM user;

I hope it goes without saying, but replace {wordpress MariaDB user password} with a secure password of your own.

Reference documents:

3. Create A Database

Now that we have a user, it would be great that she or he can access an actual database.

It may seem rather straightforward, but there is a catch actually. It relates to character sets (i.e. encoding) and collation (i.e. how characters are compared).

For instance, with the appropriate collation for Germany, ue and ü would be considered equivalent when sorting and comparing strings. If you want to find out more, I suggest you read the corresponding reference document below.

Most importantly, WordPress and MariaDB must work hand in hand and use the same character set. Looking into WordPress configuration file:

$ cd /var/www/html/wordpress/
$ grep -i charset wp-config.php
/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8mb4');

So here is our answer: utf8bm4 ! Now in MariaDB enter:

> SHOW CHARACTER SET;
> SHOW COLLATION LIKE 'utf8mb4%';

According to the last command the default collation for the ut8mb4 character set is utf8mb4_general_ci which also seems like a good choice.

> CREATE DATABASE IF NOT EXISTS wordpress CHARACTER SET utf8mb4;
> SHOW DATABASES;
> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='wordpress';

Reference documents:

4. Grant Privileges

Hang in there, we’re almost done. I told you this part would be quick and easy.

Finally, what we need to do is grant our new user all privileges on our newly created database and only that one (security should always be on your mind).

> GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'localhost';
> FLUSH PRIVILEGES;
> SHOW GRANTS FOR 'wordpress'@'localhost';
> exit

Reference document:

5. Final Words

To sum up:

  • We created a new database user as well as
  • A new database with the right character set.
  • We granted our user all privileges on the database.

The next step is simply about WordPress famous 5 minutes installation process and configuring permalinks.

As usual if you find any error or anything worth mentioning to the community, please feel free to add a comment.

Leave a comment