Solved

Mysql Database Export into CSV

Posted on 2011-02-22
4
835 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

615 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