Geekflare is supported by our audience. We may earn affiliate commissions from buying links on this site.
In WordPress Last updated: September 6, 2022
Share on:
Invicti Web Application Security Scanner – the only solution that delivers automatic verification of vulnerabilities with Proof-Based Scanning™.

Managing heavy-traffic WordPress sites has many challenges, and one of them is a database.

The database is known for utilizing high resources like CPU, Memory, IO so it would be a good idea to host the database separately.

A typical WordPress site would be something like below.

WordPress-standalone-site

Where all the software like Nginx/Apache, PHP, WordPress, MySQL/MariaDB is running on the same server.

Having everything on the same server is ok for small to medium traffic, but if you are managing a heavy-traffic website and optimizing each layer for optimal performance then you may want to decouple the database server.

There are two possible ways to do that.

  • Host database on a different server
  • Use Google Cloud Managed SQL

The first option is good, if you are ready to spend time & put effort into managing the database, then that would be cool. However, if you are not prepared for it, then you can avail of Google Cloud Managed SQL.

What is Google Cloud SQL?

Cloud SQL is a high-performance managed database (MySQL, PostgreSQL, SQL server) service by Google. You don’t have to worry about installation, managing, performance optimization.

You pay what you use and let Google handle the database infrastructure.

Google Cloud SQL can be integrated with App Engine, Compute Engine, or any other hosting/service including your local workstation. Setting up database replication, High-availability, automatic failover doesn’t require database skills and you configure them using GCP Console.

Ready to see how Cloud SQL works with WordPress?

Scenario – I have a WordPress site hosted on Google Cloud (Compute Engine) where everything is running on a single server. I will do the following to separate the database.

  • Create a Cloud SQL Instance
  • Export the data from the existing MySQL database
  • Import the data to Cloud SQL
  • Allow Cloud SQL to be connected from the existing WordPress server
  • Change WordPress configuration to connect to Cloud SQL

Creating Google Cloud SQL Instance

gcp-cloud-sql-instance

  • Choose MySQL and Next

choose-gcp-sql

  • Enter the instance info

gcp-create-sql

  • Enter the instance ID
  • Select the Database version (always latest is the better)
  • Choose the region & zone (I’m proceeding with a Single zone and can have HA anytime, later on)
  • Click SHOW CONFIGURATION OPTIONS to customize the VM instance type, backup, maintenance, etc

Tip: always prefer to have SQL instances in the same region as your existing WordPress server. However, If your WordPress is not on Google Cloud then choose the nearest location from your server.

  • Let it be the default chosen storage type as SSD
  • Automatic backup, binary logging is selected default – change if you require to
  • Better to define the maintenance window & time for patching.

Tip: always choose the day & time which is less busy for your business

  • Once all configured, click CREATE INSTANCE

It will take few seconds to create and upon completion, you will see the instance in the list.

gcp-list-sql

Getting Data Export from existing Database

  • Log in to a current server where MySQL is running
  • Use mysqldump command to export the database
mysqldump -u root -p [DATABASE_NAME] >/tmp/export.sql

There are some more procedures to export as explained in Google docs.

Once the export is done, you need to upload the SQL file to somewhere in Google Cloud Storage (S3) area.

Importing data to Cloud SQL

Let’s create a database and keep the name as existing

  • Go to SQL instance
  • Click Databases from left navigation panel >> create database
  • Enter the database name and click create

gcp-create-sql-db

Now, it’s time to import the data.

  • Go to overview of instance and click the Import button
  • Select the SQL file from the storage bucket which you uploaded in the previous step during export
  • Select the database under the destination

gcp-import-database

  • Click IMPORT

It will take few seconds, and you will get a confirmation upon completion

Configuring Cloud SQL Access

Access control is critical as this is where you define who should connect to your instance. There are two parts.

Authorizing network – add the IP address that should allow connecting to Cloud SQL instance

  • Go to Connections >> Add network (under Public IP if WordPress is hosted out of GCP and Private IP if hosted on Google Cloud VM) and enter the IP addresses.

gcp-sql-allow-ip

User account – good to have a separate user account created for the WordPress database.

  • Go to the Users >> ADD USER ACCOUNT
  • Enter the user, password, and hostname to allow access from

Tip: you can keep the user & password as existing so that you will have minimum configuration change in WordPress

Configuring WordPress to Cloud SQL

And the last part, you need to change wp-config.php to point to Cloud SQL

As usual, take a backup of wp-config.php file

Edit the file using vi and update the DB_HOST value to Cloud SQL IP address

From

/** MySQL hostname */
define('DB_HOST', 'localhost');

To

/** MySQL hostname */
define('DB_HOST', '104.155.153.82');

104.155.153.82 is my Cloud SQL instance IP which you will find under the Overview page.

Save the file and restart PHP, Nginx on WordPress server

Note: you may also want to connect to Cloud SQL instance using MySQL client from Compute Engine which is explained here.

Well done! You have successfully separated the WordPress database.

Try to access to ensure it works. Once all right, you may want to stop the MySQL on WordPress server.

Stopping MySQL

Once you are satisfied with Google CloudSQL, you can think of stopping MySQL from running on your server.

If using Ubuntu 16.x

service mysql stop

This will stop but start again when a server gets rebooted so next you need to disable from auto-startup.

update-rc.d mysql disable

for CentOS 6.x

service mysqld stop

and to disable from auto-startup

chkconfig --del mysqld

You may also want to uninstall MySQL, but I would recommend leaving it installed for the future in case you want to use it locally.

Getting managed MySQL on Google Cloud is quite straightforward, and I didn’t encounter any issue.

If you are in need to split the database from a WordPress server, then Google Cloud SQL would be a good option. Give it a try to see how it works.

Not comfortable with configuration modification, server administration-related stuff? Leverage managed Google Cloud platform for WordPress.

  • Chandan Kumar
    Author
    Chandan Kumar is the founder of Geekflare. He’s helped millions to excel in the digital realm. Passionate about technology, He’s on a mission to explore the world and amplify growth for professionals and businesses.
Thanks to our Sponsors
More great readings on WordPress
Power Your Business
Some of the tools and services to help your business grow.
  • Invicti uses the Proof-Based Scanning™ to automatically verify the identified vulnerabilities and generate actionable results within just hours.
    Try Invicti
  • Web scraping, residential proxy, proxy manager, web unlocker, search engine crawler, and all you need to collect web data.
    Try Brightdata
  • Monday.com is an all-in-one work OS to help you manage projects, tasks, work, sales, CRM, operations, workflows, and more.
    Try Monday
  • Intruder is an online vulnerability scanner that finds cyber security weaknesses in your infrastructure, to avoid costly data breaches.
    Try Intruder