Skip to main content

Database Management Use Cases (WordPress VPS)

Database operations are where most WordPress incidents become irreversible: restoring to the wrong DB, dropping a table, or migrating without verification. This page provides practical use cases and safe command patterns that you can reuse. Treat these as playbooks: preflight, execute, then verify.

Quick Summary

When something breaks, start with a simple DB connection check (SELECT 1;). When performance degrades, check running threads and active queries. When migrating, dump + compress + restore + verify.

Decision Flow​

Use Case 1: Quick DB Connectivity Check​

Goal: confirm credentials and basic query execution.

db-connectivity-check.sh
mysql -u wpuser -p -D wordpress -e "SELECT 1 AS ok;"

Expected output:

example-output-db-connectivity.txt
ok
1

Next step: if this fails, review credentials and privileges.

Use Case 2: WordPress "Error Establishing a Database Connection"​

Goal: confirm you can connect as the WordPress user and the database exists.

db-validate-wordpress-user.sh
mysql -u wpuser -p -e "SHOW DATABASES;"

Expected output:

example-output-show-databases.txt
Database
information_schema
mysql
wordpress

Next step: confirm DB_NAME, DB_USER, and DB_HOST in wp-config.php match reality.

Use Case 3: Database Disk Growth (Capacity Alert)​

Goal: confirm whether the DB volume is filling up.

db-check-db-volume-space.sh
df -h /var/lib/mysql

Expected output:

example-output-df-mysql.txt
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       100G   95G    5G  95% /var/lib/mysql

Next step: identify which tables/logs are growing and confirm backup retention.

Use Case 4: Slow WordPress (DB Under Pressure)​

Goal: see if there are many running queries or long-running queries.

db-check-threads-running.sh
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"

Expected output:

example-output-threads-running.txt
Variable_name	Value
Threads_running	25

Then check active queries:

db-processlist.sh
mysql -u root -p -e "SHOW FULL PROCESSLIST;"

Expected output (excerpt):

example-output-processlist.txt
Id	User	Host	db	Command	Time	State	Info
10	wpuser	localhost	wordpress	Query	5	Sending data	SELECT ...

Next step: enable slow query log for a short window and identify offenders.

Use Case 5: Safe Database Migration (Dump + Restore)​

Goal: export, transfer, restore, and verify a WordPress database.

db-migration-dump-and-compress.sh
mysqldump -u wpuser -p --single-transaction --quick wordpress | gzip -9 > wordpress.sql.gz

Expected output:

example-output-dump-compress.txt
(no output on success)

Restore:

db-migration-restore.sh
gzip -dc wordpress.sql.gz | mysql -u root -p wordpress

Expected output:

example-output-restore.txt
(no output on success)

Verify tables:

db-migration-verify.sh
mysql -u wpuser -p -D wordpress -e "SHOW TABLES;"

Expected output:

example-output-verify-tables.txt
Tables_in_wordpress
wp_options
wp_posts
...

Next step: validate site URL settings and run application-level checks.

warning

Never restore into production without verifying the database name, host, and backup file.

Best Practices​

  • Always preflight: confirm host + database + user + path.
  • For live sites, prefer --single-transaction for InnoDB.
  • Verify after every restore: table list + key option values.

What's Next​