Master Master replication rows not sync properly

Posted on

Question :

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

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;

Leave a Reply

Your email address will not be published. Required fields are marked *