Keeping databases in sync

Databases
1

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

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.

3

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.

4

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!

5

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.


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

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.

7

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.

8

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.

9

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.