Mysql – Master Master replication rows not sync properly

data synchronizationlinuxMySQLmysql-5.7replication

I have a wordpress website that run on 3 machines

                       Machine1      | Machine 2 {nginx+lsyncd+mysql}
visitor > cloudflare > haproxy ------
                                     | Machine 3 {nginx+lsyncd+mysql}

the machine 2 and machine 3 running centos 7.6 os 64 bit with mysql 5.7 and phpmyadmin

I have one database running for replication, the problem i see that the total sum for both database on each server not equal, for example, this is screenshot form phpmyadmin for the server number 1

enter image description here

the screenshot from phpmyadmin in server number 2

enter image description here

you can see rows not equal so I start to search for that table that miss this 1 row record and I found that the table that missing this 1 record is the table wp_options

wp_options server 1

mysql> SHOW CREATE TABLE wp_options;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2883462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

wp_options server 2

mysql> SHOW CREATE TABLE wp_options;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2883462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

checksum server 1

mysql> CHECKSUM TABLE wp_options;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| mobizil_data.wp_options | 3191386356 |
+-------------------------+------------+
1 row in set (0.03 sec)

checksum server 2

mysql> CHECKSUM TABLE wp_options;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| mobizil_data.wp_options | 2698212655 |
+-------------------------+------------+
1 row in set (0.01 sec)

the error log found in mysql-error.log file

mysql error log 2019-05-12T22:49:27.028225Z 11 [Note] Slave SQL for channel '': Could not execute Delete_rows event on table database_name.wp_options; Can't find record in 'wp_options', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000006, end_log_pos 174775695, Error_code: 1032

Here are my.cnf in both server

[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_name
binlog-format=ROW
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
expire_logs_days=14
auto_increment_increment = 10
auto_increment_offset = 2
sql_mode=""

# GENERAL #
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance-schema=0
symbolic-links=0
log-error=/var/lib/mysql/mysql-error.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000

The server-id and auto_increment_offset is different on each server

I want to know why this happens and how to troubleshoot it and prevent from happening again

Best Answer

Based on an error message that finally came to light, plus the table being MyISAM, I recommend

CHECK TABLE wp_options;

If it complains about anything, do

REPAIR TABLE wp_options;