Jan 1, 2022

How to Prepare Your MySQL Slave to be Promoted to Master?

 Just a few steps, but make sure you do them on peacetime, as during downtime every minute counts.

1. Enable bin log, you will need them when you will want to add new slaves to the promoted master.

log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100M
binlog_format = mixed

2. Copy Master nodes users and permissions from the Master to the Slave

MASTER> mysqldump -uUSER -p -t mysql user > /tmp/dump.sql
SLAVE> mysql -uUSER -p -D mysql < /tmp/dump.sql
SLAVE> mysql -uUSER -p -e "FLUSH PRIVILEGES;"

P.S Consider using Galera as a Multi-Master solution to avoid the need to promote a node manually. However, in some cases, such as a delayed slave or hidden one, that might be the right solution.

Keep Performing,
Moshe Kaplan

ShareThis

Intense Debate Comments

Ratings and Recommendations