#master [mysqld] basedir = /export/umesh/server/binaries/mysql-advanced-5.7.12 datadir = /export/umesh/server/binaries/mysql-advanced-5.7.12/master tmpdir = /export/umesh/server/binaries/mysql-advanced-5.7.12/tmp socket = /export/umesh/server/binaries/mysql-advanced-5.7.12/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.7.12/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.7.12/log/master_query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/mysql-advanced-5.7.12/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.7.12/log/master.log pid_file = /export/umesh/server/binaries/mysql-advanced-5.7.12/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.7.12 datadir = /export/umesh/server/binaries/mysql-advanced-5.7.12/slave tmpdir = /export/umesh/server/binaries/mysql-advanced-5.7.12/tmp socket = /export/umesh/server/binaries/mysql-advanced-5.7.12/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.7.12/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.7.12/log/relaylog max_relay_log_size = 100M slave_compressed_protocol = OFF general_log = OFF general_log_file = /export/umesh/server/binaries/mysql-advanced-5.7.12/log/slave_query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/mysql-advanced-5.7.12/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.7.12/log/slave.log pid_file = /export/umesh/server/binaries/mysql-advanced-5.7.12/run/slave.pid ## slave-parallel-type=LOGICAL_CLOCK slave_preserve_commit_order=1 [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 // 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 bin/mysqld --defaults-file=./m80103.cnf --initialize-insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.12 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.12/master -v bin/mysqld --defaults-file=./s80103.cnf --initialize-insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.12 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.12/slave -v bin/mysqld --defaults-file=./m80103.cnf & bin/mysqld --defaults-file=./s80103.cnf & [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.7.12/run/master.sock --max-requests=5000000 run [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: ps aux|grep mysqld umshastr 26568 11.8 1.7 45234520 4496172 pts/2 Sl 08:57 3:07 bin/mysqld --defaults-file=./m80103.cnf umshastr 26613 11.6 1.6 45302312 4472336 pts/2 Sl 08:57 3:04 bin/mysqld --defaults-file=./s80103.cnf umshastr 28628 0.0 0.0 112632 972 pts/4 S+ 09:23 0:00 grep --color=auto mysqld [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: kill -9 26613 [2]+ Killed bin/mysqld --defaults-file=./s80103.cnf [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: bin/mysqld --defaults-file=./s80103.cnf --skip-slave-start & [2] 29009 -- Connecte slave and start slave [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: bin/mysql -uroot -S run/slave.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.12-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, 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 processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 8 | Waiting on empty queue | NULL | | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ 2 rows in set (0.00 sec) 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.000015 Read_Master_Log_Pos: 59052119 Relay_Log_File: relaylog.000038 Relay_Log_Pos: 88436952 Relay_Master_Log_File: master-bin.000014 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: 88436737 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: 8d05ec67-10fb-11e6-8605-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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository mysql> -- extract from Slave's error log 2016-05-03T07:25:49.462720Z 0 [Note] - '::' resolves to '::'; 2016-05-03T07:25:49.462741Z 0 [Note] Server socket created on IP: '::'. 2016-05-03T07:25:49.476421Z 0 [Note] Slave: MTS group recovery relay log info based on Worker-Id 0, group_relay_log_name /export/umesh/server/binaries/mysql-advanced-5.7.12/log/relaylog.000038, group_relay_log_pos 88584582 group_master_log_name master-bin.000014, group_master_log_pos 88584367 2016-05-03T07:25:49.476484Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437255. 2016-05-03T07:25:49.476494Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437773. 2016-05-03T07:25:49.476502Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88438291. 2016-05-03T07:25:49.476508Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88438809. 2016-05-03T07:25:49.476515Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88439327. . . 2016-05-03T07:25:49.478337Z 0 [Note] Slave: MTS group recovery relay log info based on Worker-Id 1, group_relay_log_name /export/umesh/server/binaries/mysql-advanced-5.7.12/log/relaylog.000038, group_relay_log_pos 88586136 group_master_log_name master-bin.000014, group_master_log_pos 88585921 2016-05-03T07:25:49.478372Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437255. 2016-05-03T07:25:49.478388Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437773. 2016-05-03T07:25:49.478394Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88438291. 2016-05-03T07:25:49.478400Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88438809. . . 2016-05-03T07:25:49.480175Z 0 [Note] Slave: MTS group recovery relay log info based on Worker-Id 2, group_relay_log_name /export/umesh/server/binaries/mysql-advanced-5.7.12/log/relaylog.000038, group_relay_log_pos 88586654 group_master_log_name master-bin.000014, group_master_log_pos 88586439 2016-05-03T07:25:49.480202Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437255. 2016-05-03T07:25:49.480209Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437773. . . 2016-05-03T07:25:49.512084Z 0 [Note] Slave: MTS group recovery relay log info based on Worker-Id 20, group_relay_log_name /export/umesh/server/binaries/mysql-advanced-5.7.12/log/relaylog.000038, group_relay_log_pos 88580956 group_master_log_name master-bin.000014, group_master_log_pos 88580741 2016-05-03T07:25:49.512110Z 0 [Note] Slave: MTS group recovery relay log info group_master_log_name master-bin.000014, event_master_log_pos 88437255. . 2016-05-03T07:25:49.513917Z 0 [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 2016-05-03T07:25:49.514172Z 0 [ERROR] Failed to initialize the master info structure 2016-05-03T07:25:49.514182Z 0 [ERROR] Failed to create or recover replication info repositories. 2016-05-03T07:25:49.514185Z 0 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. 2016-05-03T07:25:49.519956Z 0 [Note] Event Scheduler: Loaded 0 events 2016-05-03T07:25:49.520162Z 0 [Note] bin/mysqld: ready for connections. Version: '5.7.12-enterprise-commercial-advanced-log' socket: '/export/umesh/server/binaries/mysql-advanced-5.7.12/run/slave.sock' port: 15001 MySQL Enterprise Server - Advanced Edition (Commercial) 2016-05-03T07:25:49.520184Z 1 [Note] Event Scheduler: scheduler thread started with id 1 2016-05-03T07:26:17.673375Z 3 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872