Possible to make mysql server both master and slave?
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?
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:
- pt-table-checksum to check if your tables/databases are in sync
- pt-table-sync to resolve potential problems once you have most of the data on the new server
- mysql's filtering of the replication - either on the master [old server] - binlog-do-db or slave [new server] side - replicate-do-db
- mysqldump's master-data and single-transaction options for taking the initial data dump
ps. i assume there's no name overlap for the databases between the old and new server.
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 !!!