Professional Web Applications Themes

Propagating schema changes from masterdb to all dbs - MySQL

Is there a function in MySQL or a known script that allows you to propagate a schema change from one db to all of the dbs on a given MySQL server? I have server that has a seperate db for each of our clients. When a new client comes on-board we just copy the schema of a master db to a new db for the client. (I know this is not ideal but I came in long after this system was created.) Whenever we make a change to the master db schema we need to run the change on all ...

  1. #1

    Default Propagating schema changes from masterdb to all dbs

    Is there a function in MySQL or a known script that allows you to
    propagate a schema change from one db to all of the dbs on a given
    MySQL server?

    I have server that has a seperate db for each of our clients. When a
    new client comes on-board we just copy the schema of a master db to a
    new db for the client. (I know this is not ideal but I came in long
    after this system was created.) Whenever we make a change to the master
    db schema we need to run the change on all of the customer db's as
    well. This was fine at first but, we now have over 100 clients.

    Any advice to allow automating or, at least, simplifying of this
    procedure would be greatly appreciated.

    Thank you all in advance.

    rbr

    ryankbrown@gmail.com Guest

  2. #2

    Default Re: Propagating schema changes from masterdb to all dbs

    Thank you Gordon. Thsi will work. However, I was hoping to find a more
    automated way to do this. I use SQLYog to scan a db for differences to
    the reference db. Then create an update sql script to run using the
    script you suggested as a template. I would like to find a way to
    schedule a job to do this compare and automatically run the update.

    I'm not sure if this is even possible but it would be tremendously
    helpful if it were. Or, if someone could make suggestions how I might
    be able to create something like this, I would appreciate it.

    Your suggestion is a great time-saver though so, thank you gain.

    Regards,

    rbr


    Gordon Burditt wrote:
    > >Is there a function in MySQL or a known script that allows you to
    > >propagate a schema change from one db to all of the dbs on a given
    > >MySQL server?
    >
    > It is possible to write a script that does a series of ALTER TABLE
    > commands that transform the schema from the old one to the new one.
    > (It can also do things like populating any standard reference tables
    > that might get added).
    >
    > This does require careful testing. Create a database with the
    > old schema, run the script, and compare the new schema against the
    > test schema.
    >
    > The scripts that update the privilege tables distributed with newer
    > versions of MySQL do this. If you've got a recent version you're
    > upgrading, some of the upgrades might bomb out (because the change
    > has already been made), but these protect against servers that are
    > behind for some reason.
    >
    > >I have server that has a seperate db for each of our clients. When a
    > >new client comes on-board we just copy the schema of a master db to a
    > >new db for the client. (I know this is not ideal but I came in long
    > >after this system was created.) Whenever we make a change to the master
    > >db schema we need to run the change on all of the customer db's as
    > >well. This was fine at first but, we now have over 100 clients.
    >
    > Write the script to update the current database. Run it for each
    > database corresponding to a customer.
    ryankbrown@gmail.com Guest

Similar Threads

  1. Group not propagating
    By Sarah Tanembaum in forum Windows Server
    Replies: 2
    Last Post: July 2nd, 11:41 PM
  2. propagating keys with scripts
    By Michael Bystroem in forum FileMaker
    Replies: 4
    Last Post: September 24th, 12:07 PM
  3. Propagating exceptions or not
    By Eirik M. in forum ASP.NET Web Services
    Replies: 0
    Last Post: September 4th, 11:09 AM
  4. Replies: 3
    Last Post: August 4th, 04:03 PM
  5. Replies: 2
    Last Post: August 3rd, 03:33 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