WordPress

Using Cloud SQL Second Generation with WordPress on Managed VMs

This is the 2nd posts of “Running WordPress on Managed VMs” series (the first post).

Now Cloud SQL Second Generation is available as Beta. If you don’t need SLA, it’s worth a try. Cloud SQL Second Generation will give you higher performance with lower cost and securer access from everywhere by using Cloud SQL Proxy. I successfully migrated the MySQL server from Cloud SQL V1 to V2 instance, so I’ll share the how-to in this post.

Create and configure a Cloud SQL V2 instance

Let’s say we will use wp for various resource names; in particular for the instance name, the database name, and the user name.

You can create a new Cloud SQL V2 instance with the following command:

$ gcloud sql instances create wp \
  --activation-policy=ALWAYS \
  --tier=db-n1-standard-1

Then change the root password for your instance:

$ gcloud sql instances set-root-password wp \
  --password YOUR_INSTANCE_ROOT_PASSWORD

To access this MySQL instance, we’ll use Cloud SQL Proxy. Please download an appropriate binary from the download page, make it executable.

Then enable Cloud SQL API.

If you haven’t created a service account for the project, please create it. Download the JSON key file and save it in a secure place.

Run the proxy by the following command:

$ cloud_sql_proxy \
  -dir /tmp/cloudsql \
  -instances=YOUR_PROJECT_ID:us-central1:wp=tcp:3306 \
  -credential_file=PATH_TO_YOUR_SERVICE_ACCOUNT_JSON

Now you can access to the Cloud SQL instance with the normal MySQL client as follows:

$ mysql -h 127.0.0.1 -u root -p
mysql> create database wp;
mysql> create user 'wp'@'%' identified by 'PASSWORD'; // Don't use it as is!
mysql> grant all on wp.* to 'wp'@'%';

In the above example, I created a new database wp and a new user wp.

Migrating the data from the Cloud SQL V1 instance to the new one

I migrated the data from the old Cloud SQL V1 instance to the new instnace. It is very straightforward because thanks to the Cloud Sql Proxy, I can just use the normal mysql command to import the database dump.

$ mysql -h 127.0.0.1 -u wp -p < mysqldump.txt

Note that the connection between the proxy and the actual Cloud SQL instance is encrypted so now I can safely access my Cloud SQL instance anywhere from the internet.

Running WordPress locally

The local instance is handy for installing Plugins and Themes on the UI while forbidding online installation in the production environment. I think it is a more suitable approach when using ephemeral runtime environment like Managed VMs.

Here is the wp-config.php file for both environment:

<?php
/** Whether running on production or not **/
define('ON_PROD', filter_var(getenv('GAE_VM'), FILTER_VALIDATE_BOOLEAN));

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'wp');

/** MySQL database username */
define('DB_USER', 'wp');

/** MySQL database password */
define('DB_PASSWORD', 'PASSWORD');

/** MySQL hostname */
define('DB_HOST', ON_PROD ? 'localhost:/cloudsql/YOUR_PROJECT_ID:us-central1:wp'
       : '127.0.0.1');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

// Auto detect the URL fails.
define('WP_HOME', ON_PROD ? 'https://'.$_SERVER['HTTP_HOST']
       : 'http://'.$_SERVER['HTTP_HOST']);
define('WP_SITEURL', ON_PROD ? 'https://'.$_SERVER['HTTP_HOST']
       : 'http://'.$_SERVER['HTTP_HOST']);

// Need this for cookies.
define('FORCE_SSL_ADMIN', ON_PROD);

// Get HTTPS value from the App Engine specific header.
$_SERVER['HTTPS'] = ON_PROD ? $_SERVER['HTTP_X_APPENGINE_HTTPS'] : false;

// unrelated configs
// ...

// Turn on WP_DEBUG when running locally
define('WP_DEBUG', ON_PROD ? false : true);

When you run locally, please use the router PHP file. For example, if you put the router wp.php in your project directory besides wordpress directory, to run your wordpress instance locally, do:

$ php -S localhost:8000 -t wordpress wp.php

Then access http://localhost:8000/.

You can install themes/plugins on the local instance, then, when the next time you deploy, those changes will take effect on the production environment. Don’t activate those plugins or themes in the local environment. If you activate them locally, the shared database will have the activation flag and your production blog will be broken! Actually you don’t necessarily need to run WordPress locally against the production database. I will use a local mysql server with the data copied from the prod database in the future. Then you can safely “activate” any themes/plugins with copied data.

Deploy

Let’s make the production environment a bit securer not setting the loose permission. Here is the new Dockerfile:

FROM gcr.io/php-mvm-a/php-nginx:latest

# The docker image will configure the document root according to
# this environment variable.
ENV DOCUMENT_ROOT /app/wordpress

# Allow direct installation of plugins and themes
RUN find /app/wordpress/wp-content -type d -exec chmod 0750 {} \;
RUN find /app/wordpress/wp-content -type f -exec chmod 0640 {} \;

To access the Cloud SQL V2 instance from the Managed VMs, you have to add a small configuration in app.yaml. Also, now I can safely use multiple instances W.R.T the themes and plugins, so let’s stop using manual_scaling. Here is the new app.yaml:

runtime: custom
vm: true

beta_settings:
 cloud_sql_instances: YOUR_PROJECT_ID:us-central1:wp

env_variables:
  DOCUMENT_ROOT: /app/wordpress

manual_scaling:
  instances: 1

Conclusion

We walked through how to migrate your database from Cloud SQL V1 to Cloud SQL V2 instance in this post. It is a bit cumbersome to setup the Cloud SQL proxy, but it’s worth it in my opinion. It will give you secure access from anywhere and you can migrate the data safely.

I also changed how to install themes/plugins, by running WordPress and installing them locally. Now the production environment became much securer. Also, now my servers autoscale! Yay! This setup works great for me because I still don’t have any images in my posts. Yes, the last missing part is the media upload. I will update here when I have a solution for media upload that works correctly among multiple servers.

4 comments on “Using Cloud SQL Second Generation with WordPress on Managed VMs

  1. I’ve been struggling with this procedure for 2 days.

    where do you run that
    $ cloud_sql_proxy \
    -dir /tmp/cloudsql \ … command and where do you rstore the credential file ?

    On the mysql instance or on VM instance which ruuns WordPress ?

  2. For images, look into WP-Stateless. I have been using it for a while and while there some issues with transferring uploaded media into Google Storage, it’s been working awesome with new items. All the media items are still in the media library but it links the image to Google’s server.

Leave a Reply

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

By submitting this form, you accept the Mollom privacy policy.