SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Database copy

  1. #1
    SitePoint Enthusiast Sam32's Avatar
    Join Date
    Sep 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database copy

    Hello,

    I'm wondering about an efficient way to copy a live database into a test database at intervals of 3 months.

    Currently, I'm manually deleting the test database and copying each table of the live database into a new test database by mean of a PHP script.

    Is it possible to schedule a job to take care of this task for instance?

    Thanks in advance.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,019
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    If you're using a linux box then you should have cron jobs available to you
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,131
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    If you're using a linux box then you should have cron jobs available to you
    In conjunction with using mysqldump and the mysql commands.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    In conjunction with using mysqldump and the mysql commands.
    assuming it's a mysql database, of course
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,131
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    assuming it's a mysql database, of course
    Of course, and if it is SQL Server, and you are running 2005 or higher, you can write an SSIS task that will perform the task for you and set it up using a Job so it runs every 3 months.

  6. #6
    SitePoint Enthusiast Sam32's Avatar
    Join Date
    Sep 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any alternatives to mysqldump? It seems to be a bit slow.

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,019
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    When you're copying across the data manually are you using a select insert query?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,131
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sam32 View Post
    Is there any alternatives to mysqldump? It seems to be a bit slow.
    Have you tried looking at the flags you can set? Can you show us the command you were running (without the username and password, of course)

  9. #9
    SitePoint Enthusiast Sam32's Avatar
    Join Date
    Sep 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made a simple test with the standard command.
    mysqldump -u username -p password db_name > dump.sql

    The dump file is stored in the same I/O drive which might be causing extra delay.
    The only option I have is to compress the output.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •