Professional Web Applications Themes

MySQLdump and restore on very large DB - MySQL

Hi, I have a very large database > 1.5GB running on 2 servers all MyISAM tables. One is a staging server and one the live server. On the staging server the data is manipulated, so rows are modified/ removed/added constantly. The live server has a copy of these tables and other tracking tables that are not on the staging server. Once a day I update the live data with the data from the staging server. I use MySQL dump and then a basic import to overwrite the existing tables with the new data. This, however is taking extremely long and ...

  1. #1

    Default MySQLdump and restore on very large DB

    Hi,

    I have a very large database > 1.5GB running on 2 servers all MyISAM
    tables. One is a staging server and one the live server.

    On the staging server the data is manipulated, so rows are modified/
    removed/added constantly. The live server has a copy of these tables
    and other tracking tables that are not on the staging server.

    Once a day I update the live data with the data from the staging
    server. I use MySQL dump and then a basic import to overwrite the
    existing tables with the new data.

    This, however is taking extremely long and the DB table on the live
    server is unavailable during the import which is not ideal.

    Is there a better way to do this? Is tion the answer? I've
    never used it and wouldn't know where to start. The only issue being
    that the live server contains some tables that are not on the staging
    server and I would like to keep it that way.

    Any expert help would be greatly appreciated. Thanks

    tucj7 Guest

  2. #2

    Default Re: MySQLdump and restore on very large DB

    tucj7 <com> wrote: 

    Hehe. 1.5GB is not considered large :-)
     

    Yes. And Yes.
     

    Then start reading the manual:

    http://dev.mysql.com/doc/refman/5.0/en/tion-intro.html
    http://dev.mysql.com/doc/refman/5.1/en/tion-howto.html


    some notes from me:

    - I intentionally gave the link to 5.1 manual because tion
    chapters have been restructured to be better understandable.

    - I suggest to use the mysqldump --master-data method to get a
    clean copy of your staging server (master) to your live server
    (slave)

    - There is no need to have the slave process running all time.
    If you want to stay with daily synchronization, just start the
    slave once a day (START SLAVE) and when it has ted all
    changes (seen in Seconds_Behind_Master from SHOW SLAVE STATUS)
    then just stop it (STOP SLAVE)

    - You should set a sensible max. binlog size on your master and/or
    rotate the binlog (e.g. with FLUSH LOGS). There are more options
    re. tion and binary log:

    http://dev.mysql.com/doc/refman/5.0/en/tion-options.html
    http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

    - If you stay with daily synchronization, you should setup a routine
    like this:
    1. FLUSH LOGS on master to start a new binlog
    2. START SLAVE, wait until the slave reached the new binlog
    3. STOP SLAVE
    4. PURGE MASTER LOGS TO '<insert current binlog>'
     

    This is no problem and will work out of the box.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: MySQLdump and restore on very large DB

    == Quote from Axel Schwenke (de)'s article 
    > Hehe. 1.5GB is not considered large :-) 
    > Yes. And Yes. 
    > Then start reading the manual:
    > http://dev.mysql.com/doc/refman/5.0/en/tion-intro.html
    > http://dev.mysql.com/doc/refman/5.1/en/tion-howto.html
    > some notes from me:
    > - I intentionally gave the link to 5.1 manual because tion
    > chapters have been restructured to be better understandable.
    > - I suggest to use the mysqldump --master-data method to get a
    > clean copy of your staging server (master) to your live server
    > (slave)
    > - There is no need to have the slave process running all time.
    > If you want to stay with daily synchronization, just start the
    > slave once a day (START SLAVE) and when it has ted all
    > changes (seen in Seconds_Behind_Master from SHOW SLAVE STATUS)
    > then just stop it (STOP SLAVE)
    > - You should set a sensible max. binlog size on your master and/or
    > rotate the binlog (e.g. with FLUSH LOGS). There are more options
    > re. tion and binary log:
    > http://dev.mysql.com/doc/refman/5.0/en/tion-options.html
    > http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
    > - If you stay with daily synchronization, you should setup a routine
    > like this:
    > 1. FLUSH LOGS on master to start a new binlog
    > 2. START SLAVE, wait until the slave reached the new binlog
    > 3. STOP SLAVE
    > 4. PURGE MASTER LOGS TO '<insert current binlog>' 
    > This is no problem and will work out of the box.
    > XL[/ref]

    Axel,
    Do you by any chance have a generic script that does the 4 step synch process?

    --
    POST BY: lark with PHP News Reader
    lark Guest

  4. #4

    Default Re: MySQLdump and restore on very large DB

    lark <net> wrote: 

    <cut>
     

    Nope.

    <joking>
    If you buy a platinum support contract I will happily write you one ;)
    </joking>


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  5. #5

    Default Re: MySQLdump and restore on very large DB

    Axel Schwenke wrote: 
    >
    > <cut>

    >
    > Nope.
    >
    > <joking>
    > If you buy a platinum support contract I will happily write you one ;)
    > </joking>
    >
    >
    > XL
    > --
    > Axel Schwenke, Support Engineer, MySQL AB
    >
    > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    Axel, Axel, Axel:

    where is the goodness of your heart?

    actually, i didn't expect you to provide something like that. i just
    wanted a pointer! but if you don't know of any, don't worry!

    thanks
    lark Guest

Similar Threads

  1. Backup & restore MySQL, not using mysqldump
    By Jerry T in forum PHP Development
    Replies: 2
    Last Post: November 15th, 09:49 PM
  2. Replies: 6
    Last Post: May 24th, 01:48 PM
  3. mysqldump restore fails: Error 10167
    By emwiel@gmail.com in forum MySQL
    Replies: 3
    Last Post: May 1st, 03:47 PM
  4. Replies: 5
    Last Post: February 16th, 09:04 AM
  5. Physical restore that doesn't require a logical restore
    By Christian Eriksson in forum Informix
    Replies: 6
    Last Post: September 24th, 11:30 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139