tonym/NastyNAS
1
0
Fork 0
Code Issues 70 Pull Requests Actions Packages Projects Releases Wiki Activity

Configure MariaDB Database Server #37

New Issue
Open
opened 2025-09-14 08:10:18 +00:00 by tonym · 0 comments
tonym commented 2025-09-14 08:10:18 +00:00
Owner
Copy Link

MariaDB Database Server Configuration for Unraid 7.0.1

Container Installation

  • From Community Applications: Search "MariaDB"
  • Container Selection: Official MariaDB
  • Repository: mariadb:latest

Container Configuration

Basic Settings:

  • Container Port: 3306
  • Host Port: 3306
  • Network Mode: bridge
  • Repository Path: mariadb:latest

Volume Mappings:

  • Data: /mnt/user/appdata/mariadb/data:/var/lib/mysql
  • Config: /mnt/user/appdata/mariadb/config:/etc/mysql/conf.d
  • Backups: /mnt/user/appdata/mariadb/backups:/backups

Environment Variables:

  • PUID: 99
  • PGID: 100
  • TZ: America/Chicago
  • UMASK: 002
  • MYSQL_ROOT_PASSWORD: (generate secure password)
  • MYSQL_DATABASE: (optional default database)
  • MYSQL_USER: (optional default user)
  • MYSQL_PASSWORD: (optional default password)

Initial Configuration

  1. First Access: Connect via mysql client
  2. Root Setup:
    • Set secure root password
    • Create application databases
    • Create application users
  3. Security Hardening: Run mysql_secure_installation equivalent

Service Configuration

Database Management:

  • Character Set: utf8mb4 (full Unicode support)
  • Collation: utf8mb4_unicode_ci
  • InnoDB Settings: Optimize for available RAM
  • Connection Limits: Set appropriate max_connections

Performance Tuning:

  • Buffer Pool: Set to 70-80% of available RAM
  • Query Cache: Enable for read-heavy workloads
  • Slow Query Log: Enable for performance monitoring
  • Binary Logging: Enable for backup and replication

Application Databases:
Create databases for each service:

CREATE DATABASE jellyfin CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER jellyfin@% IDENTIFIED BY secure_password;
GRANT ALL PRIVILEGES ON jellyfin.* TO jellyfin@%;

CREATE DATABASE navidrome CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER navidrome@% IDENTIFIED BY secure_password;
GRANT ALL PRIVILEGES ON navidrome.* TO navidrome@%;

CREATE DATABASE ombi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER ombi@% IDENTIFIED BY secure_password;
GRANT ALL PRIVILEGES ON ombi.* TO ombi@%;

FLUSH PRIVILEGES;

Integration Points

Application Connections:

  • Jellyfin: Database for metadata and user data
  • Navidrome: Database for music library metadata
  • Ombi: Database for request management
  • Other Apps: Additional databases as needed

Backup Integration:

  • mysqldump: Regular database exports
  • Kopia: File-level backups
  • Binary Logs: Point-in-time recovery

Monitoring & Notifications

Uptime Kuma Integration:

  • Monitor Type: Port Check
  • Host: [SERVER_IP]
  • Port: 3306
  • Check Interval: 60 seconds
  • Alert Threshold: 3 failures

Performance Monitoring:

  • Connection Count: Monitor active connections
  • Query Performance: Monitor slow queries
  • Disk Usage: Monitor database size growth
  • Memory Usage: Monitor buffer pool efficiency

Backup Configuration

Kopia Integration:

  • Backup Paths:
    • /mnt/user/appdata/mariadb/data (database files)
    • /mnt/user/appdata/mariadb/backups (SQL dumps)
  • Schedule: Daily at 2:00 AM
  • Retention: 30 daily, 12 monthly

Database Backups:

# Daily backup script
mysqldump --all-databases --single-transaction --routines --triggers > /backups/full_backup_$(date +%Y%m%d).sql

# Weekly backup with compression
mysqldump --all-databases --single-transaction --routines --triggers | gzip > /backups/full_backup_$(date +%Y%m%d).sql.gz

Testing Procedures

Health Checks:

  1. Container Status: docker ps | grep mariadb
  2. Port Accessibility: telnet [SERVER_IP] 3306
  3. Database Connection: mysql -h [SERVER_IP] -u root -p
  4. Service Status: mysqladmin -h [SERVER_IP] -u root -p status

Functionality Tests:

  1. Database Creation: Create test database
  2. User Management: Create test user with permissions
  3. Data Operations: INSERT, SELECT, UPDATE, DELETE
  4. Performance: Run simple benchmarks

Integration Tests:

  1. Application Connections: Test each application database
  2. Backup Restore: Test backup and restore procedures
  3. Replication: Test if replication is configured

Troubleshooting

Common Issues:

  1. Connection Refused:

    • Check container status
    • Verify port accessibility
    • Review firewall settings
    • Check bind-address configuration

  2. Authentication Failed:

    • Verify username and password
    • Check user permissions
    • Review host restrictions
    • Check authentication plugin

  3. Performance Issues:

    • Monitor slow query log
    • Check buffer pool usage
    • Review connection limits
    • Analyze query performance

Permission Fixes:

sudo chown -R 999:999 /mnt/user/appdata/mariadb
sudo chmod -R 755 /mnt/user/appdata/mariadb

Log Locations:

  • Container Logs: docker logs mariadb
  • Error Log: /mnt/user/appdata/mariadb/data/error.log
  • Slow Query Log: /mnt/user/appdata/mariadb/data/slow.log
  • General Log: /mnt/user/appdata/mariadb/data/general.log

Maintenance

Regular Tasks:

  1. Database Optimization: Weekly OPTIMIZE TABLE
  2. Log Rotation: Manage log file sizes
  3. Security Updates: Update container regularly
  4. Backup Verification: Test backup integrity
  5. Performance Review: Monthly performance analysis

Performance Optimization:

  1. Configuration Tuning: Adjust my.cnf settings
  2. Index Optimization: Review and optimize indexes
  3. Query Optimization: Analyze and optimize slow queries
  4. Memory Management: Monitor and adjust buffer settings

Security Maintenance:

  1. Password Policy: Enforce strong passwords
  2. User Audit: Review user permissions regularly
  3. Access Logs: Monitor connection attempts
  4. Vulnerability Scanning: Regular security updates
# MariaDB Database Server Configuration for Unraid 7.0.1 ## Container Installation - **From Community Applications:** Search "MariaDB" - **Container Selection:** Official MariaDB - **Repository:** mariadb:latest ## Container Configuration **Basic Settings:** - **Container Port:** 3306 - **Host Port:** 3306 - **Network Mode:** bridge - **Repository Path:** mariadb:latest **Volume Mappings:** - **Data:** /mnt/user/appdata/mariadb/data:/var/lib/mysql - **Config:** /mnt/user/appdata/mariadb/config:/etc/mysql/conf.d - **Backups:** /mnt/user/appdata/mariadb/backups:/backups **Environment Variables:** - **PUID:** 99 - **PGID:** 100 - **TZ:** America/Chicago - **UMASK:** 002 - **MYSQL_ROOT_PASSWORD:** (generate secure password) - **MYSQL_DATABASE:** (optional default database) - **MYSQL_USER:** (optional default user) - **MYSQL_PASSWORD:** (optional default password) ## Initial Configuration 1. **First Access:** Connect via mysql client 2. **Root Setup:** - Set secure root password - Create application databases - Create application users 3. **Security Hardening:** Run mysql_secure_installation equivalent ## Service Configuration **Database Management:** - **Character Set:** utf8mb4 (full Unicode support) - **Collation:** utf8mb4_unicode_ci - **InnoDB Settings:** Optimize for available RAM - **Connection Limits:** Set appropriate max_connections **Performance Tuning:** - **Buffer Pool:** Set to 70-80% of available RAM - **Query Cache:** Enable for read-heavy workloads - **Slow Query Log:** Enable for performance monitoring - **Binary Logging:** Enable for backup and replication **Application Databases:** Create databases for each service: ```sql CREATE DATABASE jellyfin CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER jellyfin@% IDENTIFIED BY secure_password; GRANT ALL PRIVILEGES ON jellyfin.* TO jellyfin@%; CREATE DATABASE navidrome CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER navidrome@% IDENTIFIED BY secure_password; GRANT ALL PRIVILEGES ON navidrome.* TO navidrome@%; CREATE DATABASE ombi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER ombi@% IDENTIFIED BY secure_password; GRANT ALL PRIVILEGES ON ombi.* TO ombi@%; FLUSH PRIVILEGES; ``` ## Integration Points **Application Connections:** - **Jellyfin:** Database for metadata and user data - **Navidrome:** Database for music library metadata - **Ombi:** Database for request management - **Other Apps:** Additional databases as needed **Backup Integration:** - **mysqldump:** Regular database exports - **Kopia:** File-level backups - **Binary Logs:** Point-in-time recovery ## Monitoring & Notifications **Uptime Kuma Integration:** - **Monitor Type:** Port Check - **Host:** [SERVER_IP] - **Port:** 3306 - **Check Interval:** 60 seconds - **Alert Threshold:** 3 failures **Performance Monitoring:** - **Connection Count:** Monitor active connections - **Query Performance:** Monitor slow queries - **Disk Usage:** Monitor database size growth - **Memory Usage:** Monitor buffer pool efficiency ## Backup Configuration **Kopia Integration:** - **Backup Paths:** - /mnt/user/appdata/mariadb/data (database files) - /mnt/user/appdata/mariadb/backups (SQL dumps) - **Schedule:** Daily at 2:00 AM - **Retention:** 30 daily, 12 monthly **Database Backups:** ```bash # Daily backup script mysqldump --all-databases --single-transaction --routines --triggers > /backups/full_backup_$(date +%Y%m%d).sql # Weekly backup with compression mysqldump --all-databases --single-transaction --routines --triggers | gzip > /backups/full_backup_$(date +%Y%m%d).sql.gz ``` ## Testing Procedures **Health Checks:** 1. **Container Status:** docker ps | grep mariadb 2. **Port Accessibility:** telnet [SERVER_IP] 3306 3. **Database Connection:** mysql -h [SERVER_IP] -u root -p 4. **Service Status:** mysqladmin -h [SERVER_IP] -u root -p status **Functionality Tests:** 1. **Database Creation:** Create test database 2. **User Management:** Create test user with permissions 3. **Data Operations:** INSERT, SELECT, UPDATE, DELETE 4. **Performance:** Run simple benchmarks **Integration Tests:** 1. **Application Connections:** Test each application database 2. **Backup Restore:** Test backup and restore procedures 3. **Replication:** Test if replication is configured ## Troubleshooting **Common Issues:** 1. **Connection Refused:** - Check container status - Verify port accessibility - Review firewall settings - Check bind-address configuration 2. **Authentication Failed:** - Verify username and password - Check user permissions - Review host restrictions - Check authentication plugin 3. **Performance Issues:** - Monitor slow query log - Check buffer pool usage - Review connection limits - Analyze query performance **Permission Fixes:** ```bash sudo chown -R 999:999 /mnt/user/appdata/mariadb sudo chmod -R 755 /mnt/user/appdata/mariadb ``` **Log Locations:** - **Container Logs:** docker logs mariadb - **Error Log:** /mnt/user/appdata/mariadb/data/error.log - **Slow Query Log:** /mnt/user/appdata/mariadb/data/slow.log - **General Log:** /mnt/user/appdata/mariadb/data/general.log ## Maintenance **Regular Tasks:** 1. **Database Optimization:** Weekly OPTIMIZE TABLE 2. **Log Rotation:** Manage log file sizes 3. **Security Updates:** Update container regularly 4. **Backup Verification:** Test backup integrity 5. **Performance Review:** Monthly performance analysis **Performance Optimization:** 1. **Configuration Tuning:** Adjust my.cnf settings 2. **Index Optimization:** Review and optimize indexes 3. **Query Optimization:** Analyze and optimize slow queries 4. **Memory Management:** Monitor and adjust buffer settings **Security Maintenance:** 1. **Password Policy:** Enforce strong passwords 2. **User Audit:** Review user permissions regularly 3. **Access Logs:** Monitor connection attempts 4. **Vulnerability Scanning:** Regular security updates
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
No items
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
tonym
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: tonym/NastyNAS#37
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?