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 status\G :
*************************** 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 '[email protected]: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 [email protected]: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 '[email protected]:3307',replication started in log 'USERMAC38-bin.000013' at position 2235
Event Scheduler: Loaded 0 events
E:\2-Softwares\mysql-5.6.10-winx64\bin\mysqld.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
