MariaDB

MariaDB is a drop-in replacement for MySQL, created by the original MySQL developers after Oracle acquired MySQL in 2009. It's the default relational database on CloudSonic servers and the right choice for WordPress, most PHP applications, and anything that would traditionally reach for MySQL.

Why MariaDB over MySQL

MariaDB has pulled ahead of MySQL in several meaningful ways. The query optimizer is noticeably better on complex joins. The Aria storage engine handles crash recovery faster than MyISAM ever did. InnoDB improvements landed in MariaDB before MySQL in many cases. For most web workloads you won't feel the difference day-to-day, but MariaDB is actively developed by a foundation rather than a corporation with competing interests, and the community is more open.

On CloudSonic servers, MariaDB is installed from the official MariaDB repository -- always the latest stable release, never the version that ships with Ubuntu which tends to be a few major versions behind.

How it's configured

The install script tunes MariaDB based on your server's RAM automatically:

Server RAM InnoDB Buffer Pool
16 GB 3 GB
8 GB 1 GB
4 GB 512 MB
Less than 4 GB 256 MB

The InnoDB buffer pool is the most important setting -- it's how much RAM MariaDB can use to cache table data and indexes. The bigger it is, the fewer reads hit the disk.

Other defaults worth knowing:

  • Bound to 127.0.0.1:3306 -- no external access
  • utf8mb4 character set and utf8mb4_unicode_ci collation by default (full Unicode + emoji)
  • Query cache disabled (it causes lock contention on busy servers -- use Redis for caching)
  • Slow query log enabled at 2 seconds -- check /var/log/mysql/slow.log if queries feel slow
  • Binary logging disabled (single-server setup)

Creating a database

The db-create command creates a database and a scoped user in one step:

# Auto-generates a username and password
db-create myapp

# Specify your own credentials
db-create myapp myapp_user Str0ngPassword123

The output shows you everything you need:

==> Database created!

    Database : myapp
    User     : myapp_user
    Password : xK9mP2qR8nL4vT6w
    Host     : 127.0.0.1
    Port     : 3306

    Credentials saved to: /etc/mariadb/databases/myapp.conf

    wp-config.php:
      define('DB_NAME',     'myapp');
      define('DB_USER',     'myapp_user');
      define('DB_PASSWORD', 'xK9mP2qR8nL4vT6w');
      define('DB_HOST',     '127.0.0.1');

Connecting to MariaDB

# Connect as root
mariadb -u root -p$(sudo cat /etc/mariadb/root.conf)

# Connect as a specific user
mariadb -u myapp_user -p myapp

# Run a single query without entering the shell
mariadb -u root -p$(sudo cat /etc/mariadb/root.conf) -e "SHOW DATABASES;"

Common database operations

List all databases

SHOW DATABASES;

Create a table

USE myapp;

CREATE TABLE orders (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    total       DECIMAL(10, 2) NOT NULL,
    status      ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert data

INSERT INTO orders (customer_id, total, status)
VALUES (1042, 89.95, 'pending');

-- Insert multiple rows at once
INSERT INTO orders (customer_id, total, status) VALUES
    (1043, 124.00, 'paid'),
    (1044, 59.50, 'pending'),
    (1045, 210.75, 'shipped');

Query data

-- All unpaid orders over $100
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'pending'
AND total > 100.00
ORDER BY created_at DESC;

-- Order count per customer
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value
FROM orders
WHERE status != 'cancelled'
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 10;

Update data

-- Mark an order as paid
UPDATE orders SET status = 'paid' WHERE id = 1;

-- Bulk update -- mark all orders older than 30 days as cancelled if still pending
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL 30 DAY;

Delete data

-- Delete a specific row
DELETE FROM orders WHERE id = 1;

-- Delete all cancelled orders older than 90 days
DELETE FROM orders
WHERE status = 'cancelled'
AND created_at < NOW() - INTERVAL 90 DAY;
Warning Always run a SELECT with the same WHERE clause before running DELETE or UPDATE on production data. It takes 10 seconds and has saved many people from an unpleasant afternoon.

Add an index

-- Add an index to speed up queries on a frequently-filtered column
ALTER TABLE orders ADD INDEX idx_created (created_at);

-- Check what indexes exist on a table
SHOW INDEX FROM orders;

Alter a table

-- Add a column
ALTER TABLE orders ADD COLUMN notes TEXT AFTER status;

-- Change a column type
ALTER TABLE orders MODIFY COLUMN total DECIMAL(12, 2) NOT NULL;

-- Rename a column (MariaDB 10.5+)
ALTER TABLE orders RENAME COLUMN notes TO customer_notes;

Importing and exporting

Export a database

# Export to a compressed file
mysqldump -u root -p$(sudo cat /etc/mariadb/root.conf) myapp | gzip > myapp-$(date +%Y%m%d).sql.gz

# Export a single table
mysqldump -u root -p$(sudo cat /etc/mariadb/root.conf) myapp orders > orders-backup.sql

Import a database

# Import from a plain SQL file
mariadb -u root -p$(sudo cat /etc/mariadb/root.conf) myapp < backup.sql

# Import from a compressed file
gunzip -c myapp-20250610.sql.gz | mariadb -u root -p$(sudo cat /etc/mariadb/root.conf) myapp
Note Large imports run faster if you disable foreign key checks for the session: SET FOREIGN_KEY_CHECKS=0; at the top of your SQL file, then SET FOREIGN_KEY_CHECKS=1; at the end.

Checking database size

-- Size of all databases
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

-- Size of tables in a specific database
SELECT
    table_name AS 'Table',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
    table_rows AS 'Rows (approx)'
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY data_length + index_length DESC;

Managing users

-- List all users
SELECT user, host FROM mysql.user;

-- Create a read-only user (useful for analytics tools)
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'ReadOnly123!';
GRANT SELECT ON myapp.* TO 'readonly'@'localhost';
FLUSH PRIVILEGES;

-- Revoke all privileges and drop a user
REVOKE ALL PRIVILEGES ON myapp.* FROM 'olduser'@'localhost';
DROP USER 'olduser'@'localhost';

-- Show what a user can do
SHOW GRANTS FOR 'myapp_user'@'localhost';

Dropping a database

DROP DATABASE myapp;
Warning This is immediate and irreversible. Make sure you have a backup before dropping any database.

Performance tips

Check the slow query log regularly. Queries that show up in /var/log/mysql/slow.log are the ones worth indexing.

# Show the 10 slowest queries from today
tail -n 1000 /var/log/mysql/slow.log | grep "Query_time" | sort -t: -k2 -rn | head -10

Use EXPLAIN to understand query plans:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1042 AND status = 'pending';

If you see type: ALL in the output, that query is doing a full table scan and needs an index.

InnoDB buffer pool hit rate -- if this drops below 99%, your buffer pool is too small:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) should be > 0.99