Migrating Wardrobe Database to the Cloud

My vision for the wardrobe database always assumed migrating from local hosting to cloud hosting. This was, in part, due to my interest in learning about the process and in facilitating future exercises

But as I begun this journey, it was apparent I had much to learn on many fronts that made it inprudent to tackle cloud hosting...until now.

I have nearly a full year's worth of daily outfits documented in the database and a complete reflection of my wardrobe in the clothing items. We're very near the point of in depth analysis of my clothing preferences and trends.

Also, doesn't hurt that I recently got a new computer which heaped another reason onto the pile to bit the cloud bullet now.

While ultimately successful, I gained an appreciation for the difficult and technical world of DevOps. I encountered many problems with the migration and various solutions.

Let's dig into what I've done.

Migration Process

1. Picking a host

I decided to experiment with the service Digital Ocean, who touts themselves as the cloud environment that scales with you. They have all kinds of services, from web hosting to cloud computing.

I was primarily interested in their database hosting. They offer a starter package of $15/mo prorated to usage for getting a MySQL database cluster spun up.

I've come to understand this cluster being a fully managed MySQL server instance. They offer 1GB of memory, 10GB of disk space, and 1vCPU. Given my extremely small use case at the moment, this was perfect.

It's also worth clarifying this isn't a single database; it's a database server instance, meaning I can spin up as many databases as I want within the confines of the storage and computing power.

Getting started was straight forward and I soon had server credentials in hand. So far so good!

2. Researching Migration

Upon hitting Google, MySQL, and Digital Ocean documentation, I quickly learned that "migration" is a term more suited to moving databases from one cloud source to another and requires infrastructure with the destination hosting provider.

The more applicable term for my situation was an Import/Export of the database. There are a handful of ways to do it, I came to learn:

• Use MySQL Workbench Import/Export wizard
• Use the included mysqldump.exe via the command line
• Install and leverage the MySQL Shell utility
• Use a third-party tool

Digital Ocean Database Migration

Import MySQL Database to Digital Ocean

Truth be told I wasn't a big fan of any of these options. I was anticipating a more elegant approach but decided to start with the Workbench (since I've interacted with the database that way over the past year).

And that's where the problems hit.

3. Export Exasperation

The Digital Ocean documentation linked above wasn't very helpful and didn't do much to aid in the knowledge gap I was suffering from.

I opted to use the MySQL Workbench Export wizard. I found two approaches: 1) exporting to a folder where each object of the database would occupy its own .sql file, or 2) export to a single .sql file.

I tried them both and found issues with each:

• The structure was exported but no data (despite having configured the settings for both)
• A statement was included that conflicted with eventual import, claiming not the requisite permissions for the user

I decided to try the other approaches: mysqldump and MySQL Shell. Both operate in a command line interface which is efficient and effective, though not very intuitive for me.

I ran into issue after issue of not getting the syntax or format quite right, being prompted for more parameters when the documentation didn't prompt any, and more run-ins with the permissions issue.

After much trial and error, I found combining MySQL Workbench Export to a folder with removing a script "DEFINDER `root@localhost`" in each .sql file resulted in successful export.

Then I had to face the music on the import side.

4. Import Impass

Importing the .sql files was a pain. I was still using the MySQL Workbench Import utility. On many of the files I received "`table_name` not found" errors.

I also observed tables and views were imported in all lower case which caused problems since the procedures and functions were still defined with reference to table names in camel case.

Ultimately I modified the .sql files for import so as to reduce my cleanup work.

I was able to see all the data in the tables and the keys were preserved. The triggers I created were also working so it seemed we were in the home stretch.

5. Final Cleanup

I renamed tables and views, dropped some duplicate imported objects, and began to test.

All the tables had the data I anticipated, the views executed properly, and the functions/procedures finally ran successfully.

This process spanned multiple evenings and I subsequently got behind on updating my daily outfits. I went through my normal process and found all was operating as expected.

Success at last!

Lessons Learned

As mentioned previously, I've gained an appreciation for the difficult work DevOps personnel undertake and why many businesses who've enjoyed on premise hosting may be reluctant to move to the cloud.

My eyes have also opened up to more annoyances of MySQL. It isn't quite the beautifully simple RDBMS I thought it'd be.

But mostly the experience has reinforced the idea that the initial learning curve is steep. Doing something for the first time is most always fraught with problems and frustration. It's part of the process and should be more often anticipated.

Next Steps

With my database off my computer and remotely hosted, it's time to focus on analysis, including development of visualizations and evaluation metrics.

Stay tuned!