Visit Our UNIX and Linux User Community


bash mysql export to file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting bash mysql export to file
# 1  
Old 05-25-2010
bash mysql export to file

I'm trying to export a mysql query to a .csv file, right now I'm running a successful query like:
Code:
us_id=`mysql -u $USER_NAME --password=$PASSWORD -D "databasename" \
        -e "SELECT * \
        FROM databasename.table \
        WHERE somefield >0 AND otherfield ='$ctry' \
        ORDER BY users \
        DESC LIMIT 0,100";`

but I want to export it to a file, which I've tried by using:
Code:
us_id=`mysql -u $USER_NAME --password=$PASSWORD -D "databasename" \
        -e "SELECT * \
        FROM databasename.table \
        WHERE somefield >0 AND otherfield ='$ctry' \
        ORDER BY users \
        DESC LIMIT 0,100 TO OUTFILE 'somefile.csv' ";`

but then I get a db access denied error. I've also tried piping the output to sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’, which also didn't work. If I could get TO OUTFILE to work I'd hopefully add this code to make the fields formatted:
Code:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ‘\\’ LINES TERMINATED BY '\n'

but I can't get the OUTFILE function to work. I could also use mysqldump I guess, but I don't know how to combine the query with a mysqldump. my output file exists and I chmod'ed it to 777 (for testing purposes).

---------- Post updated at 12:27 PM ---------- Previous update was at 10:10 AM ----------

got it, well really never got the TO OUTFILE working, but this worked:
Code:
echo "SELECT * \
        FROM somedb.sometable \
        WHERE field1 >0 AND field2 ='whatever' \
        ORDER BY field3 \
        DESC LIMIT 0,100 ;" > tmp
mysql -sN -u $USER_NAME --password=$PASSWORD -D "somedb" < tmp > temp_to_turn_into_csv_later.csv

now I have to parse the temp csv file into a csv using awk, there's probably an easier way though.
# 2  
Old 05-25-2010
Quote:
got it, well really never got the TO OUTFILE working, but this worked:
"TO OUTFILE" didn't work because it's not a valid MySQL command.

Use "INTO OUTFILE" instead.

Previous Thread | Next Thread
Test Your Knowledge in Computers #891
Difficulty: Easy
In Unix, sed is often used as a filter in a pipeline.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

2. Shell Programming and Scripting

Invoking a bash shell with an export var

Hello all, How can I invoke the bash shell (via command line) to execute another command by setting an exported environmental variable on the fly (as this env var would be used by the command -another script, the bash would execute). This needs to be done in one single line as the same would... (4 Replies)
Discussion started by: Praveen_218
4 Replies

3. Shell Programming and Scripting

BASH script to export var to env

Hi all I am trying to create a script that takes a password input then writes that to a tmp file and puts that tmp file path in my env as a var. It does everything but export the my env and I am unsure why. I am using Ubuntu 12.4 #!/bin/bash read -s -p "Enter Password: " gfpassword... (5 Replies)
Discussion started by: koikoi
5 Replies

4. Programming

Loop in bash file for mysql

Hi, I'm having problems with a script which some of you helped me with last week. It's a script to check the status of orders in an SQL database, and if the count(*) is zero, then the script stops. If it's non-zero, the script echos the result to a file and then in cron, I cat the file and mail... (3 Replies)
Discussion started by: davidm123SED
3 Replies

5. Shell Programming and Scripting

export bash history to file

Hi, I want to export bash history to a file, I used the following command history > /home/administrator/bashHistory But the exported file only contains commands with line number from 996 to the last one, How to export all the commands including commands before line 996? Thanks a lot.... (2 Replies)
Discussion started by: Roy987
2 Replies

6. UNIX and Linux Applications

MySQL Daemon failed to start - no mysql.sock file

After doing a yum install mysql mysql-server on Fedora 14 I wasn't able to fully install the packages correctly. It installed MySQL 5.1. I was getting the following error when running the: mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)... (3 Replies)
Discussion started by: jastanle84
3 Replies

7. UNIX for Advanced & Expert Users

Bash script with export variables

Hi all guys, how you can read in thread title, I'm deploying a bash script in which I have to export some variables inside it. But (I think you know) the export command works only inside the script and so, on exit command, the variables aren't set like I set inside the script. Consequently in... (8 Replies)
Discussion started by: idro
8 Replies

8. Shell Programming and Scripting

how can I export an alias and use it in any shell that I want to switch to like bash, ksh

None of the aliases that I set are available if I switch to a different shell. How can I export aliases and make them available in any shell that I switch to like ksh or bash ? I tried these $>alias godata='cd /home/kc/app/data' $>alias -x godata='cd /home/kc/app/data' $>alias |... (2 Replies)
Discussion started by: kchinnam
2 Replies

9. Shell Programming and Scripting

export not working in Bash shell

Hi Friends, I am presently migrating shell scripts writter in KSH to SH.I am stuck at this place and i am not able to find a work around:- Let the script name is x.sh Below are some of the codes in it... export abc=hello export abc=hi export abc=how When i am trying to compile the script ... (6 Replies)
Discussion started by: amit.behera
6 Replies

10. Linux

Cannot running export database using bash script

Hi all, I'm new in linux. When I try to run a bash script, it doesn't execute and i receive the following error message 20070321:220002|ERROR||exportDatabase.bash|Another EXPORT process (pid=2799) is still running. If i kill this pid, i receive "No such process". This process was running... (5 Replies)
Discussion started by: tovohery
5 Replies

Featured Tech Videos