mysql5.6升級到5.7.13后開啟多源復制遇到重復的channel name該怎么辦

144次閱讀

沒有評論

共計 8595 個字符，預計需要花費 22 分鐘才能閱讀完成。

行業資訊
數據庫
MySQL 數據庫
mysql5.6 升級到 5.7.13 后開啟多源復制遇到重復的 channel name 該怎么辦

這篇文章將為大家詳細講解有關 mysql5.6 升級到 5.7.13 后開啟多源復制遇到重復的 channel name 該怎么辦，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

點擊 (此處) 折疊或打開

一共三個數據庫，原版本均為 5.6，升級到 5.7.13 后開啟多源復制，做成兩主一從，發現 channel name 同名。

在從庫執行 reset slave all 后，從庫自動重啟。

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 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 |

+————–+

2 rows in set (0.01 sec)

mysql select CHANNEL_NAME FROM performance_schema.replication_connection_status;

+————–+

| CHANNEL_NAME |

+————–+

| master_vm2 |

+————–+

2 rows in set (0.01 sec)

mysql start slave;

Query OK, 0 rows affected (0.04 sec)

在兩主庫分別建數據庫 vm2 和 vm3, 系統正常復制到從庫。

mysql show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

| vm2 |

| vm3 |

+——————–+

7 rows in set (0.00 sec)

在從庫執行 reset slave all，從庫自動重啟。

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.01 sec)

ERROR:

No query specified

err log 里 channel name 信息顯示正確，但出現 Thread pointer 相關信息，如下：

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/O thread killed while reading event for channel master_vm2

2016-06-08T08:55:54.550814Z 3 [Note] Slave I/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/O thread killed while reading event for channel master_vm3

2016-06-08T08:55:54.563549Z 5 [Note] Slave I/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

關于 mysql5.6 升級到 5.7.13 后開啟多源復制遇到重復的 channel name 該怎么辦就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

正文完