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
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
service mysql stop on DBServer1
STEP07) Configure binary logging on DBServer1
Add this to /etc/my.cnf on DBServer1
Add this to /etc/my.cnf on DBServer2
STEP08) rsync DBServer1:/var/lib/mysql DBServer2:/var/lib/mysql (FINAL RSYNC)
service mysql start on DBServer1 (binary logs will start getting populated)
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
STEP14) Run this on DBServer2
CHANGE MASTER TO
For Steps 13 and 14
- xxx.xx.xx.xxx is the Private IP Adddress of DBServer2
- yyy.yy.yy.yyy is the Private IP Adddress of DBServer1
- 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
START SLAVE; on DBServer1
START SLAVE; on DBServer2
SHOW SLAVE STATUS\G on DBServer1 until Seconds_Behind_Master is 0
SHOW SLAVE STATUS\G 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 !!!