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

Environment description:

Mainframe

IP

MySQL version

Port

Copy the account number

Copy password

Master1

192.168.1.225

5.7.25

3306

Repl

123456

Master2

192.168.1.100

5.7.25

3306

Repl

123456

Slave

192.168.1.240

5.7.25

3306

Master1 profile:

[mysqld]

User = mysql

Socket = / tmp/mysql3306.sock

Basedir = / usr/local/mysql

Datadir = / data/mysql/mysql3306/data

Server-id = 2253306

Log_bin = / data/mysql/mysql3306/logs/mysql-bin

Log_slave_updates = 1

Binlog_format = ROW

Gtid_mode = on

Enforce_gtid_consistency = on

Master2 profile

[mysqld]

User = mysql

Socket = / tmp/mysql3306.sock

Basedir = / usr/local/mysql

Datadir = / data/mysql/mysql3306/data

Server-id = 1003306

Log_bin = / data/mysql/mysql3306/logs/mysql-bin

Log_slave_updates = 1

Binlog_format = ROW

Gtid_mode = on

Enforce_gtid_consistency = on

Slave profile

[mysqld]

User = mysql

Socket = / tmp/mysql3306.sock

Basedir = / usr/local/mysql

Datadir = / data/mysql/mysql3306/data

Server-id = 2403306

Log_bin = / data/mysql/mysql3306/logs/mysql-bin

Log_slave_updates = 1

Binlog_format = ROW

Gtid_mode = on

Enforce_gtid_consistency = on

Master_info_repository = TABLE # Multi-source replication can only be configured as table

Relay_log_info_repository = TABLE # Multi-source replication can only be configured as table

Create a replication account on Master1,Maste2:

Mysql > set sql_log_bin=0

Mysql > grant replication slave on *. * to 'repl'@'192.168.1.240' identified by' 123456'

Mysql > set sql_log_bin=1

Create a test database test1, test table T1, on Master1

Mysql > create database test1

Mysql > use test1

Mysql > create table T1 (id int)

Mysql > insert into T1 values (1)

Create a test database test2, test table T2 on Master2

Mysql > create database test2

Mysql > use test2

Mysql > create table T2 (id int)

Mysql > insert into T2 values (2)

Backup test1,test2 on the export Master1,Master2

Master1

# mysqldump-S / tmp/mysql3306.sock-- single-transaction-- master-data=2 test1 > test1.sql

Master2:

# mysqldump-S / tmp/mysql3306.sock-- single-transaction-- master-data=2 test2 > test2.sql

The warning message of the backup Times can be ignored:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events.

Slave configuration

1. First create two test1,test2 databases on Slave

Mysql > create database test1

Mysql > create database test2

two。 Import test1 data

# mysql-S / tmp/mysql3306.sock test1

< test1.sql ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 解决办法：在Slave上先执行reset master操作，后面等数据都导入之后再手动指定,后面会说。 mysql>

Reset master

# mysql-S / tmp/mysql3306.sock test1

< test1.sql; #这个时候导入就不会报错了 3.导入test2的数据 mysql>

Reset master; # needs to be cleaned again because the import of test1 data GTID_EXECUTED has been generated again.

# mysql-S / tmp/mysql3306.sock test2

< test2.sql; 4.获取sql文件中gtid_purged的值，并在Slave上手动设置 #grep -m 1 "GTID_PURGED" test1.sql SET @@GLOBAL.GTID_PURGED='31315146-adbb-11e9-b99a-525400c3d235:1-3'; #grep -m 1 "GTID_PURGED" test2.sql SET @@GLOBAL.GTID_PURGED='e712f244-adba-11e9-abe6-525400ebcfd9:1-3'; mysql>

Reset master; # needs to be cleaned again because the import of test2 data GTID_EXECUTED has been generated again.

Mysql > set @ @ global.GTID_PURGED='31315146-adbb-11e9-b99a-525400c3d235:1-3Journal e712f244muradbamur11e9murabe6-525400ebcfd9:1-3' # Note to set the values of both libraries, separated by commas.

5. Configure master-slave synchronization

1. Set the information of Master1,Master2

Mysql > change master to master_host='192.168.1.225',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1 for channel 'Master1'

Mysql > change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1 for channel 'Master2'

two。 Configure synchronization filtering rules

Because Master pushes all gtid to Slave, if only part of the library is synchronized, there will be an error when executing gtid from the library that does not have a related library.

Mysql > change replication filter replicate_do_db= (test1,test2)

6. Start Slave

Mysql > start slave for channel 'Master1'; # corresponds to the synchronization of Master1

Mysql > start slave for channel 'Master2'; # corresponds to the synchronization of Master2

7. Verification

1. Check whether the master-slave synchronization connection is abnormal, and if there is an error, solve the error report first and then carry on the second step test.

Mysql > show slave status\ G

two。 Write a record to the test1 library T1 table on Master1 and the test2 library T2 table on Master2 to see if it is synchronized to Slave.

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.