Reset mysql database using php

Posted on 2012-08-12
Medium Priority
Last Modified: 2012-10-08

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

Question by:global_expert_advice

Expert Comment

ID: 38285543
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.

Author Comment

ID: 38285804
yes i have linux server
LVL 111

Expert Comment

by:Ray Paseur
ID: 38286068
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 60

Expert Comment

by:Julian Hansen
ID: 38286635
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.

Accepted Solution

RobRotterdam earned 1500 total points
ID: 38286845
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
Shift-Z, Shift-Z

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

Step 4
Test the batch file

Step 5
Add the batch file to your cron jobs

Step 6
Check if everything works OK

Let me know if something is unclear!

Author Closing Comment

ID: 38476294

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question