0

I have a strange issue with MySql 5.6 Master-Master replication. I'm not sure if its entirely a replication issue or a client issue. We have two MySql databases on two different RHEL servers. We have setup a common schema for them and enabled schema level replication. The replication works fine when issuing any DML using the command line utilities or NaviCat client on either Master. But it does not seem to replicate the data when using SQL Developer or MySql WorkBench clients.

Here is the server information:

Linux "Hostname" 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Dec 13 06:58:20 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

Here is the my.cnf file

[client]
#password       = your_password
port=3306
socket=./mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port=3306
socket=./mysql.sock
#skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine=InnoDB
lower_case_table_names=1
server-id = 1


replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = <>mysql-bin.log
log-bin-index = <>/mysql-bin.index
log-slave-updates
replicate-do-db=schema_replicate
binlog-do-db=schema_replicate
replicate-do-table=schema_replicate.ref_tbl
relay-log = <>/relay-bin
relay-log-index = <>/relay-bin.index
relay-log-info-file = <>/relay-bin.info
slave-skip-errors = 1062

# binary logging format - mixed recommended
#binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# The number of seconds the server waits for activity on a noninteractive
# connection before closing it. Range: 1-2147483.  2073600 is 24 days, close
# to maximum
wait_timeout=2073600

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :

innodb_data_file_path = ibdata1:10M:autoextend

innodb_buffer_pool_size = 48M
innodb_additional_mem_pool_size = 2M

innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

Any help is appreciated.

0

Activate the general log on both servers at runtime (beware of doing it when you have a lot of load, as that will make all your queries slower):

SET GLOBAL general_log = 1;

You will find it on datadir/hostname.log, or you can change its location with the general_log_file parameter.

There, all connections and queries sent to the server will be logged. Compare what NaviCat and Workbench are doing to identify a possible problem. You can also check the contents of the binary logs with the utility mysqlbinlog and the changes applied with the commands SHOW MASTER STATUS and SHOW SLAVE STATUS\G on each server, making sure that the replication is not broken (very common in master-master replications).

Remember to disable the general log after you are done:

SET GLOBAL general_log = 0;
| improve this answer | |
  • Thanks for your suggestions. Turns out it we were using the default schema while running the update statements, just specifying the <SCHEMA.TABLE_NAME> isn't sufficient enough. We had to give 'use <SCHEMA>' and then run the statements on <SCHEMA.TABLE_NAME>. – rahul Aug 20 '14 at 19:07
0

Turns out it we were using the default schema while running the DML statements, just specifying the SCHEMA1.TABLE_NAME isn't sufficient enough. We had to give 'use ' and then run the statements on .

Example, when you are logged into SCHEMA1

update SCHEMA2.<TABLE_NAME> set FIELD1='VALUE';

The above statement does not replicate the SCHEMA2., rather the below works

use SCHEMA2;
update SCHEMA2.<TABLE_NAME> set FIELD1='VALUE';

This might be a common issue, but I couldn't find any similar solutions, so posted this one.

| improve this answer | |
  • You are probably using master-side replication filtering, which are a recipe for replication breaking (you just suffered from one of its many problems). Edit: Yes, you are, it is on your config file. – jynus Aug 20 '14 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.