MariaDB Cheatsheet¶

MariaDB - Open Source Relational Database

Maria DB ist eine beliebte Open Source relationale Datenbank, die als Gabel von MySQL erstellt wurde. Es ist entworfen, um ein Drop-in Ersatz für MySQL mit zusätzlichen Funktionen, bessere Leistung und verbesserte Sicherheit. < p>

generieren

Inhaltsverzeichnis¶

[Installation](#installation
(#basic-commands)
[Datenbankoperationen](LINK_2__
[Tabellen](#table-operations_
Datentypen
(CRUD Operationen)(LINK_5__
[Query Operations](LINK_6__
[Indexes](LINK_7_
Ansichten
(#stored-procedures)
Funktionen
Trigger
Transaktionen
(#user-management_)
(#backup-and-restore_)
Replikation
(#performance-optimization_)
(Monitoring)(LINK_17_)
[Sicherheit](#security_
Beste Praktiken

Installation¶

Ubuntu/Debian¶

# Update package index
sudo apt update

# Install MariaDB server
sudo apt install mariadb-server mariadb-client

# Secure installation
sudo mysql_secure_installation

# Start MariaDB service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Check status
sudo systemctl status mariadb

# Connect to MariaDB
sudo mysql -u root -p
```_

### CentOS/RHEL/Fedora
```bash
# Install MariaDB
sudo yum install mariadb-server mariadb
# or for newer versions
sudo dnf install mariadb-server mariadb

# Start MariaDB service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Secure installation
sudo mysql_secure_installation

# Connect to MariaDB
mysql -u root -p
```_

### macOS
```bash
# Using Homebrew
brew install mariadb

# Start MariaDB
brew services start mariadb

# Secure installation
mysql_secure_installation

# Connect to MariaDB
mysql -u root -p
```_

### Docker
```bash
# Pull MariaDB image
docker pull mariadb:latest

# Run MariaDB container
docker run --name mariadb-container \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e MYSQL_DATABASE=mydatabase \
  -e MYSQL_USER=myuser \
  -e MYSQL_PASSWORD=mypassword \
  -p 3306:3306 \
  -d mariadb:latest

# Connect to MariaDB in container
docker exec -it mariadb-container mysql -u root -p

# Docker Compose
cat > docker-compose.yml << EOF
version: '3.8'
services:
  mariadb:
    image: mariadb:latest
    container_name: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydatabase
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
    ports:
      - "3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql

volumes:
  mariadb_data:
EOF

docker-compose up -d
```_

## Grundlegende Befehle

### Anschluss an MariaDB
```bash
# Connect as root
mysql -u root -p

# Connect to specific database
mysql -u username -p database_name

# Connect to remote server
mysql -h hostname -u username -p database_name

# Connect with specific port
mysql -h hostname -P 3307 -u username -p

# Execute SQL from command line
mysql -u username -p -e "SELECT VERSION();"

# Execute SQL from file
mysql -u username -p database_name < script.sql
```_

### Grundlegende Informationsbefehle
```sql
-- Show MariaDB version
SELECT VERSION();

-- Show current user
SELECT USER();

-- Show current database
SELECT DATABASE();

-- Show current date and time
SELECT NOW();

-- Show databases
SHOW DATABASES;

-- Show tables
SHOW TABLES;

-- Show table structure
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

-- Show create table statement
SHOW CREATE TABLE table_name;

-- Show processes
SHOW PROCESSLIST;

-- Show status
SHOW STATUS;

-- Show variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';

-- Exit MariaDB
EXIT;
QUIT;
```_

## Datenbanken

### Datenbanken erstellen und verwalten
```sql
-- Create database
CREATE DATABASE mydatabase;
CREATE DATABASE IF NOT EXISTS mydatabase;

-- Create database with character set
CREATE DATABASE mydatabase 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Use database
USE mydatabase;

-- Show current database
SELECT DATABASE();

-- Drop database
DROP DATABASE mydatabase;
DROP DATABASE IF EXISTS mydatabase;

-- Show database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'mydatabase'
GROUP BY table_schema;
```_

### Datenbank Information
```sql
-- Show all databases with sizes
SELECT 
    table_schema AS 'Database',
    COUNT(table_name) AS 'Tables',
    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;

-- Show database character set
SELECT 
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mydatabase';
```_

## Tabelle Operationen

### Tabellen erstellen
```sql
-- Basic table creation
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table with indexes
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT,
    sku VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT idx_search (name, description)
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(255)
);

-- Create table from query
CREATE TABLE user_summary AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users
GROUP BY DATE(created_at);
```_

### Änderung der Tabellen
```sql
-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with position
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) AFTER username;

-- Modify column
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL;

-- Change column name and type
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(60) NOT NULL;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add index
ALTER TABLE users ADD INDEX idx_email (email);

-- Add foreign key
ALTER TABLE orders ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Drop foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;

-- Rename table
ALTER TABLE users RENAME TO customers;
RENAME TABLE customers TO users;

-- Drop table
DROP TABLE temp_data;
DROP TABLE IF EXISTS temp_data;
```_

### Angaben zur Tabelle
```sql
-- Show table structure
DESCRIBE users;
SHOW COLUMNS FROM users;

-- Show table creation statement
SHOW CREATE TABLE users;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

-- Show table indexes
SHOW INDEXES FROM users;

-- Show table size
SELECT 
    table_name AS 'Table',
    table_rows AS 'Rows',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;
```_

## Datentypen

### Numerische Datentypen
```sql
-- Integer types
TINYINT     -- 1 byte, -128 to 127
SMALLINT    -- 2 bytes, -32,768 to 32,767
MEDIUMINT   -- 3 bytes, -8,388,608 to 8,388,607
INT         -- 4 bytes, -2,147,483,648 to 2,147,483,647
BIGINT      -- 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Unsigned integers
INT UNSIGNED        -- 0 to 4,294,967,295
BIGINT UNSIGNED     -- 0 to 18,446,744,073,709,551,615

-- Decimal types
DECIMAL(10,2)       -- Fixed-point number
NUMERIC(10,2)       -- Synonym for DECIMAL
FLOAT               -- Single-precision floating-point
DOUBLE              -- Double-precision floating-point

-- Boolean
BOOLEAN             -- Synonym for TINYINT(1)
BOOL                -- Synonym for TINYINT(1)
```_

### String-Datentypen
```sql
-- Fixed-length strings
CHAR(10)            -- Fixed-length string, 0-255 characters

-- Variable-length strings
VARCHAR(255)        -- Variable-length string, 0-65,535 characters

-- Text types
TINYTEXT            -- 0-255 characters
TEXT                -- 0-65,535 characters
MEDIUMTEXT          -- 0-16,777,215 characters
LONGTEXT            -- 0-4,294,967,295 characters

-- Binary types
BINARY(10)          -- Fixed-length binary string
VARBINARY(255)      -- Variable-length binary string
TINYBLOB            -- 0-255 bytes
BLOB                -- 0-65,535 bytes
MEDIUMBLOB          -- 0-16,777,215 bytes
LONGBLOB            -- 0-4,294,967,295 bytes

-- Enumeration and Set
ENUM('small', 'medium', 'large')
SET('red', 'green', 'blue')
```_

### Datum und Uhrzeit
```sql
-- Date and time types
DATE                -- YYYY-MM-DD
TIME                -- HH:MM:SS
DATETIME            -- YYYY-MM-DD HH:MM:SS
TIMESTAMP           -- YYYY-MM-DD HH:MM:SS (with timezone)
YEAR                -- YYYY

-- Examples
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```_

## AUSRÜSTUNG Operationen

### Einsatzbereiche
```sql
-- Insert single row
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password) VALUES
    ('alice', 'alice@example.com', 'password1'),
    ('bob', 'bob@example.com', 'password2'),
    ('charlie', 'charlie@example.com', 'password3');

-- Insert with ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john.new@example.com', 'new_password')
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    password = VALUES(password),
    updated_at = CURRENT_TIMESTAMP;

-- Insert from SELECT
INSERT INTO user_backup (username, email, created_at)
SELECT username, email, created_at 
FROM users 
WHERE created_at > '2023-01-01';

-- Insert with IGNORE (skip duplicates)
INSERT IGNORE INTO users (username, email, password)
VALUES ('existing_user', 'existing@example.com', 'password');
```_

### Wählen Sie Operationen
```sql
-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with alias
SELECT username AS user, email AS email_address FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE created_at > '2023-01-01';

-- Select with multiple conditions
SELECT * FROM users 
WHERE username LIKE 'a%' 
AND created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- Select with ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;

-- Select with LIMIT
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Select DISTINCT
SELECT DISTINCT username FROM users;

-- Select with aggregation
SELECT COUNT(*) FROM users;
SELECT COUNT(*), AVG(id), MIN(created_at), MAX(created_at) FROM users;

-- Select with GROUP BY
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users 
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Select with HAVING
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users 
GROUP BY DATE(created_at)
HAVING COUNT(*) > 5;
```_

### Aktualisierung der Operationen
```sql
-- Update single row
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- Update multiple columns
UPDATE users 
SET email = 'john.doe@example.com', 
    updated_at = CURRENT_TIMESTAMP 
WHERE username = 'john_doe';

-- Update with conditions
UPDATE users 
SET password = 'new_hashed_password' 
WHERE created_at < '2023-01-01';

-- Update with JOIN
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.order_date
WHERE o.order_date = (
    SELECT MAX(order_date) 
    FROM orders 
    WHERE user_id = u.id
);

-- Update with LIMIT
UPDATE users 
SET status = 'inactive' 
WHERE last_login < '2022-01-01'
LIMIT 100;
```_

### Löschen von Operationen
```sql
-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM users WHERE created_at < '2022-01-01';

-- Delete with JOIN
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

-- Delete with LIMIT
DELETE FROM users 
WHERE status = 'inactive' 
LIMIT 100;

-- Delete all rows (but keep table structure)
DELETE FROM users;

-- Truncate table (faster than DELETE)
TRUNCATE TABLE users;
```_

## Abfrage von Operationen

### Mitglieder
```sql
-- INNER JOIN
SELECT u.username, o.total_amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.username, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.username, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN (using UNION)
SELECT u.username, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN
SELECT u.username, p.name
FROM users u
CROSS JOIN products p;

-- Self JOIN
SELECT u1.username, u2.username AS manager
FROM users u1
LEFT JOIN users u2 ON u1.manager_id = u2.id;
```_

### Subques
```sql
-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 100);

-- Subquery in SELECT clause
SELECT 
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- Correlated subquery
SELECT * FROM users u1
WHERE id = (SELECT user_id FROM orders WHERE user_id = u1.id ORDER BY total_amount DESC LIMIT 1);

-- EXISTS subquery
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

-- NOT EXISTS subquery
SELECT * FROM users
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);
```_

### Fensterfunktionen (MariaDB 10.2+)
```sql
-- ROW_NUMBER
SELECT 
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- RANK and DENSE_RANK
SELECT 
    username,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank
FROM user_spending;

-- PARTITION BY
SELECT 
    username,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Running totals
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

-- LAG and LEAD
SELECT 
    order_date,
    total_amount,
    LAG(total_amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(total_amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM orders;
```_

### Gemeinsame Tabellenausdrücke (CTE) - MariaDB 10.2+
```sql
-- Basic CTE
WITH young_users AS (
    SELECT * FROM users WHERE YEAR(created_at) >= 2023
)
SELECT * FROM young_users WHERE username LIKE 'a%';

-- Recursive CTE
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n+1, next_fib_n, fib_n + next_fib_n 
    FROM fibonacci 
    WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;

-- Multiple CTEs
WITH 
high_value_orders AS (
    SELECT * FROM orders WHERE total_amount > 1000
),
premium_users AS (
    SELECT DISTINCT user_id FROM high_value_orders
)
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
JOIN premium_users pu ON u.id = pu.user_id
JOIN high_value_orders o ON u.id = o.user_id
GROUP BY u.username;
```_

## Index

### Indexe erstellen
```sql
-- Simple index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_users_username_email ON users(username, email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username_unique ON users(username);

-- Partial index (with WHERE clause)
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Functional index
CREATE INDEX idx_users_email_lower ON users((LOWER(email)));

-- Full-text index
CREATE FULLTEXT INDEX idx_products_search ON products(name, description);

-- Spatial index (for geometry columns)
CREATE SPATIAL INDEX idx_locations_point ON locations(coordinates);
```_

### Index verwalten
```sql
-- Show indexes for a table
SHOW INDEXES FROM users;

-- Show index usage
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_users_email ON users;
ALTER TABLE users DROP INDEX idx_users_email;

-- Add index to existing table
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD UNIQUE INDEX idx_username_unique (username);

-- Analyze table to update index statistics
ANALYZE TABLE users;

-- Check index cardinality
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME;
```_

## Ansichten

### Ansichten erstellen
```sql
-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE status = 'active';

-- Complex view with joins
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- Updatable view
CREATE VIEW user_profiles AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- View with CHECK OPTION
CREATE VIEW premium_users AS
SELECT id, username, email, membership_level
FROM users
WHERE membership_level = 'premium'
WITH CHECK OPTION;
```_

### Verwaltung von Ansichten
```sql
-- Show views
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Show view definition
SHOW CREATE VIEW active_users;

-- Update view
ALTER VIEW active_users AS
SELECT id, username, email, created_at
FROM users 
WHERE status = 'active';

-- Drop view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

-- Update data through view (if updatable)
UPDATE user_profiles 
SET email = 'newemail@example.com' 
WHERE id = 1;
```_

## Gespeicherte Verfahren

### Erstellung gespeicherter Verfahren
```sql
-- Simple stored procedure
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS user_count FROM users;
END //
DELIMITER ;

-- Procedure with parameters
DELIMITER //
CREATE PROCEDURE GetUsersByStatus(IN user_status VARCHAR(20))
BEGIN
    SELECT * FROM users WHERE status = user_status;
END //
DELIMITER ;

-- Procedure with IN, OUT, and INOUT parameters
DELIMITER //
CREATE PROCEDURE GetUserStats(
    IN user_status VARCHAR(20),
    OUT user_count INT,
    OUT avg_age DECIMAL(5,2)
)
BEGIN
    SELECT COUNT(*), AVG(age) 
    INTO user_count, avg_age
    FROM users 
    WHERE status = user_status;
END //
DELIMITER ;

-- Procedure with conditional logic
DELIMITER //
CREATE PROCEDURE UpdateUserStatus(
    IN user_id INT,
    IN new_status VARCHAR(20)
)
BEGIN
    DECLARE user_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO user_exists 
    FROM users 
    WHERE id = user_id;

    IF user_exists > 0 THEN
        UPDATE users 
        SET status = new_status, updated_at = NOW() 
        WHERE id = user_id;
        SELECT 'User updated successfully' AS message;
    ELSE
        SELECT 'User not found' AS message;
    END IF;
END //
DELIMITER ;
```_

### Calling Stored Procedures
```sql
-- Call simple procedure
CALL GetUserCount();

-- Call procedure with parameters
CALL GetUsersByStatus('active');

-- Call procedure with OUT parameters
CALL GetUserStats('active', @count, @avg_age);
SELECT @count, @avg_age;

-- Call procedure with error handling
CALL UpdateUserStatus(1, 'inactive');
```_

### Verwaltung gespeicherter Verfahren
```sql
-- Show stored procedures
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Show procedure definition
SHOW CREATE PROCEDURE GetUserCount;

-- Drop stored procedure
DROP PROCEDURE GetUserCount;
DROP PROCEDURE IF EXISTS GetUserCount;
```_

## Funktionen

### Funktionen erstellen
```sql
-- Simple function
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN YEAR(CURDATE()) - YEAR(birth_date);
END //
DELIMITER ;

-- Function with conditional logic
DELIMITER //
CREATE FUNCTION GetUserLevel(total_spent DECIMAL(10,2))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(20);

    IF total_spent >= 10000 THEN
        SET level = 'platinum';
    ELSEIF total_spent >= 5000 THEN
        SET level = 'gold';
    ELSEIF total_spent >= 1000 THEN
        SET level = 'silver';
    ELSE
        SET level = 'bronze';
    END IF;

    RETURN level;
END //
DELIMITER ;
```_

### Funktionen nutzen
```sql
-- Use function in SELECT
SELECT 
    username,
    birth_date,
    CalculateAge(birth_date) AS age
FROM users;

-- Use function in WHERE clause
SELECT * FROM users 
WHERE CalculateAge(birth_date) >= 18;

-- Use function with aggregation
SELECT 
    GetUserLevel(SUM(total_amount)) AS level,
    COUNT(*) AS user_count
FROM orders
GROUP BY user_id;
```_

### Funktionen verwalten
```sql
-- Show functions
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- Show function definition
SHOW CREATE FUNCTION CalculateAge;

-- Drop function
DROP FUNCTION CalculateAge;
DROP FUNCTION IF EXISTS CalculateAge;
```_

## Auslöser

### Trigger erstellen
```sql
-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- AFTER INSERT trigger
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, timestamp)
    VALUES (NEW.id, 'INSERT', NOW());
END //
DELIMITER ;

-- BEFORE UPDATE trigger
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- AFTER UPDATE trigger with conditional logic
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO user_status_history (user_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END //
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users (original_id, username, email, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, NOW());
END //
DELIMITER ;
```_

### Verwaltung von Triggern
```sql
-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users';

-- Show trigger definition
SHOW CREATE TRIGGER before_user_insert;

-- Drop trigger
DROP TRIGGER before_user_insert;
DROP TRIGGER IF EXISTS before_user_insert;
```_

## Transaktionen

### Grundtransaktionen
```sql
-- Start transaction
START TRANSACTION;
-- or
BEGIN;

-- Perform operations
INSERT INTO users (username, email) VALUES ('test_user', 'test@example.com');
UPDATE users SET status = 'active' WHERE username = 'test_user';

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;
```_

### Umsetzung mit Savepoints
```sql
START TRANSACTION;

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');

SAVEPOINT sp2;

INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');

-- Rollback to savepoint
ROLLBACK TO sp1;

-- Commit remaining changes
COMMIT;
```_

### Transaction Isolation Levels
```sql
-- Show current isolation level
SELECT @@transaction_isolation;

-- Set isolation level for session
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set isolation level for next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```_

### Verriegelung
```sql
-- Shared lock (read lock)
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (write lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- Table-level locks
LOCK TABLES users READ;
LOCK TABLES users WRITE;
UNLOCK TABLES;
```_

## Benutzermanagement

### Benutzer erstellen
```sql
-- Create user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

-- Create user with specific host
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'password';

-- Create user if not exists
CREATE USER IF NOT EXISTS 'newuser'@'localhost' IDENTIFIED BY 'password';
```_

### Vorrechte gewähren
```sql
-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

-- Grant specific privileges on specific database
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'appuser'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, UPDATE ON mydatabase.users TO 'readonly'@'localhost';

-- Grant specific column privileges
GRANT SELECT (username, email), UPDATE (email) ON mydatabase.users TO 'limited'@'localhost';

-- Grant execute privilege for stored procedures
GRANT EXECUTE ON mydatabase.* TO 'appuser'@'localhost';

-- Grant with grant option
GRANT SELECT ON mydatabase.* TO 'manager'@'localhost' WITH GRANT OPTION;

-- Apply changes
FLUSH PRIVILEGES;
```_

### Benutzerrechte verwalten
```sql
-- Show user privileges
SHOW GRANTS FOR 'username'@'localhost';
SHOW GRANTS FOR CURRENT_USER();

-- Revoke privileges
REVOKE INSERT, UPDATE ON mydatabase.* FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

-- Change user password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');

-- Rename user
RENAME USER 'oldname'@'localhost' TO 'newname'@'localhost';

-- Drop user
DROP USER 'username'@'localhost';
DROP USER IF EXISTS 'username'@'localhost';

-- Show all users
SELECT User, Host FROM mysql.user;
```_

### Benutzerkontoverwaltung
```sql
-- Lock user account
ALTER USER 'username'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

-- Set password expiration
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Require SSL
ALTER USER 'username'@'localhost' REQUIRE SSL;

-- Set resource limits
ALTER USER 'username'@'localhost' 
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 100
     MAX_CONNECTIONS_PER_HOUR 10;
```_

## Backup und Wiederherstellung

### mysqldump Backup
```bash
# Backup single database
mysqldump -u username -p database_name > backup.sql

# Backup multiple databases
mysqldump -u username -p --databases db1 db2 db3 > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases > all_databases.sql

# Backup specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Backup with compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

# Backup with additional options
mysqldump -u username -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  database_name > backup.sql

# Backup structure only (no data)
mysqldump -u username -p --no-data database_name > structure.sql

# Backup data only (no structure)
mysqldump -u username -p --no-create-info database_name > data.sql
```_

### Wiederherstellen von Backup
```bash
# Restore database
mysql -u username -p database_name < backup.sql

# Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

# Restore all databases
mysql -u username -p < all_databases.sql

# Create database and restore
mysql -u username -p -e "CREATE DATABASE newdatabase;"
mysql -u username -p newdatabase < backup.sql
```_

### Binary Log Backup
```sql
-- Show binary log status
SHOW MASTER STATUS;

-- Show binary logs
SHOW BINARY LOGS;

-- Flush logs
FLUSH LOGS;

-- Purge old logs
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';
```_

### Zeitpunkt Erholung
```bash
# Extract SQL from binary log
mysqlbinlog mysql-bin.000001 > binlog.sql

# Extract SQL for specific time range
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
           --stop-datetime="2023-01-01 23:59:59" \
           mysql-bin.000001 > recovery.sql

# Apply recovery
mysql -u username -p database_name < recovery.sql
```_

## Wiederholung

### Master Configuration
```sql
-- Enable binary logging (in my.cnf)
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-format=ROW

-- Create replication user
CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;
```_

### Slawische Konfiguration
```sql
-- Configure slave (in my.cnf)
[mysqld]
server-id=2
relay-log=relay-bin
read-only=1

-- Configure replication
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Stop slave
STOP SLAVE;
```_

### Überwachung der Replikation
```sql
-- Check replication status
SHOW SLAVE STATUS\G

-- Check master status
SHOW MASTER STATUS;

-- Show processlist
SHOW PROCESSLIST;

-- Check replication lag
SELECT 
    UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag_seconds
FROM (
    SELECT FROM_UNIXTIME(variable_value) AS ts
    FROM information_schema.global_status
    WHERE variable_name = 'SLAVE_SQL_RUNNING_STATE'
) AS subquery;
```_

## Leistungsoptimierung

### Queroptimierung
```sql
-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'john@example.com';

-- Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);

-- Use ANALYZE TABLE to update statistics
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Check table for errors
CHECK TABLE users;

-- Repair table
REPAIR TABLE users;
```_

### Konfigurationsoptimierung
```sql
-- Show current configuration
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';

-- Key buffer size (for MyISAM)
SET GLOBAL key_buffer_size = 256M;

-- InnoDB buffer pool size
SET GLOBAL innodb_buffer_pool_size = 1G;

-- Query cache (deprecated in MariaDB 10.1.7+)
SET GLOBAL query_cache_size = 64M;
SET GLOBAL query_cache_type = ON;

-- Connection settings
SET GLOBAL max_connections = 200;
SET GLOBAL connect_timeout = 10;

-- Temporary table settings
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;
```_

### Slow Query Log
```sql
-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

-- Show slow query log settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Analyze slow queries (from command line)
mysqldumpslow /var/log/mysql/slow.log
```_

### Leistungsüberwachung
```sql
-- Show process list
SHOW PROCESSLIST;

-- Show status variables
SHOW STATUS;
SHOW STATUS LIKE 'Innodb%';

-- Show engine status
SHOW ENGINE INNODB STATUS;

-- Performance schema queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Show table sizes
SELECT 
    table_name,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;
```_

## Überwachung

### Systemüberwachung
```sql
-- Show server status
SHOW STATUS;

-- Show global variables
SHOW GLOBAL VARIABLES;

-- Show process list
SHOW PROCESSLIST;

-- Show open tables
SHOW OPEN TABLES;

-- Show table locks
SHOW TABLE STATUS;

-- Show engine status
SHOW ENGINE INNODB STATUS;
```_

### Leistungsüberwachung
```sql
-- Connection statistics
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';

-- Query statistics
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Queries';
SHOW STATUS LIKE 'Slow_queries';

-- InnoDB statistics
SHOW STATUS LIKE 'Innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_rows%';

-- Table cache statistics
SHOW STATUS LIKE 'Open%';
SHOW STATUS LIKE 'Opened_tables';
```_

### Überwachung der Daten
```bash
# Monitor error log
tail -f /var/log/mysql/error.log

# Monitor slow query log
tail -f /var/log/mysql/slow.log

# Monitor general log
tail -f /var/log/mysql/mysql.log

# Analyze slow queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
```_

## Sicherheit

### Grundsicherung
```sql
-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Remove test database
DROP DATABASE IF EXISTS test;

-- Set root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';

-- Disable remote root login
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Flush privileges
FLUSH PRIVILEGES;
```_

### SSL Konfiguration
```bash
# Generate SSL certificates (in my.cnf)
[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

# Require SSL for user
ALTER USER 'username'@'localhost' REQUIRE SSL;

# Connect with SSL
mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u username -p
```_

### Prüfung Plugin
```sql
-- Install audit plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- Configure audit logging
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

-- Show audit status
SHOW VARIABLES LIKE 'server_audit%';
```_

## Best Practices

### Schema Design Best Practices
```sql
-- Use appropriate data types
-- Use INT for IDs, not VARCHAR
-- Use DECIMAL for money, not FLOAT
-- Use appropriate VARCHAR lengths

-- Normalize appropriately
-- Don't over-normalize (3NF is usually sufficient)
-- Denormalize for performance when needed

-- Use constraints
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount > 0),
    status ENUM('pending', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Use indexes wisely
-- Index foreign keys
-- Index columns used in WHERE, ORDER BY, GROUP BY
-- Don't over-index (impacts INSERT/UPDATE performance)

Best Practices abfragen¶

```sql -- Use LIMIT for large result sets SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- Use specific columns instead of SELECT * SELECT id, username, email FROM users;

-- Use EXISTS instead of IN for subqueries SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

-- Use UNION ALL instead of UNION when duplicates are acceptable SELECT username FROM active_users UNION ALL SELECT username FROM inactive_users;

-- Use prepared statements to prevent SQL injection PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?'; SET @user_id = 1; EXECUTE stmt USING @user_id; ```_

Performance Best Practices¶

```sql -- Regular maintenance ANALYZE TABLE users; OPTIMIZE TABLE users;

-- Monitor slow queries SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;

-- Use connection pooling in applications -- Configure appropriate buffer sizes -- Use read replicas for read-heavy workloads -- Partition large tables when appropriate

-- Example partitioning CREATE TABLE orders ( id INT AUTO_INCREMENT, user_id INT, order_date DATE, total_amount DECIMAL(10,2), PRIMARY KEY (id, order_date) ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p_future VALUES LESS THAN MAXVALUE ); ```_

Sicherheit Best Practices¶

```sql -- Use least privilege principle -- Create specific users for applications CREATE USER 'app_read'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT ON myapp.* TO 'app_read'@'localhost';

CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write'@'localhost';

-- Regular security maintenance -- Update MariaDB regularly -- Monitor access logs -- Use SSL for connections -- Backup regularly and test restores -- Use strong passwords -- Limit network access ```_

--

Zusammenfassung¶

Maria DB ist ein leistungsstarkes, open-source relationales Datenbank-Management-System, das hervorragende Leistung, Zuverlässigkeit und Kompatibilität mit MySQL bietet. Dieses Cheatsheet bietet eine umfassende Abdeckung von MariaDB-Operationen von der Grundverwaltung bis zu erweiterten Funktionen.

Key Strengths: - MySQL Kompatibilität*: Ersatz für MySQL mit erweiterten Funktionen - **Leistung: Verbesserte Abfrageoptimierung und Speichermotoren - Open Source: Offene Quelle mit aktiver Community-Entwicklung - Erweiterte Funktionen*: Fensterfunktionen, CTE, JSON-Unterstützung und mehr - **Speichermotoren: Mehrere Speichermotoren einschließlich InnoDB, MyISAM und Aria

Best Use Cases: - Webanwendungen, die ACID-Compliance erfordern - Datenspeicherung und Analytik - E-Commerce-Plattformen - Content Management Systeme - Anwendungen, die eine hohe Verfügbarkeit und Replikation erfordern

Importierte Überlegungen: - Die richtige Indexierungsstrategie ist entscheidend für die Leistung - Regelmäßige Wartung (ANALYZE, OPTIMIZE) verbessert die Leistung - Backup- und Recovery-Verfahren müssen regelmäßig getestet werden - Sicherheitskonfiguration erfordert sorgfältige Aufmerksamkeit - Überwachung und Alarmierung sind für Produktionssysteme unerlässlich

Durch die in diesem Cheatsheet skizzierten Praktiken und Techniken können Sie effektiv MariaDB-Datenbanken erstellen, implementieren und pflegen, die für Unternehmensanwendungen sicher, performant und skalierbar sind.

<= <= <= <================================================================================= Funktion copyToClipboard() {\cHFFFF} const commands = document.querySelectorAll('code'); alle Befehle = ''; Befehle. Für jede(cmd) => alle Befehle += cmd.textContent + '\n'); navigator.clipboard.writeText (allCommands); Alarm ('Alle Befehle, die in die Zwischenablage kopiert werden!'); }

Funktion generierenPDF() { Fenster.print(); }