Solved

Mysql Database Export into CSV

Posted on 2011-02-22
4
816 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
Comment Utility
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
Comment Utility
Do you have phpmyadmin installed on the server?
0
 

Author Comment

by:Thomanji
Comment Utility
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
Comment Utility
Thanks, I notice that phpmyadmin does not take more resources, all worked fine
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now