Installing and Securing MariaDB on Ubuntu Bionic

• database
• mysql
• MariaDB
• Ubuntu-Bionic

MariaDB is a fork of the MySQL (Structured Query Language) relational database management system which allows switching from MySQL to MariaDB without having to alter your applications since the data and data structures will not need to change.

This means that:

• Data and table definition files (.frm) files are binary compatible.
• All client APIs, protocols, and structs are identical.
• All filenames, binaries, paths, ports, sockets should be the same.
• All MySQL connectors work unchanged with MariaDB.
• The mysql-client package also works with MariaDB server.

Even the command line tools are similar to mysqldump and mysqladmin still having the original names, allowing MariaDB to be a drop-in replacement.

Before you start

To complete the actions presented below, you must have:

• A Scaleway account logged into the console
• Owner status or IAM permissions allowing you to perform actions in the intended Organization
• An SSH key
• An Instance running on Ubuntu Bionic Beaver
• sudo privileges or access to the root user

Installing MariaDB

1. Connect to your instance via SSH.

   ssh root@SERVER_IP
   

   If you do not know your server IP, you can list your existing servers using scw ps (Scaleway CLI).

   Type yes when prompted to confirm that you wish to proceed. If the Scaleway CLI is not installed, you have several options:

   • (Preferred) On Mac OS using Homebrew and launching brew install scw
   • On Mac OS using a manual install

   Install the latest stable release on Mac OS X manually:
   # prepare for first install and upgrade
   mkdir -p /usr/local/bin
   mv /usr/local/bin/scw /tmp/scw.old
   # get latest release
   wget "https://github.com/scaleway/scaleway-cli/releases/download/v2.32.1/scw-darwin-amd64" -O /usr/local/bin/scw
   # test
   scw version
   Install the latest release on Linux:
   # get latest release
   export ARCH=amd64  # can be 'i386', 'amd64' or 'armhf'
   wget "https://github.com/scaleway/scaleway-cli/releases/download/v2.32.1/scw_2.32.1_${ARCH}.deb" -O /tmp/scw.deb
   dpkg -i /tmp/scw.deb && rm -f /tmp/scw.deb
   # test
   scw version
   

   • On Windows by downloading the scw-windows-amd64.exe
   Note

   If you use the root user, you can remove the sudo before each command.

2. Update the Ubuntu package manager:

   sudo apt-get update
   

3. Upgrade the Ubuntu packages already installed:

   sudo apt-get upgrade
   

4. Install MariaDB server and MariaDB client:

   sudo apt-get install mariadb-server mariadb-client
   

Managing MariaDB database server

The lines below allow you to stop, start, and enable the MariaDB server to automatically start up every time you reboot your machine.

sudo systemctl stop mysql.service
sudo systemctl start mysql.service
sudo systemctl enable mysql.service

Securing MariaDB

This program enables you to improve the security of your MySQL installation.

You can:

• set a password for root accounts.
• remove root accounts that are accessible from outside the localhost.
• remove anonymous-user accounts.
• remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

1. Run the following command:

   mysql_secure_installation
   

2. Follow the steps in the wizard:
   • Enter current password for root: Press Enter for none.
   • Set root password? Y
   • Create and confirm a new password
   • Remove anonymous users? Y
   • Disallow root login remotely? Y
   • Remove test database and access to it? Y
   • Reload privilege tables now? Y
3. Run the following command to log in to the MariaDB database server:

   mysql -u root -p
   

Resetting your MariaDB root password

If you forget or lose the root password to your MariaDB database, you can still gain access and reset the password if you have access to the server and a sudo-enabled user account.

Depending on the database used and its version, you’ll need to use different commands to recover the root password.

1. Determine the SQL version:

   mysql --version
   

   Which returns

   mysql  Ver 15.1 Distrib 10.6.18-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
   

   Note which database and which version you are running, as you will need it later.

2. Shut down the database server to change the root password:

   sudo systemctl stop mariadb.service
   

   If you run MySQL and MariaDB without loading information about user privileges, it allows you to access the database command line with root privileges without providing a password. It enables to gain access to the database without knowing it.

3. Start the database without loading the grant tables or enabling networking:

   sudo mysqld_safe --skip-grant-tables --skip-networking &
   

   Note

   The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

4. Connect to the database as the root user (the password should not be required):

   mysql -u root
   

   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 2
   Server version: 10.6.18-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   MariaDB [(none)]>
   

5. Reload the grant tables by issuing the FLUSH PRIVILEGES command:

   FLUSH PRIVILEGES;
   

6. Change the root password.

   Note

   Make sure to replace new_password with your new password of choice.

   For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   

   For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
   

   If the ALTER USER command does not work, run

   UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
   

   Which, in any case should return:

   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   

7. Reload the grant tables by issuing the FLUSH PRIVILEGES command.

8. Restart the database server.

   sudo kill `cat /var/run/mariadb/mariadb.pid`
   

9. Restart the service.

   sudo systemctl start mariadb.service
   

10. Confirm that the new password has been applied correctly.

    mysql -u root -p
    

The command should now prompt for the newly assigned password. You should gain access to the database prompt as expected.