Solved

Mysql Database Export into CSV

Posted on 2011-02-22
4
827 Views
Last Modified: 2012-06-21
Hi Guys,
I hope you can assist me. I have a MySQL database running on a remote RHEL5 server. Used PHPMySQL to export a table with 1.3 Million records into a CSV format and my server load did go up to 250 before the whole server crashed. So the question is if I can use some command maybe in an SSH session in which I can limit the resources to the export. I do not care how long it takes to do the export but somehow I want to limit the resources I give to this export so that it does not crash my server. I understand it is a lot of records so it might take a little time. So basically I like to create a CSV file from one specific Table that is saved in /root/export/ on my linux server.

Thank you for the assistance.
Thomas
0
Comment
Question by:Thomanji
  • 2
4 Comments
 
LVL 1

Expert Comment

by:RajeshTN
ID: 34958768
Hi Thomas,

you could probably export data batch by batch by selecting a range of rows at at time in a loop so that the server is not loaded in one stretch.

Excerpt from mysql tutorial:

SELECT column_list
FROM table_name
WHERE column_1 BETWEEN lower_range AND upper_range


MySQL returns all records in which the column_1 value is in the range of lower_rage and upper_range as well as the values lower_rage and upper_range. The query which is equivalent to SQL BETWEEN to get the same result is

SELECT column_list
FROM table_name
WHERE column_1 >= lower_range AND column_1 <= upper_range


Regards,
-Rajesh
0
 
LVL 11

Accepted Solution

by:
mattibutt earned 500 total points
ID: 34959351
Do you have phpmyadmin installed on the server?
0
 

Author Comment

by:Thomanji
ID: 34959490
Hi.

@RajeshTN - Thanks but I am not to familiar with the SQL statements and how I would merge them together again. Especially there are 50 cells in the table and 1.4 million rows.

@mattibutt - Yes I have phpmyadmin installed. Is there a way to limit phpmyadmin from taking so much resources to kill my server?

Best wishes,
Thom
0
 

Author Closing Comment

by:Thomanji
ID: 34959879
Thanks, I notice that phpmyadmin does not take more resources, all worked fine
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question