CheatSheet - MariaDB

CheatSheet - MariaDB

Installation

To install 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 MariaDB version:

mysql -V

Securing MariaDB

To secure your database deployment run:

sudo mysql_secure_installation

It is a 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. At the end the script will reload the privilege tables ensuring that all changes take effect immediately.

Docker Compose

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 column/field to your existing table with 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 login to host machine root account to login with root. You can disable it by running following SQL commands:

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

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


Link
No links yet.