// conf used [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: cat m77496.cnf s77496.cnf #master [mysqld] basedir = /export/umesh/server/binaries/mysql-advanced-5.6.27 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.27/master tmpdir = /export/umesh/server/binaries/mysql-advanced-5.6.27/tmp socket = /export/umesh/server/binaries/mysql-advanced-5.6.27/run/master.sock back_log = 1000 server_id = 1 port=15000 read_only = 0 event_scheduler = ON log-bin = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/master-bin binlog_format = STATEMENT binlog_row_image = minimal sync_binlog = 1 expire_logs_days = 7 log_slave_updates max_binlog_size = 100M general_log = OFF general_log_file = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/master_query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/master_slowlog long_query_time = 3 log_queries_not_using_indexes = 0 min_examined_row_limit = 10000 character_set_server = utf8 concurrent_insert = AUTO group_concat_max_len = 1048576 init_connect = 'SET NAMES utf8' key_buffer_size = 64M max_allowed_packet = 16M max_connections = 7000 max_connect_errors = 15000 max_heap_table_size = 64M myisam_repair_threads = 1 myisam_sort_buffer_size = 64M open_files_limit = 20000 optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off' query_cache_size = 0 query_cache_type = 0 read_buffer_size = 128k sort_buffer_size = 1M sql_mode = NO_ENGINE_SUBSTITUTION table_definition_cache = 3000 table_open_cache = 3000 thread_cache_size = 3500 tmp_table_size = 64M transaction-isolation = REPEATABLE-READ wait_timeout = 300 myisam_recover_options = FORCE,BACKUP host_cache_size = 2000 gtid_mode = OFF enforce_gtid_consistency = 0 default_storage_engine = InnoDB default_tmp_storage_engine = InnoDB innodb_adaptive_flushing = 1 innodb_adaptive_hash_index = 0 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_size = 32G innodb_buffer_pool_instances = 28 innodb_change_buffering = INSERTS innodb_file_per_table innodb_file_format = Barracuda innodb_flush_neighbors = 1 innodb_doublewrite = 1 innodb_flush_log_at_trx_commit = 2 innodb_io_capacity = 600 innodb_io_capacity_max = 1200 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 256M innodb_log_files_in_group = 2 innodb_log_file_size = 1024M innodb_max_dirty_pages_pct = 75 innodb_open_files = 3000 innodb_purge_threads = 1 innodb_purge_batch_size = 300 innodb_read_io_threads = 12 innodb_stats_on_metadata = 0 innodb_thread_concurrency = 32 innodb_write_io_threads = 12 innodb_support_xa = 1 performance_schema = 1 performance_schema_consumer_events_statements_current = ON performance_schema_consumer_events_statements_history_long = OFF performance_schema_consumer_events_waits_current = ON performance_schema_consumer_events_waits_history = ON performance_schema_consumer_events_waits_history_long = ON log-error = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/master.log pid_file = /export/umesh/server/binaries/mysql-advanced-5.6.27/run/master.pid [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default_character_set = utf8 [myisamchk] key_buffer_size = 768M sort_buffer_size = 256M read_buffer_size = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout # Slave [mysqld] basedir = /export/umesh/server/binaries/mysql-advanced-5.6.27 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.27/slave tmpdir = /export/umesh/server/binaries/mysql-advanced-5.6.27/tmp socket = /export/umesh/server/binaries/mysql-advanced-5.6.27/run/slave.sock back_log = 1000 server_id = 999999999 port=15001 read_only = 0 event_scheduler = ON log-bin = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/slave-bin binlog_format = STATEMENT binlog_row_image = minimal sync_binlog = 1 expire_logs_days = 7 log_slave_updates max_binlog_size = 100M relay_log = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/relaylog max_relay_log_size = 100M slave_compressed_protocol = OFF general_log = OFF general_log_file = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/slave_query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/slave_slowlog long_query_time = 3 log_queries_not_using_indexes = 0 min_examined_row_limit = 10000 character_set_server = utf8 concurrent_insert = AUTO group_concat_max_len = 1048576 init_connect = 'SET NAMES utf8' key_buffer_size = 64M max_allowed_packet = 16M max_connections = 7000 max_connect_errors = 15000 max_heap_table_size = 64M myisam_repair_threads = 1 myisam_sort_buffer_size = 64M open_files_limit = 20000 report_host = slave-5-6-27 report_port = 15001 optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off' query_cache_size = 0 query_cache_type = 0 read_buffer_size = 128k sort_buffer_size = 1M sql_mode = NO_ENGINE_SUBSTITUTION table_definition_cache = 3000 table_open_cache = 3000 thread_cache_size = 3500 tmp_table_size = 64M transaction-isolation = REPEATABLE-READ wait_timeout = 300 myisam_recover_options = FORCE,BACKUP master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = 1 sync_master_info = 10000 sync_relay_log_info = 10000 host_cache_size = 2000 gtid_mode = OFF enforce_gtid_consistency = 0 slave_parallel_workers = 25 default_storage_engine = InnoDB default_tmp_storage_engine = InnoDB innodb_adaptive_flushing = 1 innodb_adaptive_hash_index = 0 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_size = 32G innodb_buffer_pool_instances = 28 innodb_change_buffering = INSERTS innodb_file_per_table innodb_file_format = Barracuda innodb_flush_neighbors = 1 innodb_doublewrite = 1 innodb_flush_log_at_trx_commit = 2 innodb_io_capacity = 600 innodb_io_capacity_max = 1200 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 256M innodb_log_files_in_group = 2 innodb_log_file_size = 1024M innodb_max_dirty_pages_pct = 75 innodb_open_files = 3000 innodb_purge_threads = 1 innodb_purge_batch_size = 300 innodb_read_io_threads = 12 innodb_stats_on_metadata = 0 innodb_thread_concurrency = 32 innodb_write_io_threads = 12 innodb_support_xa = 1 performance_schema = 1 performance_schema_consumer_events_statements_current = ON performance_schema_consumer_events_statements_history_long = OFF performance_schema_consumer_events_waits_current = ON performance_schema_consumer_events_waits_history = ON performance_schema_consumer_events_waits_history_long = ON log-error = /export/umesh/server/binaries/mysql-advanced-5.6.27/log/slave.log pid_file = /export/umesh/server/binaries/mysql-advanced-5.6.27/run/slave.pid [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default_character_set = utf8 [myisamchk] key_buffer_size = 768M sort_buffer_size = 256M read_buffer_size = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout // Build used [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: cat docs/INFO_SRC commit: 13d05b5e381b81c49011b1646dc512a332f39465 date: 2015-07-23 10:59:54 +0530 build-date: 2015-07-23 08:13:17 +0200 short: 13d05b5 branch: mysql-5.6 MySQL source 5.6.27 // Setup Master/Slave replication, invoke sysbench(prepare, run - make sure to create db1 schema), kill slave with -9(needed 2 attempts in my tests).. and restarted slave [umshastr@hod03]~/bugs/sysbench: bin/sysbench --db-driver=mysql --num-threads=25 --test=sysbench/tests/db/inserts.lua --oltp-reconnect-mode=random --mysql-table-engine=innodb --mysql-db=db1 --oltp-table-size=5000000 --mysql-user=root --mysql-socket=/export/umesh/server/binaries/mysql-advanced-5.6.27/run/master.sock --max-requests=500000 run InnoDB: Transaction 1332238 was in the XA prepared state. InnoDB: Transaction 1332238 was in the XA prepared state. InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 0 row operations to undo InnoDB: Trx id counter is 1332736 2015-07-27 13:22:08 22709 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 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: Last MySQL binlog file position 0 83821260, file name slave-bin.000007 2015-07-27 13:22:11 22709 [Note] InnoDB: 128 rollback segment(s) are active. InnoDB: Starting in background the rollback of uncommitted transactions 2015-07-27 13:22:11 7f0c1bca0700 InnoDB: Rollback of non-prepared transactions completed 2015-07-27 13:22:11 22709 [Note] InnoDB: Waiting for purge to start 2015-07-27 13:22:11 22709 [Note] InnoDB: 5.6.27 started; log sequence number 838194738 2015-07-27 13:22:11 22709 [Note] Recovering after a crash using /export/umesh/server/binaries/mysql-advanced-5.6.27/log/slave-bin 2015-07-27 13:22:11 22709 [Note] Starting crash recovery... 2015-07-27 13:22:11 7f1528111740 InnoDB: Starting recovery for XA transactions... 2015-07-27 13:22:11 7f1528111740 InnoDB: Transaction 1332238 in prepared state after recovery 2015-07-27 13:22:11 7f1528111740 InnoDB: Transaction contains changes to 2 rows 2015-07-27 13:22:11 7f1528111740 InnoDB: 1 transactions in prepared state after recovery 2015-07-27 13:22:11 22709 [Note] Found 1 prepared transaction(s) in InnoDB 2015-07-27 13:22:11 22709 [Note] Crash recovery finished. 2015-07-27 13:22:11 22709 [Note] RSA private key file not found: /export/umesh/server/binaries/mysql-advanced-5.6.27/slave//private_key.pem. Some authentication plugins will not work. 2015-07-27 13:22:11 22709 [Note] RSA public key file not found: /export/umesh/server/binaries/mysql-advanced-5.6.27/slave//public_key.pem. Some authentication plugins will not work. 2015-07-27 13:22:11 22709 [Note] Server hostname (bind-address): '*'; port: 15001 2015-07-27 13:22:11 22709 [Note] IPv6 is available. 2015-07-27 13:22:11 22709 [Note] - '::' resolves to '::'; 2015-07-27 13:22:11 22709 [Note] Server socket created on IP: '::'. 2015-07-27 13:22:11 22709 [Note] Slave: MTS group recovery relay log info based on Worker-Id 24, group_relay_log_name /export/umesh/server/binaries/mysql-advanced-5.6.27/log/relaylog.000012, group_relay_log_pos 69157188 group_master_log_name master-bin.000008, group_master_log_pos 69157024 2015-07-27 13:22:11 22709 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000008, event_master_log_pos 69156572. 2015-07-27 13:22:11 22709 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000008, event_master_log_pos 69157024. 2015-07-27 13:22:11 22709 [ERROR] --relay-log-recovery cannot be executed when the slave was stopped with an error or killed in MTS mode; consider using RESET SLAVE or restart the server with --relay-log-recovery = 0 followed by START SLAVE UNTIL SQL_AFTER_MTS_GAPS 2015-07-27 13:22:11 22709 [ERROR] Failed to initialize the master info structure 2015-07-27 13:22:11 22709 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. 2015-07-27 13:22:11 22709 [Note] Event Scheduler: Loaded 0 events 2015-07-27 13:22:11 22709 [Note] /export/umesh/server/binaries/mysql-advanced-5.6.27/bin/mysqld: ready for connections. Version: '5.6.27-enterprise-commercial-advanced-log' socket: '/export/umesh/server/binaries/mysql-advanced-5.6.27/run/slave.sock' port: 15001 MySQL Enterprise Server - Advanced Edition (Commercial) 2015-07-27 13:22:11 22709 [Note] Event Scheduler: scheduler thread started with id 1 2015-07-27 13:28:55 22709 [ERROR] Slave SQL: Slave failed to initialize relay log info structure from the repository, Error_code: 1872 ##### [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: bin/mysql -uroot -p -S /export/umesh/server/binaries/mysql-advanced-5.6.27/run/slave.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000013 Read_Master_Log_Pos: 35147107 Relay_Log_File: relaylog.000012 Relay_Log_Pos: 69156284 Relay_Master_Log_File: master-bin.000008 Slave_IO_Running: No Slave_SQL_Running: No 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: 69156120 Relay_Log_Space: 0 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: NULL 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: 0 Master_UUID: da8aba94-344f-11e5-a2a4-0010e05f3e06 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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 1 row in set (0.00 sec) mysql> mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository mysql>