If you restart or shutdown a mysql slave with MTS enabled, the slave can get get broken. Please take a look at the report #1420606 also. The version 5.6.24-72.2 was reported to fix this bug, but the problem still exists, at least in our case. Since we know of this behavior we always do a "STOP SLAVE;" before we shutdown or restart the server.
Because of privacy we cannot offer the relay logs. In addition we are running the row based replication. I don't know, if the logs would even help.
Configuration on the master:
[mysqld]
port = 6033
user = mysql
basedir = /usr/local
datadir = /zdata/db
slave_load_tmpdir = /zdata/tmp
tmpdir = /ramdisk
bind-address = 0.0.0.0
socket = /var/run/mysql/mysqld.sock
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
slow_query_log_timestamp_always = 1
long_query_time = 4
log_slow_verbosity = full
log_queries_not_using_indexes = 1
log-slow-slave-statements = 1
min_examined_row_limit = 20000
server-id = 2
innodb_data_home_dir = /zdata/db
innodb_log_group_home_dir = /ziblog
innodb_log_buffer_size = 64M
innodb_data_file_path = ibdata1:512M:autoextend
innodb_buffer_pool_instances = 8
table_open_cache_instances = 8
innodb_buffer_pool_size = 72G
innodb_old_blocks_time = 1000
innodb_doublewrite = 0
innodb_checksum_algorithm = NONE
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_status_file = 1
innodb_file_per_table = 1
innodb_purge_threads = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 10000
innodb_io_capacity_max = 100000
innodb_lru_scan_depth = 2048
innodb_online_alter_log_max_size = 1G
thread_handling = pool-of-threads
slave-parallel-workers = 5
skip-slave-start = 1
log_slave_updates = 1
sync_binlog = 0
max_connections = 10000
log_bin = /zdata/binlog/mysql-bin.log
max_binlog_size = 512M
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 5
relay-log = master-relay-bin
relay_log_info_repository = TABLE
relay_log_recovery = 1
master_info_repository = TABLE
slave_net_timeout = 60
read_only = 0
table_open_cache = 4096
table_definition_cache = 4096
query_cache_size = 0
query_cache_type = 0
key_buffer_size = 256M
join_buffer_size = 256K
sort_buffer_size = 256K
max_heap_table_size = 1G
tmp_table_size = 1G
max_allowed_packet = 64M
slave-pending-jobs-size-max = 64M
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
log_warnings = 1
skip-host-cache
skip-name-resolve
performance_schema = 0
slave_compressed_protocol = 0
The slave:
[mysqld]
port = 6033
user = mysql
basedir = /usr/local
datadir = /zdata/db
slave_load_tmpdir = /zdata/db
tmpdir = /zdata/db
bind-address = 0.0.0.0
socket = /var/run/mysql/mysqld.sock
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
slow_query_log_timestamp_always = 1
long_query_time = 4
log_slow_verbosity = full
log_queries_not_using_indexes = 1
log-slow-slave-statements = 1
min_examined_row_limit = 8000
server-id = 4
innodb_data_home_dir = /zdata/db
innodb_log_group_home_dir = /ziblog
innodb_log_buffer_size = 64M
innodb_data_file_path = ibdata1:512M:autoextend
innodb_buffer_pool_instances = 4
table_open_cache_instances = 4
innodb_buffer_pool_size = 12G
innodb_old_blocks_time = 1000
innodb_doublewrite = 0
innodb_checksum_algorithm = NONE
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_status_file = 1
innodb_file_per_table = 1
innodb_purge_threads = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 20000
innodb_log_block_size = 4096
thread_handling = pool-of-threads
innodb_online_alter_log_max_size = 1G
slave-parallel-workers = 4
skip-slave-start = 1
log_slave_updates = 0
sync_binlog = 0
max_connections = 1000
log_bin = /zdata/binlog/mysql-bin.log
max_binlog_size = 512M
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 3
relay-log = /zdata/relaylog/slave-relay-bin
relay_log_info_repository = TABLE
relay_log_recovery = 1
master_info_repository = TABLE
slave_net_timeout = 60
read_only = 1
table_open_cache = 4096
table_definition_cache = 4096
query_cache_size = 0
query_cache_type = 0
key_buffer_size = 256M
join_buffer_size = 256K
sort_buffer_size = 256K
max_heap_table_size = 256M
tmp_table_size = 256M
max_allowed_packet = 64M
slave-pending-jobs-size-max = 64M
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
log_warnings = 1
skip-host-cache
skip-name-resolve
performance_schema = 0
slave_compressed_protocol = 1
Please, provide some more details about the way slave is "broken". Do you get errors when slave starts? Can you share any error messages from the error log or the output of
show slave status\G
that demonstrates the problem?