Link to home
Start Free TrialLog in
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)Flag for United States of America

asked on

Getting data from a MySQL DB on a Word Press web site.

Have a MySQL DB on a Word Press web site that I need to get data from.

I'm trying to figure out what the best way is to move the data based on what I have to work with.   I figure I can either:

Push data from the web site when a new entry is made in a table or on a set schedule

OR

Pull the data from the web site to the local system.

What form that takes is what I'm not sure about.   Some of the data may be un-encrypted so I want a secure form of data transfer.

What I have to work with:

The web site has the capability of running cron jobs and I have full access to everything, although I don't believe I can do remote DB access (plus it would not be secure).  On the local system, I'm working with Microsoft Access and have SFTP and SSH access to the web site.   On the local server, I can get pretty much anything done (ie. setup a SFTP server), but nothing is in place at the moment.

Push would be my preference for efficiency, but I think it will have to be a pull from the local system.

Seems like DB to DB is out, so I'm thinking of a DB trigger or a cron job that would write the data to CSV file(s), then delete the data from the DB.  Then from the local system, every xx minutes, do a SFTP session and grab any files.

The web site by the way is a WordPress site, so if there are any plug-ins that deal with data transfers along these lines, that is an option as well.

Any thought's or ideas?

Jim.
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

I don't think you have given us enough to work with to come up with a definitive answer.

The criteria would be
a) What data would need to be extracted
b) How often (regular intervals or triggered by event)
c) Where must the data end up ultimately

You have mentioned encryption and security so I am assuming the data is sensitive.

In terms of the data being in WordPress - that just means MySQL or Maria DB backend. The only question there is do you access the data directly or use the WP engine to do it. Personally I wouldn't bother with the WP engine - for this sort of task it does not add anything of value.

Let's look at the various strategies for extraction

1. Script this can be a PHP script or similar or a simple mysqldump run from a shell script although with the latter extraction to CSV is a bit more complex - the script solution gives a lot more flexibility

2. Direct connection. I manage most of my remote MySQL databases through SQLYog (Heidi and MySQL Workbench are alternatives). This gives me the option to work on and extract (locally) any data in the remote database. I have in many cases set up the connection to run over SSH to further enhance security.

If we are going with Scripts

The next question is what triggers the script

The options are
1. Cron Job
2. API Interface
3. Browsing to the site

Cron job - standard way of triggering a regular process to do something - tried and tested and in use all over the place.
Cron process extracts the data using PHP (for example) creates a CSV (optionally encrypts) and then zips the resulting file ready for download or push to wherever it needs to go.

API Interface - requires a bit more effort than the Cron Job - especially if security is an issue - need an authentication stage. Same as above essentially just streams the result to the requesting entity

Browser interface - same as above but triggers a download dialog.

Security is always going to be about how you package the data. The script / extraction means are just the engines to do the extraction - it is what you chose to do with the data after it has been pulled from the DB that is going to set your security level.
Avatar of Jim Dettman (EE MVE)

ASKER

<<I don't think you have given us enough to work with to come up with a definitive answer.>>

  Let me add a few things:

1. This will be an on-going process, not a one time deal.

2. Amount of data will not be extensive....it's collecting form submissions on the web site.  So < 1mb per form.

3. Some of the data is already encrypted, even in the database at rest.  Still, I'd like a secure method of transfer.

1. Script this can be a PHP script or similar or a simple mysqldump run from a shell script although with the latter extraction to CSV is a bit more complex - the script solution gives a lot more flexibility

 This is the direction I'm leaning as it seems the simplest overall.

Direct DB connection
I have in many cases set up the connection to run over SSH to further enhance security.

 This is something I think I need to explore on the Access side and how I might accomplish that.   A direct DB connection would make this drop dead easy as far as working with the data.

Jim.
There is an ODBC driver for MySQL but I don't know about using that over a remote connection.

One solution might be to synch to a local DB and then import to Access (if that is your final destination) using the ODBC link. Depends how much automation you want.

SQLYog has scheduled backups as part of their tool which might be worth exploring - I know they also produce some Enterprise tools - but I don't have any experience with those.

The thing here is you are playing in one of the biggest and busiest pools on the internet WP / PHP /MySQL - you are not limited for options - it is a question of which ones are going to best suite your use case.
I have a couple of methods for using PHP/curl to download info over HTTPS connections.  Requires a PHP program on the web site and on your computer.  Easy if you have a web server that can run PHP on your local computer.  If not, I use a program called Exeoutput4PHP to create standalone EXE's from PHP.   http://www.exeoutput.com/
Depends how much automation you want.

  I should have mentioned that; this needs to be a fully automated process and it will run 24x7.

  If it is a batch style process, as far as the cycle, I'm thinking once every 15 minutes max.

  I'd love it to be a push process that's triggered whenever a form is submitted.   I hate the thought of running something every xx minutes.   It wastes time when there is nothing there, and delays processing when there is something.

 Thanks for the input so far and I will close this off shortly.   I need to toss things around a bit and check out some of the things you've already raised.

Jim.
If MySQL has the ability to sync with a local copy of the database, that would seem to be the cleanest method.  Your Access app (you did include Access in your topics) can link to the MySQL database.
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm going to explore the ideas presented and will most likely end up with a script on the remote web site to create the CSV files, then have a job on the local machine to reach out and grab the files with SFTP.

 That seems the most straight forward.

Thanks,
Jim.