單臺設備基于63G的數據量快速完成mysql主從搭建

一、演示課題說明：

單臺物理機利用xtrabackup工具在線備份63G的mysql數據，來新建slave庫。
演示的目的主要是記錄下在單臺物理服務器上利用63G的測試庫數據，然后在本機上快速新建一個slave庫，大概需要多久完成。以及在新增的slave的過程中對master庫鎖表影響多大？

二、設備和系統環境說明:

設備環境： x86_64位最小化安裝

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

設備和硬盤型號：
雙硬盤：SSD盤-intel 單盤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)

三、創建測試數據：

創建測試表：

 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`) #主鍵ID
) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

創建定時器，每一秒寫入數據：

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("李四","tomcat",now());
    END $$
delimiter ;

提示：
創建上訴的測試數據，主要是想看下在xtrabackup備份數據時，會 對master庫鎖表多長時間，以及鎖表是否會導致insert數據失敗
此次演示中雖然每一秒都有寫入數據到master庫中，但是并沒有看到鎖表造成寫入的表數據失敗（可能還是每秒并發寫入表的數據太少，或者是鎖表的過程時間太短）

四、備份恢復數據并搭建主從復制

xtrabackup備份工具安裝過程此處不再贅述，具體可以看考此鏈接：
https://blog.51cto.com/wujianwei/2430281

4.1xtrackup備份數據：

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分鐘21秒

[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

耗時15秒

4.2恢復備份的數據到3307新實例：

[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

耗時6分26秒

4.3授權3307新實例data目錄mysql的權限：

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

4.4啟動3307 mysql服務

4.5 master庫上創建復制用戶:

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

查看xtrabackup 備份的完binlog的位置點：

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和不采用Gtid 兩種方式配置復制：

slave庫上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

采用上訴的方式配置主從復制，說明master庫一開始沒開啟Gtid。
+++++++++++++++
如果master庫上一開始就開啟Gtid，則在配置主從復制時slave庫采用下面的命令：

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

注意：Gtid主從復制時，master庫事先一定是開啟Gtid的

在恢復xttabackup備份的數據到新實例上并啟動新實例后，要登錄這個新的實例，清空下新實例上的gtid的信息，
清除新的3307實例的Gtid信息：

mysql> reset master;

查看xtrabackup 備份的完binlog的位置點：

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

然后再執行如下命令：

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

總結：整個備份，恢復和新建slave，基于63G的數據量在18分鐘就可以完成。而且xtrabackup 備份過程中鎖表和解鎖在1s內就完成了，在模擬的環境中看，鎖表的影響還是很小的。但是還是建議在業務低峰期進行xtrabackup 備份數據以及在slave庫上進行 xtrabckup備份數據，將鎖表對數據庫的影響降低到最小化