Best way to shcedule MySQL data dump?

Cron/php/MySQL/ftp/ecommerce data dump

[naked_ollie]

I need to extract sale info from my MySQL database and get it into a CSV format, which I then need to ftp to the mailing house so orders can be fulfilled.

I'm not too brilliant at shell scripts but I have dabbled with Cron before. Being better at php I thought I'd use it for the data extraction and ftp part.

So before I start, here's my plan - please let me know if there is a better way of doing it!

1. Set up a cron job to call a shell script at a regular time each day
2. Shell script calls a php script
3. php script gets the data, formats it in csv and saves it to disk
4. php script ftps the file to the mailing house server
5. php sends error or success email to me, end

Sugestions please!

[MattyMoose]

Hello, and welcome to WebmasterWorld!

Your plan sounds like a good one, and we regularly use this to extract reporting data from our databases and email it out.

I have a few suggestions:

Change your flow to this:

1. Set up a cron job to call your php script
2. php script gets the data, formats it in csv and saves it to disk
3. php script ftps the file to the mailing house server
4. php sends error or success email to me, end

There's no need to run a shell script, that all it does is call the PHP file.

We typically will have something like:

0 0 * * * /usr/local/bin/php /path/to/your/php/file.php

And, if your app will be sending you succss/failure notifications, you may want to change it to the following line once you've ensure it works properly.

0 0 * * * /usr/local/bin/php /path/to/your/php/file.php 2>&1 1>/dev/null

That will redirect any output from the file to /dev/null, so you won't get those pesky output emails anymore, if you receive them.

[naked_ollie]

Awesome, thanks for the tips. I'll be trying that out.

[wheel]

Do you not need to shut down the mysql server while doing the dump to prevent any data loss?

[naked_ollie]

I'd hope not - I'm not actually indending to 'dump' stuff, just run a query to extract the data I need and then save it in a file to be ftp'd.

[MattyMoose]

And you can do hot backups no problem, that maintain consistency of the backed up database using mysqldump [dev.mysql.com]. Most modern-day databases will have some functionality to provide a row-locking, hot-backup facility.

[jatar_k]

Welcome to WebmasterWorld naked_ollie,

Mr. Moose and I use php for everything and have done exactly what you are talking about tons of times. It works really well and allows us to do some baseline interpretation of the data to make it easier for the next program to use it.

i use shell scripts sometimes but most of the time php will do it.

>> shut down ... to prevent data loss

well, you shouldn't be losing anything and since, in this case, it isn't really dumping then it won't matter

loss is also relative to whatever you are doing, you aren't going to lose anything if you miss changes, you will pick those up in the next run.

[naked_ollie]

JK and Moose,

Thanks for the warm welcome and the helpful advice. I have got the cron part working nicely now and it is hitting a dummy php script just fine. All I need to do is flesh out that php...!

>> loss is also relative to whatever you are doing, you aren't going to lose anything if you miss changes, you will pick those up in the next run.

Exactly the case in this instance.