Step-by-step MySQL Slave Replication setup with Percona XtraBackup
Building MySQL infrastructure operations for WebScale involves splitting READ – WRITE optimally across several MySQL instances / nodes, The scale-out / replication solution address performance, scalability and high availability. The replication solutions are also a proven method to avoid single-point-of-failure scenarios in Database Infrastructure. In this post we have explained step-by-step setup of MySQL replication using Percona XtraBackup , Percona XtraBackup is a open source hot backup solution from Percona Server for backup of both Percona Server for MySQL and MySQL GA, In MariaDB 10.3 and later, Mariabackup is the recommended backup method to use instead of Percona XtraBackup.
Prerequisites for setting up MySQL Slave Replication using Percona XtraBackup
MySQL Master
You will call MySQL instance ” Master ” in replication infrastructure where your data is stored and one to be replicated, The following are configuration changes recommended 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 (in this example, we have made MySQL slave instance server-id = 102 )
[mysqld] server-id=102
Percona XtraBackup
Percona XtraBackup is a open source / free MySQL Hot-backup tool from Percona to complete high-speed online backup of your MySQL database
Step 1: Complete full database backup with Percona XtraBackup and prepare the same
Issue the following command on the shell of the Master:
MinervaDBMaster$ xtrabackup --backup --user=TheDBUser --password=TheDBUserPa55wD --target-dir=/dir11/backupdir/fullbackup
If full-backup completed successfully, You should be seeing:
xtrabackup: completed OK!
So you have now made a copy of your MySQL data dir to the directory /dir11/backupdir/fullbackup
The consistency of snapshot ( backup ) completed above is very important to address data reliability concerns, So in the next command below we will prepare the backup completed in previous step:
MinervaDBMaster$ xtrabackup --user=TheDBUser --password=TheDBUserPa55wD --prepare --target-dir=/dir11/backupdir/fullbackup
The previous step (prepare backup) applies transaction logs to the data files and makes those data files ready to be restored on another MySQL Server ( in our case it will be slave server)
Note – Percona XtraBackup knows default location of your my.cnf to locate data files. But, if you are retaining my.cnf on non-standard place, please use the flag –defaults-file=/nonstandard-location/of/my.cnf
Step 2: Restore the backup to Slave Server
You can directly rsync or scp to copy data from Master to Slave’s data directory but please stop mysqld in slave:
MinervaDBMaster$ rsync -avpP -e ssh /dir11/backupdir/fullbackup MinervaDBSlave:/home/Backup-From-Master/
Once data is successfully copied, Please backup the previously installed MySQL datadir ( Note – Before copying the data from datadir or moving snapshot to its datadir please shutdown mysqld )
MinervaDBSlave$ mv /var/lib/mysql/datadir /Bakup/db-bkp/mysql/mysql-datadir_bakup
Please move snapshot from the Master to datadir of Slave:
MinervaDBSlave$ xtrabackup --move-back --target-dir=/home/Backup-From-Master/
Once successfully completed the copying of data, Please make sure MySQL has permissions to access them
MinervaDBSlave$ chown -R mysql:mysql /var/lib/mysql/
Step 3: Configuring MySQL Replication
Grant login privileges for slave to connect to Master:
MinervaDBMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'$slaveip' IDENTIFIED BY 'SlavePa55wd';
Confirm you can connect to MySQL Master from MySQL Slave instance:
MinervaDBSlave$ mysql --host=MinervaDBMaster --user=repl --password=SlavePa55wd
Step 4: Configuring MySQL Slaver Server
Copy my.cnf from MySQL Master to Slave and please don’t forget to change the server-id of the slave ( in this post we have configured slave with server_id=102 )
Step 5: Start MySQL Replication
You can view MySQL master log file and position from xtrabackup_binlog_pos_innodb of the slave:
root@MinervaDBSlave:/var/lib/mysql# cat xtrabackup_binlog_pos_innodb mysql-bin.000409 336216351 root@MinervaDBSlave:/var/lib/mysql#
Configuring slave to start replication:
TheSlave|mysql> CHANGE MASTER TO
MASTER_HOST='$masterip',
MASTER_USER='repl',
MASTER_PASSWORD='SlavePa55wd',
MASTER_LOG_FILE='mysql-bin.000409',
MASTER_LOG_POS=336216351;
Start MySQL slave:
MinervaDBSlave|mysql> START SLAVE;
Monitoring MySQL Replication from Slave:
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 ”
- Confirm IO and SQL threads running.
- Make sure seconds_behind_master is “0” to confirm MySQL replication is reliable on Slave (lagging too long is serious problem to solve when flipping Slave to new Master when original Mater crashes)
- Closely monitor Last_IO_Error, Last_IO_Error, Last_SQL_Error and Last_SQL_Errorno to avoid MySQL slave outage
How can you add more Slaves to the Master ?
To add more slaves to the Master, We will be using Percona XtraBackup to clone the slave which is already operational. The following steps explain about adding more slaves:
Do a full-backup on the Slave:
MinervaDBSlave$ xtrabackup --user=bkpuser --password=bkpusrPa55Wd --backup --slave-info --target-dir=/Disk5/Bkp/data-backupdir/
When we use –slave-info , Percona XtraBackup creates additional file called xtrabackup_slave_info
Apply the logs:
MinervaDBSlave$ xtrabackup --prepare --use-memory=2G --target-dir=/Disk5/Bkp/data-backupdir/
Copy the directory from MinervaDBSlave to MinervaDBNewSlave ( new slave)
Note: Please confirm mysqld in MinervaDBNewSlave (new slave) instance is shutdown before copying the contents of snapshot into its datadir
rsync -avprP -e ssh /Disk5/Bkp/data-backupdir/ MinervaDBNewSlave:/var/lib/mysql/
Grant privileges on the Master for new slave added
MinervaDBMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'MinervaDBNewSlave(new slave IP)' IDENTIFIED BY 'MinervaDBNewSlavePa55WD';
Copy the configuration file from MinervaDBSlave instance:
MinervaDBNewSlave$ scp root@MinervaDBSlave:/etc/mysql/my.cnf /etc/mysql/my.cnf
Please don’t forget to change the server-id variable in /etc/mysql/my.cnf to 103 and disable the replication on start:
[mysqld] server-id=103
Please restart mysqld after setting server-id
Configuring new slave ( MinervaDBNewSlave instance ) to connect with Master ( MinervaDBMaster instance) to start replication:
You can view MySQL master log file and position from xtrabackup_binlog_pos_innodb of the new slave:
MinervaDBNewSlave|mysql> CHANGE MASTER TO
MASTER_HOST='$MinervaDBMasterip',
MASTER_USER='repl',
MASTER_PASSWORD='MinervaDBNewSlavePa55WD',
MASTER_LOG_FILE='mysql-bin.000409',
MASTER_LOG_POS=336216351;
Start the new slave:
MinervaDBNewSlave|mysql> START SLAVE;
We can conclude the entire exercise is over and successful if IO and SQL threads are running when you check the NewSlave / MinervaDBNewSlave instance is replicating with Master.
Conclusion
We recommend replication solution for every customer to avoid MySQL from being a single-point-of-failure. Data is business, Reliable MySQL database infrastructure operations is a key component to build highly available and fault-tolerant 24*7 datanomy business. Splitting READ-WRITEs across MySQL Master – Slave is an easiest method to build horizontally scalable MySQL Operations. If you are interested to know more about building optimal, scalable, highly available, fault-tolerant and self-healing database infrastructure operations on MySQL, MariaDB, PostgreSQL and ClickHouse, please book your appointment with MinervaDB Principal below for an no obligation 30 minutes free consulting.
