I am volunteering for an election observation agency in Ghana and they are collaborating with an organization that has set-up an Ushihidi (ghvotes2012.org) platform so they can crowdsource incidents during the election. The organization I am working for wants all the incident reports to be stored on a separate server where they can download the data as a CSV.
I have never done something like this so I’m sure I’ll have a load a questions, but my first is, what is the best way to query the Ushihidi server, and then put the results onto my organization’s server? I looked at replication, but that is an intensive process. What is the best way to go about pulling the data and inserting it somewhere else?
If you have access to the CSV file then have a look at uploading this into your MySQL database using LOAD DATA INFILE. If the data already exists in an existing MySQL database there are two alternatives open to retrieving this. The first method is to backup just the table that you would need using mysqldump databasename.tablename. The second method is using SELECT INTO OUTFILE which can then be used with the LOAD DATA INTO command.
It seems like your method would work if I was looking to insert the data one time. The issue is these incident reports will be coming in sporadically. I’m thinking someone along the lines of every time someone from my organization checks for incidents the data is reinserted. That way it is always up to date.