MariaDB Operations: Backup, Replication, Performance Tuning, and High Availability

# Basic backup
mysqldump -u root -p myapp > backup_$(date +%Y%m%d).sql

# Backup with compression
mysqldump -u root -p myapp | gzip > backup_$(date +%Y%m%d).sql.gz

# Backup specific tables
mysqldump -u root -p myapp users orders > tables_backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > full_backup.sql

# Backup with drop and create statements
mysqldump -u root -p --add-drop-table --add-drop-database myapp > backup.sql

# Backup stored procedures and events
mysqldump -u root -p --routines --events myapp > backup_with_procs.sql

# Full backup first
mysqldump -u root -p --single-transaction --master-data=2 myapp > full_backup.sql

# Enable binary logging (in my.cnf)
[mysqld]
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days = 7

# Find binlog position from backup
head -50 full_backup.sql | grep "CHANGE MASTER TO"

# Restore full backup
mysql -u root -p myapp < full_backup.sql

# Apply binary logs to point in time
mysqlbinlog mysql-bin.000001 --stop-datetime="2026-03-05 10:30:00" | mysql -u root -p

# Install mariabackup
# Ubuntu/Debian
sudo apt install mariadb-backup

# Full backup
mariabackup --backup \
    --target-dir=/var/mariadb/backup \
    --user=root --password=secret

# Prepare backup (for restore)
mariabackup --prepare \
    --target-dir=/var/mariadb/backup

# Restore backup
mariabackup --copy-back \
    --target-dir=/var/mariadb/backup \
    --datadir=/var/lib/mysql

# Incremental backup
mariabackup --backup \
    --target-dir=/var/mariadb/incr1 \
    --incremental-basedir=/var/mariadb/backup \
    --user=root --password=secret

import subprocess
import os
from datetime import datetime
import smtplib
from email.mime.text import MIMEText

class MariaDBBackup:
    def __init__(self, config):
        self.config = config
        self.backup_dir = config.get('backup_dir', '/var/backups/mariadb')
        os.makedirs(self.backup_dir, exist_ok=True)
    
    def create_backup(self):
        """Create logical backup using mysqldump."""
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        backup_file = f"{self.config['database']}_{timestamp}.sql"
        backup_path = os.path.join(self.backup_dir, backup_file)
        
        cmd = [
            'mysqldump',
            '-u', self.config['user'],
            f'-p{self.config["password"]}',
            '--single-transaction',
            '--quick',
            '--lock-tables=false',
            self.config['database']
        ]
        
        with open(backup_path, 'w') as f:
            subprocess.run(cmd, stdout=f, check=True)
        
        # Compress
        subprocess.run(['gzip', backup_path], check=True)
        
        # Clean old backups
        self.clean_old_backups()
        
        return f"{backup_path}.gz"
    
    def clean_old_backups(self, retention_days=7):
        """Remove backups older than retention period."""
        cutoff = datetime.now().timestamp() - (retention_days * 86400)
        
        for filename in os.listdir(self.backup_dir):
            filepath = os.path.join(self.backup_dir, filename)
            if os.path.getmtime(filepath) < cutoff:
                os.remove(filepath)
                print(f"Removed old backup: {filename}")
    
    def restore_backup(self, backup_file):
        """Restore database from backup."""
        if backup_file.endswith('.gz'):
            # Decompress and restore
            with open(backup_file.replace('.gz', ''), 'wb') as out:
                with open(backup_file, 'rb') as inp:
                    import gzip
                    subprocess.run(['gunzip'], stdin=inp, stdout=out)
            backup_file = backup_file.replace('.gz', '')
        
        cmd = [
            'mysql',
            '-u', self.config['user'],
            f'-p{self.config["password"]}',
            self.config['database']
        ]
        
        with open(backup_file, 'r') as f:
            subprocess.run(cmd, stdin=f, check=True)
        
        print(f"Restored from: {backup_file}")

# Usage
backup = MariaDBBackup({
    'user': 'root',
    'password': 'secret',
    'database': 'myapp',
    'backup_dir': '/var/backups/mariadb'
})

# Create backup
backup.create_backup()

# Restore
# backup.restore_backup('/var/backups/mariadb/myapp_20260305.sql.gz')

# Master server my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
binlog_do_db=mydb
binlog_ignore_db=mysql
sync_binlog=1
innodb_flush_log_at_trx_commit=1

# Slave server my.cnf
[mysqld]
server-id=2
relay-log=relay-bin
read_only=1
log_slave_updates=1

-- On master: create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Show master status
SHOW MASTER STATUS;
-- Note: File and Position values

-- On slave: configure replication
CHANGE MASTER TO
    MASTER_HOST='master-server',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123;

-- Start replication
START SLAVE;

-- Check status
SHOW SLAVE STATUS\G

-- Configure multiple masters
CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123
FOR CHANNEL 'master-1';

CHANGE MASTER TO
    MASTER_HOST='master2',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=456
FOR CHANNEL 'master-2';

-- Start all channels
START SLAVE FOR ALL CHANNELS;

-- Monitor
SHOW SLAVE STATUS FOR CHANNEL 'master-1'\G

-- Replicate only specific database
CHANGE MASTER TO
    REPLICATE_DO_DB=mydb
FOR CHANNEL 'master-1';

-- Replicate specific tables
CHANGE MASTER TO
    REPLICATE_DO_TABLE='mydb.users',
    REPLICATE_DO_TABLE='mydb.orders'
FOR CHANNEL 'master-1';

-- Ignore tables
CHANGE MASTER TO
    REPLICATE_IGNORE_TABLE='mydb.logs'
FOR CHANNEL 'master-1';

# Install Galera (Ubuntu/Debian)
sudo apt install mariadb-server mariadb-galera-server galera

# Or MariaDB 10.11+
sudo apt install mariadb-server mariadb-galera-4

# Node 1: /etc/mysql/mariadb.conf.d/galera.cnf
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=my_galera_cluster
wsrep_cluster_address="gcomm://node1,node2,node3"
wsrep_node_name=node1
wsrep_node_address=192.168.1.10
wsrep_sst_method=rsync
wsrep_sst_auth=root:secret
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Node 2: /etc/mysql/mariadb.conf.d/galera.cnf
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=my_galera_cluster
wsrep_cluster_address="gcomm://node1,node2,node3"
wsrep_node_name=node2
wsrep_node_address=192.168.1.20
wsrep_sst_method=rsync
wsrep_sst_auth=root:secret
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Bootstrap first node
sudo service mysql start --wsrep-new-cluster

# Start other nodes
sudo service mysql start

# Verify cluster
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster%';"

-- Check cluster status
SHOW STATUS LIKE 'wsrep_cluster%';
-- wsrep_cluster_size: number of nodes
-- wsrep_cluster_status: Primary/Non-Primary
-- wsrep_cluster_conf_idx: configuration index

-- Check node status
SHOW STATUS LIKE 'wsrep_local%';
-- wsrep_local_state: Synced/Joining/Donor/Desynced
-- wsrep_incoming_addresses: connected nodes

-- Manual split-brain recovery
-- On remaining node:
SET GLOBAL wsrep_force_boot_cluster=1;
RESTART;

# Install MaxScale
sudo apt install maxscale

# Configure MaxScale
# /etc/maxscale.cnf
[maxscale]
threads=auto
logdir=/var/log/maxscale

[server1]
type=server
address=192.168.1.10
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.1.20
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=192.168.1.30
port=3306
protocol=MariaDBBackend

[Galera Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
password=maxscale_pass
monitor_interval=1000

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=maxscale_pass

[Read-Only Listener]
type=listener
service=Read-Write Service
protocol=MariaDBClient
port=3306

# /etc/mysql/mariadb.conf.d/performance.cnf
[mysqld]
# InnoDB settings
innodb_buffer_pool_size=4G        # 70% of available RAM
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2  # 1=strict, 2=faster
innodb_flush_method=O_DIRECT
innodb_file_per_table=1

# Connection settings
max_connections=200
thread_pool_size=32

# Query cache (MariaDB 10.1.7+ deprecated, skip for 10.5+)
# query_cache_type=0

# Temp tables
tmp_table_size=256M
max_heap_table_size=256M

# Slow query log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2

# Binary log
max_binlog_size=100M
binlog_expire_logs_seconds=604800

-- Analyze query execution
EXPLAIN FORMAT=JSON
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 70000;

-- Show index statistics
SHOW INDEX FROM employees;

-- Table statistics
ANALYZE TABLE employees;

-- Check for full table scans
SELECT * FROM information_schema.tables
WHERE table_schema = 'mydb'
AND rows_examined_per_scan > 1000;

-- Identify slow queries
SELECT * FROM mysql.slow_log
ORDER BY start_time DESC LIMIT 10;

# Python with connection pooling
import mariadb
from mariadb.pooling import Pool

# Create pool
pool = Pool(
    pool_name="mypool",
    pool_size=10,
    host="localhost",
    user="root",
    password="secret",
    database="mydb"
)

# Get connection from pool
conn = pool.get_connection()
try:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    # Process results
finally:
    conn.close()  # Returns to pool

# Close pool when done
pool.close()

-- Server status
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

-- Connection info
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- InnoDB status
SHOW ENGINE INNODB STATUS;

-- Query statistics
SELECT * FROM information_schema.query_statistics
ORDER BY total_time DESC LIMIT 10;

-- Table statistics
SELECT * FROM information_schema.table_statistics
WHERE table_schema = 'mydb';

-- Create user with privileges
CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'localhost';

-- Create read-only user
CREATE USER 'reader'@'%' IDENTIFIED BY 'reader_password';
GRANT SELECT ON mydb.* TO 'reader'@'%';

-- Role-based access
CREATE ROLE 'developer';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'developer';
GRANT 'developer' TO 'app'@'localhost';

-- Password policies
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password_policy=STRONG;

-- Enable TLS/SSL
SHOW VARIABLES LIKE '%ssl%';

-- Create SSL certificates (external)
-- Configure in my.cnf
[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
require_secure_transport=ON

-- Encrypt tables
ALTER TABLE sensitive_data ENCRYPT='Y';

-- Encrypt binlog
binlog_encryption=ON

-- Install audit plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- Configure
SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_logging=ON;
SET GLOBAL server_audit_output_type=FILE;

-- View audit logs
SELECT * FROM mysql.server_audit;

-- Optimize tables (defragment)
OPTIMIZE TABLE users;
OPTIMIZE TABLE orders;

-- Check tables
CHECK TABLE users;

-- Repair tables (MyISAM)
REPAIR TABLE users;

-- Analyze tables (update statistics)
ANALYZE TABLE users;

-- Flush tables
FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;

#!/bin/bash
# mariadb-health.sh

DB_HOST=${1:-localhost}
DB_USER=${2:-root}
DB_PASS=${3:-secret}
ALERT_EMAIL="[email protected]"

# Check connection
if ! mysqladmin ping -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" --silent; then
    echo "MariaDB is not responding"
    # Send alert
    exit 1
fi

# Check replication
SLAVE_STATUS=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e "SHOW SLAVE STATUS\G" 2>/dev/null)
if [ -n "$SLAVE_STATUS" ]; then
    IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
    SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
    
    if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
        echo "Replication not running properly"
        # Send alert
    fi
fi

# Check disk space
DISK_USAGE=$(df -h /var/lib/mysql | tail -1 | awk '{print $5}' | sed 's/%//')
if [ "$DISK_USAGE" -gt 90 ]; then
    echo "Disk usage is ${DISK_USAGE}%"
    # Send alert
fi

echo "Health check passed"

# Production-ready MariaDB configuration
[mysqld]
# Basic
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock

# InnoDB
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1

# Logging
log_error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1

# Replication (if needed)
log-bin=mysql-bin
binlog_format=ROW
sync_binlog=1

import mariadb
import time

class MariaDBMonitor:
    def __init__(self, config):
        self.config = config
    
    def get_status(self):
        conn = mariadb.connect(
            host=self.config['host'],
            user=self.config['user'],
            password=self.config['password']
        )
        
        status = {}
        cursor = conn.cursor()
        
        # Connection count
        cursor.execute("SHOW GLOBAL STATUS LIKE 'Threads_connected'")
        status['connections'] = cursor.fetchone()[1]
        
        # Queries per second
        cursor.execute("SHOW GLOBAL STATUS LIKE 'Queries'")
        queries = int(cursor.fetchone()[1])
        time.sleep(1)
        cursor.execute("SHOW GLOBAL STATUS LIKE 'Queries'")
        queries2 = int(cursor.fetchone()[1])
        status['qps'] = queries2 - queries
        
        # InnoDB buffer pool usage
        cursor.execute("SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_bytes_data'")
        status['buffer_used'] = int(cursor.fetchone()[1])
        
        conn.close()
        return status

[mysqld]
server-id=1  # Must be unique

# Use ROW format for safety
binlog_format=ROW

# Or at minimum MIXED
binlog_format=MIXED

# Set to 70% of available RAM
innodb_buffer_pool_size=4G