20 Linux Commands Every Database Administrator Should Know
Master these essential Linux commands to diagnose performance bottlenecks, monitor database processes, track disk usage, analyze logs, and keep your databases running at peak health — with real-world examples you can copy and paste directly.
As a Database Administrator, your terminal is your most powerful diagnostic tool. When a database slows down at 2 AM, you need to diagnose it fast — is it CPU? Memory? Disk I/O? A runaway query? A network bottleneck? The commands in this guide are the ones senior DBAs reach for first, every time.
Each command includes its most useful flags, real DBA output examples, and practical troubleshooting scenarios for MySQL, PostgreSQL, and Oracle environments.
What it does: top displays a continuously updated snapshot of your system's resource usage — CPU load, memory consumption, swap usage, and a ranked list of the most resource-hungry processes. For a DBA, it's the first command to run when a server feels sluggish.
top # launch interactive view top -u mysql # show only mysql user processes top -u postgres # show only postgres user processes top -b -n 1 # batch mode: single snapshot (good for cron scripts) top -d 2 # refresh every 2 seconds
top - 14:32:11 up 42 days, 6:14, 2 users, load average: 4.21, 3.87, 3.54
Tasks: 312 total, 3 running, 309 sleeping
%Cpu(s): 62.4 us, 5.1 sy, 0.0 ni, 30.2 id, 2.1 wa, 0.0 hi
MiB Mem : 64432.0 total, 1248.3 free, 58731.2 used, 4452.5 buff/cache
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4821 mysql 20 0 58.4g 49.2g 8.1g R 94.3 78.2 182:14.33 mysqld
5103 mysql 20 0 58.4g 49.2g 8.1g S 12.1 78.2 18:02.11 mysqld
DBA Red Flag: If %wa (I/O wait) is consistently above 10%, your database is waiting on disk — suspect missing indexes, full table scans, or underpowered storage. If us (user CPU) is above 80%, you likely have long-running or poorly optimized queries.
| Interactive Key | Action |
|---|---|
| P | Sort by CPU usage (default) |
| M | Sort by memory usage |
| 1 | Toggle per-CPU core breakdown |
| k | Kill a process by PID |
| q | Quit |
DBA Trick: Run top -b -n 3 -u mysql > /tmp/top_snapshot.txt to capture 3 snapshots automatically — useful for attaching to incident tickets.
What it does: htop is a more user-friendly alternative to top. It displays CPU bars for each core, scrollable process lists, and allows killing or renicing processes interactively with arrow keys.
htop # launch interactive view htop -u mysql # filter to mysql user htop -p 4821,4822,4823 # monitor specific PIDs only htop --sort-key=PERCENT_MEM # start sorted by memory
DBA Use Case: Use htop -p $(pgrep -d',' mysqld) to monitor all mysqld threads simultaneously. Press F5 for tree view to see parent–child thread relationships — essential for understanding connection pools and background workers.
What it does: df (disk free) shows how much disk space is available and used on each mounted filesystem. For DBAs, this is critical — a full /var or /data partition will crash your database or corrupt tables.
df -h # human-readable sizes (GB, MB) df -hT # include filesystem type (ext4, xfs, nfs) df -h /var/lib/mysql # check specific MySQL data directory df -h /var/lib/postgresql # check PostgreSQL data directory df -i # check inode usage (important for many small files)
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 50G 18G 32G 36% /
/dev/sdb1 500G 481G 19G 97% /var/lib/mysql
/dev/sdc1 200G 45G 155G 23% /backups
tmpfs 32G 256M 32G 1% /dev/shm
Critical Alert: The /var/lib/mysql partition at 97% above is a disaster waiting to happen. MySQL will refuse writes and InnoDB may crash. Set up disk usage alerts at 80% — never let a database partition reach 100%.
#!/bin/bash # Add to cron: */5 * * * * /usr/local/bin/check_disk.sh THRESHOLD=80 df -h | awk 'NR>1 { gsub("%",""); if ($5 > THRESHOLD) print "ALERT: "$6" at "$5"%" }' \ THRESHOLD=$THRESHOLD | mail -s "Disk Alert" dba@yourcompany.com
Pro Tip: Don't forget to check inodes with df -i! A filesystem can run out of inodes even when disk space is available. MySQL's InnoDB with innodb_file_per_table generates one .ibd file per table — thousands of tables = thousands of inodes.
What it does: While df shows filesystem-level usage, du (disk usage) drills into directories to show exactly what's consuming space. DBAs use it to find which databases, tables, or log files are eating disk.
# Show total size of MySQL data directory du -sh /var/lib/mysql/ # Show size of each MySQL database du -h --max-depth=1 /var/lib/mysql/ | sort -rh # Find the top 10 largest tables (InnoDB .ibd files) du -h /var/lib/mysql/*/*.ibd | sort -rh | head -10 # Check binary log sizes (common cause of disk bloat) du -sh /var/lib/mysql/mysql-bin.* 2>/dev/null | sort -rh # PostgreSQL: find largest databases du -h --max-depth=1 /var/lib/postgresql/*/main/base/ | sort -rh
$ du -h --max-depth=1 /var/lib/mysql/ | sort -rh
245G /var/lib/mysql/production_orders
89G /var/lib/mysql/analytics
32G /var/lib/mysql/user_events
14G /var/lib/mysql/mysql
389G /var/lib/mysql/
Pro Tip: Binary logs often grow silently. Ensure expire_logs_days (MySQL 5.x) or binlog_expire_logs_seconds (MySQL 8.x) is set to avoid runaway disk usage.
What it does: iostat (from the sysstat package) reports CPU statistics and I/O statistics for devices and partitions. It's the go-to tool when diagnosing storage bottlenecks — slow queries that access disk heavily will show up here.
iostat -xh 2 # extended stats, human-readable, every 2 seconds iostat -xhd sdb 1 # watch specific device every 1 second iostat -p sdb # per-partition stats for device sdb
Device r/s w/s rMB/s wMB/s await svctm %util
sdb 842.3 1204.7 98.4 148.2 28.3 0.8 99.1
sdc 12.0 24.1 1.2 3.4 1.2 0.5 3.8
Key Metrics: %util near 100% means the disk is saturated. await above 20ms for HDDs or 5ms for SSDs is a concern. svctm is actual service time; if await >> svctm, the device queue is backing up.
vmstat 2 10 # report every 2s, 10 times vmstat -s # memory statistics summary vmstat -d # disk statistics
procs -------memory------ --swap-- ---io--- -system-- ----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa
4 8 171924 23456 4096 1048576 12 18 842 1204 4210 2890 62 6 22 10
DBA Signals: b (blocked processes) > 0 signals I/O contention. si/so (swap in/out) > 0 means you're swapping — terrible for database performance. wa (I/O wait) > 10 indicates disk bottleneck.
What it does: ps (process status) shows a point-in-time snapshot of running processes. Unlike top, it doesn't update continuously — making it ideal for scripting, piping to grep, or capturing process states for logs and tickets.
# Show all MySQL processes with CPU, MEM, and start time ps aux | grep mysqld # Custom columns: PID, user, CPU, memory, elapsed time, command ps -eo pid,user,%cpu,%mem,etime,cmd | grep mysqld # Count number of MySQL connections (threads) ps aux | grep mysqld | wc -l # Find zombie/stuck processes (D = uninterruptible sleep = I/O wait) ps aux | awk '$8 == "D" { print }'
$ ps -eo pid,user,%cpu,%mem,etime,cmd | grep mysqld
PID USER %CPU %MEM ELAPSED CMD
4821 mysql 94.3 78.2 42-06:14:22 /usr/sbin/mysqld --basedir=/usr
4822 mysql 0.1 0.0 42-06:14:21 /usr/sbin/mysqld --basedir=/usr
DBA Trick: Combine ps with watch for a pseudo-live view: watch -n 1 'ps aux | grep mysqld | grep -v grep'. Great for watching connections grow during a traffic spike.
What it does: netstat shows all active network connections, open ports, and socket states. For DBAs, it reveals who's connected to your database, how many connections are in use, and whether there's a connection leak.
# List all listening ports — confirm MySQL (3306) or PG (5432) is listening netstat -tlnp # Count connections by state (ESTABLISHED, TIME_WAIT, CLOSE_WAIT) netstat -an | grep :3306 | awk '{print $6}' | sort | uniq -c | sort -rn # Show which IPs are connecting to MySQL and how many connections each netstat -an | grep 'ESTABLISHED' | grep :3306 | awk '{print $5}' \ | cut -d: -f1 | sort | uniq -c | sort -rn # PostgreSQL: connections to port 5432 netstat -an | grep :5432 | awk '{print $6}' | sort | uniq -c
$ netstat -an | grep :3306 | awk '{print $6}' | sort | uniq -c | sort -rn
312 ESTABLISHED
87 TIME_WAIT
42 CLOSE_WAIT
8 SYN_SENT
Connection Leak Signals: A high count of CLOSE_WAIT connections usually means your application is not properly closing DB connections — a connection leak. Many TIME_WAIT connections are normal after spikes but can pile up with poor connection pooling.
Note: On modern Linux systems, netstat is deprecated. Use ss (command #9) for better performance on servers with thousands of connections.
ss -tlnp # TCP listening ports with process names ss -tnp sport = :3306 # all connections on MySQL port ss -tnp state established 'dport = :5432' # established PG connections ss -s # summary count of connections by state # Connection count to MySQL from each source IP ss -tn state established 'dport = :3306' \ | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -rn
What it does: grep searches for pattern matches in text. For DBAs, it's the primary tool for searching error logs, slow query logs, and configuration files. Master grep and you'll cut incident resolution time in half.
# Find all errors in MySQL error log grep -i "error" /var/log/mysql/error.log # Find crashes, deadlocks, or corruption grep -iE "(crash|deadlock|corrupt|InnoDB: Error)" /var/log/mysql/error.log # Show context: 3 lines before and after each match grep -i -B3 -A3 "deadlock" /var/log/mysql/error.log # Find slow queries over 5 seconds in slow query log grep -A2 "Query_time: [5-9]\|Query_time: [0-9][0-9]" /var/log/mysql/slow.log # Count occurrences of "Access denied" errors grep -c "Access denied" /var/log/mysql/error.log # Search PostgreSQL logs for connection failures grep -i "FATAL\|connection refused\|too many connections" \ /var/log/postgresql/postgresql-*.log # Inverse match — show lines NOT matching a pattern grep -v "Note\|Warning" /var/log/mysql/error.log | grep -i "error" # Live monitoring with color highlight tail -f /var/log/mysql/error.log | grep --color=always -i "error\|warn\|critical"
| Flag | Description |
|---|---|
| -i | Case-insensitive search |
| -r / -R | Recursive directory search |
| -l | Print only filenames that match |
| -c | Count of matching lines |
| -v | Invert match (show non-matching lines) |
| -E | Extended regex (use | for OR) |
| -o | Print only the matched part of the line |
| -B N / -A N | N lines before / after each match |
| -n | Print line numbers |
# Extract query times from slow query log and calculate average awk '/Query_time/ { sum += $2; count++ } END { print "Avg:", sum/count "s" }' \ /var/log/mysql/slow.log # Print the top query patterns (the actual SQL) from slow log awk '/^(SELECT|INSERT|UPDATE|DELETE)/ { print }' /var/log/mysql/slow.log \ | sort | uniq -c | sort -rn | head -20 # Parse PostgreSQL CSV logs — extract duration and query awk -F',' '$13 > 5000 { print $13"ms:", $15 }' /var/log/postgresql/pg.csv # Count error types by hour from MySQL log awk '/ERROR/ { match($0, /[0-9]{2}:[0-9]{2}/, t); errors[t[0]]++ } END { for (h in errors) print h, errors[h] }' /var/log/mysql/error.log | sort
# Follow MySQL error log live (Ctrl+C to stop) tail -f /var/log/mysql/error.log # Follow multiple logs simultaneously tail -f /var/log/mysql/error.log /var/log/mysql/slow.log # Show last 100 lines, then follow tail -n 100 -f /var/log/mysql/error.log # Follow with grep — live filtered view tail -f /var/log/mysql/error.log | grep --line-buffered -i "error\|warn" # PostgreSQL: follow all logs tail -f /var/log/postgresql/postgresql-$(date +%Y-%m-%d).log
Pro Tip: Use multitail (if installed) to follow multiple log files in split-screen terminal panels — invaluable during incidents when you need to watch the MySQL error log and system journal at the same time.
# Find all MySQL config files on the system find / -name "my.cnf" -o -name "my.ini" 2>/dev/null # Find large .ibd files (tables over 1 GB) find /var/lib/mysql -name "*.ibd" -size +1G -exec du -sh {} \; | sort -rh # Find backup files older than 30 days to clean up find /backups -name "*.sql.gz" -mtime +30 -delete # Find files with wrong permissions in MySQL data dir find /var/lib/mysql ! -user mysql -ls # Find core dump files that MySQL may have created find / -name "core.*" -user mysql -mtime -7 2>/dev/null
# List all files opened by MySQL process lsof -u mysql # Count open file descriptors (compare against ulimit) lsof -u mysql | wc -l # Check which process is using MySQL port 3306 lsof -i :3306 # Find deleted files still held open by MySQL lsof -u mysql | grep "deleted"
Deleted-but-open files: You delete a large binary log to free space, but df still shows the disk full — because MySQL still holds the file open. lsof | grep deleted reveals these ghost files. Restarting MySQL releases them.
# Attach to running MySQL process and trace I/O calls strace -p $(pidof mysqld | awk '{print $1}') -e trace=read,write # Trace with timestamps and output to file strace -p 4821 -tt -e trace=open,read,write,close -o /tmp/strace_mysql.txt # Count system call frequency (profile mode) strace -p 4821 -c -f
Warning: strace can add significant overhead — use only on non-production systems or during controlled maintenance windows. For production diagnosis, prefer perf or MySQL's Performance Schema instead.
What it does: sar collects and saves system activity data. Unlike top (which only shows the current moment), sar lets you go back in time and see what happened at 3 AM when the DBA was asleep. Part of the sysstat package.
# CPU usage for today, every 10 minutes sar -u 10 # CPU usage for a specific past date (day 20 of current month) sar -u -f /var/log/sysstat/sa20 # Memory usage history sar -r -f /var/log/sysstat/sa20 # Disk I/O history sar -b -f /var/log/sysstat/sa20
DBA Use Case: A user complains "the database was slow at 3 AM." Use sar -u -r -b -f /var/log/sysstat/sa$(date +%d -d yesterday) to pull CPU, memory, and I/O stats for yesterday and pinpoint the exact degradation window.
free -h # human-readable free -h -s 2 # update every 2 seconds free -m # in megabytes
total used free shared buff/cache available
Mem: 62Gi 57Gi 1.2Gi 256Mi 4.1Gi 4.8Gi
Swap: 8.0Gi 171Mi 7.8Gi
Key Insight: Don't panic if "free" memory is low — Linux uses spare RAM as disk cache (buff/cache), which benefits databases. The "available" column is what matters. If "available" is near 0 and swap is being used, you have a real memory problem.
ulimit -a # show all current limits ulimit -n # show max open files (critical for MySQL) ulimit -n 65536 # set open files limit (current shell) # Check limits of a running MySQL process cat /proc/$(pidof mysqld | awk '{print $1}')/limits | grep -i "open files" # Set permanent limits (add to /etc/security/limits.conf) echo "mysql soft nofile 65536" >> /etc/security/limits.conf echo "mysql hard nofile 65536" >> /etc/security/limits.conf
Critical for MySQL: MySQL uses one file descriptor per table when innodb_file_per_table=ON. If the open file limit is too low (default is often 1024), MySQL will log "Too many open files" errors. Set nofile to at least 65536 for production MySQL servers.
# Follow MySQL service logs live journalctl -u mysql -f # Show MySQL logs since last boot journalctl -u mysql -b # Show logs for a specific time window journalctl -u mysql --since "2025-03-20 02:00:00" --until "2025-03-20 04:00:00" # MOST IMPORTANT: Find Out-of-Memory kills (OOM killer) journalctl -k | grep -i "oom\|killed process" # Check why MySQL service stopped/restarted journalctl -u mysql --no-pager | grep -i "start\|stop\|fail\|error"
OOM Killer: The Linux OOM killer terminates processes to free memory — and mysqld is often a prime target due to its large memory footprint. If your MySQL keeps mysteriously dying, journalctl -k | grep -i oom will confirm it. Fix: tune innodb_buffer_pool_size down, or add more RAM.
What it does: sysctl reads and modifies kernel parameters at runtime. For DBAs, it's used to tune OS-level settings that directly impact database performance — network buffers, memory behavior, I/O scheduling, and file descriptor limits.
# KEY DBA TUNING: Reduce swappiness (0-10 for database servers) sysctl -w vm.swappiness=10 # Increase max open files system-wide sysctl -w fs.file-max=2097152 # Tune network buffers (good for high-throughput replication) sysctl -w net.core.rmem_max=134217728 sysctl -w net.core.wmem_max=134217728 # View all DB-relevant parameters at once sysctl -a 2>/dev/null | grep -E "swappiness|hugepages|file-max|overcommit|somaxconn"
# Add to /etc/sysctl.conf for persistence across reboots cat >> /etc/sysctl.conf << 'EOF' # MySQL/PostgreSQL performance tuning vm.swappiness = 10 vm.dirty_ratio = 15 vm.dirty_background_ratio = 5 fs.file-max = 2097152 net.core.somaxconn = 65535 EOF # Apply without reboot sysctl -p
vm.swappiness: On database servers, set this to 1–10 (not 0). A low non-zero value tells the kernel to strongly prefer physical RAM but still use swap as an absolute last resort — avoiding an OOM kill if memory is exhausted.
DBA Command Cheat Sheet
Copy-paste ready commands for the most common DBA scenarios
0 comments:
Post a Comment