I have two instances of MySQL, one master and one slave.
How can I switch them to make the slave the master and vice-versa while avoiding downtime?
If you use a DBVIP, you could remove the DBVIP from the Master and bring it up on the Slave.
Let's make up a DBVIP, like 10.1.2.30
.
Put this up on the Master in the OS
ip addr addr 10.1.2.30/24 dev eth1
Replace your IP in your application with 10.1.2.30
, Let the application continue to run for a while, making sure the DBVIP is being used by the application.
Here is something aggressive:
service httpd stop
on all Apache Serversservice mysql stop
on the Masterip addr del 10.1.2.30/24 dev eth1
on the Masterip addr add 10.1.2.30/24 dev eth1
on the Slaveservice httpd start
on all Apache ServersSHOW PROCESSLIST;
on the Slave to make sure there are incoming DB ConnectionsIf you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover.
If you cannot use a DBVIP, you must do more work instead
STEP 01) Activate Binary Logging on the Slave
Add this to /etc/my.cnf on the Slave
[mysqld]
log-bin=mysql-bin
STEP02) service mysql restart
on the Slave
Binary Logging should be enabled in the Slave
STEP03) Run the CHANGE MASTER TO
command on the Master using the Slave as its Master
STEP04) Run START SLAVE;
on the Master
At this point
This configuration is better known as
STEP05) service httpd stop
on all Apache Servers
STEP06) Change the IP address in the App to Connect to the Slave
STEP07) service httpd start
on all Apache Servers
STEP08) SHOW PROCESSLIST;
on the Slave to make sure there are incoming DB Connections
If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover without MySQL Downtime. The only downtime there is comes from the Window of time STEP05 - STEP07.
98
, 106
and 107
? How can I find the values for MariaDB 10.2.
Dec 31, 2018 at 12:57