I need to migrate data from one database (Mysql vendor, used as Alfresco DB) to custom designed Mysql DB used for reporting. Both DB's are on same DB host. My initial idea was to use view as data source for INSERT statement. I eventually confronted with lack of disk space. My view grew up to 100 GB of data.
Once I was suggested to use export/import to file and back to DB tables. Using this advice I realized my temp tables started to consume disk space too.
How can I set buffer size for exporting data to file via query? What is best approach for my problem?