Reset mysql database using php

hi

I m having a php mysql database  based demo site.
I have the backup sql file in database_file.sql.gz format.

All i want is to run a cron job so that all data from all tables in abc database get reset to original one.
Or drop all tables from database and after that re insert all tables and data from above backup file.

Any simple php code i can use.. please guide

thanks
LVL 1
global_expert_adviceAsked:
Who is Participating?
 
RobRotterdamConnect With a Mentor Commented:
Step 2
unzip the .gz file:
tar -zxvf database_filename.sql.gz

Step 2
Create a batch file:
sudo vim demo.bat
mysql -uroot -ppassword demodatabase -e "drop tables"
mysql -uroot -ppassword demodatabase < database_filename.sql
Escape
Shift-Z, Shift-Z

Step 3
Make the batch file executable:
sudo chmod 0755 demo.bat

Step 4
Test the batch file
./demo.bat

Step 5
Add the batch file to your cron jobs

Step 6
Check if everything works OK

Let me know if something is unclear!
0
 
RobRotterdamCommented:
Why must it be PHP?
By your reference to cron I assume your database is on a Linux server
It's easy to create a batch file using the mysql prompt, make it executable and start it with cron.
If this is an acceptable solution I can help you with setting it up.
0
 
global_expert_adviceAuthor Commented:
yes i have linux server
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
Do you know the names of all the tables in the data base? If not you can use SHOW TABLES to get the names for the DROP.  But you will want to know the names and table structures to recreate the tables.  If you make a backup of an existing data base and clients have changed the contents of the tables, you are at risk that the DROP and INSERT process will not reset the tables to a specific original state.
0
 
Julian HansenCommented:
If you are running a cron job then the correct way to do this is as follows

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

If you are storing your data in a .gz then you will need to create a script to unzip the file apply using above and then remove the script when done.
0
 
global_expert_adviceAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.