DigitalOcean Managed (Clustered) Database With WordPress
Overview
I have an existing WordPress site running on DigitalOcean on a self managed instance (Droplet). It was time for an OS upgrade and generally I like to start from scratch rather than doing an in place upgrade.
Ideally, I would have something like Anisble or Terraform to do this but alas, I am not there yet. This time, rather than setting it up again, I moved to DigitalOcean’s managed database cluster.
Infrastructure
Provision a database is as simple as other Droplets, all created from the web front end.
All the credentials are managed and provided in the same place as well. After creating the database, the first job was to secure access to only relevant instances.
Restoring Database
I stood up a new Droplet and install the MySQL client. The high level steps were:
- Exported the existing data from the database using mysqldump.
- Create the database (schema) in the managed database.
- Restore the data from the dump file to the managed database.
Restoring the data was when I encountered ERROR 3750 (HY000) at line 770: Unable to create or change a table without a primary key, when the system variable ‘sql_require_primary_key’.
ERROR 3750 (HY000) at line 770: Unable to create or change a table without a primary key, when the system variable ‘sql_require_primary_key’ is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
This is related to a MySQL global setting. Fortunately, DigitalOcean has a way of turning this off.
It requires calling some API’s so I recommend using a tool such as Postman.
Create a (temporary) API key from DigitalOcean’s API management page. Make sure it has read and write access.
I created a new Collection in Postman and set the authentication type as Bearer Token and entered the API key in the Token field. Ideally, this would be stored in an environment variable but this is a one time thing.
Next, Create a new GET request in the DigitalOcean Collection to the URL: https://api.digitalocean.com/v2/databases to get a list of managed databases. The authentication should default to inherit from the collection.
In the body of the response, look for correct database and make a note of the id value.
Create another request but this time set it to PATCH to the URL: https://api.digitalocean.com/v2/databases/{YOUR_DATABASE_ID}/config replacing {YOUR_DATABASE_ID} with the ID in the previous step.
In the body, set it to raw and put this in the body:
{"config": { "sql_require_primary_key": false }}
Send the PATCH request and you should get an status 200 with an empty response body.
Try restoring the database data now and it should not error on sql_require_primary_key again.
Once complete and to be completely secure, delete the API key.
WordPress
High level steps:
- Create a MySQL 8.0 user in DigitalOcean.
- Allow the Droplet access to the managed database.
- Update WordPress configuration.
Skipping straight to the wp-config.php file changes, update using the VPC network details. The main points I had to take away were:
Specify the port in the DB_Host entry. For example:
define('DB_HOST', 'private-mydatabase-do-user-1234.db.ondigitalocean.com:25060');
Add an entry to use SSL when connecting to the database:
define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
Summary
Fairly easy migration and connectivity remains relatively the same as long as you’re connecting from an instance with a private connection. I’m going to see how much maintenance free it is to justify the increase cost.
WordPress ==> SSL ==> MySQL is this configuration possible?
Unable to create or change a table without a primary key – Laravel DigitalOcean Managed Database
WordPress on Digital Ocean using Managed Database
