Mysql master-slave replication with percona xtrabackup

Published on Author admin

logo-mysql-110x57

Mysql master-slave replication with percona xtrabackup

This replication will be seted up with Percona tools and xtrabackup.

The replication in MySQL performs via binary logs. Replication is only asynchronous. In version Mysql-5.6 GTID (Global Transaction IDentifier) appeared.
In theory replication is the process when master servers sends updates to slave server.
Next I’m going to show you the process. I have a mysql-5.5 server running on default port 3306.

First, you should add key and deb souce:

Debian and Ubuntu packages from Percona are signed with a key. Before using the repository, you should add the key to apt. To do that, run the following commands:

Add this to /etc/apt/sources.list, replacing VERSION with the name of your distribution:

update the local cache:

Install xtrabackup package:


More information you can find on percona home

Second, you should do is to enable binary logs:

This variables can be set without reboot.

By default it will create a backup of your mysql and put it to folder in format yyyy-mm-dd_hh-mm-ss

It is good idea to check you backup data:

WARNING:

after –apply-log will be completed you MUST remember or to write down the binary log and position!!!

But there is a trick – binary log name and position can be found in xtrabackup_binlog_info placed in backup folder:

On the MASTER server:

We need to create user and ti grant replication rights:

Copy via rsync or scp folder with backuped data to slave server remove all files from the folder mysql is located as this folder must be empty for copy-back operation, otherwise an error occurs.

We need to delete mysql home directory, otherwise ‘copy-back’ will exit with error of non-empty folder.

After files copied to mysql home permissions and owner must be set back:

On the SLAVE server: