Duplicate data accross 2 mysql databases on seperate servers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

Duplicate data accross 2 mysql databases on seperate servers

Post by Slippy »

I was hoping someone out there would be able to shed some light on a problem that I have.

Both dB's are MySQL.

I have a banner table on one server that has an autoincement primary key on one server and I would like to update/insert/delete data on another server with seperate calls.

The table is duplicated with the same name on the second server and the key is simply set to the same data type as the original database except that autoincrement has been turned off.

So when I do an insert into the "auto-inc" database, how do I get the key for the record that I just inserted at that moment? -- so that I may pass it to the other database for a seperate insert...

I will use the key for updates and deletes so the key should be the same if I can...

Any help would be appreciated,

Thx.
jayloo
Forum Newbie
Posts: 11
Joined: Tue Jun 14, 2005 1:28 pm

Post by jayloo »

Ill take a stab at this. I recently set up 3 mysql databases all on seperate servers in seperate states. One master and 2 slaves. The master received all the insert/update/delete queries and and some select. The 2 slaves started out as just being emergency backups in case the master catches fire or something. I ended up utilizing the slaves to help off load some of the big reporting queries and it worked perfectly.

If you make updates to your slave the changes will not make it to the master. As I understand it if you make a change on the slave the master could overwrite it when it sends the log to the slave. Only make changes on the master.

I have read it is possible to have 2 way replication. Kinda like Oracle has multi-master replication. All servers are masters and they all send changes to each other but MySQL is mot quite there with this model. Maybe the next major release.
Post Reply