Fundraising/techops/procedures/services-mariadb primary swap

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

Move site to downtime/maintenance mode

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;

Update DNS handles to point to new primary host

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;