Build new mysql slave without stop service or locked table by Xtrabackup

Server environment

1
2
3
4
5
$ cat /etc/issue
Ubuntu 14.04.5 LTS

$ php -v
PHP 5.6.32-1+ubuntu14.04.1+deb.sury.org+2 (cli)

Here,I have 2 online running mysql machine, master Mysql A and slave B, they are responsible for read and write respectively.
But today there is a new business with a lot of reading operate, i hope these queries that it will not affect the performance of origin mysql slave B.
So i want to move this part of query operation to a new slave C and the C will sync data from A.

Host HostName Father
Master-A mysql-dev
Slave-B mysql-dev-slave Master-A
Slave-C (new) mysql-dev-slave2 Master-A

I build three docker container to mock this scene.

1
2
3
4
5
$ docker ps
CONTAINER ID IMAGE ... PORTS NAMES
83c29d41ad9a mysql:5.6 ... 127.0.0.1:3306->3306/tcp mysql-dev
134ba07f70d7 mysql:5.6 ... 0.0.0.0:4306->3306/tcp mysql-dev-slave
59ba368743bb mysql:5.6 ... 0.0.0.0:4307->3306/tcp mysql-dev-slave2

Install Xtrabackup In Master Machine And New Slave

1
$ sudo apt-get install -y Xtrabackup

Backup Mysql Master Server By Xtrabackup

1
2
3
$ mkdir /mysql-backup
$ cd /mysql-backup/
$ innobackupex --user=root --password=123456 ./
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

170830 20:31:20 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).

...
...
xtrabackup: The latest check point (for incremental): '235358683'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (235358683)

xtrabackup: Creating suspend file '/mysql-backup/2017-08-30_20-31-20/xtrabackup_log_copied' with pid '194'
xtrabackup: Transaction log of lsn (235358683) to (235358683) was copied.
170830 20:31:20 innobackupex: All tables unlocked

innobackupex: Backup created in directory '/mysql-backup/2017-08-30_20-31-20'
innobackupex: MySQL binlog position: filename 'binlog.000003', position 120
170830 20:31:20 innobackupex: Connection to database server closed
170830 20:31:20 innobackupex: completed OK!

We can see the backup data directory structure

1
2
3
4
5
6
7
8
9
10
11
$ tree -L 2 /mysql-backup/
/mysql-backup/
└── 2017-08-30_20-31-20
├── backup-my.cnf
├── ibdata1
├── mysql
├── performance_schema
├── symfony
├── xtrabackup_binary
├── xtrabackup_checkpoints
└── xtrabackup_logfile

Copy The Backup Directory From Master To New Slave

You can use whatever tools you want to do this. Such as scp

1
$ scp -r /mysql-backup username@new-mysql-slave-ip:~/

I’m using docker container mock the mysql.
By means of docker cp to move data directory to new slave machine, you can ignore these operation.

1
2
3
$ cd ~/backup/
$ docker cp mysql-dev:/mysql-backup/2017-08-30_20-31-20 .
$ docker cp 2017-08-30_20-31-20/ mysql-dev-slave2:/mysql-backup/

Stop New Mysql Slave Server

1
$ sudo service mysql stop

Replace The Data Directory By Backup Directory

1
2
3
$ sudo rm -rf /var/lib/mysql/
$ sudo mv 2017-08-30_20-33-10/ /var/lib/mysql
$ sudo chown mysql.mysql -R /var/lib/mysql

Start slave Mysql

1
$ sudo service mysql start

Check Master Binlog And Pos Position At New Slave Mysql

1
$ mysql -h127.0.0.1 -uroop -puman73 -P4307
1
2
3
4
5
6
7
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 120 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Set Synchronization From Log Point To Master At New Slave

1
2
3
4
5
6
7
8
9
10
11
mysql> CHANGE MASTER TO \
-> MASTER_HOST='master',\
-> MASTER_PORT=3306,\
-> MASTER_USER='repl',\
-> MASTER_PASSWORD='repl',\
-> MASTER_LOG_FILE='binlog.000001',\
-> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.29 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

Check Sync Result

Do something in master Mysql then check the change in new slave.

Thank you for reading.
This post is copyrighted by Liyuliang’s Blog.
If reproduced, please indicate the source: Liyuliang’s Blog
This blog uses Creative Commons Attribution-NonCommercial-Share-Sharing 4.0 International License Agreement to license.


Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×