Percona Xtrabackup can be used to create “hot backups” of MySQL servers fairly quickly and can avoid some of the pitfalls of mysqldump. Xtrabackup can be setup to use backup locks instead of read locks which is much less invasive, this is available on innodb tables. MyISAM and other tables will still need to be read locked to perform a backup. Xtrabackup will work on MySQL, MariaDB and Percona (versions 5.1, 5.5, 5.6, 5.7). You can then use this backup to easily replicate the MySQL slave. This guide assumes you already have a running SQL server and CentOS.Install Percona XtrabackupFirst you want to install the repository, you can get additional repositories from Percona‘s siteyum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpmThen install Percona Xtrabackupyum install -y percona-xtrabackup-24Install rsync as well as we will use this to transfer the backup to the slaveyum install -y rsyncCreate A Backup with XtrabackupFirst you will want to create a initial backup with innobackupexinnobackupex /root/percona-backupReplace /root/percona-backup with path if you need to. If you need to specify a user and password use the following flagsinnobackupex –user=username –password=passwordhere /root/percona-backupWhen it starts you should see the following messageinnobackupex: Starting the backup operationUpon completion you should see the followingxtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
170810 21:37:19 completed OK!If you look in the /root/percona-backup directory you set, you will see a time-stamp directory, this is the SQL backup# ls /root/percona-backup/
2017-08-10_21-37-17You will now need to apply the transaction log to the backup by performing the followinginnobackupex —apply-log /root/percona-backup/2017-08-10_21-37-17You will again be looking for the ‘OK’ message at the end of the programxtrabackup: The latest check point (for incremental): ‘1597945’
xtrabackup: Stopping log copying thread.
.170810 21:44:58 >> log scanned up to (1597945)
170810 21:44:58 Executing UNLOCK TABLES
170810 21:44:58 All tables unlocked
170810 21:44:58 Backup created in directory ‘/root/percona-backup/2017-08-10_21-37-17/2017-08-10_21-44-56/’
170810 21:44:58 [00] Writing /root/percona-backup/2017-08-10_21-37-17/2017-08-10_21-44-56/backup-my.cnf
170810 21:44:58 [00] …done
170810 21:44:58 [00] Writing /root/percona-backup/2017-08-10_21-37-17/2017-08-10_21-44-56/xtrabackup_info
170810 21:44:58 [00] …done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
170810 21:44:59 completed OK!Prepare the SQL MasterOn the master server, if it is not already prepared for slaves, you will want to get it ready. You will want to make sure a bin log is configured in my.cnfnano /etc/my.cnfAdd the followinglog_bin = /var/log/mysql/mysql-bin.logAlso set a server-id in /etc/my.cnfserver-id = 1The server-id can be any number it just needs to be unique on each server in the replication.You will need to restart MySQL for it to take affectsystemctl restart mysqlYou will also want to ensure the firewall is open for the port 3306firewall-cmd –zone=public –add-service=mysql –permanent
firewall-cmd –zone=public –add-port=3306/tcp –permanentThen reload the firewallfirewall-cmd –reloadPrepare the SQL SlaveYou will want to add a server-id to /etc/my.cnf on the slaveserver-id=2You will also want to open the same port in the firewall as you did the on the master. Since we are copying the entire Xtrabackup you can go ahead and shutdown MySQL on the Slavesystemctl stop mysqlGo ahead and make a backup of /var/lib/mysql incase something goes wrongcp -R /var/lib/mysql /var/lib/mysql.bakYou will also want to ensure the firewall is open for the port 3306 on the slave as wellfirewall-cmd –zone=public –add-service=mysql –permanent
firewall-cmd –zone=public –add-port=3306/tcp –permanentThen reload the firewallfirewall-cmd –reloadConfigure MySQL ReplicationSync the backup from the master to the slaversync -vPa /root/percona-backup/2017-08-10_21-37-17 192.168.1.100:/var/lib/mysqlYou will want to replace the path with the updated path to your backup and the 192.168.1.100 ip address with IP address of your slave.Once the rsync has finished you will want to change the permissions of /var/lib/mysql to be owned by the slavechown -R mysql. /var/lib/mysqlOn the master enter the mysql console:mysqlGrant permissions for replication to the slavegrant replication slave on *.* to [email protected] identified by ‘password’;You will want to change the replicationuser to a desired user name, 192.168.1.100 to the IP address of the slave and password to your desired password.Then flush privileges on the masterflush privileges;Start The MySQL SlaveOn the slave you will need to gather the needed slave information so cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001 245This contains the MASTER_LOG_FILE and MASTER_LOG_POS information you will need to enter in MySQL to connect to the masterConnect to MySQL on the slavemysql -uroot -pEnter the following informationCHANGE MASTER TO MASTER_HOST=’192.168.1.101′,
MASTER_USER=’replicationuser’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS= 245;MASTER_HOST will be the IP address of the MySQL master.MASTER_USER will be the replicationuser you set earlier on the master.MASTER_PASSWORD will be the password you configured earlier on the master.MASTER_LOG_FILE and MASTER_LOG_POS will be obtained from the xtrabackup_binlog you viewed earlierOnce you have added that information in MySQL you should see the followingMariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’replicationuser’,
-> MASTER_PASSWORD=’password’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS= 245;
Query OK, 0 rows affected (0.04 sec)You can go ahead and start slaving now:start slave;To check slave status perform type show slave status;MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replicationuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 483
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 767
Relay_Master_Log_File: mysql-bin.000001
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: 483
Relay_Log_Space: 1063
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: 1
1 row in set (0.00 sec) The specific lines you are looking for to ensure replication is working areSlave_IO_Running: Yes
Slave_SQL_Running: YesThat is all that is needed for creating MySQL replication with Perconna Xtrabackup.Aug 13, 2017LinuxAdmin.io