I need to restore a database (mysql) every 30 minutes using a cron job.

Question

I'm new to cron jobs and I need to restore a database (mysql) every 30 minutes. Is there a cron job command that can restore a database from a .sql file that's been gzipped?

Or do I need to create a php script to do this and create a cron job to call this script every thirty minutes?

Also, and this is a separate question but still related to cron jobs, I'm using a cron job to backup a different database once a day, gzip it and put it in a folder above the root. Is there a way to (automatically) delete anything older than a month? Or, at least, keep the most recent 20 backups and delete the rest?

There's not a lot of good tutorial out there on this subject other that random forum posts. Any help is appreciated.

Answer 1

Regarding how to import a dump file, simply put a

mysql -u user -ppassword databasename < /path/to/dump.sql 

into the cron job.

More details: How do I restore a MySQL .dump file?

Answer 2

You can write a bash script to do this.

mysql -uPutYourUserHere -pPutYourPasswordHere PutYourUserHere_databaseName < database.sql

There isn't anything to automatically delete something. But you can do in your cron job:

find /path/to/files -mtime +30 -exec rm  {}\;

Answer 3

MySQL can't handle the gzipped data directly, but it's trivial to pipe through gzcat and then pipe that to mysql:

gzcat name_of_file.sql.gz | mysql -u....