How to send data updates to a hosted MySQL database remotely over the internet?

Question

I have a simple MySQL database (one table with 12 rows of data and 5 columns) sitting on the web-server of my host provider.

On my home PC I create the data programmatically and store it in a free version of SQL Server (on my home PC). I would like to "upload" this data to the MySQL db in real time (or as close as I can get) over the internet (I'm assuming this is the only way to connect the pipes).

I know that opening up a MySQL database to a remote internet connection probably is not a "secure" thing to do, but the resulting data table will be publicly available anyway via an "app" so I'm not too worried about that (I suppose a hacker could "overwrite" my data with their own if they were both industrious and inclined) but I think the risk/reward is so small its not a major concern.

Anyway, what is the easiest way to do this with some semblance of security? I only know how to program in VB (I did a little HTML and ASP back in the day, but that was a long time ago). I could learn a few lines of code in another language if need be.

I do not have a static IP, and I've never actually interacted with a MySQL database before (only SQL server, so my MySQL knowledge/ familiarity is zero...but a db is a db, so how hard can it be?). Because of my home network firewall, I can't allow connections "in". I will have to make the connection to the MySQL db "out" from my home PC --> to the hosted database.

Answer 1

Ok this problem is not actually super simple.

What you will find is most shared hosting providers do not allow just any IP to access their databases.

Solution? set the IP for your computer of course! BUT.....you are probably on home internet connection so your IP address can CHANGE (if you have a static IP you are a lucky person!)

So the best way - create a mini-API!

Basically, you want to post your data to a script (with some security of course) that then inserts this data into the database.

It is a lot of work but having done all this before it seems to be the only way unless you have a dedicated server / advanced access privileges!

Answer 2

I would try this:

1. At your local computer install MySQL server, there's a free community edition available for download, try the web installer since its more lightweight. Use the custom installation and make sure MySql Workbench is selected too.
2. Workbench has a migration tool for the most common databases, Try this locally, so you can tell if all your data is correctly migrated from your local SQL Server to a MySQL db and there are no data losses in the process.
3. Then, You could probably connect through Workbench to your online MySQL db and migrate your data to it directly from your just created local db. In case you cannot connect, make a backup of your local db and send the files to your server by ftp or similar process. Then, simply restore DB from the backup file on your online server.

Hope this helps!

Answer 3

You could take a look at WAMP for your home pc. It's simple to use.

And then you should take a look at Mysql remote connections(some details here)