222222
Advanced OT User
- Joined
- Jul 3, 2007
- Messages
- 211
- Reaction score
- 160
MariaDB is one of the most popular choices for databases. It was created by the same developers of MySQL with the goal to make it better in terms of security and performance. So if you're still using MySQL in 2024, it's time to update to MariaDB. This tutorial will guide you through the process of building and installing the latest stable version of MariaDB on a GNU/Linux server. I'll be using Debian 12 in this tutorial, which is what I recommend everyone to use.
Section 1: Building and installing MariaDB
By default, Debian doesn't include the build dependencies required for MariaDB, so we will have to add that by ourselves before we are able to build it from source. Make sure you have connected to your server and then run the following to add the required apt repositories.
Then add the following lines into the file and save it using Ctrl+O → Enter → Ctrl+X.
Now let's download and install the build dependencies for MariaDB:
As with most software, MariaDB offers different versions to download. We do not want their latest version since that isn’t stable enough for a production server. Instead we want the latest stable version of MariaDB. Avoid using the Alpha, Beta or Release Candidate (RC) versions. Start by navigating to the MariaDB website and find the latest stable version number. At the time of writing this post, v11.3.2 is the latest stable version.
Now go back to your server and let's download the source code for that version of MariaDB. If the version number has changed since I wrote this post, simply change it in the commands below. The following commands will make sure we get all the necessary tables and settings setup. By default, MariaDB adds a ton of extra features that nearly nobody use. And they also exclude a lot of useful things. By specifying a few flags in our "cmake", we ensure we get everything we need, with no bloat. The "-DWITHOUT_DYNAMIC_PLUGINS=1" flag is quite a hidden gem, which I was given by a core developer from MariaDB. It includes only the recommended security and performance related configurations. This flag can not be found in their official documentation, so see it as a gift.
After the installation has finished, MariaDB will be installed at "/usr/local/mysql". Even though it was MariaDB we installed, they still use the name "mysql" for some files and directories. You will notice both the word "MariaDB" and "MySQL" will be used in files in the next coming sections. This may change in the future as MariaDB is slowly moving away from using the name "MySQL" in their files.
Section 2: Creating a systemd service for MariaDB
It's a good idea to setup a restricted system user on your server, whose only purpose is to run MariaDB. This makes sure that nothing else on the server gets compromised in case something ever happens to MariaDB. We will simply call the new user and group "mysql". Run the following commands to create it and give the new system user access to the installation directory.
Now we will create a systemd service that will run MariaDB. This makes it easy for us to start/stop/restart the service as we want. MariaDB comes with a default systemd service template, which we will copy to the systemd directory. Run the following to do that, and restart the systemd service daemon.
We will not start the service yet. Continue to the next sections first.
Section 3: Installing the default database schemas
MariaDB also comes with a few default schemas that has to be setup. If we do not do this, there is no guarantee that MariaDB will work as expected. So let's tell MariaDB where our schemas are located and tell it to use our new "mysql" user we created, to access the schema files.
We have now run the secure installation script provided by MariaDB. It will ask us to enter the administrator password. Since we have not set anything up yet, leave it blank and just press Enter to continue. Next it will ask if we want to use "unix_socket authentication", meaning: should we login to MariaDB with the users on the server? The answer is no. We want to setup custom usernames in our database. So type "n" into the prompt and hit Enter to continue.
Now it will ask you to create an administrator (root) password. Agree to that and create a new secure password. I recommend using a password manager such as KeePassXC to both generate and store all your passwords. Make sure you use a strong password (30+ characters with a mix of letters, numbers and special characters). Never share this password with anyone else.
For the remaining questions in the prompt, just leave them blank and hit Enter. When you are done setting up MariaDB, we will have to create a symbolic link so that "MariaDB" and "MySQL" can both be used. Run the following:
Section 4: Configuring the MariaDB systemd service
Now that the default schemas are installed and we have created symbolic links for MariaDB/MySQL, let's configure the systemd service we created earlier. Edit the service file using the following command:
Then find the following line in the file:
And change it to this instead. This makes sure MariaDB uses our specified data directory for all database schemas.
Save the file (using Ctrl+O → Enter → Ctrl+X) and then reload and start the MariaDB service. In case it's already running, we will kill that process before starting it with the new configuration.
At this point, I also recommend just giving your server a reboot to make sure MariaDB is running when the server restarts.
Once you're back in your server after the reboot, let's sign in to MariaDB using the root user and the password you created during the installation.
If it worked fine to login, you can disconnect from MariaDB by pressing Ctrl+C.
Section 5: Securing MariaDB access
It's generally not a good idea to use the MariaDB "root" user for your application/website/OT server, or whatever it is that you will connect to the database. The root user has full access to everything inside the database. Instead, we should setup a custom user that only has the required access to function. Whatever it should have access to is up to you. You can for example set it to only read ("SELECT") from the database, or to do everything. It's up to you.
First, let's just quickly edit the MariaBD configuration file and make sure it never uses unix socket authentication (local account login), as it is bad practice to do so.
Enter the following into the configuration file and save it (using Ctrl+O → Enter → Ctrl+X).
Restart the MariaDB service and then let's sign in to the root user.
Now that we are signed in to MariaDB, let's just change the root password here, because sometimes the "secure installation script" uses a different authentication protocol, which makes a few slight differences. I recommend using KeePassXC to generate and store all of your login credentials. Change "<password>" to your new desired password for the administrator (root) user.
Sign out from MariaDB (using Ctrl+C) and reconnec to the root user again to make sure the new password works as expected.
Now let's create our non-root user. Again, use KeePassXC to both generate and store all login credentials. Create unique usernames and passwords that you do not use anywhere else (preferably 30+ characters long). Let's only give the new user access to new database that we create. Let's name the database "otserver". Run the following to set it up and change "<username>" and "<password>" to your desired login credentials.
In this case, we gave it pretty much full access (SELECT, INSERT, UPDATE, DELETE, CREATE). But as I mentioned, you can choose what permissions it should have. If you only want it to CREATE, SELECT, INSERT, UPDATE then you can do that.
Now sign out of the root user and sign in to your newly created user ("<username>"). This is the user your OT server/website/whatever should be connecting with.
You have now installed the latest stable version of MariaDB from source (with no bloat and only using recommended modules), setup a systemd service for it which you can control using "sudo systemctl stop|start|restart mariadb", changed the password of the MariaDB root user, setup an initial database named "otserver" and created a restricted non-root MariaDB user that your application/server can use to access the database. By following these best practices, you ensure that MariaDB is going to run very fast and is secured.
Section 1: Building and installing MariaDB
By default, Debian doesn't include the build dependencies required for MariaDB, so we will have to add that by ourselves before we are able to build it from source. Make sure you have connected to your server and then run the following to add the required apt repositories.
Bash:
sudo nano /etc/apt/sources.list.d/mariadb.listThen add the following lines into the file and save it using Ctrl+O → Enter → Ctrl+X.
Code:
deb http://ftp.debian.org/debian bookworm main contrib
deb-src http://ftp.debian.org/debian bookworm main contrib
deb-src http://security.debian.org/debian-security bookworm-security main contribNow let's download and install the build dependencies for MariaDB:
Bash:
sudo apt update
sudo apt build-dep mariadb-serverAs with most software, MariaDB offers different versions to download. We do not want their latest version since that isn’t stable enough for a production server. Instead we want the latest stable version of MariaDB. Avoid using the Alpha, Beta or Release Candidate (RC) versions. Start by navigating to the MariaDB website and find the latest stable version number. At the time of writing this post, v11.3.2 is the latest stable version.
Now go back to your server and let's download the source code for that version of MariaDB. If the version number has changed since I wrote this post, simply change it in the commands below. The following commands will make sure we get all the necessary tables and settings setup. By default, MariaDB adds a ton of extra features that nearly nobody use. And they also exclude a lot of useful things. By specifying a few flags in our "cmake", we ensure we get everything we need, with no bloat. The "-DWITHOUT_DYNAMIC_PLUGINS=1" flag is quite a hidden gem, which I was given by a core developer from MariaDB. It includes only the recommended security and performance related configurations. This flag can not be found in their official documentation, so see it as a gift.
Bash:
cd ~
wget https://archive.mariadb.org/mariadb-11.3.2/source/mariadb-11.3.2.tar.gz
tar -xf mariadb-*.tar.gz
cd mariadb-11.3.2
cmake . -DWITHOUT_DYNAMIC_PLUGINS=1 -DWITH_MARIABACKUP=0 -DPLUGIN_PERFSCHEMA=NO -DWITH_UNIT_TESTS=0
cmake --build .
sudo cmake --install .After the installation has finished, MariaDB will be installed at "/usr/local/mysql". Even though it was MariaDB we installed, they still use the name "mysql" for some files and directories. You will notice both the word "MariaDB" and "MySQL" will be used in files in the next coming sections. This may change in the future as MariaDB is slowly moving away from using the name "MySQL" in their files.
Section 2: Creating a systemd service for MariaDB
It's a good idea to setup a restricted system user on your server, whose only purpose is to run MariaDB. This makes sure that nothing else on the server gets compromised in case something ever happens to MariaDB. We will simply call the new user and group "mysql". Run the following commands to create it and give the new system user access to the installation directory.
Bash:
sudo adduser --system --shell /bin/false --disabled-login --group mysql
sudo chown -R mysql /usr/local/mysqlNow we will create a systemd service that will run MariaDB. This makes it easy for us to start/stop/restart the service as we want. MariaDB comes with a default systemd service template, which we will copy to the systemd directory. Run the following to do that, and restart the systemd service daemon.
Bash:
sudo cp /usr/local/mysql/support-files/systemd/mariadb.service /etc/systemd/system/mariadb.service
sudo systemctl daemon-reloadWe will not start the service yet. Continue to the next sections first.
Section 3: Installing the default database schemas
MariaDB also comes with a few default schemas that has to be setup. If we do not do this, there is no guarantee that MariaDB will work as expected. So let's tell MariaDB where our schemas are located and tell it to use our new "mysql" user we created, to access the schema files.
Bash:
cd /usr/local/mysql
sudo /usr/local/mysql/scripts/mariadb-install-db --user=mysql
sudo /usr/local/mysql/bin/mariadbd-safe --user=mysql --datadir=/usr/local/mysql/data &
sudo systemctl enable mariadb
sudo /usr/local/mysql/bin/mariadb-secure-installationWe have now run the secure installation script provided by MariaDB. It will ask us to enter the administrator password. Since we have not set anything up yet, leave it blank and just press Enter to continue. Next it will ask if we want to use "unix_socket authentication", meaning: should we login to MariaDB with the users on the server? The answer is no. We want to setup custom usernames in our database. So type "n" into the prompt and hit Enter to continue.
Now it will ask you to create an administrator (root) password. Agree to that and create a new secure password. I recommend using a password manager such as KeePassXC to both generate and store all your passwords. Make sure you use a strong password (30+ characters with a mix of letters, numbers and special characters). Never share this password with anyone else.
For the remaining questions in the prompt, just leave them blank and hit Enter. When you are done setting up MariaDB, we will have to create a symbolic link so that "MariaDB" and "MySQL" can both be used. Run the following:
Bash:
sudo ln -s /usr/local/mysql/bin/mariadb /usr/local/bin/mariadb
sudo ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysqlSection 4: Configuring the MariaDB systemd service
Now that the default schemas are installed and we have created symbolic links for MariaDB/MySQL, let's configure the systemd service we created earlier. Edit the service file using the following command:
Bash:
sudo nano /etc/systemd/system/mariadb.serviceThen find the following line in the file:
Code:
ExecStart=/usr/local/mysql/bin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITIONAnd change it to this instead. This makes sure MariaDB uses our specified data directory for all database schemas.
Bash:
ExecStart=/usr/local/mysql/bin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --user=mysql --datadir=/usr/local/mysql/dataSave the file (using Ctrl+O → Enter → Ctrl+X) and then reload and start the MariaDB service. In case it's already running, we will kill that process before starting it with the new configuration.
Bash:
sudo systemctl daemon-reload
sudo kill -SIGTERM $(sudo cat $(sudo find /usr/local/mysql/data -name '*.pid' -print -quit))
sudo systemctl start mariadb
sudo systemctl enable mariadbAt this point, I also recommend just giving your server a reboot to make sure MariaDB is running when the server restarts.
Bash:
sudo rebootOnce you're back in your server after the reboot, let's sign in to MariaDB using the root user and the password you created during the installation.
Bash:
mariadb -u root -pIf it worked fine to login, you can disconnect from MariaDB by pressing Ctrl+C.
Section 5: Securing MariaDB access
It's generally not a good idea to use the MariaDB "root" user for your application/website/OT server, or whatever it is that you will connect to the database. The root user has full access to everything inside the database. Instead, we should setup a custom user that only has the required access to function. Whatever it should have access to is up to you. You can for example set it to only read ("SELECT") from the database, or to do everything. It's up to you.
First, let's just quickly edit the MariaBD configuration file and make sure it never uses unix socket authentication (local account login), as it is bad practice to do so.
Bash:
sudo nano /etc/my.cnfEnter the following into the configuration file and save it (using Ctrl+O → Enter → Ctrl+X).
Code:
[mariadb]
unix_socket=OFFRestart the MariaDB service and then let's sign in to the root user.
Bash:
sudo systemctl restart mariadb
mariadb -u root -pNow that we are signed in to MariaDB, let's just change the root password here, because sometimes the "secure installation script" uses a different authentication protocol, which makes a few slight differences. I recommend using KeePassXC to generate and store all of your login credentials. Change "<password>" to your new desired password for the administrator (root) user.
SQL:
ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;Sign out from MariaDB (using Ctrl+C) and reconnec to the root user again to make sure the new password works as expected.
Bash:
mariadb -u root -pNow let's create our non-root user. Again, use KeePassXC to both generate and store all login credentials. Create unique usernames and passwords that you do not use anywhere else (preferably 30+ characters long). Let's only give the new user access to new database that we create. Let's name the database "otserver". Run the following to set it up and change "<username>" and "<password>" to your desired login credentials.
SQL:
CREATE DATABASE otserver;
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON otserver.* TO '<username>'@'localhost';
FLUSH PRIVILEGES;In this case, we gave it pretty much full access (SELECT, INSERT, UPDATE, DELETE, CREATE). But as I mentioned, you can choose what permissions it should have. If you only want it to CREATE, SELECT, INSERT, UPDATE then you can do that.
Now sign out of the root user and sign in to your newly created user ("<username>"). This is the user your OT server/website/whatever should be connecting with.
Bash:
mariadb -u <username> -pYou have now installed the latest stable version of MariaDB from source (with no bloat and only using recommended modules), setup a systemd service for it which you can control using "sudo systemctl stop|start|restart mariadb", changed the password of the MariaDB root user, setup an initial database named "otserver" and created a restricted non-root MariaDB user that your application/server can use to access the database. By following these best practices, you ensure that MariaDB is going to run very fast and is secured.
Last edited:
