MariaDB — Common Queries and Admin Reference

MariaDB — Common Queries and Admin Reference Connection bash mysql -u [user] -p [database] Useful Admin Queries sql - Show all databases SHOW DATABASES; - Show all tables SHOW TABLES; - Table structure DESCRIBE tablename; SHOW CREATE TABLE tablename; - Current connections SHOW PROCESSLIST; - Table sizes SELECT tablename, ROUND(datalength/1024/1024, 2) AS datamb, ROUND(indexlength/1024/1024, 2) AS indexmb FROM informationschema.tables WHERE tableschema = 'yourdb' ORDER BY datalength DESC; Common 1c3d Queries sql - Recent nfo posts SELECT id, title, type, category, status, publishedat FROM nfoposts ORDER BY id DESC LIMIT 20; - Count by category SELECT category, COUNT() as total FROM nfoposts WHERE type='howto' AND status='published' GROUP BY category; - Recent users SELECT id, username, email, role, createdat FROM users ORDER BY id DESC LIMIT 10; - API key listing SELECT id, name, scope, createdat FROM apikeys; Backup bash mysqldump -u [user] -p [database] > backup$(date +%Y%m%d).sql Restore bash mysql -u [user] -p [database] < backup.sql JSON Column Queries sql - Search JSON config field SELECT FROM settings WHERE JSONEXTRACT(value, '$.key') = 'value'; - Update JSON field UPDATE settings SET value = JSONSET(value, '$.key', 'newvalue'); Performance sql - Explain a slow query EXPLAIN SELECT FROM nfoposts WHERE category='cnc'; - Show slow query log status SHOW VARIABLES LIKE 'slowquery%';

Related Guides

Loading_