Step-by-Step: Configure MariaDB on Linux Server

Himanshu Joshi Knowledgebase

MariaDB is an open-source relational database management system (RDBMS) that serves as a drop-in replacement for MySQL. Known for its high performance, reliability, and robust security features, MariaDB is widely used in web applications and server environments. Configure MariaDB on a Linux server to ensure the efficient management and security of your databases.

Configure MariaDB on a Linux Server

In this step-by-step guide, we will walk you through installing and configuring MariaDB on a Linux server. Whether you are using Ubuntu, CentOS, or Fedora, this guide covers everything from installation to optimization, remote access configuration, and backup setup.

Prerequisites

Before beginning the installation and configuration process, make sure you meet the following prerequisites:

  • A Linux server (Ubuntu, CentOS, Fedora, or similar).
  • A non-root user with sudo privileges.
  • Basic understanding of the Linux command line.
  • Internet connectivity is required to install MariaDB and configure remote access.

Once you’ve confirmed that these prerequisites are in place, you are ready to proceed with the installation and configuration of MariaDB.

Install MariaDB

The first step in configuring MariaDB is to install the server software. The installation process varies slightly depending on the Linux distribution you’re using.

For Ubuntu/Debian:

  • Update the package list:
sudo apt update
  • Install MariaDB:
sudo apt install mariadb-server
  • Start the MariaDB service:
sudo systemctl start mariadb
  • Enable MariaDB to start automatically on boot:
sudo systemctl enable mariadb
  • Verify the installation by checking the MariaDB version:
mariadb --version

For CentOS/RHEL/Fedora:

  • Install MariaDB:

For CentOS/RHEL 7:

sudo yum install mariadb-server

For CentOS/RHEL 8 and Fedora:

sudo dnf install mariadb-server
  • Start MariaDB:
sudo systemctl start mariadb
  • Enable MariaDB to start on boot:
sudo systemctl enable mariadb
  • Check the installation by verifying the MariaDB version:
mariadb --version

Secure MariaDB Installation

Once MariaDB is installed, it is crucial to secure the installation. By default, MariaDB comes with several insecure settings that can expose your server to potential security risks. To secure your installation, use the built-in security script.

  • Run the MariaDB secure installation script:
sudo mysql_secure_installation
  • Follow the prompts to:
    • Set a root password.
    • Remove the test database and anonymous users.
    • Disallow root login remotely (if not required).
    • Reload the privilege tables to apply changes.

By following these steps, you will remove potential vulnerabilities and strengthen the security of your MariaDB installation.

Configure MariaDB for Remote Access

By default, MariaDB only listens for local connections. If you need to access the database remotely, you’ll need to configure MariaDB to accept connections from other servers.

  • Edit the MariaDB configuration file:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
  • Change the bind-address:

To allow MariaDB to listen on all interfaces, change the bind-address to 0.0.0.0:

bind-address = 0.0.0.0
  • Allow remote connections through the firewall:

If you’re using UFW (Uncomplicated Firewall) on Ubuntu:

sudo ufw allow from any to any port 3306 proto tcp
  • Create a remote user and grant privileges:

Log in to MariaDB:

mysql -u root -p
  • Create a user with remote access:
CREATE USER 'username'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'; FLUSH PRIVILEGES;
  • Restart MariaDB to apply the changes:
sudo systemctl restart mariadb

Manage MariaDB Service

Managing the MariaDB service is essential for ensuring your database server is running smoothly. Here are some basic commands to help you control the MariaDB service.

  • Start MariaDB:
sudo systemctl start mariadb
  • Stop MariaDB:
sudo systemctl stop mariadb
  • Restart MariaDB:
sudo systemctl restart mariadb
  • Check the status of MariaDB:
sudo systemctl status mariadb

These commands will help you manage the MariaDB service efficiently and ensure it is always running when needed.

Basic MariaDB Usage

Now that MariaDB is installed and secured, you can start using it to manage databases, create users, and perform other administrative tasks.

  • Log in to MariaDB:
mysql -u root -p
  • Show existing databases:
SHOW DATABASES;
  • Create a new database:
CREATE DATABASE mydatabase;
  • Create a new user and grant privileges:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

With these commands, you can begin managing your MariaDB databases and users.

Optimize MariaDB Performance

To get the best performance from your MariaDB server, it is essential to tweak its configuration for better resource utilization. Here’s how you can optimize MariaDB’s performance.

  • Edit the MariaDB configuration file:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
  • Adjust the following settings for improved performance:
    • innodb_buffer_pool_size: Set to 70-80% of your system’s available RAM.
    • max_connections: Adjust this based on the expected load on your server.
    • query_cache_size: Increase the query cache size for frequently executed queries.
  • Restart MariaDB to apply the changes:
sudo systemctl restart mariadb

These configuration changes will help ensure that your MariaDB instance runs optimally.

Set Up Regular Backups

Regular backups are essential to safeguard your data. MariaDB provides several methods to back up your databases, such as using mysqldump.

  • Create a backup using mysqldump:
mysqldump -u root -p mydatabase > /path/to/backup/mydatabase.sql
  • Automate backups using cron jobs:

Edit the crontab to schedule daily backups:

crontab -e

Add the following cron job for daily backups at 2 AM:

0 2 * * * mysqldump -u root -p'mypassword' mydatabase > /path/to/backup/mydatabase.sql

Automating backups ensures that your data is regularly backed up and protected from loss.

Conclusion

In this guide, we have walked you through the process of installing and configuring MariaDB on a Linux server. You have learned how to secure your MariaDB installation, configure remote access, manage the MariaDB service, and optimize its performance. Additionally, we have covered the importance of regular backups to ensure data security.

Proper configuration and management of MariaDB are essential for maintaining a secure, reliable, and high-performing database server. By following these steps, you will be well on your way to configuring MariaDB to meet your needs. For more detailed information, refer to the official MariaDB documentation.

Tags:
Leave A Comment