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:
|
1 |
$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A |
Add this to /etc/apt/sources.list, replacing VERSION with the name of your distribution:
|
1 2 |
deb http://repo.percona.com/apt $VERSION main deb-src http://repo.percona.com/apt $VERSION main |
update the local cache:
|
1 |
$ apt-get update |
Install xtrabackup package:
|
1 |
$ sudo apt-get install xtrabackup |
More information you can find on percona home
Second, you should do is to enable binary logs:
|
1 2 |
[root@master-db] # [root@master-db] # less /etc/mysql/my.cnf |
|
1 2 3 4 5 6 7 8 |
... server-id = 2 binlog_format = MIXED log-bin = /home/db/mysql/db-bin.log log-slave-updates = ON expire_logs_days = 2 max_binlog_size = 100M ... |
|
1 2 3 4 5 6 7 |
[root@master-db] # [root@localhost] # mysql -e "show global variables like '%server_id%';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ |
|
1 2 3 4 5 6 7 |
[root@master-db] # [root@localhost] # mysql -e "show global variables like '%log_bin%';" +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | +---------------------------------+-------+ |
|
1 2 3 4 5 6 7 |
[root@master-db] # [root@master-db] # mysql -e "show global variables like '%expire_logs_days%';" +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 2 | +------------------+-------+ |
|
1 2 3 4 5 6 7 |
[root@master-db] # [root@master-db] # mysql -e "show global variables like '%max_binlog_size%';" +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | max_binlog_size | 104857600 | +-----------------+-----------+ |
This variables can be set without reboot.
|
1 2 3 4 |
[root@master-db] # [root@master-db] # mkdir /home/temp/ [root@master-db] # cd /home/temp/ [root@master-db] # innobackupex ./ |
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:
|
1 2 3 |
[root@master-db] # [root@master-db] # cd 2013-12-13_16-49-02/ [root@master-db] # for i in `find ./ -name *ibd`; do echo $i && innochecksum $i; done |
|
1 2 |
[root@master-db] # [root@master-db] # innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log ./ |
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:
|
1 2 |
[root@master-db] # [root@master-db] # cat xtrabackup_binlog_info |
On the MASTER server:
We need to create user and ti grant replication rights:
|
1 2 |
mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replpass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; |
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.
|
1 2 3 |
[root@master-db] # mkdir /home/temp/ [root@master-db] # rsync -aHv --progress 2013-12-13_16-49-02/ 10.0.0.2:/home/temp/2013-12-13_16-49-02 [root@master-db] # |
We need to delete mysql home directory, otherwise ‘copy-back’ will exit with error of non-empty folder.
|
1 2 3 |
[root@slave-db] # mkdir /home/temp/ [root@slave-db] # cd /home/temp/2013-12-13_16-49-02 [root@slave-db] # innobackupex --copy-back ./ |
After files copied to mysql home permissions and owner must be set back:
|
1 2 3 4 5 |
[root@slave-db] # [root@slave-db] # cd /home/db [root@slave-db] # chown -R mysql:mysql ./mysql [root@slave-db] # chmod -R 755 ./mysql [root@slave-db] # /etc/init.d/mysql start |
On the SLAVE server:
|
1 2 3 |
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='', MASTER_LOG_POS=; mysql> START SLAVE; |