// Setup replication between 5.5.24(master)->5.7.9(slave) - master - conf and build used [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.24: cat docs/INFO_SRC commit: 97543660999308893335d81b92307c389e6821a5 date: 2015-03-25 17:22:45 +0100 build-date: 2015-03-25 17:26:48 +0100 short: 9754366 branch: mysql-5.6.24-release MySQL source 5.6.24 [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.24: cat my.cnf [mysqld] basedir = /export/umesh/server/binaries/mysql-advanced-5.6.24 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.24/master tmpdir = /export/umesh/server/binaries/mysql-advanced-5.6.24/tmp socket = /export/umesh/server/binaries/mysql-advanced-5.6.24/run/mysql.sock #bind_address = 0.0.0.0 port = 15000 back_log = 2000 server_id = 186068095 read_only = 0 # This server can be written to event_scheduler = ON log-bin = /export/umesh/server/binaries/mysql-advanced-5.6.24/log/binlog binlog_format = STATEMENT binlog_row_image = full sync_binlog = 1 expire_logs_days = 7 log_slave_updates max_binlog_size = 100M relay_log = /export/umesh/server/binaries/mysql-advanced-5.6.24/log/relaylog max_relay_log_size = 100M slave_compressed_protocol = OFF log_error = /export/umesh/server/binaries/mysql-advanced-5.6.24/log/log.err general_log = OFF general_log_file = /export/umesh/server/binaries/mysql-advanced-5.6.24/log/query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/mysql-advanced-5.6.24/log/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 = 768M max_allowed_packet = 16M slave_max_allowed_packet = 128M max_connections = 3000 max_connect_errors = 15000 max_heap_table_size = 64M myisam_repair_threads = 1 myisam_sort_buffer_size = 64M open_files_limit = 20000 report_host = db-01 report_port = 3306 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,STRICT_TRANS_TABLES table_definition_cache = 3000 table_open_cache = 3000 thread_cache_size = 1500 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 slave_net_timeout = 3600 sync_master_info = 10000 sync_relay_log_info = 10000 host_cache_size = 5000 gtid_mode = OFF enforce_gtid_consistency = 0 slave_parallel_workers = 0 default_storage_engine = InnoDB default_tmp_storage_engine = InnoDB innodb_adaptive_flushing = 1 innodb_adaptive_hash_index = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_size = 76G innodb_buffer_pool_instances = 38 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 = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 600 innodb_io_capacity_max = 1200 # http://bugs.mysql.com/bug.php?id=71747 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 256M innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_max_dirty_pages_pct = 20 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 = 999 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 default-time-zone = '+00:00' [mysql] no-auto-rehash default_character_set = utf8 prompt = \u@db-01 [\d]>\_ [myisamchk] key_buffer_size = 768M sort_buffer_size = 256M read_buffer_size = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [client] socket = /export/umesh/server/binaries/mysql-advanced-5.6.24/run/mysql.sock [xtrabackup] open-files-limit = 100000 [special_replication_settings] - slave conf and build used [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: cat my.cnf [mysqld] basedir = /export/umesh/server/binaries/mysql-advanced-5.7.9 datadir = /export/umesh/server/binaries/mysql-advanced-5.7.9/slave tmpdir = /export/umesh/server/binaries/mysql-advanced-5.7.9/tmp socket = /export/umesh/server/binaries/mysql-advanced-5.7.9/run/mysql.sock #bind_address = 0.0.0.0 port = 15001 back_log = 2000 server_id = 196070182 read_only = 1 # noDynamic / DO NOT write to this instance log_error_verbosity = 3 event_scheduler = OFF log-bin = /export/umesh/server/binaries/mysql-advanced-5.7.9/log/binlog binlog_format = STATEMENT binlog_row_image = full sync_binlog = 0 expire_logs_days = 1 log_slave_updates max_binlog_size = 100M relay_log = /export/umesh/server/binaries/mysql-advanced-5.7.9/log/relaylog max_relay_log_size = 100M slave_compressed_protocol = OFF log_error = /export/umesh/server/binaries/mysql-advanced-5.7.9/log/log.err general_log = OFF general_log_file = /export/umesh/server/binaries/mysql-advanced-5.7.9/log/query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/mysql-advanced-5.7.9/log/slowlog long_query_time = 3 log_queries_not_using_indexes = 0 min_examined_row_limit = 10000 log_syslog = 1 show_compatibility_56 = 1 character_set_server = utf8 concurrent_insert = AUTO group_concat_max_len = 1048576 init_connect = 'SET NAMES utf8' key_buffer_size = 768M max_allowed_packet = 16M slave_max_allowed_packet = 128M max_connections = 3000 max_connect_errors = 15000 max_heap_table_size = 64M myisam_repair_threads = 1 myisam_sort_buffer_size = 64M open_files_limit = 20000 report_host = dbrdb-101 report_port = 3306 range_optimizer_max_mem_size = 4M query_cache_size = 0 query_cache_type = 0 read_buffer_size = 128k sort_buffer_size = 1M sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES table_definition_cache = 3000 table_open_cache = 3000 thread_cache_size = 1500 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 slave_net_timeout = 3600 sync_master_info = 10000 sync_relay_log_info = 10000 host_cache_size = 5000 gtid_mode = OFF enforce_gtid_consistency = 0 slave_parallel_workers = 0 default_storage_engine = InnoDB default_tmp_storage_engine = InnoDB innodb_adaptive_flushing = 1 innodb_adaptive_hash_index = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_dump_pct = 25 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_size = 76G innodb_buffer_pool_instances = 38 innodb_change_buffering = INSERTS innodb_file_per_table innodb_file_format = Barracuda innodb_flush_neighbors = 0 innodb_doublewrite = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 2500 innodb_io_capacity_max = 5000 # http://bugs.mysql.com/bug.php?id=71747 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 256M innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_max_dirty_pages_pct = 20 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 = 999 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 default-time-zone = '+00:00' [mysqldump] quick max_allowed_packet = 16M default-character-set = utf8mb4 [mysql] no-auto-rehash default_character_set = utf8 prompt = \u@dbrdb-101 [\d]>\_ [myisamchk] key_buffer_size = 768M sort_buffer_size = 256M read_buffer_size = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [client] socket = /export/umesh/server/binaries/mysql-advanced-5.7.9/run/mysql.sock [xtrabackup] open-files-limit = 100000 [special_replication_settings] 5.7.9 build used: [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: cat docs/INFO_SRC commit: 0efebb1df63491b46ab6f77956a6aaec509ef015 date: 2015-10-09 12:33:31 +0200 build-date: 2015-10-12 08:11:42 +0200 short: 0efebb1 branch: mysql-5.7.9-release MySQL source 5.7.9 -- start up commands used - master [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.24: scripts/mysql_install_db --defaults-file=./my.cnf [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.24: bin/mysqld --defaults-file=./my.cnf & - slave [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqld --defaults-file=./my.cnf --initialize-insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/slave [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqld --defaults-file=./my.cnf & ## Setup replication (Master->Slave) - master create schema mysql> use test Database changed mysql> CREATE TABLE `t1` ( -> `id` int(10) unsigned NOT NULL, -> `name` varchar(255) NOT NULL, -> `ids` int(10) unsigned DEFAULT NULL, -> `date1` date DEFAULT NULL, -> `date2` date DEFAULT NULL, -> `period` tinyint(3) unsigned DEFAULT NULL, -> `archived` datetime DEFAULT NULL, -> `value` mediumblob, -> PRIMARY KEY (`id`,`name`), -> KEY `index_period_archived` (`period`,`archived`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) mysql> - confirm that table is create on slave mysql> use test Database changed mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL, `name` varchar(255) NOT NULL, `ids` int(10) unsigned DEFAULT NULL, `date1` date DEFAULT NULL, `date2` date DEFAULT NULL, `period` tinyint(3) unsigned DEFAULT NULL, `archived` datetime DEFAULT NULL, `value` mediumblob, PRIMARY KEY (`id`,`name`), KEY `index_period_archived` (`period`,`archived`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> -- master - try to load data mysql> LOAD DATA INFILE '/tmp/SQL_LOAD-43156f1f-65c0-11e5-baa0-f0921c000d58-186068095-9.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`); Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t1\G *************************** 1. row *************************** id: 37 name: Actions_actions_url ids: 1 date1: 2015-10-07 date2: 2015-10-07 period: 1 archived: 2015-10-07 22:23:11 value: x▒▒▒kN▒0▒▒▒▒▒▒ss"▒▒▒▒▒▒N▒P▒▒hw▒▒▒s-P▒▒▒▒ObD5▒jTqQۺ}▒ڡ▒?UQ 굃Y▒▒x▒9R▒▒▒>1▒▒<▒~4▒<▒O▒Ф▒$▒▒}K▒{▒ ▒2▒?▒7Ev|▒{2u]5f▒▒▒z▒▒▒▒"b,?▒80▒▒▒U▒▒▒▒▒▒▒▒▒c▒J▒▒▒▒▒.4▒▒▒e▒▒ 2 rows in set (0.00 sec) -- extract from bin log # at 1290 #151015 7:14:09 server id 186068095 end_log_pos 1369 CRC32 0x3bc3af91 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1444886049/*!*/; BEGIN /*!*/; # at 1369 #151015 7:14:09 server id 186068095 end_log_pos 2120 CRC32 0xd04d7d62 #Begin_load_query: file_id: 1 block_len: 724 # at 2120 #151015 7:14:09 server id 186068095 end_log_pos 2473 CRC32 0x64784214 Execute_load_query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1444886049/*!*/; LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-1' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`) /*!*/; # file_id: 1 # at 2473 #151015 7:14:09 server id 186068095 end_log_pos 2504 CRC32 0x71d6b3f2 Xid = 25 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; -- slave mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 2504 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 791 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes 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: 1300 Last_Error: Error 'Invalid utf8 character string: '"x'' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '/export/umesh/server/binaries/mysql-advanced-5.7.9/tmp/SQL_LOAD-61bfe81f-72fa-11e5-bbc7-0010e05f3e06-186068095-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`)' Skip_Counter: 0 Exec_Master_Log_Pos: 1290 Relay_Log_Space: 2205 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: 1300 Last_SQL_Error: Error 'Invalid utf8 character string: '"x'' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '/export/umesh/server/binaries/mysql-advanced-5.7.9/tmp/SQL_LOAD-61bfe81f-72fa-11e5-bbc7-0010e05f3e06-186068095-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`)' Replicate_Ignore_Server_Ids: Master_Server_Id: 186068095 Master_UUID: f863ce69-72f9-11e5-bb44-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: 151015 07:14:09 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 1 row in set (0.00 sec) mysql> -- extract from error log 2015-10-15T05:08:19.149249Z 3 [Note] Slave I/O thread for channel '': connected to master 'repl@localhost:15000',replication started in log 'binlog.000003' at position 813 2015-10-15T05:14:09.615470Z 4 [ERROR] Slave SQL for channel '': Error 'Invalid utf8 character string: '"x'' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '/export/umesh/server/binaries/mysql-advanced-5.7.9/tmp/SQL_LOAD-61bfe81f-72fa-11e5-bbc7-0010e05f3e06-186068095-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`)', Error_code: 1300 2015-10-15T05:14:09.615512Z 4 [Warning] Slave: Invalid utf8 character string: '"x' Error_code: 1300 2015-10-15T05:14:09.615525Z 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000003' position 1290 -- extract from relay log # at 791 #151015 7:14:09 server id 186068095 end_log_pos 1369 CRC32 0x3bc3af91 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1444886049/*!*/; BEGIN /*!*/; # at 870 #151015 7:14:09 server id 186068095 end_log_pos 2120 CRC32 0xd04d7d62 #Begin_load_query: file_id: 1 block_len: 724 # at 1621 #151015 7:14:09 server id 186068095 end_log_pos 2473 CRC32 0x64784214 Execute_load_query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1444886049/*!*/; LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`) /*!*/; # file_id: 1 # at 1974 #151015 7:14:09 server id 186068095 end_log_pos 2504 CRC32 0x71d6b3f2 Xid = 25 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; // Setup replication between 5.5.27(master)->5.7.9(slave) - master - conf and build used Conf is same except the 5.6.27 build path [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: cat docs/INFO_SRC commit: 7516bc111acac1292ca0fbfd97ff07970fba064e date: 2015-09-11 15:38:10 +0200 build-date: 2015-09-11 15:40:25 +0200 short: 7516bc1 branch: mysql-5.6.27-release MySQL source 5.6.27 - slave - conf and build used Same as earlier -- start up commands used - master [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: scripts/mysql_install_db --defaults-file=./my.cnf [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: bin/mysqld --defaults-file=./my.cnf & - slave [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqld --defaults-file=./my.cnf --initialize-insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/slave [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqld --defaults-file=./my.cnf & ## Setup replication (Master->Slave) - master create schema mysql> use test Database changed mysql> CREATE TABLE `t1` ( -> `id` int(10) unsigned NOT NULL, -> `name` varchar(255) NOT NULL, -> `ids` int(10) unsigned DEFAULT NULL, -> `date1` date DEFAULT NULL, -> `date2` date DEFAULT NULL, -> `period` tinyint(3) unsigned DEFAULT NULL, -> `archived` datetime DEFAULT NULL, -> `value` mediumblob, -> PRIMARY KEY (`id`,`name`), -> KEY `index_period_archived` (`period`,`archived`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.00 sec) -- slave confirm schema is created mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL, `name` varchar(255) NOT NULL, `ids` int(10) unsigned DEFAULT NULL, `date1` date DEFAULT NULL, `date2` date DEFAULT NULL, `period` tinyint(3) unsigned DEFAULT NULL, `archived` datetime DEFAULT NULL, `value` mediumblob, PRIMARY KEY (`id`,`name`), KEY `index_period_archived` (`period`,`archived`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> -- master - try to load data mysql> mysql> LOAD DATA INFILE '/tmp/SQL_LOAD-43156f1f-65c0-11e5-baa0-f0921c000d58-186068095-9.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`); Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t1\G *************************** 1. row *************************** id: 37 name: Actions_actions_url ids: 1 date1: 2015-10-07 date2: 2015-10-07 period: 1 archived: 2015-10-07 22:23:11 value: x▒▒▒kN▒0▒▒▒▒▒▒ss"▒▒▒▒▒▒N▒P▒▒hw▒▒▒s-P▒▒▒▒ObD5▒jTqQۺ}▒ڡ▒?UQ 굃Y▒▒x▒9R▒▒▒>1▒▒<▒~4▒<▒O▒Ф▒$▒▒}K▒{▒ ▒2▒?▒7Ev|▒{2u]5f▒▒▒z▒▒▒▒"b,?▒80▒▒▒U▒▒▒▒▒▒▒▒▒c▒J▒▒▒▒▒.4▒▒▒e▒▒ 2 rows in set (0.00 sec) -- slave mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 2504 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 791 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes 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: 1300 Last_Error: Error 'Invalid utf8 character string: '"x'' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '/export/umesh/server/binaries/mysql-advanced-5.7.9/tmp/SQL_LOAD-28df8c72-7302-11e5-833f-0010e05f3e06-186068095-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`)' Skip_Counter: 0 Exec_Master_Log_Pos: 1290 Relay_Log_Space: 2205 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: 1300 Last_SQL_Error: Error 'Invalid utf8 character string: '"x'' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '/export/umesh/server/binaries/mysql-advanced-5.7.9/tmp/SQL_LOAD-28df8c72-7302-11e5-833f-0010e05f3e06-186068095-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`)' Replicate_Ignore_Server_Ids: Master_Server_Id: 186068095 Master_UUID: ec98ec0e-7301-11e5-bb77-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: 151015 08:05:32 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 1 row in set (0.00 sec) mysql> ### Workaround: As Shane suggested - master mysql> LOAD DATA INFILE '/tmp/SQL_LOAD-43156f1f-65c0-11e5-baa0-f0921c000d58-186068095-9.data' REPLACE INTO TABLE `t1` CHARACTER SET binary FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`id`, `ids`, `date1`, `date2`, `period`, `archived`, `name`, `value`); Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t1\G *************************** 1. row *************************** id: 37 name: Actions_actions_url ids: 1 date1: 2015-10-07 date2: 2015-10-07 period: 1 archived: 2015-10-07 22:23:11 value: x▒▒▒kN▒0▒▒▒▒▒▒ss"▒▒▒▒▒▒N▒P▒▒hw▒▒▒s-P▒▒▒▒ObD5▒jTqQۺ}▒ڡ▒?UQ 굃Y▒▒x▒9R▒▒▒>1▒▒<▒~4▒<▒O▒Ф▒$▒▒}K▒{▒ ▒2▒?▒7Ev|▒{2u]5f▒▒▒z▒▒▒▒"b,?▒80▒▒▒U▒▒▒▒▒▒▒▒▒c▒J▒▒▒▒▒.4▒▒▒e▒▒ 2 rows in set (0.00 sec) - slave mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 3824 Relay_Log_File: relaylog.000003 Relay_Log_Pos: 1634 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 3824 Relay_Log_Space: 3685 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: 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: 186068095 Master_UUID: ec98ec0e-7301-11e5-bb77-0010e05f3e06 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 updates 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: 1 row in set (0.00 sec) mysql> select * from t1\G *************************** 1. row *************************** id: 37 name: Actions_actions_url ids: 1 date1: 2015-10-07 date2: 2015-10-07 period: 1 archived: 2015-10-07 22:23:11 value: x▒▒▒kN▒0▒▒▒▒▒▒ss"▒▒▒▒▒▒N▒P▒▒hw▒▒▒s-P▒▒▒▒ObD5▒jTqQۺ}▒ڡ▒?UQ 굃Y▒▒x▒9R▒▒▒>1▒▒<▒~4▒<▒O▒Ф▒$▒▒}K▒{▒ ▒2▒?▒7Ev|▒{2u]5f▒▒▒z▒▒▒▒"b,?▒80▒▒▒U▒▒▒▒▒▒▒▒▒c▒J▒▒▒▒▒.4▒▒▒e▒▒ 2 rows in set (0.00 sec)