This guide explains three connection scenarios for the Custom MySQL Database feature that archives WooCommerce orders to a MySQL server. Each scenario includes prerequisites, exact steps, example commands, and troubleshooting/security notes.

Scenario 1: Connect to a new or existing database on the same server #

When to use: You want a dedicated archive database on the same MySQL server that already hosts your WordPress DB.

How it works (automatic flow) #

1. The system uses the existing WordPress DB credentials (username, password, host, port).
2. You provide only the archive database name.
3. If the database does not exist, the plugin attempts to create it.
4. Plugin creates the required table structure (e.g., wp_archived_orders) and confirms the connection.

Prerequisites: WordPress DB user must have CREATE DATABASE privilege. #

Steps (user-facing) #

1. In plugin settings, choose Custom MySQL and select the “Same server” option.
2. Enter the database name to use (example: woocommerce_archive).
3. Save/apply settings.
4. Plugin will report success or an error message indicating missing privileges.

What happens behind the scenes (technical) #

• Connects to MySQL using WordPress credentials from wp-config.php.
• Checks for the named database.
• Runs CREATE DATABASE <database_name> if it doesn’t exist.
• Creates tables/schema (e.g., wp_archived_orders).

Scenario 2: WordPress DB user lacks CREATE DATABASE privilege #

When it happens: Hosting providers often limit privileges. The plugin will fail when it tries to create a new DB, and your user can only operate inside the existing WordPress DB.

Why this happens #

Managed and strict shared hosts commonly restrict DB users for security. The WordPress user can read/write the WordPress DB, but cannot create new databases.

Error, you’ll see #

A clear permission error indicates that the user cannot execute CREATE DATABASE (or similar privilege error).

Solution options #

Option A — (Recommended for shared hosting) Ask the host support

1. Contact your hosting support and request
2. Grant your existing WordPress DB user full privileges to create a database
3. If they granted privileges to your existing user, proceed using Scenario 1 steps.

Option B — (If you have root/admin access, VPS, or dedicated server)

1. Access MySQL as root.
2. Create a dedicated user or grant privileges to an existing user.
3. Use the credentials (archive_user + password)  in the plugin.

Notes & security #

• Prefer host-managed creation if you’re on shared hosting — it’s simpler and safer.
• If you create users manually, use strong passwords, and limit host access localhost when possible.

Scenario 3: Remote MySQL connection (database on a different server) #

When to use: You want the archive DB on a separate machine (remote DB server, cloud RDS, another VPS, etc.).

Prerequisites #

• Remote MySQL server reachable from your WordPress server (network, firewall, and routing must allow it).
• MySQL user on the remote server configured to allow remote connections from the WordPress server’s IP (or %, though more permissive).
• Remote DB exists, or the user has CREATE DATABASE privilege.
• Reasonable network bandwidth for transfers.

Setup steps (detailed) #

1. Gather connection info from the remote DB admin/provider:
   • Host name or IP (e.g., db.example.com or 192.168.1.100)
   • Port (default 3306)
   • Username and password
   • Database name (already created or a name you want to use)
2. Configure remote server (self-managed MySQL)
   • Edit mysqld config:
     • Set bind-address = 0.0.0.0 (or specific IP) to accept non-local connections.
     • Ensure skip-networking is not enabled.
   • Restart MySQL
3. Open firewall ports
   • On the remote server, allow inbound traffic on MySQL port (3306 by default).
4. Enter connection details into the plugin
   • Host, Port, Username, Password, Database name.
   • Save and let the plugin create tables (if user has privileges) or confirm successful connection.