ANSWER:

Yes, of course.

The idea you should implement is known as MySQL Circular Replication.

For example, let’s say you have the following:

• DBServer1 had databases db1, db2, db3, db4
• You want to move db3, and db4 to DBServer2
• You want to restrict reads and writes to db1 and db2 on DBServer1
• You want to restrict reads and writes to db3 and db4 on DBServer2

Here is something you can try

STEP01) Run GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO replicator@'%' IDENTIFIED BY 'replpass'; on DBServer1

STEP02) Install the same version of MySQL on DBServer2 (you will have default data in /var/lib/mysql)

STEP03) rsync DBServer1:/var/lib/mysql DBServer2:/var/lib/mysql

STEP04) Repeat STEP02 until it two consecutive rsyncs are the same time, preferably < 2 min.

STEP05) Disable your app from writing anything to the databases

STEP06) service mysql stop on DBServer1

STEP07) Configure binary logging on DBServer1

Add this to /etc/my.cnf on DBServer1

[mysqld]
server-id=1
log-bin=mysql-bin

Add this to /etc/my.cnf on DBServer2

[mysqld]
server-id=2
log-bin=mysql-bin

STEP08) rsync DBServer1:/var/lib/mysql DBServer2:/var/lib/mysql (FINAL RSYNC)

STEP09) service mysql start on DBServer1 (binary logs will start getting populated)

STEP10) service mysql start on DBServer2 (binary logs will start getting populated)

STEP11) Point your apps for db1 and db2 at DBServer1

STEP12) Point your apps for db3 and db4 at DBServer2

STEP13) Run this on DBServer1

CHANGE MASTER TO
MASTER_HOST='xxx.xx.xx.xxx',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=PPPP;

STEP14) Run this on DBServer2

CHANGE MASTER TO
MASTER_HOST='yyy.yy.yy.yyy',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=PPPP;

For Steps 13 and 14

• xxx.xx.xx.xxx is the Private IP Adddress of DBServer1
• yyy.yy.yy.yyy is the Private IP Adddress of DBServer2
• PPPP is the initial position of any binary log
  • For MySQL 5.5, PPPP is 107
  • For MySQL 5.1, PPPP is 106
  • Before MySQL 5.1, PPPP is 98

STEP15) Run START SLAVE; on DBServer1

STEP16) Run START SLAVE; on DBServer2

STEP17) Run SHOW SLAVE STATUSG on DBServer1 until Seconds_Behind_Master is 0

STEP18) Run SHOW SLAVE STATUSG on DBServer2 until Seconds_Behind_Master is 0

This implementation will not allow the splitting of databases, but also allow standby servers for the database.

DBServer1 will be the place to do reads and writes for db1 and db2. It also provides a backup of db3 and db4.

DBServer2 will be the place to do reads and writes for db3 and db4. It also provides a backup of db1 and db2.

Give it a Try !!!

by RolandoMySQLDBA from http://serverfault.com/questions/377874