phpMyAdmin is visual and familiar. It also chokes on large databases, times out on slow connections, and turns a 30-second job into a 20-minute ordeal.
The command line doesn’t have these problems. Copy the command, change the database name and filename, press Enter. Done in seconds regardless of database size.
Variables Used in This Reference
Before copying any command, identify these values for your setup:
DB_NAME = your database name (e.g. wordpress_site)
DB_USER = your database user (e.g. wp_user or root)
BACKUP_FILE = your backup filename (e.g. wordpress_site_2026-06-08.sql.gz)BACKUP COMMANDS
Basic backup — single database
mysqldump -u root -p DB_NAME > backup.sqlBackup with gzip compression (recommended)
mysqldump -u root -p DB_NAME | gzip > DB_NAME_$(date +%Y-%m-%d).sql.gzThe $(date +%Y-%m-%d) part automatically adds today’s date to the filename.
Example output: wordpress_site_2026-06-08.sql.gz
Backup with specific username (not root)
mysqldump -u DB_USER -p DB_NAME | gzip > DB_NAME_$(date +%Y-%m-%d).sql.gzBackup all databases at once
mysqldump -u root -p --all-databases | gzip > all_databases_$(date +%Y-%m-%d).sql.gzBackup specific tables only
mysqldump -u root -p DB_NAME wp_posts wp_postmeta | gzip > posts_only_$(date +%Y-%m-%d).sql.gzBackup without locking tables (for busy sites)
mysqldump -u root -p --single-transaction DB_NAME | gzip > DB_NAME_$(date +%Y-%m-%d).sql.gz--single-transaction takes a consistent snapshot without locking tables — important for sites with active writes.
Backup structure only (no data)
mysqldump -u root -p --no-data DB_NAME > DB_NAME_structure_only.sqlBackup data only (no CREATE TABLE statements)
mysqldump -u root -p --no-create-info DB_NAME | gzip > DB_NAME_data_only.sql.gzRESTORE COMMANDS
Restore from uncompressed .sql file
mysql -u root -p DB_NAME < backup.sqlRestore from compressed .sql.gz file
gunzip -c BACKUP_FILE | mysql -u root -p DB_NAMERestore all databases from —all-databases backup
gunzip -c all_databases_backup.sql.gz | mysql -u root -pRestore to a different database name
# First create the new database
mysql -u root -p -e "CREATE DATABASE new_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
# Then restore into it
gunzip -c BACKUP_FILE | mysql -u root -p new_db_nameDATABASE MANAGEMENT COMMANDS
Log into MariaDB
# As root
mysql -u root -p
# As specific user
mysql -u DB_USER -p DB_NAMEShow all databases
mysql -u root -p -e "SHOW DATABASES;"Create a new database
mysql -u root -p -e "CREATE DATABASE DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"Drop a database (permanent — be careful)
mysql -u root -p -e "DROP DATABASE DB_NAME;"Create a database user
mysql -u root -p -e "CREATE USER 'DB_USER'@'localhost' IDENTIFIED BY 'password';"
mysql -u root -p -e "GRANT ALL PRIVILEGES ON DB_NAME.* TO 'DB_USER'@'localhost';"
mysql -u root -p -e "FLUSH PRIVILEGES;"Show database size
mysql -u root -p -e "
SELECT table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;"Show largest tables in a database
mysql -u root -p -e "
SELECT table_name AS 'Table',
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'DB_NAME'
ORDER BY (data_length + index_length) DESC
LIMIT 10;"WORDPRESS-SPECIFIC COMMANDS
Search and replace URL in database (after migration)
# Find all instances of old URL
mysql -u root -p DB_NAME -e "SELECT * FROM wp_options WHERE option_value LIKE '%old-domain.com%' LIMIT 5;"
# Replace — use WP-CLI for safety on large databases
wp search-replace 'https://old-domain.com' 'https://new-domain.com' --all-tablesReset WordPress admin password via SQL
mysql -u root -p DB_NAME -e "
UPDATE wp_users
SET user_pass = MD5('new_password_here')
WHERE user_login = 'admin_username';"Check WordPress table sizes (find bloat)
mysql -u root -p -e "
SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'MB'
FROM information_schema.tables
WHERE table_schema = 'DB_NAME'
AND table_name IN ('wp_options','wp_postmeta','wp_posts','wp_comments','wp_usermeta')
ORDER BY (data_length + index_length) DESC;"A bloated wp_options table (over 10MB) usually means autoloaded data from plugins. A bloated wp_postmeta means post revision data. Both slow down WordPress.
Clean up WordPress post revisions
mysql -u root -p DB_NAME -e "
DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);"QUICK REFERENCE CARD
# BACKUP one database
mysqldump -u root -p DBNAME | gzip > DBNAME_$(date +%Y-%m-%d).sql.gz
# BACKUP all databases
mysqldump -u root -p --all-databases | gzip > all_$(date +%Y-%m-%d).sql.gz
# RESTORE one database
gunzip -c BACKUPFILE.sql.gz | mysql -u root -p DBNAME
# CHECK database sizes
mysql -u root -p -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) AS MB FROM information_schema.tables GROUP BY table_schema;"
# LOG IN to MariaDB
mysql -u root -pphpMyAdmin vs Command Line
| Scenario | phpMyAdmin | Command Line |
|---|---|---|
| Database under 50MB | Fine | Fine |
| Database 100MB–1GB | Slow, may timeout | Fast, no timeout |
| Database over 1GB | Often fails | Handles fine |
| Automated/scheduled | Not possible | cron job |
| Remote server | Requires web access | SSH only |
| Speed (1GB database) | 10–30 minutes | Under 1 minute |
Once you’ve done a restore from the command line and watched a large database import in seconds, phpMyAdmin for database operations stops making sense.