Solved

export field to text file from mysqldump cli / ubuntu 10

Posted on 2011-09-07
7
618 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
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.

738 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