Cheat Sheet - MariaDB

Installation

To install the MariaDB server on Ubuntu 18.04 machine run:

sudo apt update
sudo apt install mariadb-server

Check service status:

sudo systemctl status mariadb

And lastly, you can check the MariaDB version:

mysql -V

Securing MariaDB

To secure your database deployment run:

sudo mysql_secure_installation

It is the first thing you need to do after a fresh install. This way you will not only secure your database but also set the root user password. The script also asks you if you want to remove the anonymous user, restrict root user access to the local machine, and remove the test database. In the end, the script will reload the privilege tables ensuring that all changes take effect immediately.

Docker Compose

A simple definition of MariaDB for docker deployment:

version: "3"
services:
  db:
    image: mariadb
    container_name: mariadb
    environment:
      MYSQL_ROOT_PASSWORD: giorgi123
      MYSQL_DATABASE: mydb
      MYSQL_USER: giorgi
      MYSQL_PASSWORD: giorgi123

Export Database

To export your database use the tool called mysqldump:

mysqldump -u username -p my_database > data-dump.sql

Import Database

First login to your server:

mysql -h localhost -u root -p

Then create a database where you want to import your dump:

mysql> CREATE DATABASE my_database;

Now you can finalize import by:

mysql -h localhost -u root -p my_database < data-dump.sql

Done.

Alter Table

You can add a column/field to your existing table with the following SQL command:

ALTER TABLE `users` ADD `hidden_at` DATETIME NULL DEFAULT NULL AFTER `is_hidden`;

Allow ROOT Login

By default, newer versions of MariaDB require you to log in to the host machine's root account to log in with root. You can disable it by running the following SQL commands:

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY 'giorgi123';
FLUSH PRIVILEGES;

Now you can log in as a root to your database with any user on your host.