If your end goal is to separate the servers then I would suggest running a separate instance on a different port and using standard replicaton between the two
quick google turns out this
alternatively you can use triggers to replicate, something along the lines of
CREATE TRIGGER replicatetable AFTER INSERT ON db1.table
FOR EACH ROW BEGIN
INSERT INTO db2.table(field1,field2, ...) values (db1.table.field1,db1.table.field2, ...);
END;
similar for UPDATE and DELETE
There is a specific reason why what you proposed is impossible to achieve with MyISAM and InnoDB.
A star topology warrants a Master being the center of the universe, not the slave. MySQL Replication was not designed to have a slave read from multiple masters simultaneously. It can only read from one master at a time. The CHANGE MASTER TO command connects a slave to one, and only one, master.
According to the book Understanding MySQL Internals, page 219 paragraph 2 under the subheading "Multi-Master" says the following:
MySQL Replication was not originally
written with multi-master support in
mind. A slave is natively capable of
replicating only one master. A fairly
simple patch can be created to allow
one slave to collect updates from
multiple masters without conflict
resolution. This was done at one time,
but for a number of reasons did not
make it into the main branch of the
source tree. A more complex patch to
allow some conflict resolution was
planned at one point, but for a number
of reasons did not make it to
development. It stll may be
implemented in the future.
The book High Performance MySQL: Optimization, Backups, Replication and more has a box at the top of page 364 (Chapter 8 : Replication Topologies) whose title is "MySQL Does Not Support Multimaster Replication". The box has the following paragraphs:
We use the term multimaster
replication very specifically to
describe a slave with more than one
master. Regardless of what you may
have been told, MySQL (unlike some
other database servers) does not
support the configuration illustrated
in Figure 8-6 at present. However, we
show you some ways to emulate
multimaster replication later in this
chapter.
Unfortunately, many people use this
term casually to describe any setup
where there is more than one master in
the entire topology, such as the
"tree" topology we show later in this
chapter.Other people use it to
describe what we call master-master
replication, where the servers are
mutually master and slave.
These terminology problems cause a lot
of confusion and even arguments, so we
think it's best to be careful with
names. Just imagine how hard it will
be to communicate if MySQL adds
support for a slave with two masters!
What term will you use to describe
that if you haven't reserved
"multimaster replication" fro the
purpose?
While the emulation techniques listed pages 373-375 under the subheading "Emulating multimaster replication" is theorectically possible (using the BLACKHOLE storage engine) and have been implemented successfully by others to emulate only two masters, it is still could never support your particular proposed topology.
I had addressed this question before. In fact, the answer I gave there is successfully done all the time. This is why insurance salesmen can bring a laptop to a person's house and collect insurance data on a person applying for insurance. The salesman would eventually connect to a central computer to download a new client's application. In turn, the central computer can download the latest actuary info so as to pro-rate what a policy would cost the applicant. It works on the same premise for connecting a laptop to a central computer, one laptop at a time.
Best Answer
You need to do something called multi-master replication (aka master-master replication).
There are a number of guides that walk you through the process.
There are two ways to use a master-master replication setup. The improper way is to write (INSERT/UPDATE etc.) to both masters at the same time. Although MySQL provides configuration parameters that can help you if you decide to do this, I suggest you not rely on this behavior. Particularly, don't think that you can split your writes across the machines as some kind of load balancing, there is no such thing. All writes will occur on all servers.
The proper way is to use one master as a hot standby and only write to one master at a time. Trust me this is will save you a lot of heartache when things go wrong. There is even a tool, MMM (Multi-Master Replication Manager for MySQL) that can assist with monitoring and failover for this. It does require a 3rd server to monitor the masters though.
One final thing. Do NOT, I repeat, DO NOT try to "scale" outward by adding additional masters to create a loop. Like I said earlier, this is not scaling at all since every server must do every write from every other server. It is also a complete nightmare to repair replication for a non-transient failure of one of the masters. The loss of one server "breaks" every server in the loop after it, because they no longer are getting updates from the servers downstream from them.