MariaDB Tutorial for Developers

This MariaDB tutorial for developers covers what you actually need day to day: installing MariaDB, creating a database, defining tables, writing basic SQL, adding indexes and connecting from application code. Instead of drowning you in SQL theory, it focuses on concrete CLI and code examples you can reuse in real projects.

In this MariaDB tutorial you will:

• Understand how MariaDB fits into a typical web or data stack
• Install and connect with the MariaDB command-line client
• Create a sample schema, insert data and run queries
• Add indexes and use EXPLAIN to inspect query plans
• Connect to MariaDB from apps using PHP and Python
• See how MariaDB relates to SQL, Linux and analytics tools

For more background and related content, see the
SQL basics tutorial,
Linux basics for developers,
PHP backend basics tutorial,
Python data engineering tutorial,
and the
Adobe Commerce Magento 2 tutorial.

1. MariaDB tutorial for developers: where it fits

MariaDB is a drop-in replacement for MySQL in most scenarios. It powers classic LAMP stacks, e-commerce platforms, CMSs and analytics pipelines. In a typical architecture:

• Your app (PHP, Python, Node, Java) runs on Linux.
• A driver connects the app to MariaDB over TCP.
• MariaDB stores relational data in tables, indexes and transactions.

Because MariaDB speaks standard SQL, everything you learn in the SQL basics tutorial carries over here unchanged.

2. Install MariaDB and connect with the CLI

This section of the MariaDB tutorial for developers uses a simple Linux environment. Adjust package commands for your distro.

2.1 Install server and client

# Debian/Ubuntu example
sudo apt update
sudo apt install mariadb-server mariadb-client

# check version
mariadb --version

2.2 Start the service and log in

# ensure MariaDB is running
sudo systemctl status mariadb

# log in as root on localhost
sudo mariadb

On many dev machines, the root user can connect without a password when using sudo mariadb. In production, you will use least-privilege users instead.

3. Create a database and sample schema

Now this MariaDB tutorial for developers will create a tiny schema: one table for customers and one for orders. The examples are deliberately small so you can adapt them to your own projects.

3.1 Create database and user

From the MariaDB prompt:

CREATE DATABASE demo_shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'demo_user'@'localhost' IDENTIFIED BY 'demo_pass';
GRANT ALL PRIVILEGES ON demo_shop.* TO 'demo_user'@'localhost';
FLUSH PRIVILEGES;

Then switch to the new database:

USE demo_shop;

3.2 Create tables

CREATE TABLE customers (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_customers_email (email)
);

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  total_cents  INT UNSIGNED NOT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_customer_id (customer_id),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
      ON DELETE CASCADE
);

This gives you a very simple relational structure with a one-to-many relationship from customers to orders.

4. Insert sample data and query it

Now you will insert a few rows and write basic queries. This part of the MariaDB tutorial for developers mirrors what you would do when inspecting production data or building reports.

4.1 Insert sample rows

INSERT INTO customers (email, name)
VALUES
  ('alice@example.com', 'Alice'),
  ('bob@example.com', 'Bob');

INSERT INTO orders (customer_id, total_cents)
VALUES
  (1, 1999),
  (1, 4999),
  (2, 2999);

4.2 Simple SELECT queries

-- all customers
SELECT id, email, name
FROM customers;

-- orders for a specific customer
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 1
ORDER BY created_at DESC;

4.3 Join customers and orders

SELECT
  c.email,
  COUNT(o.id) AS order_count,
  SUM(o.total_cents) AS revenue_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email
ORDER BY revenue_cents DESC;

This query is a classic pattern you will use repeatedly in dashboards, reports and debugging sessions.

5. Indexes and EXPLAIN in MariaDB

Indexes are one of the most important topics in any MariaDB tutorial for developers. Good indexes keep queries fast as data grows; missing or incorrect indexes can make everything grind to a halt.

5.1 Check existing indexes

SHOW INDEX FROM customers;
SHOW INDEX FROM orders;

The customers table already has a primary key and a unique index on email. The orders table has an index on customer_id, which supports the join queries you wrote earlier.

5.2 Use EXPLAIN to see query plans

EXPLAIN
SELECT
  c.email,
  COUNT(o.id) AS order_count,
  SUM(o.total_cents) AS revenue_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;

Look at which indexes are used and how many rows MariaDB expects to scan. As your schema grows, checking EXPLAIN regularly helps keep performance predictable.

6. Connect to MariaDB from PHP and Python

The final part of this MariaDB tutorial for developers shows minimal code examples for connecting from PHP and Python. In real projects you would layer ORMs or abstraction on top, but the basics look like this.

6.1 PHP PDO example

<?php
$dsn = 'mysql:host=127.0.0.1;dbname=demo_shop;charset=utf8mb4';
$user = 'demo_user';
$pass = 'demo_pass';

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare(
    'SELECT email, name FROM customers WHERE email = :email'
);
$stmt->execute(['email' => 'alice@example.com']);
$customer = $stmt->fetch(PDO::FETCH_ASSOC);

var_dump($customer);

6.2 Python example with mysqlclient or mysql-connector

import mysql.connector  # works with MariaDB too

conn = mysql.connector.connect(
    host="127.0.0.1",
    user="demo_user",
    password="demo_pass",
    database="demo_shop",
)

cursor = conn.cursor(dictionary=True)
cursor.execute(
    "SELECT email, name FROM customers WHERE email = %s",
    ("bob@example.com",),
)
row = cursor.fetchone()
print(row)

cursor.close()
conn.close()

These small snippets are usually enough to wire MariaDB into a backend route, API or analytics job.

7. Tiny performance summary table

To round out this MariaDB tutorial for developers, here is a small “performance table” you might build while tuning queries. Imagine measuring query time before and after adding an index:

scenario,rows,has_index,avg_ms
no_index_10k,10000,false,120
with_index_10k,10000,true,8
with_index_100k,100000,true,11

Even with fake numbers, the pattern is clear: good indexes dramatically reduce query times, which is why they matter so much in any MariaDB tutorial for developers. Combined with solid SQL basics and Linux skills, you now have the core tools to use MariaDB effectively in web, analytics and e-commerce projects.