While doing self projects across all my machines, I find it quite troublesome to keep a set of the MySQL database on the machines that I work on. To ease some of the installation headaches, I do use Docker Desktop on my Windows machine and Docker Desktop for Mac while working on my laptop. I do check in my work to GitHub and can continue working on my code whichever machine I choose, but the database data is different for the different machines that I am working on. I usually have to populate the test data across these different databases across these machines every time I switch.
As this repetitive task is annoying and troublesome, I looked around for a hosted database on the Internet so I can just connect to it to do development and probably also provision another database when I am ready to push it to production.
I searched around and found Digital Ocean https://m.do.co/c/4a8794565599 (Disclaimer: I have pasted my Digital Ocean referral link. If you find this solution suitable for you, and would like a similar setup, please consider helping me by signing up through my referral link), a popular web hosting / VPS company that is offering managed MySQL databases at an affordable price.
Sign up for a managed database at Digital Ocean
Sign up for an account at Digital Ocean and sign up for a managed database service. This tutorial relates to the MySQL managed database service.
After you have signed up, let’s walk through how to set up the database so that you can connect it using your MySQL Workbench.
Click on the Get Started to go through the steps to set up your database.
Second step would be to control the IPs that are allowed to connect to your database. This serves as a rudimentary firewall in which we can add the IPs which we specifically allow to be able to connect to the database in this list.
You can add your public IP to this list. For users who are behind a router, you probably have something an IP that starts with 192.168. This is not your public IP. To find your public IP, you can go to Google and type in “What’s my IP” in the search term. Once you find your IP, you can enter it here.
Third step would be note down the connection details of your new managed MySQL database.
Also, note the “Download the CA certificate” link at the bottom of of your database information. Since Digital Ocean requires us to securely connect to the database via SSL, click on that link to download the cert to connect to your database. Save the downloaded CA cert to somewhere which we can find it later.
Getting MySQL WorkBench
Navigate to https://dev.mysql.com/downloads/workbench/ and download the latest MySQL Workbench. Note that the version your download should be at least version 8.
Install the MySQL Workbench for your respective OS and launch it. You should be greeted with a similar screen below.
Click on the + icon beside MySQL Connection to add a new connection.
In the new connection box, click on the SSL tab and choose the previously downloaded CA file and plop it in the SSL CA File field.
Now switch back to the first Parameters tab and fill in your database information as given by Digital Ocean. You can also find this information on your Digital Ocean dashboard. Take note of the database port that Digital Ocean provides. MySQL Workbench default port is 3306 which you will need to change
Click on the Test Connection button at the bottom to test your connection. If everything goes well, you should get a Connection Successful message. Congrats !