Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
11-13-2013, 06:43 AM #1
Restore a demo database php mysql cron
Hi this is nothing fancy and still needs a bit of tweeking with some TODO stuff left but it does function and it does do the job for me as is for this particular db and sql structure. So i wanted to share it as a starter for someone who was trying to do the same thing.
Mission: I have a software demo that is still uses the mysql library and i want to restore the data in the database every 5 hours (4x a day) to keep the data fresh.
There are two files:
One is the php file that builds an array from the sql file and uses ; as the end of the query and the processes the query. This means that you have to make sure you dont have or other stuff in your text data that ends in ; other than the end of the query itself. This is on the TODO list. I actually had a routine that worked but i didnt like it so i want to redo it.
The second file is the sql file. I went through the file and removed all comments because a of now i dont filter those. Again i had a routine but it was a hack at best and i wanted to do a better job. So i just cleaned up my sql file so that this would work as is for now.
Then in cron you just execute the php file when you want.
Here is my php file
* dbrestore.php 1.0 Nov 2013
* Description: Restore demo db using this file executed by cron
$rdbhost = 'localhost';
$rdbuser = 'prefix_username';
$rdbpwd = 'password';
$rdbname = 'prefix_dbname';
// Open db connection
$rlink = mysql_connect($rdbhost, $rdbuser, $rdbpwd);
die('Could not connect: ' . mysql_error());
die ("Can't use database : " . mysql_error());
// read the sql file and write to database
$filename = 'restore.sql';
if (!($f = fopen($filename, "r" )))
$msg = 'Could not open file with sql instructions: '.$filename;
//file is open so read it
$sqlf = fread($f,filesize($filename));
$sqlarray = explode(';',$sqlf);
while ($s = fgets ($f, 10240))
//now run the loop and process statements
foreach($sqlarray as $key => $sqlstatement)
//check if there is a statement or not so you dont have an empty query
//readd the ; back to the end of the statement
$sqlstatement = $sqlstatement.';';
$results = mysql_query($sqlstatement);
//todo need to work on protecting from possible sql injection on this kind of project via cron
//todo need to filter out things like and text lines that end in ; but are not the end of the query
//todo need to filter out comments in the sql file
$mserror = mysql_error($rlink);
echo "Error: ";
echo " message: ";
} //close if sqlstatement
// at the end of each sql statement show done -- for testing only
// echo $sqlstatement." DONE!";
// echo "<br /><br />";
} //close foreach
}// close else
// close up shop and exit
//todo test to see if this echo shows up in the cron report or not, i actually dont know.
echo "db has been restored";
Any improvments or suggestions welcome... especially when it comes to protecting again sql injection on this type of project.
Last edited by durangod; 11-13-2013 at 06:46 AM.
11-13-2013, 02:42 PM #2
One thing i forgot to share last night was the fact that in my sql i added some truncates to the first part of the file to clear the tables before reload. You are not deleting the DB your are just clearing it and restoring the data.
so in my case i have 17 tables, i could have put them into an array but since so few i just put 17 truncates
TRUNCATE TABLE prefix_name;
TRUNCATE TABLE prefix_name;
TRUNCATE TABLE prefix_name; ... and so on
then i started my create and insert querys.