I got an email from the Google Could Platform yesterday telling me that the Cloud SQL engine is now readily available worldwide so I thought I would give it a go and see how difficult it was to move OpenCart’s database to the cloud and run a version of the eCommerce store from Google’s infrastructure. Setting up the clod database and choosing your plan was very easy, where it gets tricky is where we need to import the database which I will give a bit of a guide to although if you are installing OpenCart from scratch it is much easier.

Google Cloud SQL is a newly available service which joins the Cloud Platform family. Using the same infrastructure which is responsible for keeping Google Search and GMail up (they go down so occasionally that people often think it is the end of the world!) developers can run their apps from the cloud which increases reliability, availability, speed and performance by replicating information across multiple databases and protecting against failing hardware which can cause downtime on traditional hosting accounts. You also get the benefit of automatic software patches applied to the MySQL software to cover vulnerabilities and bugs without having to worry about it.

In this blog I will be taking the OpenCart database and placing it into the Google Cloud SQL service. I’ll show you two different ways to do it, both with an existing store and as a new OpenCart installation. Please make sure you grab a copy of your database, if you have one, as you will need it later on in this OpenCart tutorial and make a copy of your account just in case.

Setting up your Google Cloud SQL instance

Kudos to Google for making the process so simple for creating your Cloud Databases although bear in mind you will need to have a registered card on the account before you can go ahead. You simply need to login to your current Google Account and then head over to the Google Developer Console where you will see a list of your projects (if you have any) and clicking the red button in the top left will let you create a new project which we will use for our test OpenCart installation. Just give your project a name and let Google generate a unique, random project ID.

Once that has finished processing, click on the Cloud SQL link in the left hand sidebar then click the red “New Instance” button. Now you will see a simple form to create the MySQL database, we will go through the steps here though just for an extra explanation on what they are for.

Instance ID

Here you can create a unique name which will be used to reference this particular database instance within your project. KEEP IT SECRET!

Region

Choose the default region to keep all of the data stored, the options I have are US and Europe but not sure if that is because I am based in the UK. Choose whichever you is relevant for your store, business and main customer-base.

Tier

This is the section where you can choose the level of resources that your database requires. Since I am just following this as a test, I will choose the lowest tier (D0) but have a word with your developer and web host to see how much your site is currently using to choose the right tier so your not paying too much but have enough power to manage your store. Bear in mind, one of the great things about cloud based databases is that you can change this tier at any point if you need a little extra juice.

Billing Plan

This depends on how busy and resource hungry your site is. For this test I just went with a Pay As You Go pricing model but if you have a lot of visitors you may save money with a billing plan.

Preferred Location

You can use this to choose a zone within your chosen country to keep data closest to your most active users. I left this as No Preference.

Backup Window

Have a look into your Analytics software and decide when the site is least busy and choose a window within that timeframe to perform backups.

Replication

I left this as Synchronous, simply for the reliability factor.

IP Address

For an extra $0.01 an hour you can have a dedicated IP address for your database which you are going to need. OpenCart requires a hostname, or IP Address, to connect to the database so in order to run an OpenCart database from the cloud you will need to have an IP allocated.

Authorised IP Address

You will need the IP address which your website runs on, this means that only your website can access the database, everything else will be rejected. Get in touch with your web host if you don’t know what this is.

Authorised App Engine Applications

This is for developers who have other projects in their developer console so leave this blank unless you know what it is for.

Then click submit and let it process, then you have a brand new MySQL database to use for your OpenCart store. The next step should be to change the root password to something more memorable for you to use, go into the developer console, select your database instance and click “Access Control”, from there you can choose a new root password.

Pointing phpMyAdmin to Google Cloud SQL

By default your webhost will likely have phpMyAdmin installed so you can follow this guide to point your application to use the Google Cloud SQL database instead of your own. Go into your web hosting account file manager and get to the phpMyAdmin config settings by going to the file:

• phpmyadmin/libraries/config.default.php

Then, find the following variables and change them to your new Cloud Instance using the IP address which Google has assigned your database.

OK, so now when you load up phpMyAdmin you will be connected to Google’s Cloud SQL database rather than the default web hosts and you can manage it through phpMyAdmin as you would normally. Bear in mind there are a few restrictions on the commands and privilege settings which shouldn’t affect you too much.

Installing A new Cloud Database-powered OpenCart

OK, in this example we will be assuming that you are installing a brand new OpenCart instance on your server. In which case simply follow the installation instructions as in the step below but make sure to enter the same hostname, username and password that you used in changing the phpMyAdmin config settings.

 

Then just let OpenCart do it’s thing and it will be using the new Cloud SQL database rather than the localhost one on your web hosting account.

Moving your existing OpenCart installation to use Cloud SQL

So, if you have a currently existing OpenCart store and you simply want to move the database into the cloud, log into your newly configured phpMyAdmin and simply use the database backup you took at the start of this tutorial and import it into your new Cloud SQL database using the import button along the top. Then you will need to edit the Database information inside your OpenCart store so open up your site in FTP and find the following files:

• config.php
• admin/config.php

All you need to do then is change the database connection settings (the hostname, username and password) to point towards your new Cloud SQL database rather than the localhost version and then you are good to go! By the way, the database driver is still “mysql” so you can leave that as it is.