Bumped by Community user
    Bumped by Community user
    Bumped by Community user
7 noticed that the db must have been shutdown at some point
source | link

Edit5:

Something else i noticed today when i logged into the DB to check the replication status: It seems like the MySQL was stopped at some point (or my session timed out somehow at least) as i had left a SSH session open where i was already logged in and when i executed a show slave status \G it told me that the server had "gone away" aka was stopped/crashed but also apparently restarted as it managed to reconnect. MySQL Error log doesn't show any crashes or restarts though, which is interesting.

Edit5:

Something else i noticed today when i logged into the DB to check the replication status: It seems like the MySQL was stopped at some point (or my session timed out somehow at least) as i had left a SSH session open where i was already logged in and when i executed a show slave status \G it told me that the server had "gone away" aka was stopped/crashed but also apparently restarted as it managed to reconnect. MySQL Error log doesn't show any crashes or restarts though, which is interesting.

6 Added todays binlog position details where the replication got stuck
source | link

Edit4:

Todays entry where the system's stuck looks pretty much the same, it is just after an update to the session table, however (maybe by pure accident) i stumbled upon something:

#170408  2:35:05 server id 1  end_log_pos 815569300     Query   thread_id=38771 exec_time=10    error_code=0
SET TIMESTAMP=1491611705/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at <some number>
*some more # at <some number> entries later*
#170408  2:35:05 server id 1  end_log_pos 815569365     Table_map: 'application_storage'.'data_bin' mapped to number 296
#170408  2:35:05 server id 1  end_log_pos 815570402     Write_rows: table id 296
#170408  2:35:05 server id 1  end_log_pos 815571439     Write_rows: table id 296
#170408  2:35:05 server id 1  end_log_pos 815572476     Write_rows: table id 296

Now the data_bin table is a huge ass MyIsam table, around 20-23GB in size (huge compared to all the other tables making up 4/5 of the DBs overall size)

Edit4:

Todays entry where the system's stuck looks pretty much the same, it is just after an update to the session table, however (maybe by pure accident) i stumbled upon something:

#170408  2:35:05 server id 1  end_log_pos 815569300     Query   thread_id=38771 exec_time=10    error_code=0
SET TIMESTAMP=1491611705/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at <some number>
*some more # at <some number> entries later*
#170408  2:35:05 server id 1  end_log_pos 815569365     Table_map: 'application_storage'.'data_bin' mapped to number 296
#170408  2:35:05 server id 1  end_log_pos 815570402     Write_rows: table id 296
#170408  2:35:05 server id 1  end_log_pos 815571439     Write_rows: table id 296
#170408  2:35:05 server id 1  end_log_pos 815572476     Write_rows: table id 296

Now the data_bin table is a huge ass MyIsam table, around 20-23GB in size (huge compared to all the other tables making up 4/5 of the DBs overall size)

5 added create table for table that replication got stuck on once
source | link

Edit3:

Here's the create table for one of the tables where the replication seems to have been stuck at

   Table: session
Create Table: CREATE TABLE 'session' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'session_id' varchar(256) DEFAULT NULL,
'user_id' int(11) DEFAULT NULL,
'current_page' int(11) DEFAULT NULL,
'last_reload' int(11) DEFAULT NULL,
'ip_address' varchar(45) DEFAULT NULL,
's_nbHostsUp' int(11) DEFAULT NULL,
's_nbHostsDown' int(11) DEFAULT NULL,
's_nbHostsUnreachable' int(11) DEFAULT NULL,
's_nbHostsPending' int(11) DEFAULT NULL,
's_nbServicesOk' int(11) DEFAULT NULL,
's_nbServicesWarning' int(11) DEFAULT NULL,
's_nbServicesCritical' int(11) DEFAULT NULL,
's_nbServicesPending' int(11) DEFAULT NULL,
's_nbServicesUnknown' int(11) DEFAULT NULL,
'update_acl' enum('0','1') DEFAULT '0',
 PRIMARY KEY ('id'),
 KEY 'session_id' ('session_id'(255)),
 KEY 'user_id' ('user_id')
 ) ENGINE=InnoDB AUTO_INCREMENT=13493 DEFAULT CHARSET=utf8

Also i have to add - i wrote below the engine is MyIsam - it is indeed actually a mix of InnoDB and MyIsam with the MyIsam tables making out the part of the DB that gets changed the most including the biggest table.

Edit3:

Here's the create table for one of the tables where the replication seems to have been stuck at

   Table: session
Create Table: CREATE TABLE 'session' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'session_id' varchar(256) DEFAULT NULL,
'user_id' int(11) DEFAULT NULL,
'current_page' int(11) DEFAULT NULL,
'last_reload' int(11) DEFAULT NULL,
'ip_address' varchar(45) DEFAULT NULL,
's_nbHostsUp' int(11) DEFAULT NULL,
's_nbHostsDown' int(11) DEFAULT NULL,
's_nbHostsUnreachable' int(11) DEFAULT NULL,
's_nbHostsPending' int(11) DEFAULT NULL,
's_nbServicesOk' int(11) DEFAULT NULL,
's_nbServicesWarning' int(11) DEFAULT NULL,
's_nbServicesCritical' int(11) DEFAULT NULL,
's_nbServicesPending' int(11) DEFAULT NULL,
's_nbServicesUnknown' int(11) DEFAULT NULL,
'update_acl' enum('0','1') DEFAULT '0',
 PRIMARY KEY ('id'),
 KEY 'session_id' ('session_id'(255)),
 KEY 'user_id' ('user_id')
 ) ENGINE=InnoDB AUTO_INCREMENT=13493 DEFAULT CHARSET=utf8

Also i have to add - i wrote below the engine is MyIsam - it is indeed actually a mix of InnoDB and MyIsam with the MyIsam tables making out the part of the DB that gets changed the most including the biggest table.

4 Added some more details
source | link
3 Added binary log entry that slave seems to be stuck at
source | link
2 added 2645 characters in body
source | link
    Post Migrated Here from stackoverflow.com
1
source | link