Installing and Setting Up MariaDB on Ubuntu Linux

This tutorial guides you through installing and configuring MariaDB on an Ubuntu Linux server and creating your first multi-table database.

Part 1: Installing MariaDB

Step 1: Update the System

Open a terminal and update your package list to ensure all repositories are current:

sudo apt update && sudo apt upgrade -y

Step 2: Install MariaDB Server

Install MariaDB with the following command:

sudo apt install mariadb-server -y

Step 3: Secure MariaDB

Run the secure installation script to configure MariaDB securely:

sudo mysql_secure_installation

You’ll be prompted to:

  • Set a root password.
  • Remove anonymous users.
  • Disallow root login remotely.
  • Remove the test database.
  • Reload privilege tables.

Step 4: Verify Installation

Check if MariaDB is running:

sudo systemctl status mariadb

You should see an active status. To enable MariaDB to start on boot, run:

sudo systemctl enable mariadb

Part 2: Configuring MariaDB

Step 1: Access MariaDB Shell

Log in as the root user:

sudo mysql -u root -p

Enter the password you set earlier.

Step 2: Create a New Database and User

  1. Create a database:
    CREATE DATABASE my_database;
  2. Create a user and grant permissions:
    CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'strong_password'; GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost'; FLUSH PRIVILEGES;
  3. Exit the MariaDB shell:
    EXIT;

Part 3: Creating Your First Multi-Table Database

Step 1: Log In as the New User

Log in using the newly created user:

mysql -u my_user -p

Step 2: Use Your Database

Switch to your database:

USE my_database;

Step 3: Create Tables

  1. Create a users table:
    CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
  2. Create an orders table:
    CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(10, 2) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id) );

Step 4: Insert Sample Data

  1. Insert data into users:
    INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');
  2. Insert data into orders:
    INSERT INTO orders (user_id, order_date, amount) VALUES (1, '2025-01-01', 100.00), (2, '2025-01-02', 200.50);

Step 5: Query Your Data

  1. Join the tables to view data:
    SELECT users.name, orders.order_date, orders.amount FROM users JOIN orders ON users.user_id = orders.user_id;

Part 4: Administering MariaDB

Step 1: Back Up Your Database

Use mysqldump to back up your database:

mysqldump -u my_user -p my_database > my_database_backup.sql

Step 2: Restore from Backup

Restore your database from a backup file:

mysql -u my_user -p my_database < my_database_backup.sql

Step 3: Monitor MariaDB

Check MariaDB logs for errors:

sudo journalctl -u mariadb

Part 5: Cleaning Up

If needed, drop tables or databases:

  1. Drop a table:
    DROP TABLE orders;
  2. Drop a database:
    DROP DATABASE my_database;
Scroll to Top