The background that

Responsible for the establishment and maintenance of MySQL data warehouse of the company. As front-end business involves the sub-database of a business table, a new MySQL server is added to the whole platform, and the data of the database needs to be synchronized to the original data warehouse instance through master/slave replication.

The data framework is described as follows:

Service Environment description

MySQL: percona 8.0.16-7 MySQL: Percona 8.0.16-7

Operating system: Centos 7

steps

1: obtains the database backup from the source repository

  • For physical backup, xtrabackup is used. For logical backup, mysqldump is used directly.

  • Mysqldump;

mysqldump -uroot -p --master-data=2 --single-transaction -S /data/mysql/3306/mysql_3306.sock -E -R --comments --triggers --databases data1 > /data/data1.sql

–master-data=2; –single-transaction =2; –master-data=2

  • It is important to obtain the binlog site information in the backup file. The master/slave replication must know the binlog start site for the source instance

  • Use shell commands to view the loci information in the backup file

Grep -i "change master" /data/data1. SQL -- Find the corresponding primary and secondary loci

2: restores the backup to the target instance

This step is simple, move the backup SQL file in 1 to the target server, directly source

3: Set primary/secondary replication parameters to enable primary/secondary replication

  • Create a replication channel pointing relationship

CHANGE MASTER TO MASTER_HOST='XXXX', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.005461', MASTER_LOG_POS=918111162 FOR CHANNEL 'XXXXXX';

Replication account set up in advance, channel name set itself, generally consistent with the library name, site information is 2 return result, copy over good

  • Setting a channel’s library level filtering instance level master/slave synchronization can be ignored

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (B) FOR channel 'XXXXXX';

Keep the same above the channel name and set the library name

  • Start replication channel observation

start slave for channel 'XXXXXX';

-- STOP SLAVE FOR CHANNEL 'XXXXXX'; -- Stop the replication channel alone

4: check

  • Show slave info – Check the value of the key field, SQL process, IO process is yes, whether there is delay

  • Check whether the new database B table in the database has changed

conclusion

Master/slave replication is the basis for high availability in MySQL. It is important to obtain the binlog starting point of the source database, because the binlog is the server level log and the record statement is complete. On this basis, you can carry out table level, library level, instance level replication synchronization