How to Install and Secure MariaDB on Ubuntu

Step-by-Step Guide to Installing MariaDB

Installing MariaDB on your Ubuntu server is straightforward with the following command:

sudo apt install mariadb-server -y

Managing MariaDB Start-Up

To enable MariaDB to start at boot, use:

sudo update-rc.d mariadb defaults

If you need to disable this feature:

sudo update-rc.d mariadb disable

Configuring MariaDB for Security

To secure your MariaDB installation, run the secure installation script:

sudo mysql_secure_installation

Follow the Prompts Carefully:

• Enter current password for root (enter for none): Press Enter as there's no default password initially.
• Switch to unix_socket authentication [Y/n]: Enter n to keep the root user protected with a dedicated password.
• Set root password? [Y/n]: Enter Y and set a strong password for the root user.
• Remove anonymous users? [Y/n]: Enter Y to remove anonymous users.
• Disallow root login remotely? [Y/n]: Enter Y to prevent remote root login.
• Remove test database and access to it? [Y/n]: Enter Y to remove the test database.
• Reload privilege tables now? [Y/n]: Enter Y to apply the changes.

Testing the Root Login

To ensure everything is set up correctly, log in as root:

sudo mysql -u root -p

Checking User Authentication

You can check the authentication method for each user:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Configuring MariaDB for Network Access

By default, MariaDB listens on localhost (127.0.0.1). To allow connections from another server, modify the configuration.

Step 1: Edit the Configuration File

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

Step 2: Modify the Bind Address (optional)

Update the bind address to allow remote connections:

#bind-address = 127.0.0.1
bind-address = 0.0.0.0  # Allows connections from any IP address

Alternatively, you can use the specific IP address of your MariaDB server. Suppose your server IP address is 192.168.1.100

bind-address = 192.168.1.100

Step 3: Save and Restart MariaDB

sudo systemctl restart mysql

Managing User and Database

Setting a Password for the Root User

ALTER USER 'root'@'localhost' IDENTIFIED BY '<YOUR NEW PASSWORD>'; FLUSH PRIVILEGES;

Creating a Database with UTF-8 Support

CREATE DATABASE <NEW DATABASE NAME> CHARACTER SET utf8 COLLATE utf8_general_ci;

Creating a New User

CREATE USER '<NEW USER>'@'%' IDENTIFIED BY '<YOUR NEW PASSWORD>';

Granting Permissions

GRANT ALL ON <DATABASE NAME>.* TO '<USER>'@'%'; FLUSH PRIVILEGES;

Importing a MySQL Dump

To import an existing database:

mysql -u <MYSQL USERNAME> -p <DATABASE NAME> < DumpFile.sql

Disabling Foreign Key Checks

Add the following to the start of your DumpFile.sql:

SET FOREIGN_KEY_CHECKS=0;

And at the end of the file:

SET FOREIGN_KEY_CHECKS=1;

Resolving the ONLY_FULL_GROUP_BY Mode Error

If you encounter issues with the ONLY_FULL_GROUP_BY mode, run:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

To persist this change after a restart:

SET PERSIST sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

By following these steps, you'll have a secure and properly configured MariaDB server ready for your applications.