Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Reset Databases every midnight

[Scolpy]

Hello!

I have few demo systems(like wordpress, phpbb, joomla and etc) on my business website - for customers testing.
since I saw that people posting a lot of spam, changing the passwords for the demo users and do more things like this - I decided to reset the systems database every midnight to the default structure(I have SQL file that contains the fresh installation for each system) .

so, there is a way to do this quickly?
(like a command in CronJobs, short PHP script and etc)

Thanks!

Yonatan

[Mark Muyskens]

have a cron run a bash script that simply dumps and recreates the databases.

[Scolpy]

Originally Posted by Mark Muyskens

have a cron run a bash script that simply dumps and recreates the databases.

Can you give me some example about how to do this or link to helpful article?(I don't really familiar with bash language).

Thanks!

[tim2718281]

Originally Posted by Scolpy

Can you give me some example about how to do this or link to helpful article?(I don't really familiar with bash language).

Thanks!

You can use any script language.

You have an SQL file; do you know how to run it from a command prompt?

mysql --user=yourusername --password=yourpassword db_name < yourscript.sql > output.file

test it to ensure it works.

Then put that command inside a script file, then set up cron to run the script file.

[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.

[mattle]

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.

#!/bin/bash

/path/to/mysql -u root -ppassword my_db_name <  /somewhere/safe/joomla_orig.sql

(Notice the lack of a space between -p and your password!) Then, just make sure that script is executable, and cron will fire it off nightly.

[tim2718281]

And remember that any files containing passwords should be readable only by the userids that need to read them.

[Scolpy]

Finaly, I wrote this code:

PHP Code:

<?php

ini_set('max_execution_time', 5000);

$connection = ssh2_connect('localhost', 22);

ssh2_auth_password($connection, 'username', 'password');

ssh2_exec($connection, 'mysql --user=DB-USERNAME --password=USERNAME-PASSWORD DATABASE-NAME < /home/username/default_structure.sql > output.file');

?>

(note that you need php-ssh2 library installed on your system)


Thanks for everybody!

Yonatan

[mattle]

That seems a little pointless...why not use system()?

[Scolpy]

Originally Posted by mattle

That seems a little pointless...why not use system()?

This function is disabled in my server

[Todayisp]

i think there must be some software can do this for you, not manual management and get up in the midnight.

[chosen2host]

You should just use a cronjob!