Fundraising/techops/procedures/services-mariadb primary swap

This is a procedure that can be used to swap a replica maria DB in as a new primary. In the future, this will hopefully be replaced by an automated procedure.

Procedure

All Commands are listed in a manner to start with a comment character to protect from accidental copy/paste.

Key:

   Commands starting with # are run on the CLI
   Commands starting with -- are run in mysql

On current primary

flush tables to get consistency - in mariadb/mysql

-- FLUSH TABLES WITH READ LOCK;

check all replicas to make sure they have the same Relay_Master_Log_File and Exec_Master_Log_Pos

-- SHOW SLAVE STATUS \G

stop the db

  # systemctl stop mariadb.service

ensure the db doesn't start on boot

  # systemctl disable mariadb.service

On new primary

Stop replication - in mariadb/mysql

  -- stop slave;
  -- reset slave all;

Turn off read only

  -- SET @@global.read_only=0;

Reset 'master' - in mariadb/mysql

  -- reset master;

Get 'master' position

  -- SHOW MASTER STATUS \G

On all the other replicas

Stop replication from old primary DB

  -- stop slave;

Point replication at new host

If using GTID replication (can use replica_pos after 10.5.1):

  -- change master to MASTER_HOST = 'new_host.fqdn',
     MASTER_USE_GTID = slave_pos, 
     MASTER_USER='repl', MASTER_PASSWORD='XXXXXXXX',
     Master_SSL = 1, MASTER_SSL_CA = '/etc/mysql/cacert.pem',
     MASTER_SSL_CERT = '', MASTER_SSL_KEY = '', MASTER_SSL_VERIFY_SERVER_CERT = 1;

If using binlog replication using the File: and Position:

  -- change master to MASTER_HOST = 'new_host.fqdn',
     MASTER_LOG_FILE = '$FILE', MASTER_LOG_POS = $POSITION,
     MASTER_USER='repl', MASTER_PASSWORD='XXXXXXXX',
     Master_SSL = 1, MASTER_SSL_CA = '/etc/mysql/cacert.pem',
     MASTER_SSL_CERT = '', MASTER_SSL_KEY = '', MASTER_SSL_VERIFY_SERVER_CERT = 1;

Start replication on new primary DB

 -- start slave;

On old primary db

Turn on read only as a safety measure

  -- SET @@global.read_only=1;

Reset 'master'

  -- reset master;

Reset 'slave' knowledge

  -- reset slave all;

Point replication at new host

If using GTID replication (can use replica_pos after 10.5.1):

  -- change master to MASTER_HOST = 'new_host.fqdn',
     MASTER_USE_GTID = slave_pos, 
     MASTER_USER='repl', MASTER_PASSWORD='XXXXXXXX',
     Master_SSL = 1, MASTER_SSL_CA = '/etc/mysql/cacert.pem',
     MASTER_SSL_CERT = '', MASTER_SSL_KEY = '', MASTER_SSL_VERIFY_SERVER_CERT = 1;

If using binlog replication using the File: and Position:

  -- change master to MASTER_HOST = 'new_host.fqdn',
     MASTER_LOG_FILE = '$FILE', MASTER_LOG_POS = $POSITION,
     MASTER_USER='repl', MASTER_PASSWORD='XXXXXXXX',
     Master_SSL = 1, MASTER_SSL_CA = '/etc/mysql/cacert.pem',
     MASTER_SSL_CERT = '', MASTER_SSL_KEY = '', MASTER_SSL_VERIFY_SERVER_CERT = 1;

Start replication on new primary DB

  -- start slave;