Nova Resource:Deployment-prep/MariaDB Slave instance setup

From Wikitech
Jump to navigation Jump to search

Derived from Setting up a MySQL replica and lessons learned from task T216067

Transferring Data

  • Create a new m1.xlarge instance in horizon named deployment-dbNN where NN is an unused number.

On New Slave

  • set mariadb::config::basedir: /opt/wmf-mariadb101 in hiera. Note this is so it plays nicely with Stretch which will default to using the wmf-mariadb101 package instead of the default wmf-mariadb10.
  • ensure puppet runs cleanly
  • /opt/wmf-mariadb101/scripts/mysql_install_db --user=mysql --basedir=/opt/wmf-mariadb101 --datadir=/srv/sqldata
  • cd /srv/sqldata
  • nc -l -p 9210 | mbstream -x

On Existing DB

Run mariabackup/innobackupex

  • mariabackup --innobackupex --stream=xbstream /srv/sqldata --user=root --host= --slave-info | nc NEW-SERVER 9210

On New Slave, After mariabackup

Do this before starting mysql!

  • mariabackup --innobackupex --apply-log --use-memory=10G /srv/sqldata
  • chown -R mysql: /srv
  • systemctl start mariadb
    • tail the error log in /srv/sqldata and check for any errors
  • If this is newer version of MariaDB, run /opt/wmf-mariadb104/bin/mysql_upgrade --host= to ensure compatibility.

Starting Replication

  • Check /srv/sqldata/xtrabackup_slave_info on the new host, it provides a partial CHANGE MASTER statement based on whichever host the target was replicating from. If this is correct, use this, adding MASTER_HOST and the repl account/password portions that are missing.
    • e.g. CHANGE MASTER to MASTER_USER='repl', MASTER_PASSWORD='...', MASTER_PORT=3306, MASTER_HOST='deployment-db1', MASTER_LOG_FILE='deployment-db1-bin.000026', MASTER_LOG_POS=191406295;
    • The master repl password should be in the file /var/lib/git/labs/private/modules/secret/secrets/mysql/repl_password on the deployment-puppetmaster (currently deployment-puppetmaster04 )
  • If you ran the hot backup from the master, use the information in /srv/sqldata/xtrabackup_binlog_info as slave_info won't exists.
  • If you copied from the secondary master, xtrabackup_slave_info will point to the active master; use xtrabackup_binlog_info instead.