Export a large MySql table

Question

I have a table in MySql that I manage with PhpMyAdmin. Currently, it occupies about 960,000 lines.

I have a boss who likes to watch data in Excel, which means weekly, I have to export the data to Excel.

I am looking for a more efficient way to do this. Since I can’t actually do the whole table at once, because it is time. Thus, I was stuck with “splitting” the table into smaller queries and exporting it like this.

I tried connecting Excel (and Access) directly to my database, but with the same problem; this time. Is there a way to expand the connection limit?

Answer 1

Honestly, for this data size, I would suggest doing mysqldump, and then import the table back into another copy of MySQL installed somewhere else, possibly on a virtual machine dedicated to this task. From there, you can set timeouts that are as high as you want, and not worry about the resource limitations that blew up your production database. Using nice on a Unix-based OS or process priorities on a Windows-based system, you can do this without much impact on the production system.

Alternatively, you can configure a replica of your production database and pull data from there. Having a so-called “reporting database” that replicates various tables or even entire databases from your production system is actually a fairly common practice in large environments to ensure that you don't accidentally kill your production database by pulling numbers for someone. As an added benefit, you do not need to wait for mysqldump backup to complete before you start retrieving data for your boss; You can do it right away.

Answer 2

Is your boss Rain Man? Does it just indicate "information" in the raw "data"?

Or is he building functions in excel rather than asking what he really needs?

Sit with him for an hour and see what he actually does with the data. What questions are asked? What patterns are detected (manually)? Write a real tool to find this information, and then connect it to your monitoring / notification system.

Or, take a new boss. Jokes aside. You can tell him that I said so.

Answer 3

The lines are not too long. Run a query to export your data to a csv file:

 SELECT column,column2 INTO OUTFILE '/path/to/file/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM yourtable WHERE columnx = 'çondition'; 

Answer 4

One option is to use SELECT ... INTO OUTFILE or mysqldump to export the table to CSV, which Excel can open directly.