点击(此处)折叠或打开
- 一共三个数据库,原版本均为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>
- 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.01 sec)
-
-
- mysql> select CHANNEL_NAME FROM performance_schema.replication_connection_status;
- +--------------+
- | CHANNEL_NAME |
- +--------------+
- | master_vm2 |
- | 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26735168/viewspace-2117545/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26735168/viewspace-2117545/