Skip to main content

Database Environments

Configure and manage external database connections for storing middleware data.

Overview​

GSM Middleware uses a separate database (external or WordPress) to store order, inventory, and configuration data. Database Environments allow you to:

  • Configure multiple database connections
  • Switch between environments (live, beta, dev, local)
  • Test connections before saving
  • Keep production and testing data separate

Location: WordPress Admin → GSM Middleware → Settings → DB Environments

Why External Database?​

Benefits​

  1. Separation of Concerns: Middleware data separate from WordPress content
  2. Performance: Dedicated database for high-volume operations
  3. Scalability: Scale database independently
  4. Backup: Separate backup strategies
  5. Security: Isolated credentials and access control

When to Use WordPress Database​

Use WordPress database for:

  • Development/testing
  • Low volume sites (< 100 orders/day)
  • Single-server setups
  • Simplified deployment

Use External database for:

  • Production environments
  • High volume sites (> 100 orders/day)
  • Multi-server architectures
  • Compliance/security requirements

Environment Types​

EnvironmentPurposeUse Case
liveProductionReal customer orders
betaStaging/UATPre-production testing
devDevelopmentFeature development
localLocal TestingDeveloper workstations

Creating an Environment​

Step 1: Prepare Database​

Create database and user:

-- Create database
CREATE DATABASE gsm_middleware CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user
CREATE USER 'gsm_user'@'%' IDENTIFIED BY 'strong_password_here';

-- Grant permissions
GRANT ALL PRIVILEGES ON gsm_middleware.* TO 'gsm_user'@'%';
FLUSH PRIVILEGES;

For production, use more restrictive permissions:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER ON gsm_middleware.* TO 'gsm_user'@'%';

Step 2: Add Environment in Plugin​

  1. Go to GSM Middleware → Settings
  2. Click "DB Environments" tab
  3. Click "Add Environment"
  4. Fill in the form:

Basic Settings:

  • Name: Production Database
  • Environment: live
  • Host: db-server.example.com or localhost
  • Port: 3306 (default MySQL port)
  • Database Name: gsm_middleware
  • Username: gsm_user
  • Password: Database password

Advanced Settings:

  • Charset: utf8mb4 (recommended)
  • Collation: utf8mb4_unicode_ci
  • SSL: Enable if database requires SSL

Step 3: Test Connection​

  1. Click "Test Connection" button
  2. Wait for result:
    • ✅ Success: Shows server version and database info
    • ❌ Failure: Shows error message

Success example:

✓ Connection successful
Server: MySQL 8.0.32-0ubuntu0.22.04.2
Database: gsm_middleware
Tables: 15
Charset: utf8mb4

Failure example:

✗ Connection failed
Error: Access denied for user 'gsm_user'@'hostname'

Step 4: Initialize Tables​

If database is empty, tables will be created automatically on first activation:

Creating tables...
✓ rm_sites created
✓ rm_order created
✓ rm_address created
✓ rm_lineitems created
✓ rm_items created
✓ rm_kits created
✓ rm_error_orders created
✓ rm_order_import_errors created
✓ rm_db_environments created
✓ rm_api_connections created

All tables created successfully.

Step 5: Activate Environment​

  1. Click "Activate" button
  2. Confirm activation
  3. Environment badge shows "ACTIVE"
  4. All sync operations now use this database

Managing Environments​

Edit Environment​

  1. Find environment in the list
  2. Click "Edit"
  3. Modify settings
  4. Click "Test Connection"
  5. Click "Save"

⚠️ Note: Can't edit active environment. Deactivate first or activate another.

Switch Environments​

  1. Click "Activate" on desired environment
  2. Confirm switch
  3. Previous environment automatically deactivates
  4. All operations now use new environment

Use cases:

  • Switch to beta for testing
  • Switch back to live after testing
  • Use dev environment for development

Delete Environment​

⚠️ Warning: Deleting an environment does NOT delete the database or tables!

  1. Deactivate environment if active
  2. Click "Delete"
  3. Confirm deletion
  4. Environment configuration removed from plugin

The database and data remain intact - you can re-add the environment later.

Clone Environment​

Copy an environment configuration:

  1. Click "Clone" next to environment
  2. New environment created with same settings
  3. Modify name and environment type
  4. Save

Useful for:

  • Creating beta from live config
  • Setting up multiple development environments
  • Migrating settings

Connection Details​

Hostname/IP Address​

Options:

  • localhost - Same server as WordPress
  • 127.0.0.1 - Loopback (same server)
  • 192.168.1.100 - Private network IP
  • db-server.example.com - DNS hostname
  • db.example.com:3307 - Custom port

Considerations:

  • Use localhost for same-server (uses Unix socket, faster)
  • Use IP for static addressing
  • Use hostname for flexibility (DNS can change)

Port Number​

Default: 3306

Custom ports:

  • 3307 - Common alternative
  • 3308 - Another alternative
  • Check with database administrator

SSL Connections​

Enable SSL for:

  • Remote database connections
  • Compliance requirements (PCI-DSS, HIPAA)
  • Public network connections

Configuration:

  1. Enable "Use SSL" checkbox
  2. Provide SSL certificates (if required):
    • CA Certificate
    • Client Certificate
    • Client Key

Connection Pooling​

For high-traffic sites, consider:

  • Persistent connections
  • Connection pooling (ProxySQL, MaxScale)
  • Read replicas for reporting

Environment Indicators​

Visual Badges​

Each environment shows a colored badge:

BadgeEnvironmentColor
🔴 LIVEProductionRed
🟡 BETAStagingYellow
🔵 DEVDevelopmentBlue
⚪ LOCALLocalGray

Status Icons​

  • ✅ Active: Currently in use
  • ⏸️ Inactive: Configured but not in use
  • ⚠️ Warning: Connection issues
  • ❌ Error: Connection failed

Database Maintenance​

Backup Environments​

Automated backups:

# Daily backup script
mysqldump -h db-server.example.com -u gsm_user -p gsm_middleware > backup_$(date +%Y%m%d).sql

# Compress
gzip backup_$(date +%Y%m%d).sql

# Copy to backup location
cp backup_$(date +%Y%m%d).sql.gz /backups/

Restore:

gunzip backup_20260317.sql.gz
mysql -h db-server.example.com -u gsm_user -p gsm_middleware < backup_20260317.sql

Archive Old Data​

Keep database performant by archiving old records:

-- Archive orders older than 90 days
CREATE TABLE rm_order_archive LIKE rm_order;
INSERT INTO rm_order_archive SELECT * FROM rm_order WHERE date < DATE_SUB(NOW(), INTERVAL 90 DAY);
DELETE FROM rm_order WHERE date < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Same for line items
CREATE TABLE rm_lineitems_archive LIKE rm_lineitems;
INSERT INTO rm_lineitems_archive SELECT * FROM rm_lineitems WHERE number IN (SELECT number FROM rm_order_archive);
DELETE FROM rm_lineitems WHERE number IN (SELECT number FROM rm_order_archive);

Optimize Tables​

Run periodically to reclaim space and improve performance:

OPTIMIZE TABLE rm_order, rm_lineitems, rm_items, rm_address;

Monitor Database Size​

SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.TABLES 
WHERE table_schema = 'gsm_middleware'
ORDER BY (data_length + index_length) DESC;

Troubleshooting​

Can't Connect to Database​

Error: "Can't connect to MySQL server"

Check:

  1. Database server is running
  2. Firewall allows connections on port 3306
  3. Hostname/IP is correct
  4. MySQL is listening on correct port

Test connectivity:

# Test port
telnet db-server.example.com 3306

# Try connecting
mysql -h db-server.example.com -u gsm_user -p

Access Denied Error​

Error: "Access denied for user"

Check:

  1. Username is correct
  2. Password is correct
  3. User has permissions on database
  4. User can connect from your server's IP

Grant access from specific IP:

GRANT ALL PRIVILEGES ON gsm_middleware.* TO 'gsm_user'@'192.168.1.10';

Tables Not Created​

After activation, no tables exist

Causes:

  • User lacks CREATE permission
  • Database charset incompatible

Solutions:

  1. Grant CREATE permission
  2. Check database charset is utf8mb4
  3. Manually run schema SQL file

Slow Connection​

Connection takes > 5 seconds

Check:

  1. Network latency (ping database server)
  2. Database server load
  3. DNS resolution time

Solutions:

  1. Use IP instead of hostname
  2. Optimize database server
  3. Consider local database

Environment Won't Activate​

Causes:

  • Connection test failed
  • Another environment has lock
  • Database permissions issue

Solutions:

  1. Test connection first
  2. Deactivate other environment
  3. Verify all permissions

Security Best Practices​

Credentials​

  • Use strong, unique passwords
  • Rotate passwords every 90 days
  • Never commit credentials to version control
  • Use environment variables in production

Network Security​

  • Use SSL for remote connections
  • Restrict access to specific IPs
  • Use VPN for sensitive data
  • Enable MySQL firewall rules

Access Control​

  • Grant minimum required permissions
  • Use separate users for different environments
  • Audit database access logs
  • Disable remote root access

Encryption​

  • Enable encryption at rest
  • Use SSL/TLS for connections
  • Encrypt backups
  • Secure backup storage

Next Steps​