MySQL Forums
Forum List  »  Replication

Replication not starting after crash on master
Posted by: PER JØ'RGENSEN
Date: April 25, 2015 06:30AM

Hello Mysql Forum.
Normally I've been using MySQL for several years now based upon websites, so I got a little knowhow about MySQL - but not a prof.

I'm trying to setup a Replication enviorment for testusage.
The replication I wouæd like to run is Many Master to One Slave.
Each master have the same DB - but a unique table for each master. This table should be replicated to the slave.
I'm using SSL to this setup.
I've got the replication running and everythings works fine.
The usage is a Database on a vessel, which should be replicated on a Server Onshore.


But then I simulate a power fall out/ or disconnects the network on the masterDB - then the Slave ain't reconnecting when powering up the MasterDB.
My slave status says its Connected - but no transactions are commited to the slave. As soon as I restart the Slave - it'll reconnect and replicate the missing data - but that should be done automaticly.
I've have in the test enviorment a Raspberry which insert data each minute in the master DB. But needs the replications to handle any power/network failout on the masterDB which is sometimes not available caurse network is unreachable.

Here's a info from slave status:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 188.179.22.133
Master_User: mysql_replic
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 5513
Relay_Log_File: mysqld-relay-bin-9352585northcontender.000051
Relay_Log_Pos: 1839
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: XXXXXXXX
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: 5513
Relay_Log_Space: 2109
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: C:/Insatech/SSL/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: C:/Insatech/SSL/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: C:/Insatech/SSL/client-key.pem
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: 20
Master_UUID: dccfbad1-d601-11e4-bbb2-000c293a93f1
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 more up
dates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: dccfbad1-d601-11e4-bbb2-000c293a93f1:5158-5161
Executed_Gtid_Set: 39329855-ea88-11e4-927f-002618a9163e:1-9,
dccfbad1-d601-11e4-bbb2-000c293a93f1:4035-5161
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 9352585northcontender
1 row in set (0.00 sec)

ERROR:
No query specified

And my Errorlog shows this:
2015-04-25T11:58:29.057600Z 2 [Note] Slave SQL thread for channel '9352585northcontender' initialized, starting replication in log 'mysql-bin.000014' at position 3993, relay log '.\mysqld-relay-bin-9352585northcontender.000050' position: 4
2015-04-25T11:58:29.082600Z 2 [Note] Coordinator and workers enter synchronization procedure when scheduling event relay-log: .\mysqld-relay-bin-9352585northcontender.000051 pos: 154
2015-04-25T11:58:29.087600Z 2 [Note] Coordinator synchronized with Workers, waited entries: 0, cant_sync: 0

my my.cnf from my SLave:
max_connections=50
query_cache_size=0
table_open_cache=2000
tmp_table_size=100M
thread_cache_size=10
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=4M
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_thread_concurrency=9
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_lock_wait_timeout=20
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=1000M
max_connect_errors=100
open_files_limit=4161
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400


# ----------------------------------------------------------------------
# Replication Variables
# ----------------------------------------------------------------------
server-id=1

gtid_mode=ON
enforce_gtid_consistency = ON
enforce-gtid-consistency =TRUE
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery = 1
relay-log-purge=ON
relay-log=mysqld-relay-bin
report-host=hostname
report-user=mysql_replic
sync_master_info=0
sync_relay_log=0
sync_relay_log_info=0
slave-net-timeout=15
slave_transaction_retries=25
slave-parallel-workers=256

# Binary Logging.
# ----------------------------------------------------------------------
log-bin=mysql-bin
binlog_do_db=teamtankers
replicate-do-db=teamtankers
binlog-checksum =CRC32
binlog_row_event_max_size=16K
master-retry-count=0


Can anyone help with this problem

Options: ReplyQuote


Subject
Views
Written By
Posted
Replication not starting after crash on master
1669
April 25, 2015 06:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.