The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Cron MySql database

Discussion in 'General Discussion' started by softstor, Jul 1, 2004.

  1. softstor

    softstor Member

    Joined:
    Mar 31, 2004
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    I have a MySql database site setup as a demo. Every night, I wish to reset the database with all of the sample data.

    Is it possible to setup a cron job that would restore this database back to its origional form every night?
     
  2. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    Are your referring to just empting the database each nite so there is no data in the databases ??

    Mickalo
     
  3. softstor

    softstor Member

    Joined:
    Mar 31, 2004
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    Re: Re: Cron MySql database

    No I have some sample data entered into this database file. I wish to backup or make a copy of this database file, and save this file someplace on my server. Then on a diaily basis run a cron job that would replace the current database with my backup copy of the database.

    I am not that familiar with sql, so any assistance is appreciated.
     
  4. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    Re: Re: Re: Cron MySql database

    Yes, this can be done. as I do all my database programming in Perl, this may not help you if your not fimilar with Perl. but it wouldn't be hard to do and setup a cron job to execute it each nite. Bascially what you want to do is do a "dump" of the data in the database, then delete the data you don't want and retain the data you do, or delete all the data in each table(s) in the database, then insert the data you want back into the database :)

    Feel free to PM or email me directly if you need further assistance.

    Mickalo
     
  5. softstor

    softstor Member

    Joined:
    Mar 31, 2004
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    Actually I was thinking of a much simplier solution, but I am not sure if it can work.

    I have a backup of the sql database using the backup sql feature. I then uploaded this backup sql database to my sever.

    Is it possible to have a cron job perform the 'Restore a MySql database' feature, but instead of manually selecting an upload file, have it fetch the backup file from my server?
     
  6. GeekPatrolMille

    GeekPatrolMille Well-Known Member

    Joined:
    Mar 12, 2004
    Messages:
    84
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    McKinney, Texas, USA
    mysqlhotcopy

    The easiest way to do this would require some scripting. I will explain what needs to happen in a manual effort and maybe someone could help with writing a script and editing the process so it works well...

    First, get the sample data in your database set up the way you want it to be when you do your nightly reload.

    Next, use mysqlhotcopy to clone the database files to a safe storage point.
    # mysqlhotcopy db_name /dir/dir/dir/dirname_for_copy/db_name
    This will make a fresh copy of your sample db in the directory you specify. If you do not specify a destination dir/sb_name, the utility will create a dir called db_name_copy to store the data. Where you store the sample data copy makes no difference.

    Next, at the chosen time, reverse the mysqlhotcopy source and destination to replace db_name with db_name_copy. This is the only thing you would need to process in a cron job. depending on the size of the database, the process will likely be very fast and the only disturbance would be to any user in the sample database at the time the cron job fired.

    The only unknown in this would be... Do you need to shutdown mysql to properly do this or is this handled by the mysqlhotcopy. I know the mysqlhotcopy works well but do not know the underlying mechanics enough to know how to answer this one question.

    Hope this help...
    -greg
     
  7. bigpy2003

    bigpy2003 Well-Known Member

    Joined:
    Feb 22, 2004
    Messages:
    124
    Likes Received:
    0
    Trophy Points:
    16
    You can just use command line, I think...

    mysql -u username -ppassword database_name < file_name.sql

    will dump the database...if it's root accessing it, i dont think you need a password.
     
  8. oderland

    oderland Well-Known Member
    PartnerNOC

    Joined:
    Dec 30, 2002
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Kungsbacka, Sweden
    should be:

    mysql -u username -ppassword database_name > file_name.sql
    or as root
    mysql database_name > file_name.sql
     
Loading...

Share This Page