How to Setup a WordPress MySQL Database in the Cloud

WordPress is the largest website builder platform in the world, supporting over 34% of all websites on the internet today. MySQL is a free open source relational database management system that is leveraged across a majority of WordPress sites, and allows you to query your data such as posts, pages, images, user profiles, and more. As any WordPress developer knows, each installation requires a database in the backend, and MySQL is the database of choice for storing and retrieving your WordPress data.

In order for your WordPress website to be able to access, store and retrieve the data in your MySQL database, it needs to be hosted online through a cloud computing service. ScaleGrid offers a convenient way to setup and configure MySQL hosting for your WordPress site. In this blog post, we’ll show you how to create your MySQL deployment at ScaleGrid while setting up your new WordPress site, and how to migrate to a ScaleGrid MySQL deployment for your existing WordPress deployment.

Advantages of Using ScaleGrid MySQL Hosting for Your WordPress

Managing a database is hard, as it needs continuous updating, tuning, and monitoring to ensure the performance of your website. Fortunately, there are fully managed and hosted solutions like ScaleGrid’s MySQL-as-a-Service plans which take care of all your MySQL maintenance so you can focus on developing your websites and applications. Here are some of the ways ScaleGrid can help you improve your production WordPress setup:

1. Platform and configuration of choice

   Host MySQL on AWS, or MySQL on Azure with configurable instance sizes through the top two cloud providers in the world. We help you configure your MySQL deployment to optimize your performance based on the size of your databases.

2. High Availability

   Make sure your website doesn’t go down with a server or datacenter crash by configuring a master-slave setup for high availability. Distribute your MySQL data across different datacenter regions, so if one goes down, you can automatically failover to the replica to stay online.

3. Automated Backups

   Backups are copies of your data that are archived so you can access and restore them in case of a data loss event. This is a tedious process that needs to be performed regularly, but with ScaleGrid, you can automate a schedule to backup your systems as frequently as you like, and customize how long you want to keep your backups around.

4. Monitoring & Alerts

   Keep an eye on your database performance with advanced monitoring tools and query analysis for your database workload.

5. Scale On-Demand

   As your data size grows, you will need to scale up your MySQL deployments to ensure your performance does not suffer due to a lack in capacity. This is again a tedious process that usually involves some downtime, but at ScaleGrid, you can scale up to a larger instance size in one click with zero downtime for a master-slave deployment.

6. Unlimited Databases

   Create and deploy an unlimited number of MySQL databases through one single cluster at ScaleGrid, and easily create new database users with custom role privileges in a few simple clicks.

How to Create a MySQL Deployment at ScaleGrid

1. Sign up for a free 30-day trial on the ScaleGrid console.
2. Create a new MySQL deployment in a few easy steps. Make sure to create it in a region that matches your WordPress deployment region. We support two different MySQL DBaaS plans on both AWS and Azure.
   • Dedicated MySQL Deployment - Host through your ScaleGrid account.
   • BYOC MySQL Deployment - Host through your own cloud account.
   
3. Customize Your Plan

   Enter a name for your new MySQL cluster, and select the same cloud region you are using for your WordPress hosting site (or the closest available one) for the best deployment performance. Next, select the VM size, ranging from Micro at 10GB of storage up to X4XLarge at 700GB of storage, and then your MySQL version and storage engine.

4. Replication

   Next, you can enable a master-slave configuration to ensure high availability for your MySQL database. This means, if one of your servers goes down, we will automatically failover your primary server to a healthy and available region. By default, you can use either a 2+1 configuration (master-slave-quorum) or a 3-node setup (master-slave-slave) with either semisynchronous or asynchronous replication and customize the regions to use for your secondary nodes.

5. Advanced Security

   In this step, you have an option to enable SSL for your MySQL deployment to secure connections between WordPress and MySQL. We recommend enabling SSL for every production deployment. You can also enable disk encryption to further protect your deployment from unauthorized access.

6. Firewall Rules

   In order for your WordPress server to be able to connect with your MySQL server, you must open up access by whitelisting this IP under your clusters firewall rules. Find your WordPress servers IP address in your WordPress account under the My Sites tab > Network Admin > WP Engine > General Settings, then add it here to the IP whitelist. You can also learn more in our MySQL Firewalls documentation.

7. Summary & Create

   Review your MySQL configurations on the last Summary page, and click Create.

How to Setup Your MySQL Database & Admin User for WordPress

While setting up your WordPress site, you will be asked to supply the hostname, database name and user credentials for your MySQL database. Here’s how you can set this up:

Create a New MySQL Database

1. Go to your MySQL cluster list page, and select your newly created cluster.
2. Select the ‘Databases’ tab, and click the green ‘New Database’ button.
3. In the ‘Create a new database’ window that pops up, simply enter a name for your database, for example, ‘wordpress_database’, and click ‘Create’:

Create a New MySQL Database User

1. Select the ‘Users‘ tab on your MySQL cluster details page, and click the ‘New User’ button.
2. Select the new ‘wordpress_database’ that you just created from the dropdown field as the database for this user.
3. In the ‘New User’ window that comes up, enter a name and password for the new user, for example, ‘wpuser’.
4. Lastly, select ‘Read-Write’ as the ‘Role’ for this new user:
5. Click ‘Create’ and your new user will be created! Make note of your database name, username, and password which you’ll need for your WordPress installation.

Find Your MySQL Hostname

1. Go to your MySQL Cluster Details page and select the ‘Overview’ tab.
2. Find the Command Line Syntax section at the bottom of the page to see the command that can be used to connect your MySQL deployment through MySQL client:
3. The server name following the ‘-h’ option is the hostname of your MySQL deployment, and in this particular case, it is ‘SG-help-1-master.devservers.scalegrid.io’.

Test Connectivity Between Your WordPress Site & MySQL Server

Before you begin configuring your WordPress installation, we recommend checking the connectivity between your WordPress server and MySQL deployment. In order to check this, you will need a MySQL client on your server. Follow the instructions here to download and install and MySQL client if you dont already have one installed. Execute the following from the command line of your WordPress server and make sure you are able to connect:

mysql -u wpuser -h SG-help-1-master.devservers.scalegrid.io -p<Your Password>

If you’ve enabled SSL for your MySQL server, make sure that you see the ‘Cipher in use’ output for the SSL field as shown below:

mysql> status;

-------------------

mysql  Ver 14.14 Distrib 5.7.15, for Win64 (x86_64)

Connection id:          79854

Current database:

Current user:           wpuser@xxxx

SSL:                    Cipher in use is DHE-RSA-AES256-SHA

Using delimiter:        ;

Server version:         5.7.21-log MySQL Community Server (GPL)

Protocol version:       10

-------------------

Configuring Your WordPress Installation to Use MySQL at ScaleGrid

During your WordPress installation, you will be asked to specify your MySQL database details. Now that you’ve created a new MySQL deployment, database, user, and found your hostname, you can enter these details here to connect:

Once you submit, your WordPress installation should go through and be able to connect with your MySQL deployment hosted on ScaleGrid.

Additional Step For SSL-Enabled MySQL Deployments

If you have enabled SSL for your MySQL deployment, then your WordPress installation requires an additional setting that cannot be specified through the UI above. In such cases, before installing WordPress, you will have to edit a file called wp-config-sample.php and save it as wp-config.php.

You can follow detailed instructions here on editing your wp-config.php file. The sample below shows the MySQL section in the wp-config.php file, and the last line in this section indicates that MySQL has been configured with SSL:

Once you complete the installation, you will see that your WordPress site is up and running using ScaleGrid’s MySQL hosting as its database!

In the next part, I will talk about migrating from your existing database to ScaleGrid’s MySQL hosting for your existing WordPress site. Stay tuned!