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 [email protected]:~/

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.

Share