MySQL Data Replication, How? MySQL Data Replication, how??

[jvizueta 0]

How can I make data Replication in MySQL??, I'm making a web based software that's going to be used in 5 different drugstores of the same company, how can I make it so all 5 stablishments see the same data in real time?? is that possible? all 5 drugstores are in the same city but not so near one of each other, what kind of hardware should I use? where can I find a guide to it? what is the first step? can anyone help me please?

[jvizueta 0]

is there a program that makes it easier to replicate data? what is the better way to do it? what is fastest way? I've got another question that can be related to this topic, is replication the way website mirrors in internet work?

[yordan 10]

We need some more info. How big is the database ? Are the five drugstores on the same network ? Can the five drugstores access the same central database ?

[jvizueta 0]

database is not so big, and the drugstores are going to work in the same network of course, but the problem is that when someone is selling something to a customer they want the page to load very fast, and I don't know how fast it's going to get it if server is in some other neighborhood, maybe it sounds like a goofy question but I haven't done this before, please help methank you

[miCRoSCoPiC^eaRthLinG 0]

Why do you need to replicate ?? Have one Central Server that can be accessed by all these drugstores. Put all of them into a Virtual private network (VPN) over the internet - and all of them would behave as if they're part of a big LAN, with the data being transmitted in a secure encrypted manner over the internet. And in case you still want to replicate the same data all over - setup individual replication servers at the drugstore ends and have one central mysql server, which handles the feedbacks and changes from all the stores. This server should have binary logging enabled - allowing it to disperse the changes to all the replication servers allowed to connect to it. The actual steps are quite simple and can be found out throug a simple google on MySQL Replication Server. But the fact still remains, that when some data needs to be modified, it has to be done on the Primary server, in order for the change to be reflected to all the drugstores - which in effect, is the same as having just one central server. The only reason you might want to have such a setup, is that having the replicators will allow you to prefetch all changes thus accelerating local access speeds..

[ruben1405241511 0]

Hi,I don't know how much you know about MySQL, but if I get you right, you are wondering if it will really be "realtime" update, when Shop A sells something, can Shop E see directly "Oh, Item sold".If I got you right. Realise this with MySql, but be sure to set up your database in a way that it can't be accessed when a change is being made, then they should always get up-to-date information.The only relevant point left would be the clerks speed, how fast he types the request in and so on, but well that's not you to worry about that.I hope this answers your question (in combination with the other replies it should ^^)Ruben

[miCRoSCoPiC^eaRthLinG 0]

Hi,

 

I don't know how much you know about MySQL, but if I get you right, you are wondering if it will really be "realtime" update, when Shop A sells something, can Shop E see directly "Oh, Item sold".

If I got you right. Realise this with MySql, but be sure to set up your database in a way that it can't be accessed when a change is being made, then they should always get up-to-date information.

The only relevant point left would be the clerks speed, how fast he types the request in and so on, but well that's not you to worry about that.

I hope this answers your question (in combination with the other replies it should ^^)

Ruben

1064326949[/snapback]

Good point And MySQL does indeed provide a very easy way of doing this. All you need to do, before you start writing out your data is issue a command like: LOCK TABLES mytablename WRITE and once you are done issue another command: UNLOCK TABLES. This see's to your need that the same record/table is not being modified by two different connections at the same time. While the lock is in order, another connection cannot access that table.

 

But even for the realtime updates, a single centralized server will do. You don't need to create replications as such. Anyway, when you check up the data for a single item, the data transfer can be reduced to as minimal as possible with good planning of data normalization. Hence, even over the internet it'll produce a lag of at the most a couple of milliseconds - hardly noticeable to the end-users. But for the data to be instantly upgraded in your applications interface is another thing altogether. There's no concrete way of announcing that a certain data has changed. Thus your client application needs to constantly monitor those fields at regular intervals - by maintaining what is known as a Persistent Connection in mysql terms, where the client app connects once and always stays connected. Some event that is fired at the client's end at regular intervals polls the required databases and updates the user's screen accordingly. That's the only way of doing it I guess.

 

Any further questions - feel free to ask.

 

Regards,

m^e

[ruben1405241511 0]

Good point

thanks ^^ nice to know the command too. I'm not exactly an mySQL pro so this will help me ^^

it'll produce a lag of at the most a couple of milliseconds - hardly noticeable

Hey, we are talking about a drug store here!
They aren't selling the cure for cancer there, and it is not like it is important who gives the last bid for an item, right? :-P
You probably need the database only for stuff which is not in "very small store C" but maybe in "giant store A".
You just sell some toothpaste and other stuff, so a request to update data from "in stack" too "sold" should take you no time. I guess the only think you have to be careful about that there are no requests which take a lot of time and lock the table. I mean, if store E is doing a stocktaking the others must be able to access the database anyway.
But I'm pretty sure that MySQL will do the job well.

[miCRoSCoPiC^eaRthLinG 0]

Forgot to mention - (if you're familiar with the various MySQL Storage Engines) - the LOCK TABLES/UNLOCK etc. work the best with InnoDB storage engine - which offers Transactional capabilities. In other words you can send in a block of instructions as an atomic transaction - where either the whole block gets successfully executed, or in case there occurs some error midway, the whole series of statements is rolled back and all updates are wiped out till the beginning of the transaction.

 

This offers a tremendous advantage in say for example, cases where you're updating 5 different tables (in some banking transaction - updating accounts of some person).. and midway there's an error. With non-transactional tables, like MyISAM, half of the statements will be executed and the other half aborted. This can lead to severe discrepancy in the accounts. On the other hand, if you use InnoDB and face an error midway - nothing to worry about. You simply rollback to the beginning point, analyze the cause of the error + fix it and then try updating all the same again.

 

As for the drug store thingy - I think there might be a solution in the to-be-released MySQL 5.0 - something known as Triggers. Triggers are procedures/events that you associate with tables/rows that trigger an event when this table is modified. What the trigger function will do is entirely on you - but in this case, the trigger might be effectively used to somehow update the drugstore db's as soon as the items are marked sold in the central server.