I am trying to export multiple tables of my database in putty via the command line but putty keeps throwing back errors at me. I want to maintain the table schema of the data in the export as well. I've been trying the following command but I'm not sure whats wrong with it,
mysqldump -u(username) -p(password) (dbname) "select * from (tablename);" > output.csv
Error: Couldn't find table: "select * from assign;"
I am trying to export all the rows and columns of multiple tables. I am also unable to use OUTFILE option within SQL as the secure-file-priv option is checked and I am not in the position to change it. My only option is to export the table data on putty terminal, hopefully as a csv file so that I can open it in excel.
Many thanks for your help.
You need to list the table names in your command, and not the select query.
Like so (note this will output SQL and not CSV):
mysqldump -u(username) -p(password) (dbname) (tablename1) (tablename2) > output.sql
If you want to output CSV with mysqldump you can use the --tab option:
mysqldump --tab=/tmp --fields-enclosed-by='"' --fields-terminated-by="," --lines-terminated-by="\n" --no-create-info (dbname) (tablename)
If you do need to run a query and output CSV you can use the mysql client:
mysql --batch -e "select * from (tablename) where x=y" > output.csv
This will create a TAB delimeted CSV.