- I have a MySQL 5.5.32 server (community edition) on one Linux server which is master.
- I have a MySQL 5.6 server (community edition) on another Linux server which is slave. I have installed various instances of MySQL on this machine with having different ports. Various instances for replicating different sources
- Statement based Replication has been set up between these two servers.
If i do
show slave status\G; I don't see any errors here and replication is working , but when i see count of
records, it is not increasing at slave end.
Also, slave is continuously receiving relay log files, but I feel relay logs are not being written by the slave I/O thread.
I see below error in log file :
[ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.019362' at position 488205064 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.32-log), even though it is on the slave (version: 5.6.26-log), Error_code: 1193
Also I have made changes to
set global binlog_checksum='NONE';
but still condition is same.
Below is slave's my.cnf file
[mysqld] server-id=24 log-bin=mysql-bin binlog_format=ROW relay_log=relay-log replicate-do-table=db.tb datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock user=mysql skip-name-resolve log_bin=/var/lib/mysql/logs.index log_bin_trust_function_creators=1 old_passwords=0 bind_address=127.0.0.1 max_connections=10 symbolic-links=0 max_allowed_packet=110M query_cache_size=512M query_cache_limit=256M wait_timeout=600 key_buffer_size=512M sort_buffer_size=256K innodb_buffer_pool_size=1024M flush_time=620 # The TCP/IP Port the MySQL Server will listen on port=3306 symbolic-links=0 binlog_checksum=NONE slave_sql_verify_checksum=OFF [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Please help, what is happening around . Is this because of different versions? But MySQL doc says, replication between two different version is possible.
Below is Master my.cnf
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] performance_schema port = 3306 socket = /var/lib/mysql/mysql.sock log = /var/log/mysqld.log log_warnings=2 log-error = /var/log/mysqld.error.log long_query_time=1 log-slow-queries=/var/log/mysql-slow-queries.log slow-query-log = 1 slow-query-log-file = /var/log/mysql-slow.log log_bin_trust_function_creators = 1 skip-name-resolve skip-external-locking innodb_file_per_table=1 expire-logs-days=30 key_buffer_size = 4096M key_buffer = 1024M max_allowed_packet = 1G low_priority_updates=1 concurrent_insert=ALWAYS lower_case_table_name = 1 table_open_cache = 3000 table_cache = 5000 sort_buffer_size = 50M read_buffer_size = 15M read_rnd_buffer_size = 20M join_buffer_size = 100M myisam_sort_buffer_size = 512M thread_stack = 12M thread_cache_size = 20 query_cache_size = 512M query_cache_type = 1 query_cache_limit = 512M max_connections = 300 tmp_table_size = 256M max_heap_table_size = 512M innodb_buffer_pool_size = 40G binlog_cache_size = 5M innodb_flush_method = O_DIRECT innodb_additional_mem_pool_size = 20M innodb_log_buffer_size = 10M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 collation_server=utf8_general_ci character_set_server=utf8 init_connect="SET NAMES 'utf8'" # binary logging is required for replication log-bin=mysql-bin server-id = 1 binlog-do-db=#database name1 slave-skip-error=1062 relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/mysql-relay-log.info log-bin=/var/lib/mysql/mysql-bin symbolic-links=0 innodb_additional_mem_pool_size = 50M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
UPDATE : I just noticed that relay log files are not getting purged on their own and lot of relay log files has been keep on generating.