Intro

In this article, we will deploy our WordPress website onto Heroku, a web application hosting service. We are using Heroku because it has a free plan, but it does come with a couple of drawbacks. We will also go over the limitations of this in this article.

Heroku

Let’s start by installing Heroku’s command line interface by running:

sudo snap install --classic heroku

Heroku is a free hosting service that we will use to host our website. The service is extremely convenient to use, but the free tier has a couple of limitations and is usually not suitable for a production/customer facing website hosting. Here are some of the notable downside for the free tier:

  • If the application is inactive for a while, it will go to sleep. When a user visits the application/website for the first time, it will take more time to load and wake up.
  • The MySQL database size is only 5mb.
  • It has an ephemeral file system. This just means that any file changes or new files uploaded on the server will be undone after the server restarts. This means that users should not be uploading files on here through WordPress or making any changes tothe files.
  • Limited performance.

However, since we will only be using the website here as a development website, these limitations shouldn’t be too much. If you database does happen to get big and need to go over the 5mb (even with the WordPress database optimizing addon), you have the option to upgrade to their paid tier to significantly increase the limits.

Heroku Setup

Make sure you sign up on their website: https://www.heroku.com/
Once you have signed up, you should be able to go to the dashboard and create a new application.

Enter the name of your new application and in the Deploy section, choose the deployment method that you prefer. For this tutorial, I will choose the Github option to simplify things. This makes it so that every time you move your changes onto Github, it will also move the changes onto Heroku, where we will be hosting our development site. To enable this automatic process, go down to the Automatic deploys section and enable it on the master branch. We won’t go into detail about Github’s branches, but you can read more about it here: https://guides.github.com/introduction/flow/

Heroku Database

Now we need to setup our database on Heroku. Go to the Resources section and click on Find more add-ons.

In the Data Stores section, find the addon that says ClearDB MySQL and click it. Once you are on that addon’s page, click on Install ClearDB MySQL and enter your application’s name to install it for the app (make sure the plan is on Ignite – Free).

ClearDB

Once you have installed the database, you can access this on the Resources tab in Heroku and select ClearDB – MySQL. This should take you to your database’s page on ClearDB. From here, you can see your database in the Community Edition tab. It should be called something like heroku_4536dfb43tbdf. Click into this and then go to the System Information tab.

From here you can see your database’s username and password. Make sure to make note of this as we will be using it later.

Local Database

If you haven’t already, you can install MySQL Workbench to access your database through a GUI:

sudo apt install mysql-workbench

You can then start the program by finding it on the launcher or typing mysql-workbench in the terminal. Once opened, you should be able to click into the Local instance to access your local database.

Database Size

Since our database on Heroku only allows for 5MB, let’s open up our local instance to check its size. Once opened, the bottom left should have a SCHEMAS section that contains your database. Right-click your database and select “Set as Default Schema”. Then click on the textbox in the middle to type in the Query tab and paste this in the box:

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Then go to Query at the top and select “Execute (All of Selection)” or press Shift + Ctrl + Return.

A results table should show up showing the size of the database right beside the name of your database. If this is under 5MB then we are good to go! If not, then we may have to delete some unnecesarry data from there. You can make use of the “Optimize Database after Deleting Revisions” WordPress plugin to delete a lot of data from your database. A lot of the space can be a result of old revisions of posts that you no longer need.

If the above method still does not reduce the database tp below 5MB, then you may need to upgrade the database to a paid tier to continue.

ClearDB Remote Database

Let’s press the Home icon on the top left of MySQL Workbench now and try to connect to our ClearDB database that we just cretaed on Heroku. Click on the “+” icon next to the title that says “MySQL Connections” to start adding another connection.

To get the Hostname, go back to your website folder in your terminal and run the following Heroku command (make sure you have installed the Heroku CLI as mentioned at the top):

heroku config --app yourappname

Remember to replace yourappname with the name of your application on Heroku. You should get an output that says CLEARDB_DATABASE_URL and a bunch of text after it. The Hostname is the value after the @ sign and before the forward slash “/”. It might look something like us-cdbr-iron-east-03.cleardb.net

Connecting

The port should be 3305, username/password should be the one we got from the System Information tab on the ClearDB website earlier. The Default Schema is the database name on ClearDB, which should look something like heroku_resgerhreshrhhs3542. It can be found on the System Information tab as well as the part from the heroku config command after the forward slash and before the question mark. Connecting to it may prompt a password, which is the one from the System Information tab.

Once you have tested and saved the connection, the MySQL-Workbench home page should now have an extra tile to connect to this database now. Click into it to make sure everything connects properly.

Heroku Config Variables

Now that we have our ClearDB credentails from above, we need to let Heroku know this information. Heroku also similar to our Apache’s environment variables, Heroku has a section called Config Variables where it gets these values. Go into the Heroku Dashboard again and go to the Settings section. There should be a button called Reveal Config Vars. Click this to reveal the variables.

By default, the ClearDB database URL should be populated already. If not, you can get this URL by running the “heroku config –app yourappname” command again. Since we specified the named of the environment variables in the wp-config.php file earlier (such as DB_NAME, DB_USER, DB_PASSWORD, DB_HOST), we can add this easily in the Reveal Config Vars section.

Double Checking

Make sure to add all the environment variables that we have defined in this area and fill the values in with the ClearDB values. Double check that there are no trailing or prepended spaces in these values that are not supposed to be there.

Now check that your file changes are all on the remote repository on Github. You can go to your website folder and type ‘git status’ to check if there are any changes that need to be put on there. Once everything is up to date there, you can manually deploy the changes to Heroku by going to the Heroku Dashboard > Deploy and scroll to the Manual Deploy section to deploy.

Pushing Database Changes

Right now our ClearDB database is blank, so we want to make sure we move our local database values onto the ClearDB database. To do this, we will create a backup file from our local database, then restore it to the ClearDB one. Let’s start by creating a new folder on our machine to store the database files:

mkdir ~/mysql-db-files
cd ~/mysql-db-files

Make sure you don’t create these files in your website’s folder, because it contains sensitive information and can accidentally be pushed into Github if not careful. Once you are in this new folder, you can then then run the following to create the database backup file:

mysqldump -u root -pCHANGEME databasename > nameofoutputfile.sql

Make sure “root” is changed to your user and the CHANGEME value right after -p (no space in between) is your database user’s password. Remember to change the databasename to the name of your own database on your local machine. Then the part after the right angle bracket “>” is just the name that you want the file to be called (make sure it ends with .sql).

Double Checking

Run the “ls” command just to check that your file has been created. Now we can run the next command to restore our ClearDB database using this file:

mysql --host=changeme --user=changeme --password=changeme --reconnect heroku_changeme < nameofoutputfile.sql

Make sure you change all these values to the ones we got for the ClearDB database above and the filename at the end to the one we just generated.

Note:
You will likely get an error that says:

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_unicode_ci'

If that happens, you can read about why this occurs here so you can make a more informed decision: https://stackoverflow.com/questions/42385099/1273-unknown-collation-utf8mb4-unicode-520-ci

If you just want to resolve this quick and don’t care about the details/implicates, you can just run the following:

sed -i -e "s/utf8mb4_unicode_ci/utf8mb4_unicode_ci/g" nameofoutputfile.sql

Just make sure to change the filename at the end to the file we just generated. This command just replaces the text that is causing issues in our database file. After running it, you can try running the mysql command again to make the changes again. You can now go into MySQL-Workbench again, refresh everything and see if the ClearDB database has received all the changes.

WordPress URL Values

One thing to watch out for are the URLs stored in the database. While you are connected to the ClearDB database with MySQL-Workbench, run the following command to see the WordPress options table:

SELECT * FROM wp_options;

You should notice some columns called “siteurl” and “home”. These are currently set to http://localhost, but when you have deployed this to Heroku, it would be a different URL. You can find this URL by going into the Heroku Dashboard > Settings > and scroll down to the Domains and Certificates section. You should be able to see your application’s URL on Heroku that looks something like https://yourappname.herokuapp.com/

We should replace the localhost URL in our ClearDB database to avoid issues. Run the following SQL Query on the ClearDB database to update this:

update wp_options
set option_value = "http://yourappname.herokuapp.com"
where option_name = "siteurl";
update wp_options
set option_value = "http://yourappname.herokuapp.com"
where option_name = "home";

Make sure that this is set to http instead of https.

Note:
Please note that we will not be able to use https properly in this tutorial, since if you are using a custom domain name, you will have to upgrade to a paid plan to implement SSL certificates. If you plan to use the default Herokuapp domain, then it might require more steps to get https to work. I may write an article on this in the future, but for now we will work with this.

Once this is done, run the SELECT command again to see if it has changed properly.

Visiting the Site

With all the changes being made, let’s restart our application by going to the Heroku dashboard, click on More and select Restart all Dynos. Now you can visit your site’s URL and see the error that shows up!

If it says something about a database error, check your wp-config.php file to make sure the database values are correct and you didnt mispell anything. Since we are using environment variables for them, you can also have to check the Reveal Config Vars section in the Heroku Settings section to see if anything was entered wrong (for example, unexpected trailing or prepended spaces).

If you get an error saying the site redirected too many times, check your database value for the siteurl and home columns and make sure that their values are NOT https and the domain name matches what is listed on Heroku. If you have made changes and it keeps trying to redirect you to the https site, the redirect might be cached in your browser. In that case you can either clear your browse cache or try browsing in a new incognito tab.

If your CSS (styling) of the website is all off, but the page loads the content, then double check that your website is serving everything through http and your current URL is using http.

Once the website is loading properly, you can visit the login page as normal by going into /wp-login.php or your custom login URL if you have configured it.

Workflow

If you have followed through everything in these series of tutorials, then these are the current things that we have:

  • Local WordPress website (local database, files, server, etc). Visited through http://localhost
  • Github remote repository for files.
  • Heroku remote for files (ephemeral).
  • ClearDB remote database. Visited through http://yourappname.herokuapp.com/

Since the Heroku file system is ephemeral, you would not be able to login to the Heroku instance and upload files (pictures, media, etc) through the website. The files would just get deleted once the instance is restarted. In this case, everytime you want to make changes to the files or add posts that include pictures, you should make them on your local instance and push the file changes to Github/Heroku and the Database changes to ClearDB.

Workarounds

This all seems very tedious, but the benefit of this is that everything we use here in this series of tutorials is free. If you don’t like the ephemeral file system of Heroku, you can just avoid it by using AWS and spinning up your own web server to host your WordPress site. They also have a free trial for a year and a very cheap (around $3 a month) plan.

To make life easier for pushing database changes between your ClearDB and your local database, I have created some scripts below to help with this.

local_to_cleardb.sh

This one creates a database backup file and pushes it from your local database to ClearDB. It automatically modifies the siteurl and home values to your Herokuapp url. Make sure to change all the values to match yours if you want to use it (database name, user, siteurl, etc).

I have named the file local_to_cleardb.sh to be self explanatory. Make sure to run this in a folder NOT within your website folder by running:

bash local_to_cleardb.s
cleardb_to_local.sh

This file (cleardb_to_local.sh) does the opposite and creates a backup database file from your ClearDB database and restores it onto your local database. Make sure to change all the values in this as well and run it in a folder that is NOT your website folder:

bash cleardb_to_local.sh

Other Notes

I might also look into making a plugin in the future to work around the ephemeral file system.

Alternatively, you can also find some plugins that save the media on Amazon S3 instead of the Website file system. The cost can be around $0.023 per GB for the first 50TB so it’s almost free.

Since the free Heroku instance might not be able to handle a lot of traffic, we will only use the Heroku site as a development site if you need to add some text-only posts from other devices. We will add more to the workflow in the next tutorial as well so that we can have a website that can actually handle more traffic and activity (for free!).

Navigation

The previous article can be found here.