WordPress on Digital Ocean’s Managed MySQL Databases

Earlier this year Digital Ocean launched Managed Databases. At that time, only PostgreSQL was made available, and now MySQL and Redis have finally landed as well. I have now done some experimenting with this service, and here are my first impressions.

In short; Digital Ocean’s Managed Databases allow you set up scaleable, high availability MySQL hosting with ease. It’s just that simple.

In my previous article, I’ve already gone through how scalability, redundancy and maintenance works for this product – so make sure to check out my first impressions of Managed Databases by Digital Ocean.

Some things worth knowing

Still, there are some things to get used to. Here are the main things I’ve come across:

  • Users must be created via the DO admin panel or the API
  • Databases must be created via the DO admin panel or the API
  • Users will by default use “caching_sha2_password” authentication, which PHP doesn’t support
  • You will probably need to use the MySQL 8 client to connect to the database from the command line.
  • Sequel Pro will only work if you use a nightly version (and then “caching_sha2_password” still didn’t work for me). MySQL Workbench works fine though.
  • You can’t edit MySQL system variables
  • Users by default get full privileges

You can work around pretty much all of these, but they are still worth noting.

Changing authentication method

You are likely to meet an authentication issue when first connection your PHP code to this database. Something like this:

mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

In short; PHP doesn’t support the new default authentication method. The solution is pretty simple though, just use the old authentication method. Below, I have added an example of how to do just that (make sure to use your username and password). Run this command as the doadmin to change the privileges of the new user you created, and you’ll be good to go.

ALTER USER 'myuser'@'%' IDENTIFIED WITH mysql_native_password BY 'awesomepassword';

Increased latency

This perhaps, is the most noteworthy one. On my site, I generally experienced 100 to 200ms TTFB (Time to first byte) depending on which page you load. This means, from the moment a request was sendt from the browser, it would take somewhere around that time for the first information to return to the browser. However, by moving MySQL of my server this increased, sometimes by up to 150ms, even though the MySQL cluster was in the same region. Using the private network worsened the latency even further.

But one thing is critical to keep in mind. This is simply the cost of a having a setup that is far more scaleable. Not only that, once your site gets hammered with requests, the database cluster will most likely perform and scale better. Especially when caching options such as Varnish aren’t an alternative.

Usefulness for WordPress

For those who are already running their own servers, this looks be a great service if you want the improved scalability and redundancy without the hassle of maintaining the setup. Using a plugin such as HyperDB you could also really get the most of the read-only databases.

Combine that with the already available services such as Spaces (for handling uploads) and a Load Balancer and you got the ingredients for a very powerful setup that can handle tremendous amounts of traffic.

Try it out yourself

If you want to play around with Digital Ocean’s services, you can use my referral link to get a $100 credit towards your new account over 60 days.

I'm a webdeveloper based in Oslo, Norway. I currently work with web development at Nettmaker.

I would like to change the world, but they won't give me the source…

Leave a Reply

Your email address will not be published. Required fields are marked *