Showing posts with label without stop. Show all posts
Showing posts with label without stop. Show all posts

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