How can I regularly export from one database to another (on same server) in a daily fashion?

Question

Running a beta site (different platform than live site).

Already have export / import process via php script. Automatically gets data from one database and exports to another.

I want to automatically run this (at least daily) transfer to update the beta site with content from live site.

What's my simplest option (aside from manually running the script)?

I was considering:

1. Writing additional functionality into the script to track when last run or run based on a set server timestamp

2. Writing additional functionality to check database for new / updated data (no idea if this is possible)

3. ?????

Also, perhaps this is better suited for a different site (not serverfault), let me know.

Answer 1

You should be able to accomplish this with a simple cronjob that does a sqldump from the first, then sqlimport and restart the second SQL instance (if necessary...)

Personally, I like adding 'logger' statements to my cronjobs to note a step that's beginning and another when it ends so that I can track how long things take.

Answer 2

Sup, Dan!

Cron is the way to go. If you have ssh access to your server, you can do this:

sudo crontab -e

To bring up cron tabs...then you can just add a line showing what script / command to execute:

1 1 * * * php /path/to/script.php

Cron executes based on:

minute, , day of month, month, day of week 

So, 1 1 * * * = 1st minute of the 1st hour of every day of every month of every week

The * are wildcards.

You'll need to have PHP command line interface running to do that. An alternative would be to write a bash / shell script that performs the transfer form the command line. If you've already got the PHP script, though, I'd just stick with that and call it with cron.

Answer 3

You might use mysqlbackup to dump your DB-contents into an sql-file.

That file can be imported with the mysql-super-user (normally called root) by just parsing that file.

There is no PHP involved in this...