Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Synchronization error repair of database multi-source replication

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

The error report goes like this:

A database was found to have reported an error. The error is as follows.

Coordinator stopped because there were error (s) in the worker (s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000029, end_log_pos 6389312. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Solution:

Enter the slave server, mysql

1. View the status of slave libraries

Mysql > show slave status

two。 Follow the prompts from the library to find the cause.

Select * from performance_schema.replication_applier_status_by_worker\ G

It means that when a table in the main database is deleted, the deleted record cannot be found from the library.

Now that this record for both tables has been deleted. You can skip this mistake.

The solution.

Stop synchronization, skip 1 step, and then turn on synchronization.

Stop slave for channel 'channelname'

Set global sql_slave_skip_counter=1

Start slave for channel 'channelname'

There are ways to fix similar problems on the Internet. Let's take a look at several related articles.

Http://blog.csdn.net/u011277123/article/details/53487831

Http://www.bcty365.com/content-35-5816-1.html

Https://www.jb51.net/article/109107.htm

Http://www.cnblogs.com/zhoujinyi/p/5704567.html

Http://www.bubuko.com/infodetail-2472122.html

MySQL5.7 multiple source replication (Multi-Source Replication)

If the amount of data is small, the following method is used. But the amount of data is large, so it is not suitable, because the whole synchronization takes a lot of time.

3. Stop master-slave synchronization first.

Mysql > stop slave

4. Synchronize the database with the navicate tool.

Navicate menu bar, tools-> data synchronization.

First structure synchronization, then data synchronization.

Then, after synchronization, go to the main library. Use orders.

Mysql > SHOW MASTER STATUS

Because what I'm doing here is multi-source replication. So after synchronizing the two databases, record them one after another.

Get:

'mysql-bin.000029', '20698487'

'mysql-bin.000042', '84950954'

5. Clear the original synchronization settings

Mysql > reset slave all

6. And then set up synchronization.

The following ip address, username,password, is an example. Let's do it your own way.

Change master to master_host='192.168.0.1',master_user='username1',master_port=3306,master_password='password1',master_log_file='mysql-bin.000029',master_log_pos=20698487 for channel 'databasename1'

Change master to

Master_host='192.168.0.2',master_user='username2',master_port=3306,master_password='password2',master_log_file='mysql-bin.000042',master_log_pos=84950954 for channel 'databasename2'

7. Start synchronization

Mysql > start slave

8. View synchronization status

Mysql > show slave status

Synchronization is back to normal.

Supplement: you can be alone.

START SLAVE; / / start replication of all threads and all channels, commonly used

START SLAVE SQL_THREAD; / / start the SQL_ thread for all channels

START SLAVE IO_THREAD; / / start the IO_ thread for all channels

START SLAVE FOR CHANNEL 'master_3306'

START SLAVE SQL_THREAD FOR CHANNEL 'master_3306'

START SLAVE IO_THREAD FOR CHANNEL 'master_3306'

The stop copy command is similar to START, replacing START with STOP

Reset can choose to reset all and reset a single channel:

RESET SLAVE

RESET SLAVE FOR CHANNEL CHANNELNAME

Several instructions for multi-source replication:

Use multi-source replication to avoid that the source library is a library with the same name, so that errors will occur during replication

For the mysql library, it is recommended to use REPLICATE_IGNORE_DB to mask it. In MySQL5.7, dynamic operation on these parameters is already supported.

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (mysql)

When operating on the mysql library on the main library, you need to add use mysql, otherwise it will not be filtered

The same is true for REPLICATE_DO_DB, use db is required during operation, otherwise synchronization will not take place.

For errors in master-slave replication, whether based on binlog_position or GTID, it is the same as the common one-to-one replication solution, only start this channel separately when starting replication, and then start other channels when there is no problem with this channel replication. An error occurred in the example master_3307. This example is based on binlog_position:

STOP SLAVE

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

START SLAVE FOR CHANNEL 'master_3307'

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report