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

Environmental information

\

Primary server 1

Master Server 2

From server 1

Ip

192.168.31.79

192.168.31.188

192.168.31.90

Hostname

Mysql

Mysql2

Mysql3

Mysql-version

5.7.23

5.7.23

5.7.23

Server-id

8e33e4d6-66a2-11ea-b90d-080027c1c1ff

9646d8c8-66a2-11ea-a1e5-080027b0e953

E54ee48b-66a2-11ea-8c1a-08002742a5e1

Related database

TEST1

TEST2

Note:

1) Master server 1 and master server 2 cannot have the same database name, otherwise the database will be overwritten.

2) Master server 1-> slave and master server 2-> slave should have different replication accounts

3) the configuration of database parameters between the three machines should be the same as that copied by GTID. Make sure to turn on the GTID function.

4) inconsistency between server-id. The binlog format is row mode.

1. Master server 1 configuration 1.1 modify parameter file

[root@mysql bin] # vi / etc/my.cnf

Gtid_mode=on

Enforce_gtid_consistency=on

Log_bin=on

Binlog_format=row

Server-id=79

1.2 configure permissions

[root@mysql bin] # mysql-uroot-p

Mysql > create user 'bak1'@'192.168.31.%' identified by' Oracle123'

Mysql > grant replication slave on *. * to 'bak1'@'192.168.31.%'

Mysql > flush privileges

1.3 back up the database

[root@mysql] # mysqldump-- single-transaction-uroot-pOracle123 TEST1 > TEST1_20200310.sql

two。 Master server 2 configuration 2.1 modify parameter file

[root@mysql2 bin] # vi / etc/my.cnf

The content is added as follows:

Gtid_mode=on

Enforce_gtid_consistency=on

Log_bin=on

Binlog_format=row

Server-id=188

2.2 configure permissions

[root@mysql2 bin] # mysql-uroot-p

Mysql > create user 'bak2'@'192.168.31.%' identified by' Oracle123'

Mysql > grant replication slave on *. * to 'bak2'@'192.168.31.%'

Mysql > flush privileges

2.3 back up the database

[root@mysql2] # mysqldump-- single-transaction-uroot-pOracle123 TEST2 > TEST2_20200310.sql

3. Modify the parameter file from server configuration 3.1

[root@mysql3 bin] # vi / etc/my.cnf

The content is added as follows:

Gtid_mode=on

Enforce_gtid_consistency=on

Log_bin=on

Binlog_format=row

Server-id=90

Master_info_repository=table

Relay_log_info_repository=table

3.2 restore the database

[root@mysql3 bin] # mysql-uroot-p

Mysql > create database TEST1

Mysql > create database TEST2

Mysql > reset master

[root@mysql3 soft] # mysql-uroot-pOracle123 TEST1

Change master to master_host='192.168.31.79',master_user='bak1', master_password='Oracle123',master_auto_position=1 for channel 'm1'

Mysql > change master to master_host='192.168.31.188',master_user='bak2', master_password='Oracle123', master_auto_position=2 for channel 'm2'

Mysql > start slave for channel 'm1'

Mysql > start slave for channel 'm2'

4. Check 4.1 check process status

Mysql > use performance_schema

Mysql > select * from replication_connection_status\ G

4.2 Verification

1) Primary server 1

Mysql > use TEST1

Mysql > insert into test1 values (1)

Mysql > commit

2) Primary server 2

Mysql > use TEST2

Mysql > insert into test2 values (2)

3) from the server

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.