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=newidlog_bin=mysql-bin#This option should be enabled for passing replicated data from old-master to slavelog_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
6-2) dump partial data with mysqlbinlogmysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases --insert-ignore > backup.sql
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
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
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