Occasionally we need to do.
If a host on the copy number of the main library, can be very simple: the standby database host to start multiple MySQL instances (mysqld_muilt), each instance uses a port, copy more than one main library on it. But if you want to copy over the data in the same instance, things are complicated.
1 The problem and the open source community needs to try: the main library are copied to more than a standby database, the data are copied in one instance, so the application layer of transparent. Currently, MySQL does not provide such functionality itself (considered, but has not been achieved).
1.1 open source community, there are many programs and ideas, such as Tungsten replicator: can support heterogeneous data replication, JAVA implementation, data is large, poor performance, and MySQL version updated faster than the software support is slow;
Another example is P. Linux 1.2 an attempt: to pull more through the main library on the Binlog applications in a single standby database on the way, there is no invasion of MySQL, is also relatively simple to achieve.
1.3 High Performance MySQL is also multi-level replication can be achieved: for example there is the main repository DB1, DB2, all need to be copied to S1, the first configuration all the data D1, D2 copy, and then replicate from S1 D2 on all the data on it. Finishing on the note D2 log-slave-update will need to open, in order to reduce the pressure of D2, DB tables can be copied on all use Blackhole engine.
1.4 In addition, there are some less mature Patch achieve.
2. MySQL Federated engine "soft link" feature
Federated in the local database engine can create a remote table in the "soft link." In this way, access to remote data access local data table like the table. (This remote can be a different instance on different hosts)
3 Use Federated tables to achieve a preparation such as multi-master, master library DB1, DB2, all need to be copied to S1, where the DB1, DB2, S1, respectively, in host H1, H2, H3 on. First, the host H3 (S1 where the host) on the other two MySQL instances from S2, S3, respectively, the main library copy DB1 and DB2, and then create multiple instances of S1 associated with Federated table S2, S3 in the data table ( create the equivalent in S1 to S2, S3 soft connection).
At this time applications, connect to the S1, you can also transparent access to DB1, DB2 in real-time data table.
4 Some note
4.1 the method described above can also be set up directly on S1 to DB1, DB2 Connect all the soft table, but to the greatest degree of reduction of the impact of the main library, the best prepared as above and more with a layer of libraries.
4.2 The simplest can be configured directly on the standby database on multiple instances of the application to connect to multiple instances of like, so no need to copy the above configuration.
1. MySQL Manual: The FEDERATED Storage Engine
2. Post of MySQL Forums: Multiple masters to single slave
3 more by tungsten replicator to achieve master-slave mysql backup architecture
4. Is it possible to do N-master => 1-slave replication with MySQL
5. MySQL Multi-Master - Single-Slave - Replication