3

SETUP:

I have a WAMP MySQL Master-Master replication setup on 2 machines with sync_binlog=1 for safety. Machine config: Windows Azure Standard 2: 2 core, 4GB RAM, Windows Server 2008, MySQL 5.6.12, WAMP 2.4

PROBLEM:

If i start the slaves for the first 5-10 minutes everything is ok. After this time my inserts\updates will never sync in both DBs. If i check "show slave status\G":

Slave_IO_State: Waiting for master to send event
Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

If i do "stop slave\start slave" the machines sync.

QUESTION:

What is the proper way to solve this, or which param am i setting wrong?

//later edit Here are my 2 mysql.ini

master1:

# The MySQL server
[wampmysqld]
socket      = /tmp/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# 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
log_bin = binlog
log_bin_index = binlogindex
relay_log = relaylog
relay_log_index = relaylogindex
log_slave_updates = 1

auto-increment-increment = 2
auto-increment-offset = 1

sync-binlog = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir     = /tmp/     
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
bulk_insert_buffer_size = 256M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#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

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
innodb_file_per_table
max_connections = 300
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
sync-binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1

master2:

# The MySQL server
[wampmysqld]
socket      = /tmp/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# 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   = 2
log_bin = binlog
log_bin_index = binlogindex
relay_log = relaylog
relay_log_index = relaylogindex
log_slave_updates = 1

auto-increment-increment = 2
auto-increment-offset = 2

sync-binlog = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir     = /tmp/     
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
bulk_insert_buffer_size = 256M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#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

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
innodb_file_per_table
max_connections = 300
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
sync-binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
  • How do you know they aren't syncing? What do you see that indicates this? – Valerie Parham-Thompson Jun 1 '14 at 1:23
  • i create an insert and check it both DBs, and it only appears in one of them. – bob Jun 1 '14 at 9:56
1

PROBLEM

MySQL needs to be very explicit when flushing to disk in Windows. You have caught on to this when you did STOP SLAVE; and START SLAVE;

You said you are using sync-binlog = 1. That's great. That only addresses half the log file issues.

SOLUTION (or at least SUGGESTION)

You need to synchronize all other replication components

[mysqld]
sync-binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1

The SQL thread reads binlog events of the master from the relay logs. Doing sync-binlog only flushes master logs. By flushing relay logs and the associated replication status files, this should make reading replication status and binlog events from the relay logs more accurate.

Here is the MySQL Documentation for these options

Give it a Try !!!

CAVEAT: There may be a small impact on disk I/O. Please monitor it.

  • i've setup all MySQL params acording to many of your answers on different posts. i also tried for durability and consistency in replication: innodb-flush-log-at-trx-commit. If i restart the sync, the slaves sync in the first few minutes and after that the sync never gets back to work. I will try your suggestion right now and see what happens. – bob Jun 1 '14 at 13:02
  • same problem happens -it works after starting the sync for a few minutes of inserts and updates, after this period nothing syncs anymore. any suggestions? – bob Jun 1 '14 at 13:57
  • i add my full my.ini config files, maybe it helps – bob Jun 1 '14 at 14:09
1

Give a try to that option in your slave my.cnf file : slave-net-timeout = 60

(The number of seconds to wait for more data or a heartbeat signal from the master before the slave considers the connection broken, aborts the read, and tries to reconnect.)

I use MariaDB (<= MariaDB 10.2.3) which has 3600 (sec) as default value.

Now my database is synchronized.

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.