SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Keeping databases in sync

    At the agency I am working at, they all use a software application that ties into a MySQL database. I am looking to build off of this application to add some new tables of data for some additional requirements that have nothing to do with the application itself.

    So I am looking to create a new database that will mirror the one used for the application with the exception of my newly added tables.

    What would be the easiest way to keep the second database in sync, but ignoring my new tables? Are there any real-time solutions or will I have to have something run hourly?

    Thank you for any advice you might have.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why do you need to duplicate the first database at all? If you need to access a table in it just refer to it by database name and table name. If you have permission to duplicate the values there then you should also have the ability to select from the tables.

    Code:
    SELECT
      foo,
      bar,
      qux
    FROM
      databaseone.otherguystable as OGT
    INNER JOIN
      databasetwo.yourtable as YT
    ON
      OGT.somecolumn=YT.somecolumn

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to duplicate the first database to ensure the compatibility with the software program we are using. If I am creating new tables or manipulating any data at all there is a chance something could change which would affect the data integrity throughout the application in the whole office.

    If I duplicate the database, the additions or changes are not being merged back into the core database to prevent any damage to application.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you would be creating NEW tables in the NEW database you create. You don't have to recreate ANY of the existing tables in the existing database because you can simply join on them as they presently exist.

  5. #5
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand, but I would really prefer not to touch the other database at all unless it was syncing it to another one that way no updates or inserts would ever touch the application database.

    If there is no other way to do it I would consider your option. I am just trying to rule out any possibility of any data corruption or error due to working with multiple databases at the same time.

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Couldn't you just create a new DB user that only is only granted SELECT (i.e. not INSERT, UPDATE, etc) on the original database?
    That way, if your code accidentally wants to UPDATE or INSERT in the original database, MySQL won't allow it.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, you are right. I will try it this way. I have been just paranoid of affecting the data in any way. Ill just make sure to take backups!

  8. #8
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its probably worth mentioning having MySQL replication and working off the slave database as obviously the master updates are one way.

    However I like the other solution better.
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My idea in not suggesting replication was that it seemed like extra data only needed to refer to existing data but the OP above may not be in position to request a M/S system to be set up. My cue was the additional data having nothing to do with existing data. hard to make a requirement to set up replication in that case.


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
  •