Managing heavy-traffic WordPress site 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.
Where all the software 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 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
If you are ready to spend time & put efforts in managing database, then that would be cool. However, if you are not prepared for it, then you can avail Google Cloud Managed SQL.
What is Google Cloud SQL?
Cloud SQL is high-performance managed database (MySQL & PostgreSQL) 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 Google 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 existing MySQL database
- Import the data to Cloud SQL
- Allow Cloud SQL to be connected from existing WordPress server
- Change WordPress configuration to connect to Cloud SQL
Creating Google Cloud SQL Instance
- Go to Cloud SQL and click Create instance
- Choose MySQL and Next
- Select the second generation
As you can see 2nd generation has a high throughput at lower cost
- Enter the instance ID
- Select the Database version (always latest is the better)
- Choose the region & zone
Tip: always prefer to have SQL instance 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 hosting.
- By default, n1-standard-1 is selected under machine type. Feel free to change if you need to.
- Let it be 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
- Enter the SQL root password and click Create
It will take few seconds to create and upon completion, you will see the instance in the list.
Getting Data Export from existing Database
- Log in to a current server where MySQL is running
mysqldumpcommand 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.
Importing data to Cloud SQL
Let’s create a database and keep the name as existing
- Go to SQL instance
- Click Database tab >> create database
- Enter the database name and click create
Now, I will import the data
- Click Import button
- Select the SQL file and click Import
It will take few seconds, and you will get a confirmation upon completion
Configuring Cloud SQL Access Control
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 who should allow connecting to Cloud SQL instance
- Go to Access Control >> Add network (under Authorization sub-tab), add the IP address of WordPress server and Save
User account – good to have a separate user account created for the WordPress database.
- Go to Users tab and Create a user account
- Enter the user and password
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
/** MySQL hostname */ define('DB_HOST', 'localhost');
/** MySQL hostname */ define('DB_HOST', '220.127.116.11');
18.104.22.168 is my Cloud SQL instance IP which you will find it under Properties in Overview tab.
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 WordPress database.
Try to access to ensure it works. Once all right, you may want to stop the MySQL on WordPress server.
Once you are satisfied with Google CloudSQL, you can think of stopping MySQL 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 the MySQL, but I would recommend leaving it installed for 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 WordPress server, then Google Cloud SQL would be a good option. Give a try to see how it works.
Not comfortable with configuration modification, server administration related stuff? Leverage managed Google Cloud platform for WordPress.