Automatically reset MySQL to default??

Hi everyone,

I have a quick question for all the MySQL-savvy people out there. But before I ask I must warn you, I am quite new at all of this (compared to most of you guys anyway), so my questions may be rather stupid.

I sell a few scripts on my site and have live demo's of the scripts for potential customers to try out. These live demo's include access to the admin control panel, where anyone can change any setting, so....

I need a way to automatically reset the demo to it's default settings (by resetting the associated Mysql database to how it was after fresh install, ie: all created tables to 0 records) every hour or so. If there was a command to run it could be done with a cron job, no?? if so, how and what command?

any help would be greatly appreciated.

Thanks,
Sean

 

Best bet is to create a script that will drop and then recreate all of the tables. You would then use a cron to run the script every hour or so.

You can dump the tables to get the create table scripts. You would then just need to run drop table ... and then create table ...

You could also make a stored procedure that does all of this. It would be fairly complicated by you would only need to make a single query to get it all completed.

 

Ok, thanks for the info, but.....

I guess I should have elaborated, when I said I was quite new at all of this, I actually meant that I am a complete and utter novice that has no idea.

I have dumped my db, and got the create table scripts, is this what you meant by create table script, eg:


CREATE TABLE IF NOT EXISTS `admin` (
`auser` varchar(25) default NULL,
`apass` varchar(25) default NULL,
`adminid` int(11) NOT NULL auto_increment,
PRIMARY KEY (`adminid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2


but I have no idea how to make a drop tables script, and how to compile it all into one script (I am guessing .sql?) that can be run by a cron.

how would I go about making a stored procedure?? would that be easier than creating a script and all that?? (taking into consideration that I have no idea what I am doing in either case).


sean

 

Haven't tested it, not tuned it, but this should work. Rename Tables_in_Database to Tables_in_<YourDBName>

function backup(){
$sql="show tables";
$result = mysql_query($sql);
	while($row = mysql_fetch_array($result))
	{
	$table=$row['Tables_in_database'];
	$back=$table."-back";
	$sql2="create table $back like $table;";
	$result2 = mysql_query($sql2);
	$sql2="insert into $back select * from $table;";
	$result2 = mysql_query($sql2);
	}
}

function restore(){
$sql="show tables like '%-back';";
$result = mysql_query($sql);
	while($row = mysql_fetch_array($result))
	{
	$back=$row['Tables_in_database{%-back)'];
	$table= str_replace("-back", "", $back);
	$sql2="drop table $table;";
	$result2 = mysql_query($sql2);
	$sql2="rename table $back to $table;";
	$result2 = mysql_query($sql2);
	backup();
	}
}

PHP:

 

Skip the stored procedure for now. Also, are you using php, I'm assuming so.

You will need to make sure your user has create and drop priviledges in case it doesn't.

You will need to have 1 script, name it something like reset.php.

//reset.php

//Put database connection stuff up here.


//Repeat the following for each table...
mysql_query("
DROP TABLE `admin`
");

mysql_query("
CREATE TABLE IF NOT EXISTS `admin` (
`auser` varchar(25) default NULL,
`apass` varchar(25) default NULL,
`adminid` int(11) NOT NULL auto_increment,
PRIMARY KEY (`adminid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1");

PHP:

For the cron every hour it would normally be:

0 * * * * nohup /usr/local/bin/php -f /path/to/reset.php > /dev/null &

This may be different depending on your server configuration. Do you use cpanel or another control panel?

 

Worked like a treat, Thanks!