MariaDB — Common Queries and Admin Reference

Connection

mysql -u [user] -p [database]

Useful Admin Queries

-- Show all databases
SHOW DATABASES;

-- Show all tables
SHOW TABLES;

-- Table structure
DESCRIBE table_name;
SHOW CREATE TABLE table_name;

-- Current connections
SHOW PROCESSLIST;

-- Table sizes
SELECT table_name, 
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY data_length DESC;

Common 1c3d Queries

-- Recent nfo posts
SELECT id, title, type, category, status, published_at
FROM nfo_posts ORDER BY id DESC LIMIT 20;

-- Count by category
SELECT category, COUNT(*) as total
FROM nfo_posts WHERE type='howto' AND status='published'
GROUP BY category;

-- Recent users
SELECT id, username, email, role, created_at
FROM users ORDER BY id DESC LIMIT 10;

-- API key listing
SELECT id, name, scope, created_at FROM api_keys;

Backup

mysqldump -u [user] -p [database] > backup_$(date +%Y%m%d).sql

Restore

mysql -u [user] -p [database] < backup.sql

JSON Column Queries

-- Search JSON config field
SELECT * FROM settings WHERE JSON_EXTRACT(value, '$.key') = 'value';

-- Update JSON field
UPDATE settings SET value = JSON_SET(value, '$.key', 'new_value');

Performance

-- Explain a slow query
EXPLAIN SELECT * FROM nfo_posts WHERE category='cnc';

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