MySQL slave server getting stopped after each replication request from Master
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
2
Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports.
Now when executing update or insert in master server, slave server getting stopped after that event. Once restarting slave server and check updates then update/insert is successfully executed in slave through replication setup.
What could be the possible root cause of this issue?
Log of show slave statusG :
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: masteradmin
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: USERMAC38-bin.000007
Read_Master_Log_Pos: 840
Relay_Log_File: USERMAC38-relay-bin.000004
Relay_Log_Pos: 290
Relay_Master_Log_File: USERMAC38-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 840
Relay_Log_Space: 467
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: 0
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: 1
Master_UUID: 63ac2f83-44ac-11e5-bafe-d43d7e3ca358
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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:
Auto_Position: 0
Error log of slave before it got stopped :
'CHANGE MASTER TO executed'. Previous state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000008', master_log_pos= 123, master_bind=''. New state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000013 [truncated, 295 bytes total]
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 498
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 498, relay log '.USERMAC38-relay-bin.000001' position: 4
General log of slave before it got stopped :
150819 11:04:44 10 Query stop slave
150819 11:04:45 8 Query SHOW GLOBAL STATUS
150819 11:04:48 8 Query SHOW GLOBAL STATUS
150819 11:04:51 8 Query SHOW GLOBAL STATUS
10 Query CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'masteradmin' MASTER_PASSWORD = <secret> MASTER_PORT = 3307 MASTER_LOG_FILE = 'USERMAC38-bin.000013' MASTER_LOG_POS = 498
150819 11:04:54 8 Query SHOW GLOBAL STATUS
150819 11:04:55 10 Query start slave
11 Connect Out masteradmin@127.0.0.1:3307
150819 11:04:57 8 Query SHOW GLOBAL STATUS
150819 11:05:00 8 Query SHOW GLOBAL STATUS
150819 11:05:02 10 Query show slave status
150819 11:05:03 8 Query SHOW GLOBAL STATUS
150819 11:05:06 8 Query SHOW GLOBAL STATUS
150819 11:05:09 8 Query SHOW GLOBAL STATUS
150819 11:05:12 8 Query SHOW GLOBAL STATUS
150819 11:05:15 8 Query SHOW GLOBAL STATUS
150819 11:05:18 8 Query SHOW GLOBAL STATUS
150819 11:05:21 8 Query SHOW GLOBAL STATUS
150819 11:05:24 8 Query SHOW GLOBAL STATUS
150819 11:05:27 8 Query SHOW GLOBAL STATUS
150819 11:05:30 8 Query SHOW GLOBAL STATUS
150819 11:05:33 8 Query SHOW GLOBAL STATUS
150819 11:05:37 8 Query SHOW GLOBAL STATUS
150819 11:05:40 8 Query SHOW GLOBAL STATUS
150819 11:05:43 8 Query SHOW GLOBAL STATUS
150819 11:05:46 8 Query SHOW GLOBAL STATUS
150819 11:05:49 8 Query SHOW GLOBAL STATUS
150819 11:05:52 8 Query SHOW GLOBAL STATUS
150819 11:05:55 8 Query SHOW GLOBAL STATUS
150819 11:05:58 8 Query SHOW GLOBAL STATUS
150819 11:06:01 8 Query SHOW GLOBAL STATUS
150819 11:06:04 8 Query SHOW GLOBAL STATUS
150819 11:06:07 8 Query SHOW GLOBAL STATUS
150819 11:06:10 8 Query SHOW GLOBAL STATUS
150819 11:06:13 8 Query SHOW GLOBAL STATUS
150819 11:06:16 8 Query SHOW GLOBAL STATUS
150819 11:06:18 12 Query BEGIN
12 Query COMMIT /* implicit, from Xid_log_event */
150819 11:06:19 8 Query SHOW GLOBAL STATUS
Error log after restarting slave :
You need to use --log-bin to make --log-slave-updates work.
You need to use --log-bin to make --binlog-format work.
Plugin 'FEDERATED' is disabled.
2015-08-19 12:11:26 150 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Windows interlocked functions
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU does not support crc32 instructions
InnoDB: Initializing buffer pool, size = 165.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 8556085
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 8556558
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 8556558
Server hostname (bind-address): '127.0.0.1'; port: 3309
- '127.0.0.1' resolves to '127.0.0.1';
Server socket created on IP: '127.0.0.1'.
2015-08-19 12:11:27 150 InnoDB: Warning: table 'mysql/slave_worker_info'
InnoDB: in InnoDB data dictionary has unknown flags 50.
Recovery from master pos 2235 and file USERMAC38-bin.000013.
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 2235
Event Scheduler: Loaded 0 events
E:2-Softwaresmysql-5.6.10-winx64binmysqld.exe: ready for connections.
Version: '5.6.10-log' socket: '' port: 3309 MySQL Community Server (GPL)
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 2235, relay log '.USERMAC38-relay-bin.000011' position: 4
replication mysql-5.6
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 4 more comments
2
Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports.
Now when executing update or insert in master server, slave server getting stopped after that event. Once restarting slave server and check updates then update/insert is successfully executed in slave through replication setup.
What could be the possible root cause of this issue?
Log of show slave statusG :
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: masteradmin
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: USERMAC38-bin.000007
Read_Master_Log_Pos: 840
Relay_Log_File: USERMAC38-relay-bin.000004
Relay_Log_Pos: 290
Relay_Master_Log_File: USERMAC38-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 840
Relay_Log_Space: 467
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: 0
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: 1
Master_UUID: 63ac2f83-44ac-11e5-bafe-d43d7e3ca358
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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:
Auto_Position: 0
Error log of slave before it got stopped :
'CHANGE MASTER TO executed'. Previous state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000008', master_log_pos= 123, master_bind=''. New state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000013 [truncated, 295 bytes total]
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 498
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 498, relay log '.USERMAC38-relay-bin.000001' position: 4
General log of slave before it got stopped :
150819 11:04:44 10 Query stop slave
150819 11:04:45 8 Query SHOW GLOBAL STATUS
150819 11:04:48 8 Query SHOW GLOBAL STATUS
150819 11:04:51 8 Query SHOW GLOBAL STATUS
10 Query CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'masteradmin' MASTER_PASSWORD = <secret> MASTER_PORT = 3307 MASTER_LOG_FILE = 'USERMAC38-bin.000013' MASTER_LOG_POS = 498
150819 11:04:54 8 Query SHOW GLOBAL STATUS
150819 11:04:55 10 Query start slave
11 Connect Out masteradmin@127.0.0.1:3307
150819 11:04:57 8 Query SHOW GLOBAL STATUS
150819 11:05:00 8 Query SHOW GLOBAL STATUS
150819 11:05:02 10 Query show slave status
150819 11:05:03 8 Query SHOW GLOBAL STATUS
150819 11:05:06 8 Query SHOW GLOBAL STATUS
150819 11:05:09 8 Query SHOW GLOBAL STATUS
150819 11:05:12 8 Query SHOW GLOBAL STATUS
150819 11:05:15 8 Query SHOW GLOBAL STATUS
150819 11:05:18 8 Query SHOW GLOBAL STATUS
150819 11:05:21 8 Query SHOW GLOBAL STATUS
150819 11:05:24 8 Query SHOW GLOBAL STATUS
150819 11:05:27 8 Query SHOW GLOBAL STATUS
150819 11:05:30 8 Query SHOW GLOBAL STATUS
150819 11:05:33 8 Query SHOW GLOBAL STATUS
150819 11:05:37 8 Query SHOW GLOBAL STATUS
150819 11:05:40 8 Query SHOW GLOBAL STATUS
150819 11:05:43 8 Query SHOW GLOBAL STATUS
150819 11:05:46 8 Query SHOW GLOBAL STATUS
150819 11:05:49 8 Query SHOW GLOBAL STATUS
150819 11:05:52 8 Query SHOW GLOBAL STATUS
150819 11:05:55 8 Query SHOW GLOBAL STATUS
150819 11:05:58 8 Query SHOW GLOBAL STATUS
150819 11:06:01 8 Query SHOW GLOBAL STATUS
150819 11:06:04 8 Query SHOW GLOBAL STATUS
150819 11:06:07 8 Query SHOW GLOBAL STATUS
150819 11:06:10 8 Query SHOW GLOBAL STATUS
150819 11:06:13 8 Query SHOW GLOBAL STATUS
150819 11:06:16 8 Query SHOW GLOBAL STATUS
150819 11:06:18 12 Query BEGIN
12 Query COMMIT /* implicit, from Xid_log_event */
150819 11:06:19 8 Query SHOW GLOBAL STATUS
Error log after restarting slave :
You need to use --log-bin to make --log-slave-updates work.
You need to use --log-bin to make --binlog-format work.
Plugin 'FEDERATED' is disabled.
2015-08-19 12:11:26 150 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Windows interlocked functions
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU does not support crc32 instructions
InnoDB: Initializing buffer pool, size = 165.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 8556085
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 8556558
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 8556558
Server hostname (bind-address): '127.0.0.1'; port: 3309
- '127.0.0.1' resolves to '127.0.0.1';
Server socket created on IP: '127.0.0.1'.
2015-08-19 12:11:27 150 InnoDB: Warning: table 'mysql/slave_worker_info'
InnoDB: in InnoDB data dictionary has unknown flags 50.
Recovery from master pos 2235 and file USERMAC38-bin.000013.
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 2235
Event Scheduler: Loaded 0 events
E:2-Softwaresmysql-5.6.10-winx64binmysqld.exe: ready for connections.
Version: '5.6.10-log' socket: '' port: 3309 MySQL Community Server (GPL)
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 2235, relay log '.USERMAC38-relay-bin.000011' position: 4
replication mysql-5.6
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
That shows yes/yes, that makes you think it's stopped?
– winmutt
Aug 18 '15 at 14:38
This status is after restarting slave. Everything seems OK after restart. Now as and when new event is there, it will execute it successfully and then slave server will get stopped again.
– Harshit patel
Aug 19 '15 at 4:45
Any errors? Include the log and status after the execution.
– Tan Hong Tat
Aug 19 '15 at 5:03
Are the master and slave reading the same data directory?
– Tan Hong Tat
Aug 19 '15 at 7:41
Data directory is different for both.
– Harshit patel
Aug 19 '15 at 8:23
|
show 4 more comments
2
2
2
1
Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports.
Now when executing update or insert in master server, slave server getting stopped after that event. Once restarting slave server and check updates then update/insert is successfully executed in slave through replication setup.
What could be the possible root cause of this issue?
Log of show slave statusG :
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: masteradmin
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: USERMAC38-bin.000007
Read_Master_Log_Pos: 840
Relay_Log_File: USERMAC38-relay-bin.000004
Relay_Log_Pos: 290
Relay_Master_Log_File: USERMAC38-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 840
Relay_Log_Space: 467
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: 0
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: 1
Master_UUID: 63ac2f83-44ac-11e5-bafe-d43d7e3ca358
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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:
Auto_Position: 0
Error log of slave before it got stopped :
'CHANGE MASTER TO executed'. Previous state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000008', master_log_pos= 123, master_bind=''. New state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000013 [truncated, 295 bytes total]
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 498
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 498, relay log '.USERMAC38-relay-bin.000001' position: 4
General log of slave before it got stopped :
150819 11:04:44 10 Query stop slave
150819 11:04:45 8 Query SHOW GLOBAL STATUS
150819 11:04:48 8 Query SHOW GLOBAL STATUS
150819 11:04:51 8 Query SHOW GLOBAL STATUS
10 Query CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'masteradmin' MASTER_PASSWORD = <secret> MASTER_PORT = 3307 MASTER_LOG_FILE = 'USERMAC38-bin.000013' MASTER_LOG_POS = 498
150819 11:04:54 8 Query SHOW GLOBAL STATUS
150819 11:04:55 10 Query start slave
11 Connect Out masteradmin@127.0.0.1:3307
150819 11:04:57 8 Query SHOW GLOBAL STATUS
150819 11:05:00 8 Query SHOW GLOBAL STATUS
150819 11:05:02 10 Query show slave status
150819 11:05:03 8 Query SHOW GLOBAL STATUS
150819 11:05:06 8 Query SHOW GLOBAL STATUS
150819 11:05:09 8 Query SHOW GLOBAL STATUS
150819 11:05:12 8 Query SHOW GLOBAL STATUS
150819 11:05:15 8 Query SHOW GLOBAL STATUS
150819 11:05:18 8 Query SHOW GLOBAL STATUS
150819 11:05:21 8 Query SHOW GLOBAL STATUS
150819 11:05:24 8 Query SHOW GLOBAL STATUS
150819 11:05:27 8 Query SHOW GLOBAL STATUS
150819 11:05:30 8 Query SHOW GLOBAL STATUS
150819 11:05:33 8 Query SHOW GLOBAL STATUS
150819 11:05:37 8 Query SHOW GLOBAL STATUS
150819 11:05:40 8 Query SHOW GLOBAL STATUS
150819 11:05:43 8 Query SHOW GLOBAL STATUS
150819 11:05:46 8 Query SHOW GLOBAL STATUS
150819 11:05:49 8 Query SHOW GLOBAL STATUS
150819 11:05:52 8 Query SHOW GLOBAL STATUS
150819 11:05:55 8 Query SHOW GLOBAL STATUS
150819 11:05:58 8 Query SHOW GLOBAL STATUS
150819 11:06:01 8 Query SHOW GLOBAL STATUS
150819 11:06:04 8 Query SHOW GLOBAL STATUS
150819 11:06:07 8 Query SHOW GLOBAL STATUS
150819 11:06:10 8 Query SHOW GLOBAL STATUS
150819 11:06:13 8 Query SHOW GLOBAL STATUS
150819 11:06:16 8 Query SHOW GLOBAL STATUS
150819 11:06:18 12 Query BEGIN
12 Query COMMIT /* implicit, from Xid_log_event */
150819 11:06:19 8 Query SHOW GLOBAL STATUS
Error log after restarting slave :
You need to use --log-bin to make --log-slave-updates work.
You need to use --log-bin to make --binlog-format work.
Plugin 'FEDERATED' is disabled.
2015-08-19 12:11:26 150 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Windows interlocked functions
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU does not support crc32 instructions
InnoDB: Initializing buffer pool, size = 165.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 8556085
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 8556558
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 8556558
Server hostname (bind-address): '127.0.0.1'; port: 3309
- '127.0.0.1' resolves to '127.0.0.1';
Server socket created on IP: '127.0.0.1'.
2015-08-19 12:11:27 150 InnoDB: Warning: table 'mysql/slave_worker_info'
InnoDB: in InnoDB data dictionary has unknown flags 50.
Recovery from master pos 2235 and file USERMAC38-bin.000013.
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 2235
Event Scheduler: Loaded 0 events
E:2-Softwaresmysql-5.6.10-winx64binmysqld.exe: ready for connections.
Version: '5.6.10-log' socket: '' port: 3309 MySQL Community Server (GPL)
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 2235, relay log '.USERMAC38-relay-bin.000011' position: 4
replication mysql-5.6
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports.
Now when executing update or insert in master server, slave server getting stopped after that event. Once restarting slave server and check updates then update/insert is successfully executed in slave through replication setup.
What could be the possible root cause of this issue?
Log of show slave statusG :
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: masteradmin
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: USERMAC38-bin.000007
Read_Master_Log_Pos: 840
Relay_Log_File: USERMAC38-relay-bin.000004
Relay_Log_Pos: 290
Relay_Master_Log_File: USERMAC38-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 840
Relay_Log_Space: 467
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: 0
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: 1
Master_UUID: 63ac2f83-44ac-11e5-bafe-d43d7e3ca358
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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:
Auto_Position: 0
Error log of slave before it got stopped :
'CHANGE MASTER TO executed'. Previous state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000008', master_log_pos= 123, master_bind=''. New state master_host='127.0.0.1', master_port= 3307, master_log_file='USERMAC38-bin.000013 [truncated, 295 bytes total]
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 498
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 498, relay log '.USERMAC38-relay-bin.000001' position: 4
General log of slave before it got stopped :
150819 11:04:44 10 Query stop slave
150819 11:04:45 8 Query SHOW GLOBAL STATUS
150819 11:04:48 8 Query SHOW GLOBAL STATUS
150819 11:04:51 8 Query SHOW GLOBAL STATUS
10 Query CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'masteradmin' MASTER_PASSWORD = <secret> MASTER_PORT = 3307 MASTER_LOG_FILE = 'USERMAC38-bin.000013' MASTER_LOG_POS = 498
150819 11:04:54 8 Query SHOW GLOBAL STATUS
150819 11:04:55 10 Query start slave
11 Connect Out masteradmin@127.0.0.1:3307
150819 11:04:57 8 Query SHOW GLOBAL STATUS
150819 11:05:00 8 Query SHOW GLOBAL STATUS
150819 11:05:02 10 Query show slave status
150819 11:05:03 8 Query SHOW GLOBAL STATUS
150819 11:05:06 8 Query SHOW GLOBAL STATUS
150819 11:05:09 8 Query SHOW GLOBAL STATUS
150819 11:05:12 8 Query SHOW GLOBAL STATUS
150819 11:05:15 8 Query SHOW GLOBAL STATUS
150819 11:05:18 8 Query SHOW GLOBAL STATUS
150819 11:05:21 8 Query SHOW GLOBAL STATUS
150819 11:05:24 8 Query SHOW GLOBAL STATUS
150819 11:05:27 8 Query SHOW GLOBAL STATUS
150819 11:05:30 8 Query SHOW GLOBAL STATUS
150819 11:05:33 8 Query SHOW GLOBAL STATUS
150819 11:05:37 8 Query SHOW GLOBAL STATUS
150819 11:05:40 8 Query SHOW GLOBAL STATUS
150819 11:05:43 8 Query SHOW GLOBAL STATUS
150819 11:05:46 8 Query SHOW GLOBAL STATUS
150819 11:05:49 8 Query SHOW GLOBAL STATUS
150819 11:05:52 8 Query SHOW GLOBAL STATUS
150819 11:05:55 8 Query SHOW GLOBAL STATUS
150819 11:05:58 8 Query SHOW GLOBAL STATUS
150819 11:06:01 8 Query SHOW GLOBAL STATUS
150819 11:06:04 8 Query SHOW GLOBAL STATUS
150819 11:06:07 8 Query SHOW GLOBAL STATUS
150819 11:06:10 8 Query SHOW GLOBAL STATUS
150819 11:06:13 8 Query SHOW GLOBAL STATUS
150819 11:06:16 8 Query SHOW GLOBAL STATUS
150819 11:06:18 12 Query BEGIN
12 Query COMMIT /* implicit, from Xid_log_event */
150819 11:06:19 8 Query SHOW GLOBAL STATUS
Error log after restarting slave :
You need to use --log-bin to make --log-slave-updates work.
You need to use --log-bin to make --binlog-format work.
Plugin 'FEDERATED' is disabled.
2015-08-19 12:11:26 150 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Windows interlocked functions
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU does not support crc32 instructions
InnoDB: Initializing buffer pool, size = 165.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 8556085
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 8556558
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-08-19 12:11:27 1f64 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 8556558
Server hostname (bind-address): '127.0.0.1'; port: 3309
- '127.0.0.1' resolves to '127.0.0.1';
Server socket created on IP: '127.0.0.1'.
2015-08-19 12:11:27 150 InnoDB: Warning: table 'mysql/slave_worker_info'
InnoDB: in InnoDB data dictionary has unknown flags 50.
Recovery from master pos 2235 and file USERMAC38-bin.000013.
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
Slave I/O thread: connected to master 'masteradmin@127.0.0.1:3307',replication started in log 'USERMAC38-bin.000013' at position 2235
Event Scheduler: Loaded 0 events
E:2-Softwaresmysql-5.6.10-winx64binmysqld.exe: ready for connections.
Version: '5.6.10-log' socket: '' port: 3309 MySQL Community Server (GPL)
Slave SQL thread initialized, starting replication in log 'USERMAC38-bin.000013' at position 2235, relay log '.USERMAC38-relay-bin.000011' position: 4
replication mysql-5.6
replication mysql-5.6
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
edited Aug 19 '15 at 6:44
Harshit patel
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
asked Aug 18 '15 at 11:28
Harshit patelHarshit patel
115
115
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
That shows yes/yes, that makes you think it's stopped?
– winmutt
Aug 18 '15 at 14:38
This status is after restarting slave. Everything seems OK after restart. Now as and when new event is there, it will execute it successfully and then slave server will get stopped again.
– Harshit patel
Aug 19 '15 at 4:45
Any errors? Include the log and status after the execution.
– Tan Hong Tat
Aug 19 '15 at 5:03
Are the master and slave reading the same data directory?
– Tan Hong Tat
Aug 19 '15 at 7:41
Data directory is different for both.
– Harshit patel
Aug 19 '15 at 8:23
|
show 4 more comments
1
That shows yes/yes, that makes you think it's stopped?
– winmutt
Aug 18 '15 at 14:38
This status is after restarting slave. Everything seems OK after restart. Now as and when new event is there, it will execute it successfully and then slave server will get stopped again.
– Harshit patel
Aug 19 '15 at 4:45
Any errors? Include the log and status after the execution.
– Tan Hong Tat
Aug 19 '15 at 5:03
Are the master and slave reading the same data directory?
– Tan Hong Tat
Aug 19 '15 at 7:41
Data directory is different for both.
– Harshit patel
Aug 19 '15 at 8:23
1
1
That shows yes/yes, that makes you think it's stopped?
– winmutt
Aug 18 '15 at 14:38
That shows yes/yes, that makes you think it's stopped?
– winmutt
Aug 18 '15 at 14:38
This status is after restarting slave. Everything seems OK after restart. Now as and when new event is there, it will execute it successfully and then slave server will get stopped again.
– Harshit patel
Aug 19 '15 at 4:45
This status is after restarting slave. Everything seems OK after restart. Now as and when new event is there, it will execute it successfully and then slave server will get stopped again.
– Harshit patel
Aug 19 '15 at 4:45
Any errors? Include the log and status after the execution.
– Tan Hong Tat
Aug 19 '15 at 5:03
Any errors? Include the log and status after the execution.
– Tan Hong Tat
Aug 19 '15 at 5:03
Are the master and slave reading the same data directory?
– Tan Hong Tat
Aug 19 '15 at 7:41
Are the master and slave reading the same data directory?
– Tan Hong Tat
Aug 19 '15 at 7:41
Data directory is different for both.
– Harshit patel
Aug 19 '15 at 8:23
Data directory is different for both.
– Harshit patel
Aug 19 '15 at 8:23
|
show 4 more comments
1 Answer
1
active
oldest
votes
0
Found issue with different MySQL server version. Master server was running on MySQL version 5.7.8 and slave MySQL version was 5.6.10.
Synced both Master and Slave versions to 5.6.26. Replication is working fine without slave server being stopped.
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f111406%2fmysql-slave-server-getting-stopped-after-each-replication-request-from-master%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Found issue with different MySQL server version. Master server was running on MySQL version 5.7.8 and slave MySQL version was 5.6.10.
Synced both Master and Slave versions to 5.6.26. Replication is working fine without slave server being stopped.
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
add a comment |
0
Found issue with different MySQL server version. Master server was running on MySQL version 5.7.8 and slave MySQL version was 5.6.10.
Synced both Master and Slave versions to 5.6.26. Replication is working fine without slave server being stopped.
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
add a comment |
0
0
0
Found issue with different MySQL server version. Master server was running on MySQL version 5.7.8 and slave MySQL version was 5.6.10.
Synced both Master and Slave versions to 5.6.26. Replication is working fine without slave server being stopped.
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
Found issue with different MySQL server version. Master server was running on MySQL version 5.7.8 and slave MySQL version was 5.6.10.
Synced both Master and Slave versions to 5.6.26. Replication is working fine without slave server being stopped.
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
answered Aug 19 '15 at 10:28
Harshit patelHarshit patel
115
115
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
add a comment |
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
Your master version should always be less than, or the same as, the slave. Otherwise the master can create binary log message the slave doesn't understand.
– danblack
Sep 28 '18 at 3:05
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f111406%2fmysql-slave-server-getting-stopped-after-each-replication-request-from-master%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
That shows yes/yes, that makes you think it's stopped?
– winmutt
Aug 18 '15 at 14:38
This status is after restarting slave. Everything seems OK after restart. Now as and when new event is there, it will execute it successfully and then slave server will get stopped again.
– Harshit patel
Aug 19 '15 at 4:45
Any errors? Include the log and status after the execution.
– Tan Hong Tat
Aug 19 '15 at 5:03
Are the master and slave reading the same data directory?
– Tan Hong Tat
Aug 19 '15 at 7:41
Data directory is different for both.
– Harshit patel
Aug 19 '15 at 8:23