Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am getting ready to move a database from one server to another. In order to reduce downtime for the client, I am wondering if it would be possible for me to turn on replication and give it time to replicate fully, then just point the customer to the new server.

The issue I have is that the server I'm moving to has existing, active databases for other customers. And, the server I'm moving from has other active customers who will not be moving at this time.

Is this even possible? If so, how do I configure the server I am moving from and the one I am moving to?

share|improve this question
1  
You might want to try your question on dba.stackexchange.com -- you might get better mileage there. –  KM. Apr 9 '12 at 16:37

2 Answers 2

up vote 4 down vote accepted

yes - it's possible for mysql server to be both master and a slave at the same time.

if you use innodb as your storage engine for that data and you move whole databases it should be possible to:

  • enable binlogging on the old server
  • take a full consistent dump from the old server without blocking any ongoing traffic
  • transfer that dump to the new server, restore there
  • turn on replication for selected databases and allow the new server to 'catch up' to the production machine
  • once the servers have needed databases in sync - switch the traffic

this might come helpful:

ps. i assume there's no name overlap for the databases between the old and new server.

share|improve this 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 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

STEP15) Run START SLAVE; on DBServer1

STEP16) Run START SLAVE; on DBServer2

STEP17) Run SHOW SLAVE STATUS\G on DBServer1 until Seconds_Behind_Master is 0

STEP18) Run 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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.