Ask a Jedi: MySQL Dumping

A user asks:

I perform nightly site maintenance via the scheduler, but before I delete any db rows I'd like to email myself a mysql dump as a flat file for backup. How can I do this?

So, let me preface my answer by saying I’m no MySQL expert. I’ve used MySQL for one client, and outside of that, I just use it for testing of my various applications. That being said, I have done something similar for a client before. The client wanted a nightly backup of the data. All the info in database X would be copied to database Y. I did it using this script:

mysqldump -u x -pPASSWORD foo > foo.sql mysqladmin -u x -pPASSWORD -f drop foo_auditor mysqladmin -u x -pPASSWORD create foo_auditor mysql -u x -pPASSWORD foo_auditor < foo.sql

As I said, I’m no expert, so there may be a simpler way. That being said, the only thing you care about is line one. That line uses the mysqldump command to dump the database, in this case foo, to a SQL file. You should be able to run this command via CFEXECUTE. You would then use cffile to read in the file and mail it to yourself. Or attach the file to a mail.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments