- 5.7.28 build cat docs/INFO_SRC commit: 2f8088af9733d4275c46ea8e9d8beed14e5d5fe4 date: 2019-09-27 12:28:40 +0530 build-date: 2019-09-27 09:00:29 +0200 short: 2f8088a branch: mysql-5.7.28-release MySQL source 5.7.28 -- Conf file, table DDL used from the report rm -rf master bin/mysqld --defaults-file=./master.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/master --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./master.cnf --log-error-verbosity=3 2>&1 & rm -rf slave bin/mysqld --defaults-file=./slave.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/slave --log-error-verbosity=3 bin/mysqld --defaults-file=./slave.cnf --log-error-verbosity=3 2>&1 & -- Setup Master-slave repl - master bin/mysql -uroot -S /tmp/mysql_master.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.28: bin/mysql -uroot -S /tmp/mysql_master.sock --prompt='Master>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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. Master>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) Master>CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) Master>FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) Master>SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000002 | 1220 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) Master> - slave bin/mysql -uroot -S /tmp/mysql_slave.sock --prompt='Slave>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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. Slave> Slave>CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=3333, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='master-bin.000002', -> MASTER_LOG_POS=1220; 2019-12-14T06:44:03.299160Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='localhost', master_port= 3333, master_log_file='master-bin.000002', master_log_pos= 1220, master_bind=''. Query OK, 0 rows affected, 2 warnings (0.01 sec) Slave>start slave; 2019-12-14T06:44:16.808338Z 3 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2019-12-14T06:44:16.809193Z 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'master-bin.000002' at position 1220, relay log '/export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/relaylog.000001' position: 4 Query OK, 0 rows affected (0.00 sec) Slave>2019-12-14T06:44:16.809378Z 3 [Note] Slave I/O thread for channel '': connected to master 'repl@localhost:3333',replication started in log 'master-bin.000002' at position 1220 Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1220 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000002 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: 1220 Relay_Log_Space: 521 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: 1 Master_UUID: b2e2b8de-1e3c-11ea-a1b8-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: Master_TLS_Version: 1 row in set (0.00 sec) Slave> - ## Create problemetic table on master, once replicated then drop and recreate with diff charset - on master create database test; use test CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; show create table db_slaves\G - on slave use test; show create table db_slaves\G drop table db_slaves; CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; show create table db_slaves\G Master>create database test; Query OK, 1 row affected (0.00 sec) Master>use test Database changed Master>CREATE TABLE `db_slaves` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `parent_id` int(11) DEFAULT NULL, -> `dsn` varchar(255) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) Master>show create table db_slaves\G *************************** 1. row *************************** Table: db_slaves Create Table: CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) -- slave status should be fine Slave>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 Slave>show create table db_slaves\G *************************** 1. row *************************** Table: db_slaves Create Table: CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Slave>drop table db_slaves; Query OK, 0 rows affected (0.00 sec) Slave> CREATE TABLE `db_slaves` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `parent_id` int(11) DEFAULT NULL, -> `dsn` varchar(255) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.01 sec) Slave>show create table db_slaves\G *************************** 1. row *************************** Table: db_slaves Create Table: CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1714 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 815 Relay_Master_Log_File: master-bin.000002 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: 1714 Relay_Log_Space: 1015 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: 1 Master_UUID: b2e2b8de-1e3c-11ea-a1b8-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: Master_TLS_Version: 1 row in set (0.00 sec) -- Now, provision some data on master - on master Master>insert into db_slaves(parent_id,dsn) VALUES(1,'mysql.com'); Query OK, 1 row affected (0.00 sec) Master>select * from db_slaves; +----+-----------+-----------+ | id | parent_id | dsn | +----+-----------+-----------+ | 1 | 1 | mysql.com | +----+-----------+-----------+ 1 row in set (0.00 sec) - slave is broken Slave>2019-12-14T06:48:29.461576Z 4 [ERROR] Slave SQL for channel '': Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765(bytes))' to type 'varchar(1020(bytes) utf8mb4)', Error_code: 1677 2019-12-14T06:48:29.461608Z 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 'master-bin.000002' position 1714. Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1993 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 815 Relay_Master_Log_File: master-bin.000002 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: 1677 Last_Error: Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765(bytes))' to type 'varchar(1020(bytes) utf8mb4)' Skip_Counter: 0 Exec_Master_Log_Pos: 1714 Relay_Log_Space: 1294 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: 1677 Last_SQL_Error: Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765(bytes))' to type 'varchar(1020(bytes) utf8mb4)' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: b2e2b8de-1e3c-11ea-a1b8-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: 191214 07:48:29 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) #### Master/Slave conf cat ../mysql-5.7.28/master.cnf ../mysql-5.7.28/slave.cnf [mysqld] basedir = /export/umesh/server/binaries/GABuilds/mysql-5.7.28 datadir = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master tmpdir = /tmp/ socket = /tmp/mysql_master.sock back_log = 2000 port=3333 server_id = 1 read_only = 0 log_error_verbosity = 2 event_scheduler = ON log-bin = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master/master-bin binlog_format = ROW binlog_row_image = full sync_binlog = 1 expire_logs_days = 7 log_slave_updates max_binlog_size = 100M relay_log = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master/relaylog max_relay_log_size = 100M slave_compressed_protocol = OFF general_log = OFF general_log_file = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master/query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master/slowlog long_query_time = 3 log_queries_not_using_indexes = 0 min_examined_row_limit = 10000 log_syslog = 0 default_password_lifetime = 0 show_compatibility_56 = 1 #character_set_server = utf8mb4 concurrent_insert = AUTO group_concat_max_len = 1048576 #init_connect = 'SET NAMES utf8mb4' 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 = server5728 report_port = 3333 range_optimizer_max_mem_size = 16M 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 = 10G innodb_change_buffering = ALL innodb_file_per_table 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 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_autoinc_lock_mode=2 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 performance_schema_instrument='wait/lock/metadata/sql/mdl=1' [mysqld_safe] log-error = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master/mysqld.log pid_file = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/master/mysqld.pid core-file-size = unlimited [mysqldump] quick max_allowed_packet = 16M default-character-set = utf8mb4 ignore-table=mysql.gtid_executed [mysql] no-auto-rehash default_character_set = utf8mb4 prompt = \u@server5728 [\d]>\_ [myisamchk] key_buffer_size = 768M sort_buffer_size = 256M read_buffer_size = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [client] socket = /tmp/mysql_master.sock [mysqld] basedir = /export/umesh/server/binaries/GABuilds/mysql-5.7.28 datadir = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave tmpdir = /tmp/ socket = /tmp/mysql_slave.sock port = 6666 back_log = 2000 server_id = 2 read_only = 1 log_error_verbosity = 2 event_scheduler = OFF log-bin = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/slave-bin binlog_format = ROW binlog_row_image = full sync_binlog = 0 expire_logs_days = 1 log_slave_updates max_binlog_size = 100M relay_log = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/relaylog max_relay_log_size = 100M slave_compressed_protocol = OFF general_log = OFF general_log_file = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/query.log slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/slowlog long_query_time = 3 log_queries_not_using_indexes = 0 min_examined_row_limit = 10000 log_syslog = 0 default_password_lifetime = 0 show_compatibility_56 = 1 character_set_server = utf8mb4 concurrent_insert = AUTO group_concat_max_len = 1048576 init_connect = 'SET NAMES utf8mb4' 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 = server5728 report_port = 6666 range_optimizer_max_mem_size = 16M 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 = READ-COMMITTED 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 = 10G innodb_change_buffering = ALL innodb_file_per_table 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 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_autoinc_lock_mode=2 #innodb_numa_interleave=ON performance_schema = 1 [mysqld_safe] log-error = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/mysqld.log pid_file = /export/umesh/server/binaries/GABuilds/mysql-5.7.28/slave/mysqld.pid core-file-size = unlimited [mysqldump] quick max_allowed_packet = 16M default-character-set = utf8mb4 ignore-table=mysql.gtid_executed [mysql] no-auto-rehash default_character_set = utf8mb4 prompt = \u@server5728 [\d]>\_ [myisamchk] key_buffer_size = 768M sort_buffer_size = 256M read_buffer_size = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [client] socket = /tmp/mysql_slave.sock