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

Spec:
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';

mysql> FLUSH PRIVILEGES;

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@172.16.64.10 "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',
MASTER_LOG_POS=205683232;

mysql> start slave;

Check replication status

mysql> show slave status \G;

mysql> SHOW SLAVE STATUS \G
         ...
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
         ...
         Seconds_Behind_Master: 13

source1 percona & MySQL

Advertisements