Solved

export field to text file from mysqldump cli / ubuntu 10

Posted on 2011-09-07
7
622 Views
Last Modified: 2012-05-12
Using:
Ubuntu 10.10
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686) using readline 6.1
mysqldump

I need to export a field from a table into a text file and have the following format:

line 1
line 2
line 3
line n
0
Comment
Question by:_3mp3ror_
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:jackiechen858
ID: 36497963
try
mysqldump -T/tmp/ databasename tablename -pDatabasepassword

This will create two files in /tmp/ folder:

tablename.sql the SQL query to create the table.
tablename.txt, a text file with all the data in this table.

For some reason, on my Ubuntu machine, the first file is created by user root, the second txt file is created by user mysql, so make sure mysql user have permission to the output directory set by -T ( i use /tmp/, so mysql user has enough permission.)


0
 
LVL 8

Accepted Solution

by:
stalhw earned 250 total points
ID: 36498062
mysqldump won't allow you to output only one field (column)...
But the mysql CLI tools will, try something like:

mysql -e "select yourField from yourTable" -u myuser -pxxxxxxxxx mydatabase > yourdumpfile.txt
0
 
LVL 9

Author Comment

by:_3mp3ror_
ID: 36498295
stalhw your method worked

mysql -e "SELECT field FROM table" --default-character-set=utf8 -u user -pxxxxxx database > dump.txt as i also had some eastern european charset

but what is the syntax needed so that I don't get the field name on the first line in the dump file ?

 
0
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 
LVL 8

Expert Comment

by:stalhw
ID: 36498495
I think it's  --skip-column-names
0
 
LVL 7

Expert Comment

by:jackiechen858
ID: 36498499
mysql -e "SELECT field FROM table" --default-character-set=utf8 -u user -pxxxxxx | tail  -n+2  >dump.txt
0
 
LVL 7

Expert Comment

by:jackiechen858
ID: 36498513
oh yeah, there is a --skip-column-names, this one is better.
0
 
LVL 9

Author Closing Comment

by:_3mp3ror_
ID: 36498802
Perfect, thanks.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

628 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