dblisted

# Database Skill Full-stack database expertise — relational, document, wide-column, cache, and search. From initial setup to production tuning. **RULE: Show all schema changes, config changes, and migrations before applying. Wait for GO.** --- ## MySQL — Setup & Optimization ### Installation (Ubuntu) ```bash apt-get install -y mysql-server mysql_secure_installation ``` ### Key tuning (my.cnf) ```ini [mysqld] innodb_buffer_pool_size = 1G # 70-80% of RAM for DB-only servers innodb_buffer_pool_instances = 4 # 1 per GB of buffer pool innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 2 # slightly less durable, much faster innodb_flush_method = O_DIRECT query_cache_type = 0 # disabled in MySQL 8+ slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 max_connections = 200 ``` ### Common operations ```sql -- Show running queries SHOW PROCESSLIST; SHOW FULL PROCESSLIST; -- Explain a slow query EXPLAIN SELECT * FROM orders WHERE user_id = 123; EXPLAIN ANALYZE SELECT ...; -- MySQL 8+ -- Check index usage SHOW INDEX FROM table_name; -- Add index ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at); -- Backup mysqldump -u root -p --single-transaction --routines --triggers mydb > backup.sql ``` --- ## PostgreSQL — Setup & Optimization ### Installation (Ubuntu) ```bash apt-get install -y postgresql postgresql-contrib sudo -u postgres psql ``` ### Key tuning (postgresql.conf) `