Skip to main content

MariaDB 101

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS). It was created by the original developers of MySQL who were concerned about its acquisition by Oracle. MariaDB is intended to remain free and open-source under the GNU General Public License.

Overview​

MariaDB is designed to be a drop-in replacement for MySQL, meaning it is highly compatible with MySQL's APIs and commands. This makes it easy for developers to migrate their applications from MySQL to MariaDB. MariaDB includes several new features and performance improvements over MySQL, such as the Aria and ColumnStore storage engines.

Installation​

The installation process for MariaDB is similar to MySQL. Here are the general steps for a Debian-based Linux distribution like Ubuntu.

Ubuntu/Debian​

  1. Update Package Index:

    sudo apt update

  2. Install the MariaDB Server package:

    sudo apt install mariadb-server

  3. Run the Security Script: After installation, it's recommended to run the included security script to remove insecure default settings.

    sudo mysql_secure_installation

    This script will guide you through setting a root password, removing anonymous users, and other security-related configurations.

Basic Concepts and Commands​

Since MariaDB is a fork of MySQL, the basic concepts and commands are nearly identical.

Connecting to MariaDB​

To connect to the MariaDB server from the command line, use the mysql client (the same client used for MySQL):

sudo mysql -u root -p

You will be prompted to enter the root password you set during the secure installation.

Common SQL Commands​

The SQL commands for MariaDB are the same as for MySQL. Here are some examples:

  • Show Databases:

    SHOW DATABASES;

  • Create a Database:

    CREATE DATABASE my_new_mariadb;

  • Use a Database:

    USE my_new_mariadb;

  • Create a Table:

    CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        hire_date DATE
    );

  • Insert Data:

    INSERT INTO employees (first_name, last_name, hire_date) VALUES ('Jane', 'Doe', '2023-01-15');

  • Select Data:

    SELECT * FROM employees;

Docker Installation​

Running MariaDB in a Docker container is a straightforward process.

  1. Pull the MariaDB Image:

    docker pull mariadb:latest

  2. Run the MariaDB Container:

    docker run --name some-mariadb -e MARIADB_ROOT_PASSWORD=my-secret-pw -d mariadb:latest

    This command starts a new container named some-mariadb, sets the root password, and runs it in detached mode.

  3. Connect to the Container:

    docker exec -it some-mariadb mysql -uroot -p

Kubernetes Deployment​

Deploying MariaDB on Kubernetes can be done using a Deployment and a PersistentVolume, similar to MySQL. A popular way to manage MariaDB on Kubernetes is by using the MariaDB Operator.

  1. Install the MariaDB Operator: You can install the operator using Helm:

    helm repo add mariadb-operator https://mariadb-operator.github.io/mariadb-operator
    helm install mariadb-operator mariadb-operator/mariadb-operator

  2. Create a MariaDB Custom Resource:

    apiVersion: mariadb.mmontes.io/v1alpha1
    kind: MariaDB
    metadata:
      name: mariadb
    spec:
      rootPasswordSecretKeyRef:
        name: mariadb-secret
        key: root-password
      image: mariadb:10.6
      replicas: 1
      storage:
        size: 10Gi

Configuration and Tuning​

  • Configuration File: The main configuration file for MariaDB is typically my.cnf, located in /etc/mysql/my.cnf or /etc/my.cnf.
  • Key Configuration Options:
    • innodb_buffer_pool_size: Similar to MySQL, this is a critical setting for performance. Set it to 50-70% of available RAM on a dedicated server.
    • max_connections: Controls the maximum number of concurrent connections.
    • query_cache_type and query_cache_size: MariaDB still supports a query cache, which can be beneficial for read-heavy workloads.
  • Tuning:
    • Storage Engines: MariaDB offers multiple storage engines, such as InnoDB, Aria, and MyRocks. Choose the one that best fits your workload.
    • Indexing: Proper indexing is crucial for query performance.
    • Monitoring: Use tools like mysqltuner or monitoring solutions like Prometheus with the mysqld_exporter to monitor performance.