How to setup slave for MySQL replication with Percona-xtrabackup

On this section, i will show howto setup slave node (pxc-5.6) with percona-xtrabackup.

MySQL Version : 5.6.36
xtrabackup version : 2.3

The steps is simple, so follow me

need some variables like bellow to run replication MySQL

server-id = 80
log-bin = /data/ssd/mysql-binlog/mysql-bin
expire-logs-days = 7
sync-binlog = 1

at masterDB

Grant user for running replication slave

mysql> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$slaveip' IDENTIFIED BY '$slavepass';


Make backup on the masterDB add option –stream to send the backup to slaveDB

sudo innobackupex --user=backup_usr --password=password123 --compress --no-lock --parallel=40 --stream=xbstream /data/ssd/backupdb | ssh erick@ "xbstream -x -C /data/ssd/backupdb"

after this process finish, go to the slaveDB

at slaveDB

Uncompress backup file and make sure qpress already installed on slaveDB

# innobackupex --decompress /data/ssd/backupdb

next prepare the data. Default on innobackupex use 100MB to do this process, so add option –use-memory to make it running fast

# innobackupex --apply-log --use-memory=100G /data/ssd/backupdb

next if the process finish, stop service mysql and restore the database

# service mysql stop

# rm -rfv /data/ssd/mysql/*

# innobackupex --copy-back /data/ssd/backupdb/

change owner dir mysql to mysql after copy-back finish and start service mysql

# chown -R mysql: /data/ssd/mysql

# service mysql start

Configure slave MySQL server

add some variables to file my.cnf

server-id = 99
relay-log = /data/ssd/mysql-relay/relay
slave-net-timeout = 60s
log-slave-updates = TRUE

#To minimize and prevent slave crash when slave suddenly server shutdown
relay_log_info_repository = TABLE
relay_log_recovery = ON

#To enable multi threads slave (minimize slave lag)
slave-parallel-workers = 24
slave-pending-jobs-size-max = (default 16MB in bytes)

Restart service mysql

# service mysql restart

Before start the replication look content of file xtrabackup_binlog_pos_innodb

# cat /data/ssd/mysql/xtrabackup_binlog_pos_innodb
/data/ssd/mysql-binlog/mysql-bin.002871	205683232

run command change master to start replication

mysql>CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl_usr', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='mysql-bin.002871',

mysql> start slave;

Check replication status

mysql> show slave status \G;

         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
         Seconds_Behind_Master: 13

source1 percona & MySQL


4 thoughts on “How to setup slave for MySQL replication with Percona-xtrabackup

  1. Can I askyou.
    Let say after I UNLOCK TABLE at master , I do insert update at master.
    Will it update at slave when I START SLAVE?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.