Solved

Mysql Database Export into CSV

Posted on 2011-02-22
4
826 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

813 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