Welcome to WebmasterWorld Guest from 35.170.81.210

Forum Moderators: bakedjake

Message Too Old, No Replies

Best way to shcedule MySQL data dump?

Cron/php/MySQL/ftp/ecommerce data dump

     

naked_ollie

2:51 pm on Oct 5, 2005 (gmt 0)

Inactive Member
Account Expired

 
 


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!

11:33 pm on Oct 5, 2005 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 28, 2003
posts:366
votes: 0


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

10:07 am on Oct 6, 2005 (gmt 0)

Inactive Member
Account Expired

 
 


Awesome, thanks for the tips. I'll be trying that out.
4:13 pm on Oct 6, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member wheel is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Feb 11, 2003
posts:5072
votes: 12


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

naked_ollie

4:18 pm on Oct 6, 2005 (gmt 0)

Inactive Member
Account Expired

 
 


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.
4:49 pm on Oct 6, 2005 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 28, 2003
posts:366
votes: 0


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.
4:56 pm on Oct 6, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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

7:26 pm on Oct 6, 2005 (gmt 0)

Inactive Member
Account Expired

 
 


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members