MariaDB Introduction

Posted on In

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License.

Installation

Install mariadb using apt for ubuntu see https://computingforgeeks.com/how-to-install-mariadb-on-ubuntu-lts-linux/

Command to check mariadb service status

1
sudo systemctl status mariadb

Commands to Start/Stop/Restart mariadb

1
2
3
4
sudo systemctl start mariadb
sudo systemctl stop mariadb
sudo systemctl restart mariadb
sudo systemctl enable mariadb

You can now use mysql command to connect to mariadb from localhost. When you install mariadb, you should have mysql client installed already. To login as root:

1
sudo mysql -u root -p

Like MySQL, MariaDB by default creates a user root with no password. Here root has nothing to do with operating system’s root user. It is only meaningful to MariaDB.

To check database version:

1
select version();

Create Admin User

User root can do anything from the localhost but not from remote. You often need to create a user to access database from remote.

Create a new user ‘newuser’ with password ‘newpass’

1
CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpass';

Here % means any host. You can use ‘localhost’ if you only want to user to access from localhost.

Grant all permission to user ‘newuser’

1
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%'

Now you can login to MariaDB as ‘newuser’

1
mysql -u newuser -p

Setup Remote access

Edit MariaDB config file. It can be in /etc/my.conf or /etc/mysql/my.conf

set up remote access and bind to 0.0.0.0

1
2
3
[mysqld]
skip-networking=0
bind-address=0.0.0.0

Docker setup

Start a mariadb server instance

1
2
3
4
5
6
7
8
docker network create some-network 
docker run --detach \
  --network some-network \
  --name some-mariadb \
  --env MARIADB_USER=example-user \
  --env MARIADB_PASSWORD=my_cool_secret \
  --env MARIADB_ROOT_PASSWORD=my-secret-pw  \
  mariadb:latest

Connect to MariaDB from the MySQL/MariaDB command line client

1
docker run -it --network some-network --rm mariadb mysql -hsome-mariadb -uexample-user -p

Database Commands

Show list of databases

1
SHOW DATABASES;

Create Database

1
CREATE DATABASE post;

Delete database

1
DELETE DATABASE post;

Select database

1
USE post;

Table Commands

Show tables

1
SHOW TABLES;

Create table

1
2
3
4
5
6
7
8
9
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    password VARCHAR(50),
    verified TINYINT(1),
    regdate DATETIME,
    PRIMARY KEY(id)
);

Describe table structure

1
DESCRIBE customer;

Basic Operations

Insert

1
INSERT INTO customer (name, email, password, verified, regdate) VALUES('alice', 'alice@example.com', 'pass', 1, now());

Select

1
2
SELECT * FROM customer;
SELECT id, name, email FROM customer;

Update

1
UPDATE customer SET name = 'Alice' WHERE id = 1;

Delete

1
DELETE FROM customer WHERE id=2;

Drop Table

1
DROP TABLE customer;

Reference