3 nodes Master-Master Mysql replication not sync properly
Posted by: Mr Linux
Date: February 27, 2018 08:19PM
Date: February 27, 2018 08:19PM
I have website that run on 1 haproxy server with roundrobin method that distribute multiple connections between 3 dedicated Centos servers that running as webserver/php and Master-Master Mysql replication
The website is php web script that you can register, login , update , add posts . The problem is i get different data between all 3 servers because and the only way to do it is to backup and delete all tables in database then restore the backup again , I am not an expert in SQL to fix it without dropping and restore the database
I want to know what exactly happen that makes the data become different after 3 or 4 days of last restore for database backup
I have phpMyAdmin and i noticed that tables rows sum count is different between all databases
Node1
https://s14.postimg.org/6mixjhpwt/0ydq_C.png
Node2
https://s14.postimg.org/w5b9wi1r1/0_Lk2_Y.png
Node3
https://s14.postimg.org/bxxu47jp9/pq8p_N.png
Node01 my.cnf configuration
[mysqld]
# REPLICATION #
skip-name-resolve
server-id=1
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db = database_ooo
binlog-format=MIXED
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
sync_binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
expire_logs_days=1
auto_increment_increment = 3
auto_increment_offset = 1
# GENERAL #
default-storage-engine=MyISAM
innodb_file_per_table=1
max-allowed-packet = 16M
max-connect-errors = 1000000
key-buffer-size = 22G
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
SHOW SLAVE STATUS \G
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3722492
Server version: 5.5.58-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.3
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1006041939
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 18205945
Relay_Master_Log_File: mysql-bin.000001
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: 1006041939
Relay_Log_Space: 18206095
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: 3
1 row in set (0.00 sec)
mysql>
Node02 my.cnf configuration
[mysqld]
# REPLICATION #
skip-name-resolve
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db = database_ooo
binlog-format=MIXED
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
sync_binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
expire_logs_days=1
auto_increment_increment = 3
auto_increment_offset = 2
# GENERAL #
default-storage-engine=MyISAM
innodb_file_per_table=1
max-allowed-packet = 16M
max-connect-errors = 1000000
key-buffer-size = 22G
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
max_allowed_packet=268435456
open_files_limit=2048
SHOW SLAVE STATUS \G
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3148494
Server version: 5.5.58-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 10.0.0.1
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 998311588
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 992821343
Relay_Master_Log_File: mysql-bin.000001
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: 998311588
Relay_Log_Space: 992821493
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
1 row in set (0.00 sec)
mysql>
Node03 my.cnf configuration
[mysqld]
# REPLICATION #
skip-name-resolve
server-id=3
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db = database_ooo
binlog-format=MIXED
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
sync_binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
expire_logs_days=1
auto_increment_increment = 3
auto_increment_offset = 3
# GENERAL #
default-storage-engine=MyISAM
innodb_file_per_table=1
max-allowed-packet = 16M
max-connect-errors = 1000000
key-buffer-size = 22G
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
SHOW SLAVE STATUS \G
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3727805
Server version: 5.5.58-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 10.0.0.2
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1004908903
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1000026851
Relay_Master_Log_File: mysql-bin.000001
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: 1004908301
Relay_Log_Space: 1000027603
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: 1
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: 2
1 row in set (0.02 sec)
mysql>
The question is I want to find the root that cause this problem and fix it , i don't want to fix the current tables differences because i know it will be hard for me as i have no experience in SQL at all . but i can backup and restore it again and all rows will be equal in all 3 databases , but how can i prevent this problem happen again after restoring the database ?
The website is php web script that you can register, login , update , add posts . The problem is i get different data between all 3 servers because and the only way to do it is to backup and delete all tables in database then restore the backup again , I am not an expert in SQL to fix it without dropping and restore the database
I want to know what exactly happen that makes the data become different after 3 or 4 days of last restore for database backup
I have phpMyAdmin and i noticed that tables rows sum count is different between all databases
Node1
https://s14.postimg.org/6mixjhpwt/0ydq_C.png
Node2
https://s14.postimg.org/w5b9wi1r1/0_Lk2_Y.png
Node3
https://s14.postimg.org/bxxu47jp9/pq8p_N.png
Node01 my.cnf configuration
[mysqld]
# REPLICATION #
skip-name-resolve
server-id=1
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db = database_ooo
binlog-format=MIXED
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
sync_binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
expire_logs_days=1
auto_increment_increment = 3
auto_increment_offset = 1
# GENERAL #
default-storage-engine=MyISAM
innodb_file_per_table=1
max-allowed-packet = 16M
max-connect-errors = 1000000
key-buffer-size = 22G
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
SHOW SLAVE STATUS \G
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3722492
Server version: 5.5.58-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.3
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1006041939
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 18205945
Relay_Master_Log_File: mysql-bin.000001
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: 1006041939
Relay_Log_Space: 18206095
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: 3
1 row in set (0.00 sec)
mysql>
Node02 my.cnf configuration
[mysqld]
# REPLICATION #
skip-name-resolve
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db = database_ooo
binlog-format=MIXED
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
sync_binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
expire_logs_days=1
auto_increment_increment = 3
auto_increment_offset = 2
# GENERAL #
default-storage-engine=MyISAM
innodb_file_per_table=1
max-allowed-packet = 16M
max-connect-errors = 1000000
key-buffer-size = 22G
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
max_allowed_packet=268435456
open_files_limit=2048
SHOW SLAVE STATUS \G
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3148494
Server version: 5.5.58-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 10.0.0.1
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 998311588
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 992821343
Relay_Master_Log_File: mysql-bin.000001
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: 998311588
Relay_Log_Space: 992821493
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
1 row in set (0.00 sec)
mysql>
Node03 my.cnf configuration
[mysqld]
# REPLICATION #
skip-name-resolve
server-id=3
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db = database_ooo
binlog-format=MIXED
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
sync_binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1
expire_logs_days=1
auto_increment_increment = 3
auto_increment_offset = 3
# GENERAL #
default-storage-engine=MyISAM
innodb_file_per_table=1
max-allowed-packet = 16M
max-connect-errors = 1000000
key-buffer-size = 22G
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
SHOW SLAVE STATUS \G
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3727805
Server version: 5.5.58-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 10.0.0.2
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1004908903
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1000026851
Relay_Master_Log_File: mysql-bin.000001
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: 1004908301
Relay_Log_Space: 1000027603
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: 1
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: 2
1 row in set (0.02 sec)
mysql>
The question is I want to find the root that cause this problem and fix it , i don't want to fix the current tables differences because i know it will be hard for me as i have no experience in SQL at all . but i can backup and restore it again and all rows will be equal in all 3 databases , but how can i prevent this problem happen again after restoring the database ?
Subject
Views
Written By
Posted
579
February 27, 2018 08:36PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.