MariaDB Notes

Setup

1. Install MariaDB and its client libraries:

pacman -S mariadb mariadb-clients mariadb-libs

2. Initialize MariaDB data directory:

sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

This command serves multiple purpose:

• Generates tables that handle the store of user, privileges, time zones, etc.
• Setup the main user so MariaDB can read and write.
• Finally sets where the software lives and stored the data.

2. Start the MariaDB service:

sudo systemctl start mariadb

3. Secure Installation

Run the built-in script to set root password and secure MariaDB:

sudo mysql_secure_installation

This will allow you to:

• Set the root password
• Remove anonymous users
• Disallow root login remotely
• Remove test database

Enter the MariaDB Command Line

Switch to the MariaDB command line with:

sudo mariadb

To exit the MariaDB CLI:

exit;

Basics of MariaDB

Creating a New User

[!WARNING] Use a dedicated user per database for security.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

Creating a Database

CREATE DATABASE mydb;

Grant privileges on the database to your user:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

To revoke privileges:

REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';
FLUSH PRIVILEGES;

To delete a database:

DROP DATABASE mydb;

To delete a user:

DROP USER 'myuser'@'localhost';

Useful Commands

Show users:

SELECT User, Host FROM mysql.user;

Show databases:

SHOW DATABASES;

Connect to a specific database:

USE mydb;

Show tables in the current database:

SHOW TABLES;

Describe table structure:

DESCRIBE table_name;

Export DB into a file

mariadb-dump -u myuser -p --no-data mydb > mydb.sql

Change myuser and mydb accordingly to your MariaDB user and database, you can also specify the place where the file will be saved mydb.sql