9.3

Edit

Configure MySQL DB replication

This guide describes configuring of the replication between MySQL databases using the Percona toolkit. It is assumed that 2 SPA environments are already installed.

Prerequisites

  • /opt/workfusion: <INSTALL_DIR> is the default Workfusion installation directory.
  • 10.0.0.1 is the IP address of the Master role.
  • 10.0.0.2 is the 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@%')'

    2. Add the following lines to the file:

      server-id = 10
log-bin = /opt/workfusion/mysql/var/log/mysql-replication.log

  2. Log in to the 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. Back up old data:

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

  5. Restore files from the backup to the 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 /opt/workfusion/mysql/usr/my.cnf MySQL configuration file, 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;
note

This information can be found in $BACKUP_DIR/xtrabackup_binlog_info.

Change role from Slave to Master

To change a role:

  1. Log in to the MySQL DB to grant the 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 →