Replication setup

MySQL & MariaDB Other MySQL® Questions
1

I have Master slave replication setup on Windows server MySQL 5.6.22 without GTID. With below parameters in my.ini

masterA> my.ini

log_bin=mysql-bin.log
log_bin_index=mysql-bin.index
binlog_format=ROW
max_binlog_size=1073741824
sync_binlog=1
server-id=1

master status: mysql-bin-0002.log POS: 19548

slaveB> my.ini

log_bin=mysql-bin.log
log_bin_index=mysql-bin.index
binlog_format=ROW
max_binlog_size=1073741824
log_slave_updates=ON
sync_binlog=1
relay_log=mysql-relay-bin.log
relay_log_index= mysql-relay-bin.index
max_relay_log_size=1073741824
read_only=1
server-id=2
skip-slave-start

ON slave: mysql-bin-0002.log POS: 19548 * As I have log-bin and log-slave-updates enabled

I need to promote slave as master and master as slave. But I want the new slave to continue from mysql-bin-0002.log POS: 19548. What I need to know is whether below steps are correct. Is it necessary to issue reset slave command on slave

  1. Stop tomcat so that there are no connections to mysql.
  2. Verfiy whether master and slave are in sync. if yes then proceed further.
  3. On SLAVE> stop slave; Change master to master_host=‘’; ###I will keep it blank.
  4. On SLAVE> comment read_only parameter
  5. On MASTER> Stop Mysql service. Add below parameters in my.ini
    relay_log=mysql-relay-bin.log
    relay_log_index= mysql-relay-bin.index
    max_relay_log_size=1073741824
    read_only=1
    log_slave_updates=ON
    skip-slave-start
  6. On MASTER> start mysql service. Issue change master to command.
    CHANGE MASTER TO MASTER_HOST=‘slaveB IP’,MASTER_USER=‘repluser’,MASTER_PASSWORD=‘replpwd’,MASTER_PORT=3307,MASTER_LOG_FILE=‘mysql-bin.000002’,MASTER_LOG_POS=19548,MASTER_CONNECT_RETRY=10;
  7. Start slave.
2

I’ve started playing around with MHA and I should say it works great for manual or even automated failover of master-slave replication servers. :slight_smile:

Steps 1 and 2 are solid.
Step 3. you’d want to do, STOP SLAVE; RESET SLAVE ALL; as it is 5.6
Step 4. set global read_only=0;
Step 5. these are optional and read_only can be changed online.
Step 6. SHOW MASTER STATUS; on old slave/new master
Step 7. CHANGE MASTER TO … (use log file and position from #6)
Step 8. set global read_only=1; on new slave/old master
Step 8. start slave; show slave status\G on new slave/old master.

Again with MHA ([url]https://code.google.com/p/mysql-master-ha/wiki/Overview[/url]), it would be as easy as :

shell> masterha_master_switch --master_state=alive --conf=/etc/app1.cnf --new_master_host=old_slave --orig_master_is_new_slave