MySQL Slave Replication setup with Percona XtraBackup addresses performance, scalability, and high availability.

In addition, they avoid single-point-of-failure scenarios in Database Infrastructure.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how we can set up MySQL Slave Replication.

MySQL Slave Replication setup with Percona XtraBackup

Moving ahead, let us see the step-by-step setup of MySQL replication using Percona XtraBackup.

MySQL Master

We use MySQL instance “Master” in replication infrastructure where the data is stored and to replicate.

Our Support Techs recommend the following configuration changes to setup MySQL Master my.cnf:

• Configure MySQL Master instance with server_id
• Configure MySQL Master with binary logging

[mysqld]
log-bin=mysql-bin
server-id=101

MySQL Slave

In MySQL Slave, the settings will be same as the MySQL master, except the server-id on slave will be unique.

[mysqld]
server-id=102

Percona XtraBackup

Now, let us get into the installation. Here is the steps our Support Techs employ for the same.

• Complete full database backup with Percona XtraBackup

Initially, on the shell of the Master, we run:

xtrabackup --backup --user=TheDBUser --password=TheDBUserPa55wD --target-dir=/dir11/backupdir/fullbackup

In case of a successful completion of full-backup we will see:

xtrabackup: completed OK!

Now we made a copy of the MySQL data dir to the directory /dir11/backupdir/fullbackup

After this, we will apply transaction logs to the data files and makes them ready to restore on another MySQL Server.

xtrabackup --user=TheDBUser --password=TheDBUserPa55wD --prepare --target-dir=/dir11/backupdir/fullbackup

Percona XtraBackup knows default location of the my.cnf. But, if we retain my.cnf on non-standard place, we use the flag:

--defaults-file=/nonstandard-location/of/my.cnf

• Restore the backup to Slave Server

We can directly rsync or scp to copy data from Master to Slave’s data directory.

However, we need to stop mysqld in slave:

rsync -avpP -e ssh /dir11/backupdir/fullbackup MinervaDBSlave:/home/Backup-From-Master/

Once we copy the data, we backup the MySQL datadir:

Make sure to shut down mysqld before copying the data from datadir or moving snapshot.

mv /var/lib/mysql/datadir /Bakup/db-bkp/mysql/mysql-datadir_bakup

After that, we move snapshot from the Master to datadir of Slave:

xtrabackup --move-back --target-dir=/home/Backup-From-Master/

Once done, we make sure MySQL has permissions to access them:

chown -R mysql:mysql /var/lib/mysql/

• Configure MySQL Replication

First, we need to grant login privileges for slave to connect to Master:

GRANT REPLICATION SLAVE ON . TO 'repl'@'$slaveip' IDENTIFIED BY 'SlavePa55wd';

Then we confirm we can connect to MySQL Master from MySQL Slave instance:

mysql --host=MinervaDBMaster --user=repl --password=SlavePa55wd

• Configure MySQL Slaver Server

To do so, we copy my.cnf from MySQL Master to Slave.

In addition, we need to change the server-id of the slave.

• Start MySQL Replication

We can view MySQL master log file and position from xtrabackup_binlog_pos_innodb of the slave:

cd /var/lib/mysql;
cat xtrabackup_binlog_pos_innodb;
mysql-bin.000409 336216351

Then, we configure slave to start replication:

CHANGE MASTER TO
MASTER_HOST='$masterip',
MASTER_USER='repl',
MASTER_PASSWORD='SlavePa55wd',
MASTER_LOG_FILE='mysql-bin.000409',
MASTER_LOG_POS=336216351;

After that, we start MySQL slave :

START SLAVE;

To monitor MySQL Replication from Slave, we run:

mysql> show slave status\G
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 10.168.0.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000465
Read_Master_Log_Pos: 671936951
Relay_Log_File: flip-db3-relay-bin.000169
Relay_Log_Pos: 671825092
Relay_Master_Log_File: mysql-bin.000465
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 671824879
Relay_Log_Space: 671937421
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: d3575a73-1f76-11ea-a643-00163e016ecb
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Interpreting the results of "show slave status\G"

Eventually, we confirm IO and SQL threads are running..

[Stuck in between? We will be glad to be of assistance]

Conclusion

In short, we saw how our Support Techs go about MySQL Slave Replication.