Database Management

Complete guide to database operations with svp.

Overview

svp automatically creates and configures MariaDB databases for each site with:

✅ Secure random passwords
✅ Per-site database and user
✅ Proper permissions
✅ Automatic configuration in CMS

Database Creation

Automatic Setup

When provisioning a site, svp automatically:

Creates database
Creates database user
Generates secure password
Grants proper permissions
Configures CMS settings

Database Naming

Databases are named based on domain:

example.com           → drupal_example_com
staging.example.com   → drupal_staging_example_com
myblog.com            → wordpress_myblog_com

Pattern: {cms}_{domain_with_underscores}

Database Credentials

View Credentials

Credentials are stored in /etc/svp/sites/:

sudo cat /etc/svp/sites/example.com.db.txt

Output:

Database: drupal_example_com
Username: drupal_example_com
Password: [secure-random-password]
Host: localhost
Port: 3306

Access Database

Using stored credentials:

# Read credentials
source /etc/svp/sites/example.com.db.txt

# Connect to database
mysql -u "$Username" -p"$Password" "$Database"

Or directly:

# Drupal
drush-example.com sql-cli

# WordPress
cd /var/www/myblog.com
sudo -u admin wp db cli

Database Import

During Initial Setup

Import database during provisioning:

sudo svp setup example.com \
  --cms drupal \
  --git-repo https://github.com/myorg/mysite.git \
  --db /path/to/backup.sql.gz \
  --le-email admin@example.com

Supported formats:

.sql - Plain SQL dump
.sql.gz - Gzip compressed dump

After Setup

Import database after site is provisioned:

# Drupal
cd /var/www/example.com
drush-example.com sql-drop -y
zcat /path/to/backup.sql.gz | drush-example.com sql-cli

# WordPress
cd /var/www/myblog.com
sudo -u admin wp db reset --yes
zcat /path/to/backup.sql.gz | sudo -u admin wp db cli

Manual Import

Using mysql directly:

# Uncompressed
mysql -u drupal_example_com -p drupal_example_com < backup.sql

# Compressed
zcat backup.sql.gz | mysql -u drupal_example_com -p drupal_example_com

Database Export

Export Database

Drupal:

# Plain SQL
drush-example.com sql-dump > backup.sql

# Compressed
drush-example.com sql-dump --gzip > backup-$(date +%Y%m%d).sql.gz

# With structure only (no data)
drush-example.com sql-dump --structure > structure.sql

# Specific tables
drush-example.com sql-dump --tables=node,users > backup.sql

WordPress:

cd /var/www/myblog.com

# Plain SQL
sudo -u admin wp db export backup.sql

# Compressed
sudo -u admin wp db export backup.sql
gzip backup.sql

# Add timestamp
sudo -u admin wp db export backup-$(date +%Y%m%d).sql

Automated Backups

Create /usr/local/bin/backup-db.sh:

#!/bin/bash
DOMAIN="example.com"
BACKUP_DIR="/backups/databases"
DATE=$(date +%Y%m%d-%H%M%S)

mkdir -p $BACKUP_DIR

# Backup database
drush-$DOMAIN sql-dump --gzip > $BACKUP_DIR/$DOMAIN-$DATE.sql.gz

# Keep only last 30 days
find $BACKUP_DIR -name "$DOMAIN-*.sql.gz" -mtime +30 -delete

echo "Backup complete: $BACKUP_DIR/$DOMAIN-$DATE.sql.gz"

Make executable:

sudo chmod +x /usr/local/bin/backup-db.sh

Schedule with cron:

sudo crontab -e

# Daily backup at 2 AM
0 2 * * * /usr/local/bin/backup-db.sh

Database Operations

List Databases

mysql -e "SHOW DATABASES;"

Database Size

mysql -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;"

Optimize Database

Drupal:

# Via Drush
drush-example.com sql-query "OPTIMIZE TABLE cache_bootstrap, cache_config, cache_data, cache_default, cache_discovery, cache_dynamic_page_cache, cache_entity, cache_menu, cache_page, cache_render;"

# Or specific tables
drush-example.com sql-query "OPTIMIZE TABLE cache_render;"

WordPress:

cd /var/www/myblog.com
sudo -u admin wp db optimize

Manual:

mysql -u drupal_example_com -p drupal_example_com -e "OPTIMIZE TABLE table_name;"

Repair Database

# WordPress
cd /var/www/myblog.com
sudo -u admin wp db repair

# Manual
mysql -u drupal_example_com -p drupal_example_com -e "REPAIR TABLE table_name;"

Database Users

View Users

mysql -e "SELECT User, Host FROM mysql.user WHERE User LIKE 'drupal%' OR User LIKE 'wordpress%';"

Change Password

# Generate new password
NEW_PASS=$(openssl rand -base64 32)

# Update in MySQL
mysql -e "ALTER USER 'drupal_example_com'@'localhost' IDENTIFIED BY '$NEW_PASS';"

# Update credentials file
sudo sed -i "s/Password: .*/Password: $NEW_PASS/" /etc/svp/sites/example.com.db.txt

# Update Drupal settings
sudo sed -i "s/'password' => '.*'/'password' => '$NEW_PASS'/" /var/www/example.com/web/sites/default/settings.svp.php

Grant Permissions

mysql -e "GRANT ALL PRIVILEGES ON drupal_example_com.* TO 'drupal_example_com'@'localhost';"
mysql -e "FLUSH PRIVILEGES;"

Database Migrations

Migrate to New Database

1. Export from old database:

mysqldump -u old_user -p old_database | gzip > migration.sql.gz

2. Create new database:

sudo svp setup --cms drupal --domain example.com

3. Import to new database:

zcat migration.sql.gz | drush-example.com sql-cli

Migrate Between Servers

Old server:

# Export database
drush-example.com sql-dump --gzip > export.sql.gz

# Transfer to new server
scp export.sql.gz user@new-server:/tmp/

New server:

# Import database
zcat /tmp/export.sql.gz | drush-example.com sql-cli

Database Configuration

MariaDB Configuration

Main config: /etc/mysql/mariadb.conf.d/50-server.cnf

Important settings:

[mysqld]
max_connections = 100
innodb_buffer_pool_size = 512M
query_cache_size = 64M

Restart after changes:

sudo systemctl restart mariadb

Per-Site Settings

Drupal (/var/www/example.com/web/sites/default/settings.svp.php):

$databases['default']['default'] = [
  'database' => 'drupal_example_com',
  'username' => 'drupal_example_com',
  'password' => '[password]',
  'host' => 'localhost',
  'port' => '3306',
  'driver' => 'mysql',
  'prefix' => '',
  'collation' => 'utf8mb4_general_ci',
];

WordPress (/var/www/myblog.com/wp-config.php):

define('DB_NAME', 'wordpress_myblog_com');
define('DB_USER', 'wordpress_myblog_com');
define('DB_PASSWORD', '[password]');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

Database Reprovisioning

Fresh Database

Drop and recreate database:

# Reprovision with fresh database
sudo svp setup example.com --cms drupal

# When prompted about existing directory:
Delete and reprovision? [y/N]: y

Creates:

New database
New credentials
Fresh installation

Keep Existing Database

Reprovision but reuse database:

sudo svp setup example.com --cms drupal --keep-existing-db

This:

Keeps same database name
Keeps same credentials
Drops all tables
Reinstalls fresh CMS

Use when:

You want to test reinstall
You need to keep credentials stable
Automated scripts depend on fixed credentials

Troubleshooting

Can’t Connect to Database

Check credentials:

sudo cat /etc/svp/sites/example.com.db.txt

Test connection:

mysql -u drupal_example_com -p
# Enter password from credentials file

Check MariaDB is running:

sudo systemctl status mariadb

Database Import Fails

Error: “Access denied”

Check user permissions:

mysql -e "SHOW GRANTS FOR 'drupal_example_com'@'localhost';"

Error: “Table already exists”

Drop tables first:

drush-example.com sql-drop -y

Database Too Large

Check size:

drush-example.com sql-query "SELECT table_name, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' 
  FROM information_schema.TABLES 
  WHERE table_schema = 'drupal_example_com' 
  ORDER BY (data_length + index_length) DESC 
  LIMIT 10;"

Clear cache tables:

drush-example.com sql-query "TRUNCATE cache_bootstrap;"
drush-example.com sql-query "TRUNCATE cache_render;"
# Repeat for other cache tables

Corrupt Tables

Check tables:

drush-example.com sql-query "CHECK TABLE table_name;"

Repair:

drush-example.com sql-query "REPAIR TABLE table_name;"

Performance Tuning

Optimize Queries

Enable slow query log:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Restart:

sudo systemctl restart mariadb

View slow queries:

sudo tail -f /var/log/mysql/slow.log

Add Indexes

Find missing indexes:

drush-example.com sql-query "
  SELECT * FROM information_schema.TABLES 
  WHERE table_schema = 'drupal_example_com' 
  AND index_length = 0;"

← Git Deployment

PHP Version Management →