Mastering of MariaDB: MariaDB Administration

Prepare a Linux environment with VirtualBox and Rocky Linux, install MariaDB 10.6, disable swap to maximize RAM, access via SSH and PuTTY with port forwarding, and snapshot the VM.

Explore MariaDB, an open source relational database management system forked from MySQL, designed for speed, stability, and scalability. Understand how tables, keys, SQL, and ACID govern reliable data.

Design a MariaDB storage layout on Linux with three separate mount points for data, binlog, and backups on dedicated disks to boost performance and enable replication and point-in-time recovery.

Explore the architecture of MariaDB, including client authentication, query processing, and the pluggable storage engine model with InnoDB and binary log.

Learn to connect to MariaDB from the command line, locally or remotely, using proper syntax, handling non-default ports, and removing the anonymous user for security.

Learn to create a database in MariaDB, choosing character sets and collations, and verify defaults with show commands and information_schema.

Create and manage MariaDB users using create user, with username@host, password, authentication plugins, TLS, resource limits, password expiry, and lock; alter or drop users with a local host Tom example.

Learn to grant privileges in MariaDB at global, database, table, and column levels, using grant statements and revoke commands, with the grant option to delegate rights.

Create a role, grant privileges to it, and assign the role to a user in MariaDB, then manage current and default roles with set role and set default role.

Activate binary log to support replication and data recovery; configure mixed bin log format, set server ID, and max bin log size, and use expire log days.

Mastering MariaDB administration explains server system variables, including dynamic and static types and global versus session scopes, and shows how to view and configure them for reliable performance.

Configure server variables such as socket, pid file, and innodb_file_per_table; set lowercase table names and enable slow query logging. Use show status and show processlist to verify, then restart MariaDB.

Explore redo and undo logs and InnoDB buffer pool and flush settings in MariaDB to understand durability, performance, and crash recovery.

Explore benchmarking in MariaDB using Sysbench to evaluate performance under OLTP workloads, covering fair setup, reproducible tests, and interpreting transactions per second and latency.

Learn how to create or replace a table, use if not exists, and create table like, plus define primary and foreign keys, constraints, and various data types and table options.

Create indexes in MariaDB to optimize queries, including unique, full text, and spatial indexes, and learn how to choose B3, hash, or R3 types using explain.

Master how to create and manage MariaDB routines, including definer and invoker sql security, in/out parameters, and the difference between procedures and functions.

Create an event in MariaDB to schedule and automate recurring tasks inside the database, such as data backup and log cleanup, using on schedule and on completion options.

Discover how triggers in MariaDB automatically enforce rules, log changes, and cascade actions, with a before update trigger example that blocks salary changes beyond 10 percent.

Create views in MariaDB to present data from tables as virtual tables, simplifying complex queries and restricting access to selected columns or rows, illustrated by name and birthday from M2.

Explore the main MariaDB data types, including int, decimal, float, tinyint, varchar, char, text, date, datetime, timestamp and blob variants, with practical examples of primary key, autoincrement, and table definitions.

Master MariaDB string functions by focusing on the binary operator for binary case sensitive comparisons, bit length, cast, concat, substr, left, right, lower, upper, replace, and the position/locate functions.

Master date and time handling in MariaDB by building an employee table and using cur date, current date, now, current timestamp, and extract.

Explore numeric functions in MariaDB, including greatest and maximum values, smallest and minimum values, truncate to decimal places without rounding, and ceil and floor operations for precise decimal control.

Explore MariaDB's important operators and functions, including case, if, if null, nvl and nvl two, null if, and utilities like connection_id, current_user, current_role, database, schema, and version.

Learn how partitioning in MariaDB divides large tables into partitions by range, such as by year or date, to speed queries, simplify purging, optimize storage, and ease maintenance.

Learn to alter partitions in MariaDB by converting a partition to a table and a table to a partition, drop partitions, and remove partitioning while preserving data.

Reorganize partitions in MariaDB to split partitions into smaller ones, merge partitions, adjust range boundaries, update value lists, and rename partitions using alter table reorganize partition, without risking data loss.

Explore commits, rollbacks, and auto commit in MariaDB, start and end transactions, and how locking and the ACID properties—atomicity, consistency, isolation, durability—ensure data integrity.

Explore how MariaDB isolation levels control transaction visibility to prevent dirty reads, non-repeatable reads, and phantom reads, covering read uncommitted, read committed, repeatable read, and serializable.

Configure MariaDB auditing with the built-in audit plugin, enabling events like connect, query, and table, setting a log path and rotation, and preventing uninstallation to monitor database activity.

Explore how information schema in MariaDB provides metadata on schema, objects, permissions, and configurations to support database management, auditing, and documentation, and is safe to use for ongoing database monitoring.

Mastering MariaDB performance schema guides you to monitor and analyze runtime statistics, from sql statement execution to memory and file I/O, enabling targeted performance tuning and troubleshooting.

Explore how to monitor a Mariadb server using dashboards that show CPU, RAM, hit rates, connections and threads, and compare free and paid options with Prometheus, Grafana, and other tools.