1, Description of demonstration project:

A single physical machine uses xtrabackup to back up 63G mysql data online to create a new slave database.
The purpose of the demonstration is to record the test database data of 63G used on a single physical server, and then quickly create a slave database on the local machine, about how long it will take to complete. What is the impact of the new slave on the master lock table?

2, Description of equipment and system environment:

Device environment: x86 64 bit minimum installation

 [root@localhost scripts]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 

Device and hard disk model:
Dual hard disk: SSD disk - intel single disk raid0

+--------------------------------------------------------------+
| This Machine's Hyper-Threading is Enabled(recommend disable) |
+--------------------------------------------------------------+
Systembit    :    64
MEM info     :    6*16384 MB
Disk_totle   :    
Pro_SN_name  :    Product Name: PowerEdge R630 Serial Number: G8WFKM2
System name  :    \S
Board_SN name:    Product Name: 02C2CP Serial Number: .G8WFKM2.CNIVC0079R1641.
CPU model    :    48  Intel(R) Xeon(R) CPU E5-2690 v3 @ 2.60GHz
CPU_phy_num  :    2
CPU_core_num :    12
CPU_logic_num:    24
Netcard info :    Broadcom Limited NetXtreme BCM5720 Gigabit Ethernet PCIe
+++++++++++++++++++++++++++++++++++
<<raid informations>>
 Virtual Drive: 0 (Target Id: 0)
Name                :
RAID Level          : Primary-0, Secondary-0, RAID Level Qualifier-0
Size                : 223.0 GB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives    : 1
--
Virtual Drive Information:
Virtual Drive: 1 (Target Id: 1)
Name                :
RAID Level          : Primary-0, Secondary-0, RAID Level Qualifier-0
Size                : 223.0 GB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives    : 1
+++++++++++++++++++++++++++++++++++
<<Virtual Drive Information>>
Device Id: 0
PD Type: SATA
Raw Size: 223.570 GB [0x1bf244b0 Sectors]
Inquiry Data: PHDV7234023C240AGN  INTEL SSDSC2BB240G7                     N2010112
Device Speed: 6.0Gb/s 
Drive Temperature :27C (80.60 F)
Device Id: 1
PD Type: SATA
Raw Size: 223.570 GB [0x1bf244b0 Sectors]
Inquiry Data: PHDV717003D9240AGN  INTEL SSD
Device Speed: 6.0Gb/s 
Drive Temperature :26C (78.80 F)

3, Create test data:

To create a test table:

 CREATE TABLE `test_event` (
`id` int(8) NOT NULL AUTO_INCREMENT, 
`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(20) COLLATE utf8_unicode_ci NOT NULL, 
`create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`) #Primary key ID
) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create timer, write data every second:

delimiter $$
create event event_2 
on schedule every 1 second STARTS   CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
COMMENT 'xiaowu create'
do 
    BEGIN
           insert into test_event(username,password,create_time) values("Li Si","tomcat",now());
    END $$
delimiter ;

Tips:
To create the appeal test data, we want to see how long the master database will lock the table when xtrabackup backs up the data, and whether the lock table will cause the insert data to fail
In this demonstration, although there are write data to the master database every second, we do not see the failure of write table data caused by locking the table (maybe there is too little data written to the table concurrently every second, or the process time of locking the table is too short)

4, Backup and restore data and set up master-slave replication

The installation process of xtrabackup backup tool will not be described here. Please refer to this link for details:
https://blog.51cto.com/wujianwei/2430281

4.1 xtrackup backup data:

time innobackupex --defaults-file=/etc/my.cnf  -ubackupuser -p654321ccs  --host=127.0.0.1  -S /tmp/mysql.sock --parallel=2 --no-timestamp /data/backup/db_3306_`date +%Y%m%d`

200115 17:22:33 Executing UNLOCK TABLES
200115 17:22:33 All tables unlocked
200115 17:22:33 [00] Copying ib_buffer_pool to /data/backup/db_3306_20200115/ib_buffer_pool
200115 17:22:33 [00]        ...done
200115 17:22:33 Backup created in directory '/data/backup/db_3306_20200115/'
MySQL binlog position: filename 'mysql-bin.000109', position '491959802', GTID of the last change '64a062d1-2e92-11ea-847e-801844ed7bbc:1-84402'
200115 17:22:33 [00] Writing /data/backup/db_3306_20200115/backup-my.cnf
200115 17:22:33 [00]        ...done
200115 17:22:33 [00] Writing /data/backup/db_3306_20200115/xtrabackup_info
200115 17:22:33 [00]        ...done
xtrabackup: Transaction log of lsn (117743624292) to (117743935718) was copied.
200115 17:22:33 completed OK!

real    11m21.125s
user    0m49.376s
sys 2m15.140s

11 minutes, 21 seconds

[root@localhost backup]# time innobackupex  --apply-log  /data/backup/db_3306_20200115/
InnoDB: Shutdown completed; log sequence number 117743937576
200115 17:38:41 completed OK!

real    0m14.873s
user    0m0.093s
sys 0m2.428s

It takes 15 seconds.

4.2 restore the backup data to the 3307 new instance:

[root@localhost 3307]# time innobackupex --defaults-file=/data1/mysql/3307/my3307.cnf  --parallel=2 --use-memory=1024M --copy-back /data/backup/db_3306_20200115/
200115 17:49:35 [01] Copying ./ibtmp1 to /data1/mysql/3307/data/ibtmp1
200115 17:49:35 [01]        ...done
200115 17:49:59 [02]        ...done
200115 17:49:59 completed OK!

real    6m25.941s
user    0m0.122s
sys 3m21.198s

It takes 6 minutes and 26 seconds

4.3 authorize the permission of mysql in the data directory of 3307 new instance:

chown -R mysql.mysql /data1/mysql/3307/data 

4.4 start the 3307 mysql service

4.5 create a replication user on the master database:

grant replication slave on *.* to rep@'127.0.0.1' identified by 'JuwoSdk21TbUser'; flush privileges;

To view the location of the binlog after xtrabackup backup:

cat /data/backup/db_3306_20200115/xtrabackup_binlog_info
[root@localhost db_3306_20200115]# cat /data/backup/db_3306_20200115/xtrabackup_binlog_info
mysql-bin.000109    491959802   64a062d1-2e92-11ea-847e-801844ed7bbc:1-84402

4.6 Gtid and Gtid are used to configure replication:

change master to:

change master to master_host='127.0.0.1',master_user='rep',master_password='JuwoSdk21TbUser',master_log_file='mysql-bin.000109',master_log_pos=491959802;start slave;show slave status\G

The master-slave replication is configured in the way of appeal, which means that the master database has not opened Gtid at the beginning.
+++++++++++++++
If Gtid is enabled at the beginning of the master database, the slave database uses the following command when configuring master-slave replication:

  CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_USER='rep',
  MASTER_PASSWORD='JuwoSdk21TbUser',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION = 1;

Note: when copying Gtid master-slave, the master database must be enabled in advance

After recovering the data backed up by xttabackup to a new instance and starting a new instance, log in to the new instance and clear the gtid information on the next new instance,
Clear the Gtid information for the new 3307 instance:

mysql> reset master;

To view the location of the binlog after xtrabackup backup:

cat /data/backup/db_3306_20200115/xtrabackup_binlog_info
[root@localhost db_3306_20200115]# cat /data/backup/db_3306_20200115/xtrabackup_binlog_info
mysql-bin.000109    491959802   64a062d1-2e92-11ea-847e-801844ed7bbc:1-84402

Then execute the following command:

  CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_USER='rep',
  MASTER_PASSWORD='JuwoSdk21TbUser',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION = 1;

Summary: the whole backup, recovery and new slave can be completed in 18 minutes based on the data volume of 63G. In addition, during xtrabackup, locking and unlocking of tables are completed within 1s. In the simulated environment, the impact of locking tables is still small. However, it is recommended to backup xtrabackup data in the low peak period and xtrabckup data in the slave database to minimize the impact of locking tables on the database