Easily Backup App Data to MySQL Database

Scott Costello Blog, Podio, Tutorial 4 Comments

This will probably be one of the more challenging articles I have written.  The difficulty will be in guiding people through the different technologies in order to get this immensely useful functionality implemented for us Podio users.

Being able to backup and access your Podio data for reporting, exporting and searching is invaluable.  These things are very difficult to do in Podio as your workspaces get more complicated with Apps linking to each other all over the place.  Plus the more records you get the slower a Globiflow search action will be.  Plus Globiflow limits you to 500 records at a time in a Flow.

The good news is that GlobiFlow has a tool that will Sync all Items of an App with a MySQL Database.  It will also keep everything in Sync as you make changes in Podio. That is Awesome right?!

The kicker, as you might have guessed, is that MySQL Database.  Where do you get one? Is it expensive? and besides all of that, What the hell is a MySQL database?

In this article I'm going to answer all those questions for you as well as show you how to setup the entire Globiflow, Podio App and MySQL syncing setup.

Before We Get Started...

Some of you might be wondering why you would need a backup of your Podio App Data.  In reality that is not really the end game here.  Have a backup of your data is actually a side benefit of getting your data into a MySQL database.  The real benefit is...REPORTING!

The Real Benefit is...REPORTING!

Podio's reporting is extremely limited. You can only pull the basic reports and they can only be created based on a single App's data.  

If you have your data in a MySQL database it opens you up to a TON of different reporting tools that can easily hook up to your data.  I'll cover those in a follow-up article, but this opens you up to a huge feature.

The Pieces to the Puzzle

There are 3 main pieces to getting this setup that are required.  So if you don't already have them, you'll need to get them before you will be able to setup your sync/backup.  I'll go over each briefly and then later in the article will go over how to setup them up.

Podio: You all have this part already or you wouldn't be reading this article.  That being the case I just wanted to mention it to make sure I don't leave any detail out.  In your Podio account, you'll need a Workspace and an App(s) that you want to backup.

Globiflow: This is the magician that makes the good stuff happen.  You'll need the Podio Premium Subscription to get access to Globiflow.  If you are using Podio with out Globiflow you are really missing out on the true power.  Worth every penny!

MySQL Database:  This is why the tutorial is going to be a challenge.  There are no Free Hosted MySQL Databases.  And to be honest I wouldn't trust a free solution. You could get a hosted MySQL Database from Amazon AWS, Google or other companies but their packages are geared towards solutions where you will be having large amounts of data and queries.  Think large scale mobile apps and web apps...such as Podio itself.

The best option that I've found so far is to get a website host.  Almost all will provide a plan that contains a MySQL database because they support Wordpress.  These plans are also cheap!  You can get one for as little as $2.95 per month.  That will give you all you'll need to get started.

You will also need to register a domain name ($7 to $15 per  year). You won't have to create a website, but if you ever wanted to you now have the hosting for it.  I'll go through setting all this up for you and give you my go to host for stuff like this.

Getting a MySQL Database using Bluehost

Like I mentioned above, we need to get our hands on a hosted MySQL database in order to do the Podio App Backup and Syncing.  In my mind the best and cheapest solution to to use a website host.  There are dedicated MySQL hosts out there but they are all setup to work with large scale applications, not for what we need.

I have tried out a bunch of hosting companies over the years and right now my favorite, and what I host Struggling Investor and REIFLOW on is Bluehost (affiliate link).  Below I have an outline of the process for signing up as well as a video. 

If you go through the video I explain each step of the process fairly well, but here is a breakdown of each step with a few more details.

Step #1: Go to Bluehost

Sign up Link for Bluehost.com

Cost: $3.95/mo (that is based on purchasing 36 months of service up front.  It's $5.45/mo if you go 12 months like I did)

Step #2: Select a Plan

Since all plans come with the ability to create MySQL Databases we can choose any of them.  So with that being the case lets get the cheapest!  That is the Basic Plan.

The price fluctuates depending on the wind direction so it may not be $3.95/mo when you try and sign up today.

TIP
After selecting my plan and moving to fill out my payment info, if I moved my mouse towards the browsers back button, bluehost popped up a window offering a 20% discount.  Give it a shot!

Step #3: Choose a Domain Name

You can actually skip this step, but I suggest you go ahead and register a domain name if you don't have one already.  It's like $15/year.  Plus I'm not 100% sure how this will work using the temporary domain/address you'll get from bluehost if you don't.

I will warn you that selecting a domain name is frustrating!  That is because it seems like all the good ones are taken.  Just keep at it until  you find something you like enough to use. I ended up finding REITutorials.com.  Which I can use down the road.

In the end, what the domain name is is not important.  You'll just be using it behind the scenes to access your MySQL Database.

Here is an article I wrote about choosing a domain name if you are interested.

Step #4: Account and Package Info

In this step you will be asked to enter in your personal information like Name, address, phone and email.  This is for the registrar and required by law.

The package information area is basically where you select your payment term and the add-on options.  The longer the commitment the cheaper the monthly rate will be.  The $3.95/mo price is for a 36 month term.

My only recommendation here is to get the Domain Privacy Protection.  This will shield your personal information (the info you entered above) from the public.  It should also greatly limit the spam calls, texts and emails you get.  You'll see in the video I didn't select this option and I got about 25 calls over the next 3 days offering one spam service after another.

Step #5: Clicking Through and Waiting

The last few steps of the signup process is not important.

  1. Don't sign up for the credit card
  2. Create a password
  3. Log In
  4. Choose your wordpress theme (or skip this step like I did) .  Like I said before we aren't concerned with setting u pa webpage right now.
  5. All Done

Finally you'll need to wait for about 24 hours before your domain name is activated.  You'll get an email when it is ready.

Congrats! You now have a webhost and the ability to create a MySQL database.  I will go over how to create the database in the next section.

Setting Up MySQL Database

Now that you have access to a MySQL Database through Bluehost (or if you used another host) we can start the fairly simple process of creating a database and the database table. Globiflow will be using this database table to backup your Podio App's data to.  

Take a look at the video as I walk you through the steps for setting up the database.  You also have to setup a user as well, but I cover it all in the video.

The process for setting up a table is very easy.  The hard part for someone who has never seen a web hosts back-end administration tools is knowing where and what everything is.  There really is only 3 steps for setting up the database and I'll walk you through each step.

Bluehost uses the popular CPANEL as the administrator dashboard.  Pretty much all major webhosts use it as well. So if you are working with another webhost, this tutorial should work for you as well.

Step #1: Logging In

Bluhost Account login => https://my.bluehost.com

Your user name is the doain name that you registered during the setup process.  For me it is REITutorials.com

Your password is that password you created during the bluehost account setup process.

Step #2: Going to the Database Manager

Follow the steps below to get into the Database Manager so we can then create the database and user.

  1. Click Advanced Tab on Left
  2. Click Databases in the sub items under Advanced.

Step #3: Add Database

In the Database Management area, the first area is where we want to add a new database.  So in the New Database: input box enter a name for the database.  I'm using Podio_Sync.  Feel free to use what ever name you want but make sure to use underscores ("_") instead of spaces.

You'll notice that bluehost appends part of your domain name to the database name.  This is fine and used to make sure it is Unique within their system.  So in my case the true database name is reitutor_podio_sync.

Now click "Create Database" to create the database. You will be brought to a confirmation page saying the database has been created.  Just click the link to go back to the database manager.

Step #4: Create a User

Every database must have a user account (login account) assigned to it so we can access it through other systems...like globiflow.  Here is were we create that User.

Scroll down the screen until you get to the the MySQL Users section.  Enter the username you want to use (I use podio) and then enter a password.  I like to use the Password Generator to make life easier.  But make sure to record that password somewhere.

The click "Create User".  You'll be forwarded to the next screen where it tells you a user has been created.   Just click the link to go back to the database manager.

Step #5: Linking Database and User

Lastly we have to link the newly created user to the new database.  So scroll down a bit until you reach the Add User To Database area.

Here you will select the user we created from step #3 and the Database we created from step #2. Then click "Add".

You'll be forwarded to where you can set the user rights for the database.  Select All Privileges then Click "Make Changes" and go back.

Your database is now setup and ready to go.

Creating the Database Table

Next up in the setup process is the Database Table.  This introduces another tool called MyPHPAdmin.  This is the tool we use to actually add, edit, delete tables within a database...because right now we have an empty database.

The video below will walk you through the process of creating a simple table that will accept the app field values from Podio.  There are 2 required fields that must be created but the rest will be determined by the Podio App that you want to backup and what info is in it.

If you are not currently logged into your bluehost dashbaord then start with Step #1.  If you are, then you can jump to step #2.

Step #1: Logging In

Bluhost Account login => https://my.bluehost.com

Your user name is the domain name that you registered during the setup process.  For me it is REITutorials.com

Your password is that password you created during the bluehost account setup process.

Step #2: Opening phpMyAdmin

Follow the steps below to get into the phpMyAdmin tool so we can create the table.

  1. Click Advanced Tab on Left
  2. Click phpMyAdmin in the sub items under Advanced.

A new browser tab should open with the phpMyAdmin app ready to go.

Step #3: Navigate to Your Database

What you are seeing now is the Navigation Menu on the left and on the right you'll see the operational tools.  Right now we need to navigate to the database we created.

  1. Expand your server branch.  It is the top node in the tree and mine reads reitutor. Yours will be the first 8 letters of your domain name. 
  2. You will now see 2 Databases listed.  One will be the database you created and the other will be the one that bluehost setup to work with a WordPress website.  Click on your Database.  I will select reitutor_podio_sync.

Step #4: Create a Table

Since you have an empty database, the only option you have is to Create Table. So enter a name for the table (use underscores ("_") instead of spaces.  For example I named my table sync_properties.  I also set the number of columns (fields) to have to 10.  Add as many as you think you will need, though you can always add more later.  Then press "Go".

Step #5: Adding Columns (fields)

Now that you have created the table, we need to add fields to it.    Let me explain some of the important bits here...

  • item_id is a required field by Globiflow.  Each Podio item as a unique ID that identifies it.  It also must have a Type of INT, an Attribute of UNSIGNED and an Index of PRIMARY.
  • hash is a required field by Globiflow.  This is used by Globiflow to quickly determine if the item has changed and when.  
  • all text fields should have a Type of VARCHAR and a Length long enough to hold the max number of characters a field may have.  An example is I set the City field to length 100 which I feel comfortable that no city will be longer then 100 characters.
  • general number fields have type INT. No need to enter in a Length as the default is fine.
  • currency fields should have a Type of Double

With all that said, see the image below for how I setup my table fields (click image to see larger size)...

If you need to have more fields, you can do so at the top of the screen, by entering the number of fields you want to add and click "add".

If you have too many fields, just leave the ones you don't need blank and they won't be added.

Once you have all your fields set, click "Save"

Podio to MySQL with Globiflow

The last and final piece to the puzzle!  In this step we are going to be using a Globiflow feature that syncs a specified Podio App's data with a MySQL database table.  What syncing means is that when a change occurs to an Item in Podio, that change will also be reflected in the database table.  Globiflow says this process is instant, meaning it happens when you make the change, not an hour later, not 10 minutes later but right now....well I would say in less the 60 seconds.

After you set this process up, Globiflow will pull ALL the records from your selected app and upload them to your new database table.  This process could take awhile depending on how many items you have in the app.  My test app had about 850 items and it took 15 minutes or so.  

Okay, so lets get on with it...

Step #1: Allow Globiflow Access to your MySQL Database

Before we get started setting up the Sync, we have to whitelist the Globiflow IP addresses in your Bluehost account.  If you don't do this Bluehost will not let Globiflow sync the data.

To do this follow the steps below..

  1. Go here to get the list of Globiflow IP addresses to whitelist: IP Addresses.
  2. Go to Bluehost Dashboard
  3. Navigate to Advanced area.  This will open up the CPANEL to the right.
  4. In the Databases section click on Remote MySQL
  5. In the Add Access Host input field, enter in one of the IP Addresses from the Globiflow IPs and click "Add Host"
  6. Repeat step #5 untill all IPs are added.

Step #2: Go to Globiflow and Login

Link => http://www.globiflow.com

Make sure you login with the Podio account that your App is in.  Also as a reminder, you must have the Premium Podio plan in order to have access to Globiflow.

Step #3: Navigate to Your App

The Globiflow feature that we will be using can be found from an App's settings.  So we must select the app we want to backup/sync.  To do this just do the following...

  1. Expand your Organization
  2. Expand the Workspace
  3. Select the App

Step #4: Sync with a MySQL Database

Above the existing App Flows you'll see a gear button.  Click this to expand a menu where you can select the MySQL: Sync with a MySQL Database tool.

Step #5: Fill in the Tool Information

Remember back where we created the Database and User?  We are going to need that information right now, so hopefully you remembered to write that down.  If not you will have to go back to your bluehost dashboard and get that info from the Database Manager.

Sync Name: This can be anything you want

From App: This should already be set to the App, but if not you can select the right now from here.

MySQL Server: This is going to be your domain name.  Mine is set to ReiTutorials.com

MySQL PortLeave this as 3306

MySQL User: The Username of the user you setup back in the create database section.  Mine is reitutor_podio

Password: this is the password for the user you setup back in the create database section.

Database: This is the database name that you setup back in the create database section.  Mine is reitutor_podio_sync

MySQL Table: This will be the name of the table we setup back in the create table section.  Mine is sync_properties

Okay, If you notice that we are not seeing a drop down with the list of our tables.  This is because you have missed one of the IP addresses to whitelist.  Go back to Step #1 and makes sure you got them all.

If everything is setup, this is what you should see...

Step #6: Select Sync Options

leave this as the default "Podio to MySQL"

Step #7: Map the Podio Fields to the Database Fields

Here you will see the complete list of Podio Fields in the App you selected.  For each field that you want to backup/sync with the database table you'll need to select the corresponding database table from the drop down.  A few things of note here...

  • Make sure to assign the Podio field, item_id, to the MySQL item_id column.  If you do not see the column in the dropdown, you probably set up the column wrong in the database.  Make sure to have the Attribute set to UNSIGNED, the Type set to INT.
  • Make sure to assign the Hash Checksum field to the MySQL hash column.  If you do not see the hash column then you probably set the wrong Type.  It should be VARCHAR and Length of 32.

Here is how I matched up my MySQL columns with my Podio Properties App.

You are all finished!  Just click the "Save" button and Globiflow will begin the process of pulling in all the App's items into the MySQL Database. 

Depending on how many records you have, this could take awhile.  My Properties App has 850 items and it took about 15 minutes for Globiflow to import them all.

Once the import process is done you are all set.  Any changes made to an item in your App will be synced with the database from now on.

Note
Please note that this is a one way sync only.  If you make a change to the data in your database table, it will not be changed in your Podio app.

What's Next?

Having a accessible database of all your data is a wonderful thing!  It allows you to do anything you want with your data.  Like I said in the Before We Get Started section,  there are 3rd party tools that can hook directly up to a MySQL database to provide things such as...

  • Extensive Report 
  • One off Querying
  • Backups
  • Webpage Generation
  • and more..

In future articles I will talk about and show you how to do each of these things.  I'm very excited to learn more about each one of the above options.  

Like always, if you have any comments or questions please leave a comment below.

Follow me

Scott Costello

Scott is a part time wholesaler, but full time real estate investing addict! As his family grows and his free time shrinks,He has been slowing building his wholesaling business over the past 7 years in between life events.Drive, dedication and never giving up are his strengths.
Follow me

Latest posts by Scott Costello (see all)

Comments 4

    1. Post
      Author

      It’s really an easy process to setup. The only part that would probably seem confusing is the webhost setup, but though there are more steps then I would like, it really isn’t that bad.

      I would love to hear feedback from someone who has tried setting this up. I can then add or make adjustments to the tutorial to make it easier.

  1. Hi Scott,
    As always… congrats on a superb tutorial! So far, I only had one minor hiccup. When configuring the database name in the “Configure MySQL Data Sync” of Globiflow, the MySQL Table was only enabled when I used the correct and exact capitalization of the database name. In my case, I had to write “onetwrx0_Podio_Sync”; i.e., “onetwrx0_podio_sync” does not work for me.

    Also, thanks for your videos. They are always helpful.

    1. Post
      Author

Leave a Reply

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

CommentLuv badge