Originally Posted by VIPoint
Create a file in /usr/local/src with the following contents.
mysqldump -u USER -p PASSWORD DATABASE > /usr/local/backup/filename.sql
mysqladmin drop database <DATABASE>
mysqladmin create database <DATABASE>
Make sure you change the <DATABASE> with your database name. Change the permission of the file to 755 and run it as cron everynight. Also try it on a test database before you try it on real database.
Wrong! This will not retain any of your structure! Do not do this!
What you want to do is the following:
1. Start with a fresh install of Joomla/whatever that you want to restore to every night. Include any sample data, etc.--the point is to get it to the exact state you want to start from every day.
2. Do a mysqldump of that database and save it somewhere safe.
mysqldump -u root -p my_db_name > /somewhere/safe/joomla_orig.sql
3. Now, use Tim's method to reload that saved state nightly. It could be as simple as putting the following little bash script in /etc/cron.daily.
(Notice the lack of a space between -p and your password!)
/path/to/mysql -u root -ppassword my_db_name < /somewhere/safe/joomla_orig.sql
Then, just make sure that script is executable, and cron will fire it off nightly.