How to Create A Managed MySQL Database on DigitalOcean – CloudSavvy IT

DigitalOcean’s Managed Databases allow you to create database clusters within a few minutes. You can scale clusters at any time to add capacity as server load grows. The product incorporates automatic failover and backup capabilities.

Managed Databases is a commercial product with pricing starting at $15/mo. You will accrue charges on your DigitalOcean account if you follow this tutorial. Billing is by the hour so you can spin up a cluster, briefly look around and then destroy it without paying more than a few cents.

DigitalOcean supports the MySQL, PostgreSQL and Redis database engines. We’re focusing on MySQL for this tutorial. Getting setup with the other two is a very similar procedure.

Create Your Cluster

Login to your DigitalOcean control panel and click the green “Create� button in the top-right corner. Choose “Databases� from the dropdown menu.

Click the “MySQL� database engine on the next screen. Scroll down to the “Cluster configuration� section. This is where you choose the hardware resources to make available to your database.

The basic $15/mo plan gives you 1 vCPU, 1GB RAM and 10GB storage on a single node. Customise your node plan to suit your budget and performance requirements. All plans except the entry-level $15 option allow you to add optional standby nodes at a reduced cost. These enable automatic failover in the event the master node suffers an outage.

Next select the datacentre to store your cluster in. You should try to keep all your resources within the same datacentre to minimise latency. This also allows you to use DigitalOcean’s private VPC network for communication between resources, maximising security and performance.

At the bottom of the screen, give your database cluster a name. You may also assign it to a “project.� This allows you to group related resources together within the DigitalOcean Cloud Control Panel.

Click the green “Create a Database Cluster� button to create your database. The provisioning process will take a few minutes to complete. You’ll be taken to the database’s details page where you’ll be able to monitor the progress.

Adding Databases and Users

Once provisioning has finished, you can start to add database schemas and user accounts to your cluster. This can be done within the DigitalOcean Control Panel by clicking the “Users & Databases� tab at the top of the screen.

To add a new user, type a username into the “Add new user� field at the bottom of the Users table. You should normally use the default MySQL 8 password encryption for the best security. You may need to change this if you’re working with some legacy MySQL clients.

DigitalOcean will add your database user and generate a secure password. You can reset the password by clicking the “More� link to the right of the table. This also allows you to delete a user.

Adding and removing databases works similarly. Use the databases table at the bottom of the screen to quickly define the schemas within your cluster. There’s no way to populate your new database with tables – this must be done later by connecting using a MySQL client.

Connecting to Your Database

You can get your database’s connection details from the dedicated panel on the “Overview� screen. Use the dropdown above the information to switch between a basic listing of parameters (“Connection parameters�), MySQL connection string (“Connection string�) and prepared MySQL command-line client command (“Flags�). You can change the connection user and database using the respective dropdowns below the connection details.

By default, the displayed details reflect a public connection external to DigitalOcean’s datacentre. If you’re going to be connecting from another DigitalOcean resource, such as a droplet in your account, click the “Private network� toggle button. This will keep connections within the datacentre, increasing performance and security.

Setting up Trusted Sources

Trusted Sources allow you to restrict connections to only whitelisted IP addresses and DigitalOcean resources. Once you’ve got at least one Trusted Source, connections which don’t originate from a whitelisted source will be rejected.

Trusted Sources can be setup either from your cluster’s “Settings� page or by using the panel on the “Overview� page. Use the input to specify the IP addresses and DigitalOcean account resources which can connect to your cluster.

Resizing Your Cluster

You can scale your cluster at any time from the “Settings� page. Click the “Edit� button next to “Cluster configuration� and choose a new Node and Standby Node plan.

Click “Save� to begin resizing your database. There’ll be no downtime. DigitalOcean will keep your existing setup running until the new nodes have been brought online.

You can also add read-only nodes to your cluster. These replicate your data to increase database read performance. Read-only nodes are created via the panel on the “Overview� tab. The plans offered for read-only nodes match the options given for primary nodes.

Restoring Backups

DigitalOcean creates backups of your cluster automatically. This service is included within the price of your node plan.

You can restore a backup at any time by visiting your cluster’s details page and clicking the grey “Actions� button in the top-right. Click “Restore from backup� and then select the backup to use.

The interface allows you to choose the latest backup or a target timeframe. DigitalOcean will restore the database to the first transaction before the specified time.

Restores always create a new database cluster provisioned with the data from the selected backup. You’ll be able to run both clusters concurrently. This model lets you recover records from the original which need to be applied on top of the older backup. Once you’re done, you can delete the old cluster and update your applications to connect to the new one.

Conclusion

Using Managed Databases to run MySQL significantly reduces the operational overheads of getting up-and-running. DigitalOcean host the MySQL server, maintain the firewall and manage backups and replication so you can focus on your application’s data.

Using the Control Panel, you can view graphs of your cluster’s resource usage (click the “Insights� tab). The “Settings� tab lets you change advanced configurations, including the ability to set SQL Mode flags.

The platform does have limitations. You’re most likely to run into issues if you need to customise MySQL runtime settings. Your managed database users will lack the necessary permissions to make changes to MySQL-level global variables. Many – but not all – variables can be changed on request to DigitalOcean’s support team but this procedure is only really suitable for one-time setup.

Managed Databases is best suited to workloads which don’t stray far from the MySQL defaults. Its aimed at teams who favour convenience over configuration. The service’s use of private networks means it pairs well with DigitalOcean’s other offerings, including App Platform and Managed Kubernetes.