MariaDB/MySql promote slave server to master server

Scenario: Master server had a problem and was gone. Promote slave 1 as a master server.

Before: (Images are from dev.mysql.com)

redundancy-before

After:

redundancy-after

One all slave servers,  see message “Slave has read all relay log”.

$ mysql -u root -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 732
Server version: 10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  4 | system user |           | NULL | Connect |  308 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|  8 | root        | localhost | NULL | Query   |    0 | init                                                                        | SHOW PROCESSLIST |    0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
2 rows in set (0.00 sec)

On all slave servers

MariaDB [(none)]> STOP SLAVE;

On Slave 1

MariaDB [(none)]> RESET MASTER;

On other slave servers

MariaDB [(none)]> change master to 
    -> master_host='10.0.0.31',     # IP of Slave 1
    -> master_user='replica',     # replication ID
    -> master_password='password';     # replication ID's password

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status\G

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

Leave a Reply

Your email address will not be published. Required fields are marked *