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

The following content mainly brings you the multi-source copy handout of mysql5.7 configuration. The knowledge mentioned is slightly different from the book. It is summarized by professional technicians in the process of contacting users. It has certain experience sharing value and hopes to bring help to readers.

Suppose there are three hosts, two master libraries are master1, master2, and one slave library.

1. Open binary logs on the main database respectively, and configure different server-ids;

[mysqld]

log-bin=mysql-bin

server-id=1

2. Create a user dedicated to replication in two master libraries respectively, and grant replication permission;

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

3. Execute state query in two master libraries respectively, and record query state;

SHOW MASTER STATUS;

Record file and position respectively, assuming that the query result of master1 is file1, pos1, and the query result of master2 is file2, pos2.

4. Configure slave libraries

1) Set the following two variables to table;

set global master_info_repository='TABLE';

set global relay_log_info_repository='TABLE';

2), execute the following setting statements respectively:

CHANGE MASTER TO

MASTER_HOST='master1,

MASTER_USER='repl',

MASTER_PASSWORD='password',

MASTER_LOG_FILE='file1',

MASTER_LOG_POS=rpos1 for channel 'master1';

CHANGE MASTER TO

MASTER_HOST='master2,

MASTER_USER='repl',

MASTER_PASSWORD='password',

MASTER_LOG_FILE='file2',

MASTER_LOG_POS=rpos2 for channel 'master2';

3) Start copying from the library

Start simultaneously:

start slave;

It can also be activated separately:

start slave for channel 'master1';

start slave for channel 'master2';

Stop simultaneously:

stop slave;

You can also stop:

stop slave for channel 'master1';

stop slave for channel 'master2';

Note: If there is a copy of the virtual machine during the operation through the virtual machine, such as creating a virtual slave by copying master1,

Then it is likely that the slave library cannot be started. In this case, you need to perform the following operations in the slave library:

1. Empty the following tables from the library:

slave_master_info

slave_relay_log_info

slave_worker_info

innodb_index_stats

innodb_table_stats

2. Stop the mysqld service, delete the auto.cnf file under the data directory, and restart the mysqld service;

3. Continue configuring slave libraries.

For the above multi-source copy handout on mysql5.7 configuration, if you still need to know more, you can continue to pay attention to our industry promotion. If you need to get professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.