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.
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.
This will give the following steps.
- Set a long safe password.
- Remove anonymous users Yes
- Disallow root login remotely Yes
- Remove test database and access to it Yes
- 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.
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:
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.
Open database to the outside
I separate my pi’s One runs php+nginx and the other runs Mysql. That does require you to allow connection to the mysql from the other Pi.
This requires a few extra steps.
When creating a user also add this:
CREATE USER 'raspimain_user'@'192.168.2.%' IDENTIFIED BY '%%SAFE_PASSWORD%%'; GRANT ALL PRIVILEGES ON `raspimain_db`.* TO `raspimain_user`@`192.168.2.%`; FLUSH PRIVILEGES;
So you create a “second user” with the same name and password but allow access from the IP
Globally you still need to adjust one setting. In the
/etc/mysql/mariadb.conf.d/99-custom-open-ip.cnf file add:
[mysqld] skip-networking=0 skip-bind-address
And restart with
sudo service mysql restart
For more details check the mariaDB page.