MASTER YOUR
DATABASE

// The world's most advanced open source database.

MARIADB IS THE FUTURE OF OPEN SOURCE DATABASES.

Created by the original MySQL developers, MariaDB is a drop-in replacement for MySQL with more features, better performance, and true open source governance. It's used by millions of organizations worldwide.

WHY MARIADB?

MariaDB offers superior performance through advanced storage engines, horizontal scaling with MariaDB ColumnStore, and the Galera Cluster for high availability. It's fully open source with no vendor lock-in.

TAKE CONTROL OF YOUR DATA.

Learn to design efficient databases, write optimized queries, implement replication, and master the tools that power the modern web.

BEGIN YOUR JOURNEY →

// The Path to Mastery

12 lessons. Complete MariaDB control.

LESSON 01

Introduction to MariaDB

Learn SQL database fundamentals

Beginner
LESSON 02

Installing MariaDB

Set up and configure your database server

Beginner
LESSON 03

Basic SQL Queries

SELECT, INSERT, UPDATE, DELETE operations

Beginner
LESSON 04

Table Design & Data Types

Create tables with proper data types

Beginner
LESSON 05

Joins & Relationships

Connect tables with INNER, LEFT, RIGHT joins

Intermediate
LESSON 06

Aggregations & Grouping

Use GROUP BY, COUNT, SUM, AVG functions

Intermediate
LESSON 07

Indexes & Query Optimization

Speed up queries with proper indexing

Intermediate
LESSON 08

Users & Permissions

Manage database access and security

Intermediate
LESSON 09

Backups & Restoration

Export and import data safely.

Intermediate
LESSON 10

Replication

Set up master-slave replication for high availability.

Advanced
LESSON 11

Stored Procedures & Functions

Write reusable database routines.

Advanced
LESSON 12

Troubleshooting Common Issues

Fix performance and connection problems.

Advanced

// Why MariaDB

MariaDB Server is one of the most popular open source relational databases. It's built by the original creators of MySQL, ensuring compatibility while adding innovative features.

MariaDB's plugin architecture allows you to customize functionality with storage engines like InnoDB, Aria, ColumnStore, and more. Whether you need transaction safety, full-text search, or big data analytics, MariaDB has you covered.

The MariaDB Foundation ensures the database remains open source forever. No single company controls its direction—it's community-driven development at its best.

The data layer of the modern web runs on MariaDB. Now you can too.

// Tools & References

📖 Official Docs

MariaDB Knowledge Base

mariadb.com/kb

🔧 MariaDB Client

Command-line client tools

mariadb client

🗄️ Storage Engines

InnoDB, Aria, ColumnStore

engines

📊 SQL Reference

Complete SQL syntax

sql reference

🔄 Replication

Master-slave & Galera

replication

⚡ Optimization

Query optimization tips

optimization

// Introduction to MariaDB

×

What is MariaDB?

MariaDB is an open-source relational database management system (RDBMS). It was created by the original developers of MySQL in 2009 after MySQL was acquired by Oracle. MariaDB is designed as a "drop-in replacement" for MySQL, meaning most applications work without modification.

Why MariaDB?

  • Truly Open Source: GPL license, no proprietary components
  • Superior Performance: Faster than MySQL in most benchmarks
  • More Features: Additional storage engines and functions
  • Active Development: New releases every year
  • Community Driven: Governed by MariaDB Foundation
MARKET POSITION: MariaDB powers applications at Wikipedia, Google, CentOS, Red Hat, and thousands of other organizations. It's one of the top 3 open-source databases.

MariaDB vs MySQL

  • MariaDB has more storage engines (Aria, ColumnStore, etc.)
  • MariaDB has more optimizations and faster queries
  • MariaDB has additional SQL features
  • MariaDB is fully open source; MySQL has enterprise (closed) options
  • MariaDB maintains API/protocol compatibility with MySQL

Checking MariaDB Version

$ mariadb --version mariadb Ver 15.1 Distrib 10.11.4-MariaDB, for debian-linux-gnu (x86_64)
$ mysql --version mysql Ver 15.1 Distrib 10.11.4-MariaDB, for debian-linux-gnu (x86_64)

Connecting to MariaDB

$ sudo mariadb Welcome to the MariaDB monitor. Commands end with ; or \\g. Your MariaDB connection id is 52 Server version: 10.11.4-MariaDB Ubuntu 22.04 MariaDB [(none)]>

MariaDB File Structure (Ubuntu)

  • /etc/mysql/ - Main configuration directory
  • /var/lib/mysql/ - Data directory
  • /var/log/mysql/ - Log files
  • /usr/bin/ - Client binaries (mariadb, mysqld)

Quiz

1. MariaDB was created by the original _____ developers.

Hint: Original MySQL team

2. MariaDB is designed as a drop-in _____ for MySQL.

Hint: Compatible replacement

3. MariaDB is governed by the _____ Foundation.

Hint: Non-profit organization

4. The default data directory is _____.

Hint: Under /var/lib

5. MariaDB uses the _____ license.

Hint: Open source license

6. To connect as root, use _____ mariadb.

Hint: Superuser access

7. The MariaDB monitor uses _____ to end commands.

Hint: Semicolon

8. MariaDB has more _____ than MySQL.

Hint: Engine types

Show Answers

Answers

  1. MySQL
  2. replacement
  3. MariaDB
  4. /var/lib/mysql
  5. GPL
  6. sudo
  7. ;
  8. storage engines

// Installing MariaDB

×

Installation on Ubuntu/Debian

Installing MariaDB on Debian-based systems:

$ sudo apt update Hit:1 http://archive.ubuntu.com jammy InRelease Reading package lists... Done
$ sudo apt install mariadb-server mariadb-client Reading package lists... Done The following NEW packages will be installed: mariadb-server mariadb-client mariadb-server-core Do you want to continue? [Y/n] y

Installation on CentOS/RHEL/Fedora

$ sudo dnf install mariadb-server Last metadata expiration check: 0:00:01 ago Package mariadb-server-10.11.4-1.el9.x86_64 is already installed.

Starting and Enabling MariaDB

$ sudo systemctl start mariadb # Start MariaDB
$ sudo systemctl enable mariadb # Enable on boot
$ sudo systemctl status mariadb ● mariadb.service - MariaDB 10.11.4 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled) Active: active (running)

Secure Installation

Run the security script:

$ sudo mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED! It will take you through a series of options to: - Remove anonymous users - Disallow root login remotely - Remove test database - Reload privilege tables

Interactive Setup

Enter current password for root (enter for none): Press Enter Switch to unix_socket authentication [Y/n]: n Change the root password? [Y/n]: Y Remove anonymous users? [Y/n]: Y Disallow root login remotely? [Y/n]: Y Remove test database and access to it? [Y/n]: Y Reload privilege tables now? [Y/n]: Y

Testing Your Installation

$ sudo mariadb Welcome to the MariaDB monitor... MariaDB [(none)]> SELECT VERSION(); MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+

Useful Packages

  • mariadb-server - Database server
  • mariadb-client - Command-line client
  • mariadb-backup - Backup tools
  • mariadb-tools - Utility programs

Quiz

1. On Ubuntu, install with _____ install mariadb-server.

Hint: Package manager

2. To start MariaDB, use _____.

Hint: Systemd

3. To enable on boot, use _____ enable mariadb.

Hint: Systemd

4. The security script is _____.

Hint: Secures the installation

5. The test database should be _____.

Hint: Deleted for security

6. The default data directory is _____.

Hint: Database files

7. To connect locally as root, use _____ mariadb.

Hint: Superuser

8. SELECT _____ shows the MariaDB version.

Hint: Function

Show Answers

Answers

  1. apt
  2. systemctl start mariadb
  3. systemctl
  4. mysql_secure_installation
  5. removed
  6. /var/lib/mysql
  7. sudo
  8. VERSION()

// Basic SQL Queries

×

Introduction to SQL

SQL (Structured Query Language) is the standard language for interacting with relational databases. MariaDB supports the full SQL standard plus extensions.

Creating a Database

CREATE DATABASE myapp; USE myapp;

Creating Tables

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

INSERT - Adding Data

INSERT INTO users (username, email, password) VALUES ('alice', 'alice@example.com', 'hashed_password');
INSERT INTO users (username, email, password) VALUES ('bob', 'bob@example.com', 'hash1'), ('charlie', 'charlie@example.com', 'hash2');

SELECT - Retrieving Data

SELECT * FROM users;
SELECT username, email FROM users;
SELECT * FROM users WHERE id = 1;

UPDATE - Modifying Data

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

DELETE - Removing Data

DELETE FROM users WHERE id = 1;
WARNING: Always use WHERE in UPDATE and DELETE statements. Without it, you'll modify or delete ALL rows!

Filtering with WHERE

SELECT * FROM users WHERE username = 'alice'; SELECT * FROM users WHERE id > 5; SELECT * FROM users WHERE email LIKE '%@example.com';

Sorting Results

SELECT * FROM users ORDER BY created_at DESC; SELECT * FROM users ORDER BY username ASC;

Limiting Results

SELECT * FROM users LIMIT 10; SELECT * FROM users LIMIT 5, 10;

Quiz

1. To create a database, use _____ DATABASE.

Hint: SQL command

2. To add data, use _____.

Hint: INSERT INTO

3. To retrieve data, use _____.

Hint: SELECT * FROM

4. To modify data, use _____.

Hint: UPDATE table

5. To remove data, use _____.

Hint: DELETE FROM

6. The _____ clause filters results.

Hint: Conditional

7. To sort results, use _____ BY.

Hint: ORDER BY

8. To limit results, use _____.

Hint: LIMIT 10

Show Answers

Answers

  1. CREATE
  2. INSERT
  3. SELECT
  4. UPDATE
  5. DELETE
  6. WHERE
  7. ORDER
  8. LIMIT

// Table Design & Data Types

×

Choosing Data Types

Choosing the right data type is crucial for performance and storage efficiency. MariaDB offers a wide variety of types.

Numeric Types

  • TINYINT: -128 to 127 (or 0 to 255 unsigned)
  • SMALLINT: -32,768 to 32,767
  • INT: -2.1B to 2.1B (most common)
  • BIGINT: Huge numbers
  • DECIMAL: Exact precision (for money)
  • FLOAT/DOUBLE: Approximate numbers

String Types

  • CHAR(n): Fixed-length string (0-255)
  • VARCHAR(n): Variable-length (0-65,535)
  • TEXT: Large text (65KB)
  • MEDIUMTEXT: 16MB
  • LONGTEXT: 4GB
  • ENUM: One of predefined values
  • SET: Multiple predefined values

Date/Time Types

  • DATE: 'YYYY-MM-DD'
  • TIME: 'HH:MM:SS'
  • DATETIME: 'YYYY-MM-DD HH:MM:SS'
  • TIMESTAMP: Unix timestamp (auto-updates)
  • YEAR: Year (4 digits)

Creating a Well-Designed Table

CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(200) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, category_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX (category_id), INDEX (sku) );

Column Attributes

  • NOT NULL: Column must have a value
  • DEFAULT: Default value if none provided
  • AUTO_INCREMENT: Auto-generate sequential numbers
  • UNIQUE: No duplicate values allowed
  • PRIMARY KEY: Unique identifier (NOT NULL + UNIQUE)
  • COMMENT: Description for documentation

Altering Tables

-- Add a column ALTER TABLE products ADD COLUMN image_url VARCHAR(255); -- Modify a column ALTER TABLE products MODIFY COLUMN name VARCHAR(300); -- Drop a column ALTER TABLE products DROP COLUMN image_url; -- Add an index ALTER TABLE products ADD INDEX (price);

Keys and Indexes

-- Primary key PRIMARY KEY (id), -- Foreign key FOREIGN KEY (category_id) REFERENCES categories(id), -- Index for faster queries INDEX idx_username (username), -- Composite index INDEX idx_name_price (name, price)

Table Engines

CREATE TABLE mytable (...) ENGINE = InnoDB; CREATE TABLE mytable (...) ENGINE = Aria;

Quiz

1. For exact money values, use _____.

Hint: Exact precision

2. VARCHAR is for _____ length strings.

Hint: Not fixed

3. TIMESTAMP automatically updates on _____.

Hint: Row changes

4. NOT NULL means the column cannot be _____.

Hint: Must have value

5. AUTO_INCREMENT generates _____ numbers.

Hint: Auto numbers

6. PRIMARY KEY combines NOT NULL and _____.

Hint: No duplicates

7. To add a column, use _____ TABLE.

Hint: ALTER TABLE

8. The default engine is _____.

Hint: Storage engine

Show Answers

Answers

  1. DECIMAL
  2. variable
  3. modification
  4. empty
  5. sequential
  6. UNIQUE
  7. ALTER
  8. InnoDB

// Joins & Relationships

×

Understanding Relationships

Relational databases derive their power from relationships between tables. MariaDB supports several types of table relationships.

Types of Relationships

  • One-to-One: Each row in Table A matches one row in Table B
  • One-to-Many: Each row in Table A matches multiple rows in Table B
  • Many-to-Many: Multiple rows in Table A match multiple rows in Table B

Setting Up Related Tables

-- Categories table (parent) CREATE TABLE categories ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL ); -- Products table (child - one-to-many) CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, price DECIMAL(10,2), category_id INT, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL );

INNER JOIN

Returns only matching rows from both tables:

SELECT p.name, p.price, c.name AS category FROM products p INNER JOIN categories c ON p.category_id = c.id;

LEFT JOIN

Returns all rows from left table, matched rows from right:

SELECT p.name, c.name AS category FROM products p LEFT JOIN categories c ON p.category_id = c.id;

RIGHT JOIN

Returns all rows from right table, matched from left:

SELECT p.name, c.name AS category FROM products p RIGHT JOIN categories c ON p.category_id = c.id;

Multiple Joins

SELECT o.id, u.username, p.name, oi.quantity FROM orders o JOIN users u ON o.user_id = u.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id;

Self Join

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), manager_id INT, FOREIGN KEY (manager_id) REFERENCES employees(id) ); SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Many-to-Many with Junction Table

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE courses ( id INT PRIMARY KEY, title VARCHAR(200) ); CREATE TABLE enrollments ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id) );

Quiz

1. INNER JOIN returns only _____ rows.

Hint: Both tables

2. LEFT JOIN returns all rows from the _____ table.

Hint: First table

3. A junction table implements _____ relationships.

Hint: Bridge table

4. FOREIGN KEY creates a _____ relationship.

Hint: Referential

5. ON DELETE SET NULL keeps the row but sets key to _____.

Hint: Empty

6. A self-join references the same _____ twice.

Hint: Table name

7. Multiple JOINs can connect _____ tables.

Hint: More than 2

8. Composite primary key on junction table uses both _____.

Hint: Both columns

Show Answers

Answers

  1. matching
  2. left
  3. many-to-many
  4. parent-child
  5. NULL
  6. table
  7. multiple
  8. foreign keys

// Aggregations & Grouping

×

Aggregate Functions

Aggregate functions perform calculations on sets of rows and return a single result.

Common Aggregate Functions

  • COUNT(): Count rows
  • SUM(): Add values
  • AVG(): Calculate average
  • MIN(): Find minimum
  • MAX(): Find maximum

Basic Aggregates

SELECT COUNT(*) AS total_products, AVG(price) AS avg_price, MIN(price) AS min_price, MAX(price) AS max_price, SUM(stock) AS total_stock FROM products;

GROUP BY

Group rows by a column:

SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id;

HAVING

Filter groups (WHERE doesn't work on aggregates):

SELECT category_id, COUNT(*) AS count FROM products GROUP BY category_id HAVING count > 5;

Complete Example

SELECT c.name AS category, COUNT(p.id) AS products, AVG(p.price) AS avg_price, SUM(p.stock) AS total_stock FROM categories c LEFT JOIN products p ON c.id = p.category_id GROUP BY c.id, c.name HAVING products > 0 ORDER BY products DESC;

DISTINCT

Remove duplicates:

SELECT DISTINCT category_id FROM products; SELECT COUNT(DISTINCT category_id) FROM products;

GROUP_CONCAT

Concatenate values within groups:

SELECT category_id, GROUP_CONCAT(name ORDER BY name) AS products FROM products GROUP BY category_id;

ROLLUP

Add subtotals and grand total:

SELECT category_id, SUM(stock) AS total FROM products GROUP BY category_id WITH ROLLUP;

Quiz

1. COUNT counts _____.

Hint: Number of rows

2. AVG calculates the _____.

Hint: Mean value

3. GROUP BY groups rows by a _____.

Hint: Column value

4. _____ filters groups, not rows.

Hint: Like WHERE

5. DISTINCT removes _____.

Hint: Unique values

6. GROUP_CONCAT concatenates values in a _____.

Hint: Grouped rows

7. WITH ROLLUP adds _____ totals.

Hint: Subtotals

8. SUM adds all _____ in a group.

Hint: Column values

Show Answers

Answers

  1. rows
  2. average
  3. column
  4. HAVING
  5. duplicates
  6. group
  7. grand
  8. values

// Indexes & Query Optimization

×

Understanding Indexes

Indexes are data structures that speed up data retrieval. Think of them like a book index - they help you find information faster.

How Indexes Work

Without index: Full table scan (reads every row)

With index: Direct lookup (reads only matching rows)

Creating Indexes

-- Single column index CREATE INDEX idx_username ON users(username); -- Unique index CREATE UNIQUE INDEX idx_email ON users(email); -- Composite index CREATE INDEX idx_name_status ON users(name, status); -- Full-text index CREATE FULLTEXT INDEX idx_content ON articles(content); -- Index on CREATE TABLE CREATE TABLE products ( ... INDEX idx_price (price), INDEX idx_cat_price (category_id, price) );

Viewing Indexes

SHOW INDEX FROM users;

Dropping Indexes

DROP INDEX idx_username ON users;

EXPLAIN - Analyzing Queries

EXPLAIN SELECT * FROM users WHERE username = 'alice';
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | users | ref | idx_username | idx_username | 152 | const | 1 | NULL | +------+-------------+-------+------+---------------+------+---------+-------+------+-------+

Key EXPLAIN Fields

  • type: How MariaDB accesses the table (const, eq_ref, ref, range, all)
  • key: Which index is used
  • rows: Estimated rows to examine
  • Extra: Additional information

Index Best Practices

  • Index columns used in WHERE, JOIN, ORDER BY
  • Use composite indexes for multi-column queries
  • Order matters in composite indexes (left-to-right)
  • Don't over-index (slows writes)
  • Use EXPLAIN to verify index usage

Composite Index Order

For WHERE status = 'active' AND created > '2024-01-01':

INDEX idx_status_created (status, created_at)

For WHERE status = 'active' only:

INDEX idx_status (status)

Quiz

1. Indexes speed up data _____.

Hint: Reading data

2. CREATE _____ creates an index.

Hint: SQL command

3. EXPLAIN shows how a query is _____.

Hint: Query plan

4. type: _____ means index lookup.

Hint: Using index

5. Composite indexes use columns in _____.

Hint: Left to right

6. Too many indexes slow down _____.

Hint: INSERT, UPDATE

7. FULLTEXT index is for _____ search.

Hint: Full-text

8. DROP _____ removes an index.

Hint: SQL command

Show Answers

Answers

  1. retrieval
  2. INDEX
  3. executed
  4. ref
  5. order
  6. writes
  7. text
  8. INDEX

// Users & Permissions

×

MariaDB User Management

Proper user management is crucial for database security. Grant only the minimum privileges needed.

Creating Users

-- Create a user CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong_password'; -- Create user with specific host CREATE USER 'admin'@'192.168.1.%' IDENTIFIED BY 'password'; -- Create user that can connect from anywhere CREATE USER 'remote'@'%' IDENTIFIED BY 'password';

Granting Privileges

-- Grant all privileges to a database GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost'; -- Grant specific privileges GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'appuser'@'localhost'; -- Grant read-only access GRANT SELECT ON myapp.* TO 'reader'@'%';

Common Privileges

  • ALL PRIVILEGES: All except grant
  • SELECT: Read data
  • INSERT: Add data
  • UPDATE: Modify data
  • DELETE: Remove data
  • CREATE: Create tables/databases
  • DROP: Delete tables/databases
  • ALTER: Modify tables

Revoking Privileges

REVOKE DELETE ON myapp.* FROM 'appuser'@'localhost';

Viewing User Privileges

SHOW GRANTS FOR 'appuser'@'localhost';

Dropping Users

DROP USER 'olduser'@'localhost';

Require SSL

ALTER USER 'appuser'@'localhost' REQUIRE SSL;

Password Management

-- Change password SET PASSWORD FOR 'appuser'@'localhost' = PASSWORD('new_password'); -- Lock user account ALTER USER 'suspended'@'localhost' ACCOUNT LOCK; -- Expire password ALTER USER 'appuser'@'localhost' PASSWORD EXPIRE;

Apply Changes

FLUSH PRIVILEGES;

Quiz

1. CREATE _____ creates a new user.

Hint: SQL command

2. GRANT _____ gives privileges.

Hint: Permissions

3. localhost means only _____ connections.

Hint: Same machine

4. To remove privileges, use _____.

Hint: Take away

5. SHOW _____ shows user privileges.

Hint: Permissions

6. REQUIRE _____ forces encrypted connections.

Hint: TLS

7. FLUSH _____ applies privilege changes.

Hint: Reload

8. DROP _____ removes a user.

Hint: Delete user

Show Answers

Answers

  1. USER
  2. privileges
  3. local
  4. REVOKE
  5. GRANTS
  6. SSL
  7. PRIVILEGES
  8. USER

// Backups & Restoration

×

Backup Strategies

Regular backups are essential. Test your backups regularly!

mysqldump - Logical Backup

Creates SQL statements to recreate the database:

$ mysqldump -u root -p myapp > backup.sql # Backup single database
$ mysqldump -u root -p --all-databases > all_databases.sql # Backup all databases
$ mysqldump -u root -p myapp users orders > partial_backup.sql # Specific tables

mysqldump Options

$ mysqldump -u root -p --single-transaction --quick myapp > backup.sql # For InnoDB (consistent backup)
$ mysqldump -u root -p --routines --triggers myapp > backup.sql # Include routines and triggers
$ mysqldump -u root -p --databases db1 db2 db3 > backup.sql # Multiple databases

Restoring from Backup

$ mysql -u root -p myapp < backup.sql # Restore database
$ mysql -u root -p < all_databases.sql # Restore all databases

mariadb-backup - Physical Backup

Hot backup tool (requires MariaDB 10.1.7+):

$ mariabackup --backup --target-dir=/backup/full --user=root --password=pass # Full backup
$ mariabackup --prepare --target-dir=/backup/full # Prepare backup
$ mariabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql # Restore

Point-in-Time Recovery

$ mysqlbinlog mysql-bin.000001 | mysql -u root -p # Restore from binary log

Automated Backups with Cron

$ crontab -e
0 2 * * * mysqldump -u root -p'secret' myapp > /backups/myapp_$(date +\%Y\%m\%d).sql

Backup Best Practices

  • Backup regularly (daily minimum)
  • Store backups off-site
  • Test restoration regularly
  • Compress large backups
  • Use --single-transaction for InnoDB
  • Enable binary logging for point-in-time recovery

Quiz

1. _____ creates logical SQL backups.

Hint: Backup tool

2. To restore, use _____ < backup.sql.

Hint: Restore command

3. --single-transaction is for _____ tables.

Hint: Transactional

4. mariabackup is a _____ backup tool.

Hint: Hot backup

5. Binary logs enable point-in-_____ recovery.

Hint: PITR

6. Store backups _____ the server.

Hint: Remote location

7. Test _____ regularly.

Hint: Backup testing

8. Cron is used for _____ backups.

Hint: Scheduled

Show Answers

Answers

  1. mysqldump
  2. mysql
  3. InnoDB
  4. physical
  5. time
  6. off-site
  7. restoration
  8. automated

// Replication

×

Database Replication

Replication copies data from one database to another for scalability, high availability, or backup purposes.

Replication Types

  • Master-Slave: One master, multiple slaves
  • Master-Master: Bidirectional replication
  • Galera Cluster: Multi-master synchronous

Master Configuration

-- Enable binary logging SET GLOBAL log_bin = 'mariadb-bin'; SET GLOBAL binlog_format = 'ROW'; SET GLOBAL server_id = 1;

Create Replication User

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Get Master Status

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mariadb-bin.000001| 1234 | myapp | | +------------------+----------+--------------+------------------+

Slave Configuration

SET GLOBAL server_id = 2; SET GLOBAL relay_log = 'mariadb-relay-bin'; SET GLOBAL read_only = ON;

Start Replication

CHANGE MASTER TO MASTER_HOST='master.example.com', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=1234; START SLAVE;

Check Replication Status

SHOW SLAVE STATUS\G

Galera Cluster Setup

-- Install Galera apt install mariadb-server mariadb-client galera
[mysqld] bind-address=0.0.0.0 default-storage-engine=innodb binlog_format=ROW innodb_autoinc_lock_mode=2 wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="my_cluster" wsrep_cluster_address="gcomm://node1,node2,node3" wsrep_node_name="node1" wsrep_node_address="192.168.1.1"

Galera Commands

$ mysqld --wsrep-new-cluster # Start new cluster
$ mysql -e "SHOW STATUS LIKE 'wsrep_%';" # Check cluster status

Quiz

1. _____ replication has one master and multiple slaves.

Hint: Traditional

2. Galera provides _____ replication.

Hint: Multi-master

3. Binary logging is required on the _____.

Hint: Primary server

4. CREATE _____ creates replication user.

Hint: Account

5. SHOW _____ STATUS shows replication state.

Hint: On slave

6. Galera uses the _____ storage engine.

Hint: Required

7. wsrep_provider provides _____ cluster support.

Hint: Cluster

8. SET GLOBAL read_only = _____ on slave.

Hint: Read-only

Show Answers

Answers

  1. master-slave
  2. synchronous
  3. master
  4. USER
  5. SLAVE
  6. InnoDB
  7. Galera
  8. ON

// Stored Procedures & Functions

×

Stored Routines

Stored routines are SQL code saved in the database that can be called later. They improve performance and encapsulation.

Stored Procedures

DELIMITER // CREATE PROCEDURE get_user_by_id(IN user_id INT) BEGIN SELECT * FROM users WHERE id = user_id; END // DELIMITER ;

Calling Procedures

CALL get_user_by_id(1);

Procedures with OUT Parameters

DELIMITER // CREATE PROCEDURE count_users(OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM users; END // DELIMITER ;

Stored Functions

DELIMITER // CREATE FUNCTION get_user_count() RETURNS INT DETERMINISTIC BEGIN DECLARE cnt INT; SELECT COUNT(*) INTO cnt FROM users; RETURN cnt; END // DELIMITER ;

Using Functions

SELECT get_user_count() AS total_users; SELECT name, get_user_count() AS all_users FROM products;

Variables

DECLARE my_var VARCHAR(100); SET my_var = 'hello'; SELECT column INTO my_var FROM table;

Conditionals

IF condition THEN -- code ELSEIF condition THEN -- code ELSE -- code END IF;

Loops

WHILE i DO < 10 -- code SET i = i + 1; END WHILE;

Dropping Routines

DROP PROCEDURE IF EXISTS get_user_by_id; DROP FUNCTION IF EXISTS get_user_count;

Listing Routines

SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS;

Quiz

1. Stored procedures are created with _____ PROCEDURE.

Hint: SQL command

2. Functions must _____ a value.

Hint: Output

3. IN parameters pass _____ to the procedure.

Hint: Input

4. OUT parameters return _____.

Hint: Output

5. DECLARE creates a _____.

Hint: Local variable

6. DELIMITER changes the _____.

Hint: Statement end

7. DROP _____ removes a procedure.

Hint: SQL command

8. Functions can be used in _____ statements.

Hint: SQL

Show Answers

Answers

  1. CREATE
  2. RETURN
  3. values
  4. values
  5. variable
  6. separator
  7. PROCEDURE
  8. SELECT

// Troubleshooting Common Issues

×

MariaDB Troubleshooting

When database issues arise, systematic diagnosis is key.

Check MariaDB Status

$ sudo systemctl status mariadb ● mariadb.service - MariaDB 10.11.4 database server Active: active (running)

Common Error Messages

"Too many connections"

Too many clients connected:

SHOW GLOBAL VARIABLES LIKE 'max_connections'; SET GLOBAL max_connections = 200;

"Table is full"

Check partition or filesystem limits:

SHOW CREATE TABLE mytable;

"Lock wait timeout"

Check for long-running transactions:

SHOW PROCESSLIST;

"Cannot connect"

Check if service is running:

$ sudo systemctl restart mariadb

Diagnosing Slow Queries

-- Enable slow query log SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2;
$ tail -f /var/log/mysql/slow-query.log

Analyze Query Performance

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com'; EXPLAIN FORMAT=JSON SELECT * FROM users;

Check Table Size

SELECT table_name, ROUND(data_length/1024/1024, 2) AS 'MB', ROUND(index_length/1024/1024, 2) AS 'Index MB' FROM information_schema.tables WHERE table_schema = 'myapp' ORDER BY data_length DESC;

Repair Corrupted Tables

$ mysqlcheck -u root -p --repair myapp mytable

Check Database Size

SELECT ROUND(SUM(data_length + index_length)/1024/1024, 2) AS 'Total MB' FROM information_schema.tables WHERE table_schema = 'myapp';

Kill Long-Running Queries

SHOW PROCESSLIST; KILL 123;

Reset Root Password

$ sudo systemctl stop mariadb $ sudo mysqld_safe --skip-grant-tables --skip-networking & $ mysql -u root
FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Quick Troubleshooting Steps

  1. Check status: systemctl status mariadb
  2. Check logs: tail -f /var/log/mysql/error.log
  3. Check processes: SHOW PROCESSLIST;
  4. Run EXPLAIN on slow queries
  5. Check disk space
  6. Restart service if needed

Quiz

1. Check service status with _____.

Hint: Systemd

2. Too many connections means increase _____ connections.

Hint: Limit

3. EXPLAIN shows query _____.

Hint: How it runs

4. Enable slow query _____.

Hint: Logging

5. KILL stops a running _____.

Hint: Process

6. Repair corrupted tables with _____ table.

Hint: mysqlcheck

7. Check table size in _____ schema.

Hint: Metadata

8. --skip-grant-tables resets the _____.

Hint: Root password

Show Answers

Answers

  1. systemctl status mariadb
  2. max
  3. execution plan
  4. log
  5. query
  6. REPAIR
  7. information_schema
  8. password