SEO

Hi all,

I'm trying to do something with MySQL and I don't know where to start.
We currently have a MySQL Master / Slave replication system running in
our production facility (we're a digital printing shop). We're
thinking about expanding to another facility across the country and
I'm trying to find out if it's possible to do some things with MySQL,
and how to do them. In no particular order:

1) Create new records in the database with auto-incrementing
primary keys that are unique across both facilities, each of which has
a MySQL system.

2) If I can't do the above, can I insert new records in one
facility's database (thereby generating the unique primary keys) and
have those records synchronized across the country through a
relatively slow link?

3) If either of the above is possible, and I perform both reads
and writes to records either database and have those update
synchronized with the other database?

I think that data replication will allow me to synchronize the
databases without to much trouble, but everything I read makes me
think the "slave" is intended for read-only access to the slave and I
need read-write access to the slave.

Any thoughts, suggestions, ideas would be greatly appreciated,
Thanks,
Doug

writeson wrote:
> Hi all,
>
> I'm trying to do something with MySQL and I don't know where to start.
> We currently have a MySQL Master / Slave replication system running in
> our production facility (we're a digital printing shop). We're
> thinking about expanding to another facility across the country and
> I'm trying to find out if it's possible to do some things with MySQL,
> and how to do them. In no particular order:
>
> 1) Create new records in the database with auto-incrementing
> primary keys that are unique across both facilities, each of which has
> a MySQL system.
>
> 2) If I can't do the above, can I insert new records in one
> facility's database (thereby generating the unique primary keys) and
> have those records synchronized across the country through a
> relatively slow link?
>
> 3) If either of the above is possible, and I perform both reads
> and writes to records either database and have those update
> synchronized with the other database?
>
> I think that data replication will allow me to synchronize the
> databases without to much trouble, but everything I read makes me
> think the "slave" is intended for read-only access to the slave and I
> need read-write access to the slave.
>
> Any thoughts, suggestions, ideas would be greatly appreciated,
> Thanks,
> Doug


Questions to determine better suggestions.
What kind of txn rates are we talking about for both sites - is the
current bandwidth sufficient?

How do you or do you need to differentiate data entered for each site?

as requested - *a* suggestion -(worked in a LOT of multi-site companies)
Have you considered 2 identical systems that use an auto-increment field
concatenated with a site_id field to determine the PK(s) - instead of
just the AI field?

syntax not exact:
Virginia site (for example)
create table xyz (a varchar(2) default 'VA', b integer
autoincrement ,,, PRIMARY KEY(a,b)....)
California site
create table xyz (a varchar(2) default 'CA', b integer
autoincrement ,,, PRIMARY KEY(a,b)....)
^^^
guarantees uniqueness across your sites.

And then to roll both systems in to a Data Warehouse. If the PK is as
described above, you could execute reports daily to get the information
required. This would also make it much simpler to add additional sites
should it be necessary.

I have not done a lot of testing with the MySQL cluster, but from what I
have read is looks to be for Active/Passive type failover.

FROM THE DOCS:
"Note that MySQL Cluster is not intended for use in a network for which
throughput is less than 100 Mbps. For this reason (among others),
attempting to run a MySQL Cluster over a public network such as the
Internet is not likely to be successful, and is not recommended."

Michael,

Thanks for the feedback. I think your idea of using a combined primary
key (an auto-incrementing field combined with a site specific field)
is a good one. We could certainly write some scripts (we work in
Python in the production facility) or do some MySQL magic to
periodically synchronize the databases. Once a record is entered at a
site, most of the write activity for that record would occur at that
site.

I also agree with what you said about the MySQL cluster, it's intended
for Active/Passive failover rather than synchronization. Plus, as the
quote you include shows, it's not recommended for anything other than
a local network.

I'll be talking with our DBA guy and see what we come up with.

Again, thanks for the feedback, very helpful!

Doug