Reset mysql database using php

Posted on 2012-08-12
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

    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.
    LVL 1

    Author Comment

    yes i have linux server
    LVL 107

    Expert Comment

    by:Ray Paseur
    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.
    LVL 49

    Expert Comment

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

    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!
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now