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

 

[SeLLeRoNe]

you should make a script .sh that run eveyr night...

ive found on google a single command line for drop all tables but didn tested...

the script should be like

nano restore_db.sh

paste this editing the right information

#!/bin/bash

#DROP ALL TABLE INTO THE DB
mysql -u USERNAME -pPASSWORD DB_NAME -e "show tables" | grep -v Tables_in | grep -v "+" | \
gawk '{print "drop table " $1 ";"}' | mysql -uUSERNAME -pPASSWORD DB_NAME

#IMPORT ORIGINAL .SQL
mysql -uUSERNAME -pPASSWORD DB_NAME < FILE.SQL

chmod +x restore_db.sh

Didnt tested it, but should work, im sure about IMPORT section cause ive used that command line much times...

Not tested DROP ALL section cause i took online... and he sayd that he print all the tables and do a DROP for each one, so, should work

Ofc try it out, if work, set a crontab to run that comand everyday at midnight

Hope it help

 

[Scolpy]

you should make a script .sh that run eveyr night...

ive found on google a single command line for drop all tables but didn tested...

the script should be like

nano restore_db.sh

paste this editing the right information

#!/bin/bash

#DROP ALL TABLE INTO THE DB
mysql -u USERNAME -pPASSWORD DB_NAME -e "show tables" | grep -v Tables_in | grep -v "+" | \
gawk '{print "drop table " $1 ";"}' | mysql -uUSERNAME -pPASSWORD DB_NAME

#IMPORT ORIGINAL .SQL
mysql -uUSERNAME -pPASSWORD DB_NAME < FILE.SQL

chmod +x restore_db.sh

Didnt tested it, but should work, im sure about IMPORT section cause ive used that command line much times...

Not tested DROP ALL section cause i took online... and he sayd that he print all the tables and do a DROP for each one, so, should work

Ofc try it out, if work, set a crontab to run that comand everyday at midnight

Hope it help

I posted this thread in WebHostingTalk to and finaly, I wrote this 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 you help!

Yonatan

 

[SeLLeRoNe]

i wouldnt prefer access ssh from php

 

[Scolpy]

i wouldnt prefer access ssh from php

Okay, I tried to rum your code but I got this error:

-bash: ./restore_script.sh: /bin/bash^M: bad interpreter: No such file or direct
ory

 

[SeLLeRoNe]

ok try to remove

#!/bin/bash

not really needed i think, or try to change with

#!/bin/sh

 

[SeLLeRoNe]

ive just realized that in your code you dont drop all the table

and.. . i just realized that if you put the drop table into the .sql file you dont need nothing more then this:

mysql -uUSERNAME -pPASSWORD DB_NAME < FILE.SQL

into the .sh file.. so.. just that line will do all the work

 

[Scolpy]

ive just realized that in your code you dont drop all the table

and.. . i just realized that if you put the drop table into the .sql file you dont need nothing more then this:

mysql -uUSERNAME -pPASSWORD DB_NAME < FILE.SQL

into the .sh file.. so.. just that line will do all the work

Now I got this error:

-bash: ./restore_script.sh: /bin/sh^M: bad interpreter: No such file or direct
ory

 

[SeLLeRoNe]

just put

mysql -uUSERNAME -pPASSWORD DB_NAME < FILE.SQL

nothing else