Professional Web Applications Themes

Making and breaking mirrors - MySQL

I have an application that is going to be required to go on the road. At present, my client has a MySQL 4.0.1 database with more than a million records in it. Their business is changing so that the users are looking at taking portable computers (laptops and/or tablets) into the field to do their work. They need to take a significant portion of the database with them, since sometimes the work takes place in remote areas where there is no internet connectivity, and they need to refer to data in the database. They also need to add new records ...

  1. #1

    Default Making and breaking mirrors

    I have an application that is going to be required to go on the road. At
    present, my client has a MySQL 4.0.1 database with more than a million
    records in it. Their business is changing so that the users are looking
    at taking portable computers (laptops and/or tablets) into the field to
    do their work. They need to take a significant portion of the database
    with them, since sometimes the work takes place in remote areas where
    there is no internet connectivity, and they need to refer to data in the
    database. They also need to add new records and update existing records
    while they are away, and have them merged back into the main database
    when they return.

    I was thinking that I could have each machine that is running locally
    act as a tion-style slave to keep in sync with the main server,
    and then when they need to go roaming, they could have the local (slave)
    server become their main one, and they will then already have all the
    data they need. If I do this, is it going to flood the network keeping
    things up to date? There aren't *that* many updates, but there are
    something like 10 machines that might have to go out into the field. It
    sounds somehow wasteful to be doing this sort of thing on the off-chance
    that some machine (call it PC00) has to go out. Then there is the
    problem of another machine (PC09) that spends very little time in the
    office, because it's always out gallivanting around the country-side. I
    think it's fairly reasonable to expect this sort of variation. Then
    comes the problem of bringing things back up to date on the main server
    when they get back into the office. Can the local machine be easily
    turned from being the only server back into a slave, and have the
    changes merged back into the main server? Remember I'm dealing with
    4.0.1 here.

    I implemented a scheme to allow this in a similar application in
    PostgreSQL. In this case, each of the important tables had a timestamp
    field updated by insert and update triggers. When the user started
    roaming, the whole database was backed up and dumped onto the local
    machine, and the triggers were changed on the local machine to keep an
    audit trail of inserts, updates and deletes. The audit trail table kept
    table names, record IDs, the event type, and the event timestamp. Then
    when the users returned, they ran a program that read the audit trail
    table from the local database, and applied changes to the main server
    where appropriate. Where there were conflicts (the roaming user had
    changed the record whilst out, and a local user had also changed it on
    the main server since the dump), I simply presented a dialog box with
    the field names, local values, main server values and "merged" values,
    and got the user to approve the change. It made for slow-ish departures,
    but very quick returns.

    I saw the posting by Peter Chant that asked a similar question, and
    looked at daffodil. However, I'm using C++ with native MySQL access.
    Only Windows machines are used in this application.

    TIA for any advice or suggestions,
    Phil.
    Phil Cairns Guest

  2. #2

    Default Re: Making and breaking mirrors

    Phil Cairns wrote:
    > I have an application that is going to be required to go on the road. At
    > present, my client has a MySQL 4.0.1 database with more than a million
    > records in it. Their business is changing so that the users are looking
    > at taking portable computers (laptops and/or tablets) into the field to
    > do their work. They need to take a significant portion of the database
    > with them, since sometimes the work takes place in remote areas where
    > there is no internet connectivity, and they need to refer to data in the
    > database. They also need to add new records and update existing records
    > while they are away, and have them merged back into the main database
    > when they return.
    [snip]

    Can I assume that no-one has any experience in dealing with this sort of
    thing? Or was the posting just too big?

    Phil
    Phil Cairns Guest

Similar Threads

  1. FileXtra mirrors?
    By Jeff Stewart in forum Macromedia Director Lingo
    Replies: 0
    Last Post: November 12th, 09:20 PM
  2. Connections to external sites, fallback to mirrors
    By DesignGuy in forum PHP Development
    Replies: 0
    Last Post: September 2nd, 07:40 PM
  3. wanted: official mirrors for 1.8.0
    By Yukihiro Matsumoto in forum Ruby
    Replies: 13
    Last Post: July 31st, 09:01 PM
  4. Euruko 03 - videos -- mirrors wanted;
    By Armin Roehrl in forum Ruby
    Replies: 6
    Last Post: July 28th, 01:54 PM
  5. mirrors from indonesia
    By Kisanak in forum PERL Modules
    Replies: 0
    Last Post: July 12th, 02:40 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