User Tools

Site Tools


mysql:changemasters

This is an old revision of the document!


Changing Masters

The problem

I have a mysql master database with an slave. I want to change the master server. Downtime it is not a problem, however, I will try to reduce downtime.

My servers, for the sake of this example will be named: master, future-master, slave.

First: make future-master to become an slave of master

For that, I've found this:

http://plusbryan.com/mysql-replication-without-downtime

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A -h MASTER-SERVERNAME -u root -p  > ~/dump.sql

As it is said in the document, take note of the value of MASTER_LOG_FILE and MASTER_LOG_POS:

head dump.sql -n80 | grep "MASTER_LOG_POS"

In the slave, edit the config file:

server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1

This explains how to change the master of a running mysql database by the mechanism of:

  1. Create a new slave wihtout downtime
  2. Stop the current master and start a new master (the last slave)

https://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html

mysql/changemasters.1435073020.txt.gz · Last modified: 2022/12/02 22:02 (external edit)