8.5

Edit

Configure MySQL DB replication

The guide describes configuring of replication between MySQL Databases using the Percona toolkit. We assume that two SPA environments are already installed.

Prerequisites

  • /opt/workfusion: <INSTALL_DIR> is the default Workfusion installation directory
  • 10.0.0.1: IP address of the Master role
  • 10.0.0.2: IP address of the Slave role
  • export MASTER_NAME=10.0.0.1
  • export SLAVE_NAME=10.0.0.2
  • export BACKUP_DIR=$INSTALL_DIR/backup
  • export INSTALL_DIR=/opt/workfusion
  • export REPL_USER=repl
  • export REPL_PASS=repl

Configure replication on Master

To configure replication:

  1. In /opt/workfusion/mysql/usr/my.cnf, turn on log-bin:

    1. Comment the following line:
    init_connect='CALL wfdb.change_isolation_level('wfdb_read@%')'
    1. Add the following lines to the file:
    server-id = 10
log-bin = /opt/workfusion/mysql/var/log/mysql-replication.log

  2. Log in to Mysql DB to grant permissions to $REPL_USER:

    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

  3. Create a backup file:

    $ mkdir -p $BACKUP_DIR 
$ innobackupex --user=mysql -p $BACKUP_DIR --no-timestamp
$ innobackupex --apply-log $BACKUP_DIR

  4. Restart the MySQL service:

    $ wfmanager restart mysql

Configure replication on Slave

To configure replication:

  1. Create a directory for backups:

    $ mkdir -p $BACKUP_DIR 
$ cd $BACKUP_DIR

  2. Copy a backup file from Master to Slave:

    $ rsync -avu $MASTER_NAME:$BACKUP_DIR/* $SLAVE_NAME:$BACKUP_DIR

  3. Stop the mysql service:

    $ wfmanager stop mysql

  4. Backup old data:

    $ mv $INSTALL_DIR/mysql/var/lib/mysql $INSTALL_DIR/mysql_OLD

  5. Restore files from backup to data location:

    $ cd $BACKUP_DIR
$ mkdir -p $INSTALL_DIR/mysql/data
$ xtrabackup --move-back --target-dir=$BACKUP_DIR

  6. Check connection from Slave to Master:

    $ mysql --host=$MASTER_NAME --user=mysql -p

  7. In the Mysql configuration file /opt/workfusion/mysql/usr/my.cnf, specify server-id:

    server-id = 20

  8. Start the mysql service:

    $ wfmanager stop mysql

  9. Log in to Mysql DB to change the MASTER_LOG_FILE and MASTER_LOG_POS parameters:

    CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-replication.000001', MASTER_LOG_POS=10;
START SLAVE;
SHOW SLAVE STATUS \G;

    For this information, see $BACKUP_DIR/xtrabackup_binlog_info.

Change role from Slave to Master

To change a role:

  1. Log in to Mysql DB to grant permission to $REPL_USER:

    STOP SLAVE;
RESET MASTER;
RESET SLAVE ALL;

  2. Check the status. The command must return the empty set:

    SHOW SLAVE STATUS \G;

  3. Restart all services:

    $ wfmanager restart all
← PreviousNext →