Shulou(Shulou.com)05/31 Report--

This article will explain in detail what to do when you start multi-source replication when you encounter duplicate channel name after upgrading mysql5.6 to 5.7.13. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Click (here) to collapse or open

A total of three databases, the original version is 5.6.After upgrading to 5.7.13, multi-source replication is enabled to make two masters and one slave, and it is found that channel name has the same name.

After executing reset slave all from the library, it automatically restarts from the library.

5.6-> 5.7.13:

5.6:

1 cd / usr/local/mysql; scripts/mysql_install_db-user=mysql-defaults-file=/57data/my56.cnf

2 / usr/local/mysql/bin/mysqld_safe-- defaults-file=/57data/my56.cnf &

3 mysqladmin-S / tmp/mysql3307.sock-u root-P3307-p password

4 mysql-uroot-S / tmp/mysql3307.sock-pmysql

5 set global innodb_fast_shutdown=0

6 mysqladmin-S / tmp/mysql3307.sock shutdown

5.7:

7 cd / usr/local/mysql57;. / bin/mysqld_safe-- defaults-file=/57data/my.cnf &

8 cd / usr/local/mysql57;. / bin/mysql_upgrade-S / tmp/mysql3307.sock

9 cd / usr/local/mysql57;. / bin/mysqladmin-S / tmp/mysql3307.sock shutdown

10 cd / usr/local/mysql57;. / bin/mysqld_safe-- defaults-file=/57data/my.cnf &

11 / usr/local/mysql57/bin/mysql-S / tmp/mysql3307.sock

12 grant replication slave,replication client on *. * to repl@'%' identified by 'passw0rd'

13

Mysql > SET GLOBAL master_info_repository = 'TABLE'

Query OK, 0 rows affected (0.00 sec)

Mysql > SET GLOBAL relay_log_info_repository = 'TABLE'

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql > change master to master_host='vm2'

-> master_user='repl'

-> MASTER_PORT=3307

-> master_password='passw0rd'

-> master_auto_position=1 FOR CHANNEL 'master_vm2'

Change master to master_host='vm3'

Master_user='repl'

MASTER_PORT=3307

Master_password='passw0rd'

Query OK, 0 rows affected, 2 warnings (0.08 sec)

Mysql >

Mysql > change master to master_host='vm3'

-> master_user='repl'

-> MASTER_PORT=3307

-> master_password='passw0rd'

-> master_auto_position=1 FOR CHANNEL 'master_vm3'

Query OK, 0 rows affected, 2 warnings (0.04 sec)

Mysql >

Mysql >

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: vm2

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: vm4-relay-bin-master_vm2.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 0

Relay_Log_Space: 154

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 45ee9543-2d51-11e6-ba5d-0800276e6667:1-2

4acf2a8c-2d51-11e6-ba5d-080027806975:1-2

4c01fc54-2d51-11e6-ba5d-08002781f86e:1-6

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name: master_vm2

Master_TLS_Version:

* 2. Row * *

Slave_IO_State:

Master_Host: vm3

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: vm4-relay-bin-master_vm3.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 0

Relay_Log_Space: 154

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 45ee9543-2d51-11e6-ba5d-0800276e6667:1-2

4acf2a8c-2d51-11e6-ba5d-080027806975:1-2

4c01fc54-2d51-11e6-ba5d-08002781f86e:1-6

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name: master_vm2

Master_TLS_Version:

2 rows in set (0.00 sec)

ERROR:

No query specified

Mysql >

Mysql > select CHANNEL_NAME FROM performance_schema.replication_connection_status

+-+

| | CHANNEL_NAME |

+-+

| | master_vm2 |

| | master_vm2 |

+-+

2 rows in set (0.01sec)

Mysql > select CHANNEL_NAME FROM performance_schema.replication_connection_status

+-+

| | CHANNEL_NAME |

+-+

| | master_vm2 |

| | master_vm2 |

+-+

2 rows in set (0.01sec)

Mysql > start slave

Query OK, 0 rows affected (0.04 sec)

The database vm2 and vm3 are built in the two master databases respectively, and the system is copied to the slave database normally.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | test |

| | vm2 |

| | vm3 |

+-+

7 rows in set (0.00 sec)

Reset slave all is executed from the slave library and automatically restarted from the slave library.

Mysql > reset slave all

ERROR 2013 (HY000): Lost connection to MySQL server during query

Mysql > 2016-06-08T08:56:18.890685Z mysqld_safe Number of processes running now: 0

2016-06-08T08:56:18.906614Z mysqld_safe mysqld restarted

Mysql >

Mysql > reset slave all

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: * * NONE * *

Query OK, 0 rows affected (0.09 sec)

Mysql > show slave status\ G

Empty set (0.01sec)

ERROR:

No query specified

The channel name information is displayed correctly in err log, but the Thread pointer-related information appears, as follows:

2016-06-08T08:55:54.540373Z 4 [Note] Error reading relay log event for channel 'master_vm2': slave SQL thread was killed

2016-06-08T08:55:54.550729Z 3 [Note] Slave I master_vm2' O thread killed while reading event for channel'

2016-06-08T08:55:54.550814Z 3 [Note] Slave I master_vm2', read up to log O thread exiting for channel 'master_vm2', read up to log' mysql_bin.000005', position 653

2016-06-08T08:55:54.556301Z 6 [Note] Error reading relay log event for channel 'master_vm3': slave SQL thread was killed

2016-06-08T08:55:54.563404Z 5 [Note] Slave I master_vm3' O thread killed while reading event for channel'

2016-06-08T08:55:54.563549Z 5 [Note] Slave I master_vm3', read up to log O thread exiting for channel 'master_vm3', read up to log' mysql_bin.000005', position 653

08:56:18 UTC-mysqld got signal 11

This could be because you hit a bug. It is also possible that this binary

Or one of the libraries it was linked against is corrupt, improperly built

Or misconfigured. This error can also be caused by malfunctioning hardware.

Attempting to collect some information that could help diagnose the problem.

As this is a crash and something is definitely wrong, the information

Collection process might fail.

Key_buffer_size=8388608

Read_buffer_size=131072

Max_used_connections=1

Max_threads=151

Thread_count=1

Connection_count=1

It is possible that mysqld could use up to

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_threads = 68189 K bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f9550000b10

Attempting backtrace. You can use the following information to find out

Where mysqld died. If you see no messages after this, something went

Terribly wrong...

Stack_bottom = 7f9583175e28 thread_stack 0x40000

/ usr/local/mysql57/bin/mysqld (my_print_stacktrace+0x35) [0xf25c45]

/ usr/local/mysql57/bin/mysqld (handle_fatal_signal+0x4a4) [0x7b8604]

/ lib64/libpthread.so.0 [0x37f160f7e0]

/ usr/local/mysql57/bin/mysqld (_ ZN16Multisource_info9delete_miEPKc+0x2df) [0xf082ff]

/ usr/local/mysql57/bin/mysqld (_ Z11reset_slaveP3THDP11Master_infob+0x1c5) [0xef1e05]

/ usr/local/mysql57/bin/mysqld (_ Z11reset_slaveP3THD+0x2e2) [0xef21b2]

/ usr/local/mysql57/bin/mysqld (_ Z15reset_slave_cmdP3THD+0x98) [0xef2388]

/ usr/local/mysql57/bin/mysqld (_ Z20reload_acl_and_cacheP3THDmP10TABLE_LISTPi+0x30c) [0xd2ae3c]

/ usr/local/mysql57/bin/mysqld (_ Z21mysql_execute_commandP3THDb+0x83a) [0xcfaf2a]

/ usr/local/mysql57/bin/mysqld (_ Z11mysql_parseP3THDP12Parser_state+0x3cd) [0xcff97d]

/ usr/local/mysql57/bin/mysqld (_ Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x1099) [0xd00a79]

/ usr/local/mysql57/bin/mysqld (_ Z10do_commandP3THD+0x194) [0xd016e4]

/ usr/local/mysql57/bin/mysqld (handle_connection+0x2a4) [0xdce6e4]

/ usr/local/mysql57/bin/mysqld (pfs_spawn_thread+0x171) [0x121b951]

/ lib64/libpthread.so.0 [0x37f1607aa1]

/ lib64/libc.so.6 (clone+0x6d) [0x37f0ee893d]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (7f95500054f0): is an invalid pointer

Connection ID (thread ID): 2

Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

Information that should help you find out what is causing the crash.

2016-06-08T08:56:18.890685Z mysqld_safe Number of processes running now: 0

2016-06-08T08:56:18.906614Z mysqld_safe mysqld restarted

This is how to start multi-source replication after upgrading mysql5.6 to 5.7.13. If you encounter duplicate channel name, I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.