Extracting Data Daily from MySQL to a Local MySQL DB

Question

I'm doing some experiments locally that require some data from a production MySQL DB that I only have read access to. The schemas are nearly identical with the exception of the omission of one column. My goal is to write a script that I can run everyday that extracts the previous day's data and imports it into my local table.

The part that I'm most confused about is how to download the data. I've seen names like mysqldump be tossed around but that seems a way to replicate the entire database. I would love to avoid using php seeing as I have no experience with it. I've been creating CSVs but I'm worried about having the data integrity (what if there is a comma in a field or a \n) as well as the size of the CSV (there are several hundred thousand rows per day).

Answer 1

No way will be faster than mysqdump, if you have hundreds of thousands rows best and easy solution will be it and mysqldump not only exports entire database, you can export just the tables you want.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

mysqldump.exe -u root -p DATABASENAME TABLE1 TABLE2 > exported_tables.sql

this will export table1 and table2 from databasename.

Also you can use where parameter to filter results:

 --where='where_condition', -w 'where_condition'

Dump only rows selected by the given WHERE condition. Quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter.

Examples:

--where="user='jimf'"
-w"userid>1"
-w"userid<1"

Answer 2

You need some form of dblink between the two databases. Please see this discussion and pick up the suitable answer depending on whether your production database is going to be accessed locally or from a remote machine:

Oracle Database Link - MySQL Equivalent?

My understanding is you have to access to the production server from a separate MySQL server plus you don't want an exact copy and you need to write customized queries. If so, using FEDERATED MySQL storage engine would perhaps be the best available option you could have. When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. Here.