Thursday, December 26, 2013

MySQL Replication Master Server Change without stopping

How to change master server without stop when  we have Server Old-Master, Slave, New-Master
I'm not an expert of database administration. If there are better way, share me please.

I. Database no-stop server change

1. New-Master Server Setting 
Setup New-Master as slave, also master

1) edit my.cnf
server-id=newid
log_bin=mysql-bin
#This option should be enabled for passing replicated data from old-master  to slave
log_save_updates=true 

2) Backup data from Old-Master and restore to New-Master by using mysqldump or other utilities.
mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > backup.sql

3) start New-Master as slave
mysql-shell) stop slave;
mysql-shell) CHANGE MASTER TO MASTER_HOST = 'Old-Master-Host', MASTER_USER = 'old-master-user', MASTER_PASSWORD = 'old-master-password', MASTER_LOG_FILE = 'old-master-logfile', MASTER_LOG_POS = old-master-pos;
mysql-shell) start slave;
4) check status
mysql-shell) show slave status\G;

5) stop slave

6) Backup data from Old-Master and restore to New-Master again (for changes)
choose alternative methods.

6-1) dump all data
mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases --insert-ignore > backup.sql
6-2) dump partial data with mysqlbinlog
mysqlbinlog binlog.lastlog > backup-partial.sql
mysql -uroot -p -e "source backup-partial.sql
* if there are duplicate key problem
sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" < backup-partial.sql > convert-insertinto.sql

7) start slave

8) Create new master replication users for slave and grant them.
create user 'replicator'@'%' identified by 'password';
grant replication slave on *.* to 'replicator'@'%';
9) flush logs


2. Slave Servers
change master to New-Master dynamically, also change my.cnf
mysql-shell) stop slave;
mysql-shell) CHANGE MASTER TO MASTER_HOST = 'New-Master-Host', MASTER_USER = 'New-master-user', MASTER_PASSWORD = 'New-master-password', MASTER_LOG_FILE = 'New-master-logfile', MASTER_LOG_POS = New-master-pos; 
mysql-shell) start slave;

3. Change your application setting. 

4. Stop Old-Master server


II. Database stop, easy to change

First, Setup New-Master server (I-1)
Second, shutdown old-master Server
Third, change new-master address to old-master server
Finally, Bring up New-Master Server

No comments:

Post a Comment