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.
- 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.
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.