Auto Export MySQL Database to CSV File

I want to create a file that when ran will export my whole MySQL database to a csv file. Ultimately this will be done nightly. I would just like to see it done automatically rather than manually.

Any tips or tutorials to walk me through it?
BHUCAsked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
You may use SELECT INTO OUTFILE to export single table as a CSV file. To do this for whole database, I'd recommend you to write looped shell script.

SELECT * INTO OUTFILE '/csv_files/db.tablename.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db.tablename;

Open in new window



Even better use this and all your pain is eased:--

mysqldump --tab=/var/lib/mysql-files/ --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\n' --all-databases

Now you will have the .txt file for each of the table under /var/lib/mysql-files
0
 
BHUCAuthor Commented:
Thank you
0
 
theGhost_k8Database ConsultantCommented:
Glad that this solved your problem. Since I saw this at multiple places, I thought of writing a post about it. Read it here -> 3 ways to export from MySQL to CSV
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.