Hello experienced colleagues,
I have a web application built on a MySQL database. I need to run another copy in a remote location, and refresh the data weekly. The size of the refresh is about 500k records, and is not the entire database, which is >15 million records.
My idea was to get the data running on a local Test Server, then connect to the remote Production Server's port 3306, and copy the data. the 'copy' part is not problem, we have a python script, it is fast allright.
However, I have been warned that connecting to port 3306 is not safe, the data is not encrypted, etc etc.
I am in a desperate search for a solution. Here is what I have come up with so far:
Connection to MySQL client's port 3306 with SSL -- is it an off-the-shelf solution?
Connection over SSH tunnel -- I'm worrying about the speed of the connection.
Make the remote machine a VPN client of our intranet, and connect to the intranet IP's port 3306 (there are problems with DNS)
Write a sort of a 'back door', a MySQL client that would receive the data over SSL and update the database
As you see, the ideas are sorted in the order of ease-of-implementation, and I would really like one of the first two to work.
