Edit

Master-slave solution for MySQL with Percona

MySQL master

1. On the Master server, run the following commands:

   $ export MASTER_NAME=ip-172-31-46-150
   $ export SLAVE_NAME=ip-172-31-39-130
   $ export BACKUP_DIR=$INSTALL_DIR/backup
   $ export INSTALL_DIR=/opt/workfusion
   

2. Turn on log-bin.

3. Open the my.cnf file.

   vi $<INSTALL_DIR>/mysql/usr/my.cnf
   

4. Add the following lines to the file:

   #init_connect='CALL wfdb.change_isolation_level('wfdb_read@%')'
   server-id = 10
   log-bin = /opt/workfusion/mysql/var/log/mysql-replication.log
   

5. Grant permission to $REPL_USER:

   mysql --host=$MASTER_NAME --user=mysql --password='BhYx!@o5%m'
   
   SQL>
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
   

6. Create a backup file:

   $ mkdir -p $BACKUP_DIR 
   $ innobackupex --user=mysql --password='BhYx!@o5%m' $BACKUP_DIR --no-timestamp
   $ innobackupex --apply-log $BACKUP_DIR
   

7. Restart the Master server:

   wfmanager restart mysql
   

Slave server

1. On the slave server, run the following commands:

   $ export MASTER_NAME=ip-172-31-39-130
   $ export SLAVE_NAME=ip-172-31-46-150
   $ export SLAVE_PASSWORD='BhYx!@o5%m'
   $ export ADMIN_PASSWORD='BhYx!@o5%m'
   $ export ADMIN_USER=mysql
   $ export BACKUP_DIR=$INSTALL_DIR/backup
   $ export INSTALL_DIR=/opt/workfusion
   $ export REPL_USER=repl
   $ export REPL_PASS=repl
   

2. Create a backup directory:

   $ mkdir -p $BACKUP_DIR 
   $ cd $BACKUP_DIR 
   

3. Copy a backup file from the master server to the slave one:

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

4. Stop MySQL on the slave server:

   wfmanager stop mysql
   

5. Move the old data to the safe location if needed:

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

6. Restore the files from backup to the $INSTALL_DIR/mysql/ data location (by default):

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

7. Check connectivity from the slave server to the Master server:

   mysql --host=$MASTER_NAME --user=mysql --password='BhYx!@o5%m'
   

8. Open the my.cnf file and add the following lines in it:

   vi $INSTALL_DIR/mysql/etc/my.cnf
   

   server-id = 20
   

9. Change MASTER_LOG_FILE and MASTER_LOG_POS. For more information, see $BACKUP_DIR/xtrabackup_binlog_info.

   cat $BACKUP_DIR/xtrabackup_binlog_info
   

10. Start the Master server:

    $ wfmanager start mysql
    $ mysql --user=mysql --password='BhYx!@o5%m'
    $ SQL>
    CHANGE MASTER TO MASTER_HOST='ip-172-31-39-130', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-replication.000001', MASTER_LOG_POS=154;
    

11. Start the slave server:

    SHOW SLAVE STATUS \G;
    

Change role from slave to Master

To change the server's role:

1. On the slave server, run the following command:

   $ mysql --user=mysql --password='BhYx!@o5%m'
   

2. Stop the slave master:

   STOP SLAVE;
   

3. Reset the Master server:

   RESET MASTER;
   

4. Reset Slave All.

5. Run the check:

   SHOW SLAVE STATUS \G;
   

   The command must return an empty set.

6. Restart all servers:

   wfmanager restart all