Shulou(Shulou.com)06/03 Report--

The following is mainly to bring you a single device to complete the mysql master-slave building brief analysis as soon as possible. I hope these words can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

First, the description of the demonstration project:

A single physical machine uses xtrabackup tools to back up 63G mysql data online to build a new slave library.

The main purpose of the demonstration is to record the test library data of using 63G on a single physical server, and then quickly create a slave library on this machine, which will take about how long to complete. And how much impact is it on the master library lock table in the process of adding slave?

2. Description of equipment and system environment:

Device environment: x86mm 64-bit minimized installation

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

Device and hard disk models:

Dual hard disk: SSD disk-intel single disk raid0

+-- + | This Machine's Hyper-Threading is Enabled (recommend disable) | +-- -+ Systembit: 64MEM info: 6' 16384 MBDisk_totle: Pro_SN_name: Product Name: PowerEdge R630 Serial Number: G8WFKM2System name:\ SBoard_SN name: Product Name: 02C2CP Serial Number: .G8WFKM2.CNIVC0079R1641.CPU model: 48 Intel (R) Xeon (R) CPU E5-2690 v3 @ 2.60GHzCPU_phy_num: 2CPU_core_num: 12CPU_logic_num: 24Netcard info: Broadcom Limited NetXtreme BCM5720 Gigabit Ethernet PCIe++ Virtual Drive: 0 (Target Id: 0) Name: RAID Level: Primary-0 Secondary-0, RAID Level Qualifier-0Size: 223.0 GBState: OptimalStrip Size: 64 KBNumber Of Drives: 1--Virtual Drive Information:Virtual Drive: 1 (Target Id: 1) Name: RAID Level: Primary-0, Secondary-0 RAID Level Qualifier-0Size: 223.0 GBState: OptimalStrip Size: 64 KBNumber Of Drives: 1++Device Id: 0PD Type: SATARaw Size: 223.570 GB [0x1bf244b0 Sectors] Inquiry Data: PHDV7234023C240AGN INTEL SSDSC2BB240G7 N2010112Device Speed: 6.0Gb/s Drive Temperature: 27C (80.60F) Device Id: 1PD Type: SATARaw Size: 223.570 GB [0x1bf244b0 Sectors] Inquiry Data: PHDV717003D9240AGN INTEL SSDDevice Speed: 6.0Gb / s Drive Temperature: 26C (78.80F) 3. Create test data:

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

Create a timer to write data every second:

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

Tip:

The main purpose of creating the appellate test data is to see how long the table will be locked to the master library when xtrabackup backs up the data, and whether locking the table will cause the insert data to fail.

Although data is written to the master library every second in this demonstration, it is not seen that the written table data failed due to the locking table (maybe there is too little data written concurrently to the table per second, or the locking process time is too short).

Backup and restore data and build master-slave replication

The installation process of the xtrabackup backup tool is not discussed here. For more information, please see this link:

Https://blog.51cto.com/wujianwei/2430281

4.1xtrackup 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 TABLES200115 17:22:33 All tables unlocked200115 17:22:33 [00] Copying ib_buffer_pool to / data/backup/db_3306_20200115/ib_buffer_pool200115 17:22:33 [00] ... done200115 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.cnf200115 17:22:33 [00]... done200115 17:22:33 [00] Writing / data/backup/db_3306_20200115/xtrabackup_info200115 17:22:33 [00]... donextrabackup: Transaction log of lsn (117743624292) to (117743935718) was copied.200115 17:22 : 33 completed OK!real 11m21.125suser 0m49.376ssys 2m15.140s

It takes 11 minutes and 21 seconds

[root@localhost backup] # time innobackupex-- apply-log / data/backup/db_3306_20200115/InnoDB: Shutdown completed; log sequence number 117743937576200115 17:38:41 completed OK real 0m14.873suser 0m0.093ssys 0m2.428s

It takes 15 seconds

4.2 restore the backed-up 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/ibtmp1200115 17:49:35 [01]... done200115 17:49:59 [02]... done200115 17:49:59 completed Oklahoma real 6m25.941suser 0m0.122ssys 3m21.198s

It takes 6 minutes and 26 seconds

4.3.Authorize the permissions of the new 3307 instance data directory mysql:

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

4.4 start 3307 mysql service

Create a replication user on the 4.5 master library:

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

View the location of the finished binlog backed up by xtrabackup:

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

4.6 configure replication with or without gtid:

Change master to on the slave library:

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-slave replication is configured in the way of appeal, indicating that the master library did not open Gtid at the beginning.

+

If Gtid is enabled at the beginning of the master library, the slave library 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 replicating Gtid master / slave, the master library must have Gtid enabled in advance.

After restoring the data backed up by xttabackup to the new instance and starting the new instance, log in to the new instance and clear the gtid information on the new instance.

Clear the Gtid information for the new 3307 instance:

Mysql > reset master

View the location of the finished binlog backed up by xtrabackup:

Cat / data/backup/db_3306_20200115/xtrabackup_binlog_ info [root @ localhost db_3306_20200115] # cat / data/backup/db_3306_20200115/xtrabackup_binlog_infomysql-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 entire backup, recovery and new slave, based on 63G data volume can be completed in 18 minutes. Moreover, during the xtrabackup backup, table locking and unlocking are completed within 1 second. In the simulated environment, the impact of table locking is still very small. However, it is still recommended that xtrabackup backup data during the business trough and xtrabckup backup data on the slave library to minimize the impact of locking tables on the database.

For the above about the fastest completion of a single device mysql master-slave building brief analysis, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.