MariaDB Cheat Sheet (Basics to Advanced)

MariaDB is a powerful, open-source relational database management system (RDBMS) widely used for web applications, analytics, and more. This cheat sheet is designed to help you quickly reference important MariaDB commands, SQL injection techniques, and relevant Markdown formatting tips for documentation. This guide integrates essential topics for beginners and advanced users alike.

Introduction to MariaDB

MariaDB is a fork of MySQL designed to provide improved performance, scalability, and enhanced features. It uses SQL as its query language and is compatible with most MySQL tools and frameworks. MariaDB is available for Windows, Linux, and macOS.

Key Features:

• Open-source and community-driven.
• Improved security features.
• Support for JSON and dynamic columns.
• Advanced clustering with Galera Cluster.

Setting Up MariaDB

Installation

Linux

sudo apt update
sudo apt install mariadb-server
sudo systemctl start mariadb
sudo systemctl enable mariadb

Windows

1. Download the installer from the MariaDB website.
2. Run the installer and follow the setup wizard.

macOS

brew install mariadb
brew services start mariadb

Initial Configuration

Secure your MariaDB installation:

sudo mysql_secure_installation

• Set a root password.
• Remove anonymous users.
• Disable remote root login.
• Remove test databases.

MariaDB Commands Cheat Sheet

Connecting to MariaDB

mysql -u <username> -p

Show Available Databases

SHOW DATABASES;

Create a New Database

CREATE DATABASE database_name;

Select a Database

USE database_name;

Create a Table

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

Insert Data

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

Retrieve Data

SELECT * FROM table_name;

Update Data

UPDATE table_name SET column1 = 'value' WHERE condition;

Delete Data

DELETE FROM table_name WHERE condition;

Working with Databases and Tables

Backup a Database

mysqldump -u <username> -p database_name > backup.sql

Restore a Database

mysql -u <username> -p database_name < backup.sql

Drop a Database

DROP DATABASE database_name;

Alter Table

Add a Column

ALTER TABLE table_name ADD column_name datatype;

Remove a Column

ALTER TABLE table_name DROP column_name;

Data Manipulation Commands

Aggregate Functions

SELECT COUNT(column_name), SUM(column_name), AVG(column_name)
FROM table_name;

Joins

Inner Join

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Left Join

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

Right Join

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

User Management in MariaDB

Create a New User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Show Privileges

SHOW GRANTS FOR 'username'@'host';

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

Security Best Practices

• Regularly update MariaDB to the latest version.
• Use strong, unique passwords for database users.
• Restrict access to the MariaDB server.
• Implement firewalls and IP-based restrictions.
• Monitor logs for suspicious activity.

MariaDB SQL Injection Cheat Sheet

SQL injection is a security vulnerability that allows attackers to interfere with queries. Here’s a cheat sheet to understand and prevent SQL injection in MariaDB.

Common SQL Injection Payloads

Retrieve All Data

' OR '1'='1

Bypass Authentication

' OR '1'='1' --

Extract Database Names

UNION SELECT schema_name FROM information_schema.schemata;

Prevention Techniques

Use parameterized queries:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->bindParam(':username', $username);

• Validate and sanitize user input.
• Limit database user permissions.
• Regularly audit your database and application code.

Advanced MariaDB Features

JSON Support

SELECT JSON_EXTRACT(json_column, '$.key') FROM table_name;

Dynamic Columns

ALTER TABLE table_name ADD COLUMN dynamic_column BLOB;

Stored Procedures

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements
END //
DELIMITER ;

Using Markdown to Document MariaDB Workflows

Markdown is a lightweight markup language for creating formatted text. Documenting your MariaDB workflows in Markdown ensures clarity and ease of sharing.

Markdown Language Cheat Sheet

# Heading 1
## Heading 2
**Bold** and *Italic*
`Inline code`

Markdown Formatting Cheat Sheet

• Bold: **text**
• Italic: *text*
• Inline Code: `code`
• Block Code:

 SELECT * FROM table_name;

Markdown Editor Cheat Sheet

• Visual Studio Code: Install the Markdown All in One extension.
• Typora: A WYSIWYG Markdown editor.
• Obsidian: Great for note-taking and linking.

Conclusion

This comprehensive MariaDB cheat sheet covered essential commands, SQL injection techniques, and a Markdown guide for effective documentation. Whether you're a beginner or a seasoned user, keeping this guide handy will improve your workflow and help secure your database systems.

More Cheat Sheets and Top Picks

• HTML Cheat Sheet
• Java Cheat Sheet
• Git Cheat Sheet
• Tailwind CSS Cheat Sheet
• CSS Cheat Sheet
• JavaScript Cheat Sheet