Mysql: database to database transfer

Discussion in 'Servers and Contol Panels' started by mhackl, Jan 31, 2007.

  1. mhackl

    mhackl Enthusiast

    104
    31
    +0
    :idea:
    I need a script that can incrementally transfer all the data from one MySQL database to another. That's my first objective, second is to be able to schedule it.

    What I am trying to do is create a second (backup) database for my Vbulletin website that will be updated automatically during off-peak hours. This would create a twin database that I could hotswap in times of trouble by changing three values in my VB config file.

    The key is that it has to be incremental to accommodate a large database and small import thresholds. What I'm looking for is something like the BIGDUMP script but for a DB to DB transaction.

    This solution could already exist and I wouldn't know. PHP and MySQL are well outside my realm of understanding, so any info you could provide would be greatly appreciated.
     
    1
  2. C1ay

    C1ay Aspirant

    37
    11
    +0
    Search on MySQL replication. It's probably come up in the forums at vbulletin.org so you might look there for any caveats related to vb. Otherwise MySQL is covered in the manual.
     
  3. Rockatteer

    Rockatteer Devotee

    2,730
    412
    +68
    I do this with my database.. In fact I have about 3 "hot" copies of the database (daily, monthly, yearly) as well as regular offline backups.

    I use the below mysql script, which empties out the second database and then copies the tables from your main database... it works on table at a time.. I don't know if theres any other way of copying a database... lord knows I'm no expert at this.

    TRUNCATE `second_database_name`.`ipb_spider_logs`;

    INSERT INTO `second_database_name`.`ipb_spider_logs` SELECT * FROM `First_database_name`.`ipb_spider_logs`;


    replace the second and first database names with the actual names of your database.

    Then repeat these lines for each table of your database.