Saltar al contenido principal

Root and Superuser Capabilities in MariaDB

On Filess.io Dedicated Runtime, you have full root/superuser access to your MariaDB instance. This post explores the advanced capabilities this unlocks, from custom configurations to installing extensions and performing administrative tasks.

🎯 Understanding Root Access​

What is Root/Superuser?​

Root access in MariaDB provides:

  • Full administrative privileges: Complete control over the database
  • Configuration changes: Modify server settings
  • Extension installation: Install custom plugins and modules
  • User management: Create and manage database users
  • System maintenance: Perform advanced administrative tasks

Filess.io Shared vs Dedicated​

Shared Runtime:

  • Managed credentials only
  • No root/superuser access
  • Limited configuration options
  • Pre-installed extensions only

Dedicated Runtime:

  • Full root/superuser access
  • Custom configuration files
  • Install any compatible extension
  • Complete administrative control

⚙️ Custom Configuration​

Accessing Configuration Files​

On Filess.io Dedicated, configuration files are accessible:

# Main configuration file
/etc/mysql/mariadb.conf.d/50-server.cnf

# Custom configuration directory
/etc/mysql/mariadb.conf.d/

Common Configuration Tweaks​

Increase connection limits:

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
max_connections = 500
max_user_connections = 100

Optimize for your workload:

# For read-heavy workloads
[mysqld]
innodb_buffer_pool_size = 4G
innodb_read_io_threads = 8
innodb_write_io_threads = 8
query_cache_size = 256M

Enable binary logging for PITR:

[mysqld]
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7

Apply Configuration Changes​

# Test configuration
sudo mariadb-check-config

# Restart MariaDB (on Filess.io, use maintenance window)
sudo systemctl restart mariadb

🔌 Installing Extensions and Plugins​

Available Plugin Types​

  1. Storage Engines: InnoDB, Aria, MyRocks
  2. Authentication Plugins: PAM, LDAP
  3. Audit Plugins: Server Audit
  4. Performance Plugins: Query Response Time
  5. Custom Plugins: Third-party extensions

Install Server Audit Plugin​

Enable comprehensive audit logging:

-- Check available plugins
SHOW PLUGINS;

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

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

-- Verify
SHOW VARIABLES LIKE 'server_audit%';

Install Query Response Time Plugin​

Monitor slow queries:

-- Install plugin
INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';

-- Enable
SET GLOBAL query_response_time_stats='ON';

-- View statistics
SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;

Install Connect Engine​

Enable connections to external data sources:

-- Install CONNECT engine
INSTALL PLUGIN CONNECT SONAME 'ha_connect.so';

-- Create table from CSV file
CREATE TABLE customers_csv
ENGINE=CONNECT
TABLE_TYPE=CSV
FILE_NAME='/data/customers.csv'
HEADER=1;

👥 Advanced User Management​

Create Custom Users​

-- Create user with specific privileges
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON filess_example.* TO 'app_user'@'%';

-- Create read-only user
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_pass';
GRANT SELECT ON filess_example.* TO 'readonly_user'@'%';

-- Create admin user
CREATE USER 'admin_user'@'%' IDENTIFIED BY 'admin_pass';
GRANT ALL PRIVILEGES ON filess_example.* TO 'admin_user'@'%' WITH GRANT OPTION;

-- Flush privileges
FLUSH PRIVILEGES;

Advanced Privilege Management​

-- Grant procedure execution
GRANT EXECUTE ON PROCEDURE filess_example.calculate_total TO 'app_user'@'%';

-- Grant trigger creation
GRANT TRIGGER ON filess_example.* TO 'app_user'@'%';

-- Grant file operations (use with caution)
GRANT FILE ON *.* TO 'backup_user'@'localhost';

-- View user privileges
SHOW GRANTS FOR 'app_user'@'%';

Password Policies​

-- Set password expiration
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Require password change
ALTER USER 'app_user'@'%' PASSWORD EXPIRE;

-- Set password complexity
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;

🔧 Performance Tuning​

InnoDB Optimization​

-- Check InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Optimize buffer pool
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB

-- Configure flush method
SET GLOBAL innodb_flush_method = O_DIRECT;

-- Tune log file size
SET GLOBAL innodb_log_file_size = 268435456; -- 256MB

Query Cache Configuration​

-- Enable query cache
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 268435456; -- 256MB

-- Check cache status
SHOW STATUS LIKE 'Qcache%';

Index Optimization​

-- Analyze tables for better query plans
ANALYZE TABLE customers, products, sales;

-- Optimize tables
OPTIMIZE TABLE customers;

-- Check index usage
SHOW INDEX FROM customers;

📊 System Maintenance​

Table Maintenance​

-- Check table status
CHECK TABLE customers;

-- Repair table (if needed)
REPAIR TABLE customers;

-- Defragment table
OPTIMIZE TABLE customers;

Database Maintenance​

-- Check all tables
CHECK TABLE customers, products, sales;

-- Analyze all tables
ANALYZE TABLE customers, products, sales;

-- Optimize all tables
OPTIMIZE TABLE customers, products, sales;

Log Management​

-- Rotate binary logs
FLUSH BINARY LOGS;

-- Purge old binary logs
PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';

-- View log files
SHOW BINARY LOGS;
SHOW MASTER STATUS;

🔐 Security Hardening​

Enable SSL/TLS​

-- Check SSL status
SHOW VARIABLES LIKE '%ssl%';

-- Require SSL for specific user
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- Require X509 certificate
ALTER USER 'app_user'@'%' REQUIRE X509;

Enable SQL Mode Strict​

-- Set strict SQL mode
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- Verify
SHOW VARIABLES LIKE 'sql_mode';

Disable Dangerous Features​

-- Disable LOAD DATA LOCAL
SET GLOBAL local_infile = 0;

-- Disable symbolic links
SET GLOBAL symbolic_links = 0;

🧪 Advanced Features​

Partitioning​

-- Create partitioned table
CREATE TABLE sales_partitioned (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Full-Text Search​

-- Add full-text index
ALTER TABLE products ADD FULLTEXT(name, description);

-- Search
SELECT * FROM products 
WHERE MATCH(name, description) AGAINST('laptop' IN NATURAL LANGUAGE MODE);

JSON Functions​

-- Create table with JSON column
CREATE TABLE customer_preferences (
    id INT PRIMARY KEY,
    customer_id INT,
    preferences JSON
);

-- Insert JSON data
INSERT INTO customer_preferences VALUES
(1, 1, '{"theme": "dark", "notifications": true}');

-- Query JSON
SELECT 
    customer_id,
    JSON_EXTRACT(preferences, '$.theme') AS theme
FROM customer_preferences;

🎯 Integration with Example App​

Custom Functions​

-- Create function to calculate sale total
DELIMITER //
CREATE FUNCTION calculate_sale_total(sale_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(subtotal) INTO total
    FROM sale_items
    WHERE sale_id = sale_id;
    RETURN total;
END //
DELIMITER ;

-- Use in queries
SELECT id, calculate_sale_total(id) AS total FROM sales;

Stored Procedures​

-- Create procedure for complex operations
DELIMITER //
CREATE PROCEDURE process_sale(
    IN p_customer_id INT,
    IN p_items JSON
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Process sale logic here
    
    COMMIT;
END //
DELIMITER ;

📋 Monitoring and Diagnostics​

Performance Schema​

-- Enable performance schema
SET GLOBAL performance_schema = ON;

-- View active connections
SELECT * FROM performance_schema.threads;

-- View statement statistics
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Process List​

-- View active processes
SHOW PROCESSLIST;

-- Kill specific process
KILL 12345;

-- View full process list
SELECT * FROM information_schema.PROCESSLIST;

✅ Best Practices​

  1. Backup before changes: Always backup before configuration changes
  2. Test in staging: Test changes in non-production first
  3. Document changes: Keep track of all configuration modifications
  4. Monitor impact: Watch performance after changes
  5. Use maintenance windows: Schedule changes during low-traffic periods
  6. Limit root access: Use specific users for applications
  7. Regular audits: Review user privileges regularly

🔮 Filess.io Specific Features​

Placeholder for Filess.io specific API/UI examples

✅ Checklist​

  • Root credentials secured
  • Configuration files backed up
  • Performance tuning applied
  • Security hardening completed
  • Extensions installed and tested
  • User privileges reviewed
  • Monitoring enabled
  • Documentation updated

With root/superuser access, you have complete control over your MariaDB instance, enabling advanced configurations and optimizations tailored to your specific needs.