MariaDB

Database and User Setup

-- create a database named newdb
CREATE DATABASE newdb;

-- create a user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strong_password';

-- grant all permissions on one database
GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost';

-- grant specific permissions on one table
GRANT SELECT, INSERT, UPDATE, DELETE ON newdb.table_name TO 'newuser'@'localhost';

-- allow the user to execute a stored procedure
GRANT EXECUTE ON PROCEDURE newdb.procedure_name TO 'newuser'@'localhost';

FLUSH PRIVILEGES; is usually unnecessary after CREATE USER or GRANT on modern MariaDB versions, but it remains safe if you need to force a reload.

Common Commands

-- list all databases
SHOW DATABASES;

-- switch database
USE newdb;

-- list all tables
SHOW TABLES;

-- set the server time zone
SET GLOBAL time_zone = 'Asia/Kuala_Lumpur';

-- change the user password
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'new_password';

Stored Procedure Example

DELIMITER //

CREATE PROCEDURE sp_next_no(IN seq_type VARCHAR(10), IN prefix VARCHAR(10))
BEGIN
  DECLARE next_no DECIMAL(15, 0);

  UPDATE seq
  SET curr_no = curr_no + 1
  WHERE `type` = seq_type;

  SELECT curr_no INTO next_no
  FROM seq
  WHERE `type` = seq_type;

  IF next_no IS NULL THEN
    INSERT INTO seq (`type`, curr_no, prefix)
    VALUES (seq_type, 1, prefix);
    SET next_no = 1;
  END IF;

  SELECT next_no;
END//

DELIMITER ;

Production-ready recommendations

Create a dedicated application user instead of using root:

CREATE USER 'app_user'@'10.%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON app_db.* TO 'app_user'@'10.%';

Basic server settings:

# /etc/mysql/mariadb.conf.d/99-custom.cnf
[mysqld]
bind-address = 127.0.0.1
max_connections = 200
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-name-resolve

Enable binary logging if backups or replication matter:

[mysqld]
server_id = 1
log_bin = mariadb-bin
binlog_format = ROW
expire_logs_days = 7

Operational checklist:

• Keep MariaDB bound to private IPs or localhost unless public access is required.
• Back up with mysqldump or physical snapshots before schema changes.
• Monitor slow queries and enable the slow query log on busy systems.