The Ultimate Guide to High Availability with MariaDB

MariaDB Galera Cluster Out of sync suddenly (Only sync the schema change but data not) Innodb engine

Hi
We are running a 3 (A,B,C) nodes mariadb galera cluster on production. The applications only connect to A node with read/write account. Recently we found the B and C were of sync with A and only the schema change can be syncd to each other. (Use InnoDB engine).
From the SHOW STATUS LIKE 'wsrep%' for each node , both 3 nodes has the same wsrep_cluster_state_uuid and wsrep_local_state_uuid , the wsrep_local_state_comment was labeled as 'synced'.
How to trouble shooting and resolve this kind of issue ?

Attach the status of wsrep of A node

A nodeB nodeB Node WSERP VARIABLES
wsrep_apply_oooe0.033243wsrep_apply_oooe0wsrep_osu_methodTOIwsrep_osu_methodTOI
wsrep_apply_oool0.000002wsrep_apply_oool0wsrep_auto_increment_controlONwsrep_auto_increment_controlON
wsrep_apply_window1.044903wsrep_apply_window1wsrep_causal_readsOFFwsrep_causal_readsOFF
wsrep_causal_reads0wsrep_causal_reads0wsrep_certify_nonpkONwsrep_certify_nonpkON
wsrep_cert_deps_distance57.829916wsrep_cert_deps_distance57.826126wsrep_cluster_address"gcomm:192.168.10.11192.168.10.12192.168.10.13"wsrep_cluster_address"gcomm:192.168.10.11192.168.10.12192.168.10.13"
wsrep_cert_index_size9wsrep_cert_index_size54wsrep_cluster_namewise_galerawsrep_cluster_namewise_galera
wsrep_cert_interval0.052688wsrep_cert_interval0.052686wsrep_convert_lock_to_trxOFFwsrep_convert_lock_to_trxOFF
wsrep_cluster_conf_id16wsrep_cluster_conf_id16wsrep_data_home_dir/var/lib/mysql/wsrep_data_home_dir/var/lib/mysql/
wsrep_cluster_size3wsrep_cluster_size3wsrep_dbug_optionwsrep_dbug_option
wsrep_cluster_state_uuid556505cf-becc-11e7-8799-8ae35d8b57adwsrep_cluster_state_uuid556505cf-becc-11e7-8799-8ae35d8b57adwsrep_debugOFFwsrep_debugOFF
wsrep_cluster_statusPrimarywsrep_cluster_statusPrimarywsrep_desyncOFFwsrep_desyncOFF
wsrep_commit_oooe0wsrep_commit_oooe0wsrep_dirty_readsOFFwsrep_dirty_readsOFF
wsrep_commit_oool0.000001wsrep_commit_oool0wsrep_drupal_282555_workaroundOFFwsrep_drupal_282555_workaroundOFF
wsrep_commit_window1.011446wsrep_commit_window1wsrep_forced_binlog_formatNONEwsrep_forced_binlog_formatNONE
wsrep_connectedONwsrep_connectedONwsrep_gtid_domain_id0wsrep_gtid_domain_id0
wsrep_desync_count0wsrep_desync_count0wsrep_gtid_modeOFFwsrep_gtid_modeOFF
wsrep_evs_delayedwsrep_evs_delayedwsrep_load_data_splittingONwsrep_load_data_splittingON
wsrep_evs_evict_listwsrep_evs_evict_listwsrep_log_conflictsOFFwsrep_log_conflictsOFF
wsrep_evs_repl_latency0/0/0/0/0wsrep_evs_repl_latency0/0/0/0/0wsrep_max_ws_rows0wsrep_max_ws_rows0
wsrep_evs_stateOPERATIONALwsrep_evs_stateOPERATIONALwsrep_max_ws_size2147483647wsrep_max_ws_size2147483647
wsrep_flow_control_paused0.000266wsrep_flow_control_paused0.000264wsrep_mysql_replication_bundle0wsrep_mysql_replication_bundle0
wsrep_flow_control_paused_ns7836436956029wsrep_flow_control_paused_ns7836339878098wsrep_node_address192.168.10.13wsrep_node_address192.168.10.12
wsrep_flow_control_recv52306wsrep_flow_control_recv52337wsrep_node_incoming_addressAUTOwsrep_node_incoming_addressAUTO
wsrep_flow_control_sent17wsrep_flow_control_sent46986wsrep_node_nameprclsmariadb03wsrep_node_nameprclsmariadb02
wsrep_gcomm_uuid810736cf-c1be-11e7-b8e3-82ec0e916937wsrep_gcomm_uuida6f2a4a7-bee0-11e7-a8a7-2befee40d3a4wsrep_notify_cmdwsrep_notify_cmd
wsrep_incoming_addresses"192.168.10.11:3306192.168.10.12:3306192.168.10.13:3306"wsrep_incoming_addresses"192.168.10.11:3306192.168.10.12:3306192.168.10.13:3306"wsrep_onONwsrep_onON
wsrep_last_committed50683398wsrep_last_committed50683429wsrep_patch_versionwsrep_25.20wsrep_patch_versionwsrep_25.20
wsrep_local_bf_aborts59wsrep_local_bf_aborts0wsrep_provider/usr/lib/galera/libgalera_smm.sowsrep_provider/usr/lib/galera/libgalera_smm.so
wsrep_local_cached_downto50675404wsrep_local_cached_downto50675404
wsrep_provider_optionsbase_dir = /var/lib/mysql/; base_host = 192.168.10.13; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S;...wsrep_provider_optionsbase_dir = /var/lib/mysql/; base_host = 192.168.10.12; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs....
wsrep_local_cert_failures58wsrep_local_cert_failures0wsrep_recoverOFFwsrep_recoverOFF
wsrep_local_commits50662978wsrep_local_commits1wsrep_replicate_myisamOFFwsrep_replicate_myisamOFF
wsrep_local_index1wsrep_local_index2wsrep_restart_slaveOFFwsrep_restart_slaveOFF
wsrep_local_recv_queue0wsrep_local_recv_queue0wsrep_retry_autocommit1wsrep_retry_autocommit1
wsrep_local_recv_queue_avg0.008634wsrep_local_recv_queue_avg0.113087wsrep_slave_fk_checksONwsrep_slave_fk_checksON
wsrep_local_recv_queue_max30wsrep_local_recv_queue_max190wsrep_slave_uk_checksOFFwsrep_slave_uk_checksOFF
wsrep_local_recv_queue_min0wsrep_local_recv_queue_min0wsrep_slave_threads1wsrep_slave_threads1
wsrep_local_replays16wsrep_local_replays0wsrep_sst_auth**wsrep_sst_auth**
wsrep_local_send_queue0wsrep_local_send_queue0wsrep_sst_donorwsrep_sst_donor
wsrep_local_send_queue_avg0.002476wsrep_local_send_queue_avg0.000003wsrep_sst_donor_rejects_queriesOFFwsrep_sst_donor_rejects_queriesOFF
wsrep_local_send_queue_max167wsrep_local_send_queue_max2wsrep_sst_methodxtrabackupwsrep_sst_methodxtrabackup
wsrep_local_send_queue_min0wsrep_local_send_queue_min0wsrep_sst_receive_addressAUTOwsrep_sst_receive_addressAUTO
wsrep_local_state4wsrep_local_state4wsrep_start_position00000000-0000-0000-0000-000000000000:-1wsrep_start_position00000000-0000-0000-0000-000000000000:-1
wsrep_local_state_commentSyncedwsrep_local_state_commentSyncedwsrep_sync_wait0wsrep_sync_wait0
wsrep_local_state_uuid556505cf-becc-11e7-8799-8ae35d8b57adwsrep_local_state_uuid556505cf-becc-11e7-8799-8ae35d8b57ad
wsrep_protocol_version7wsrep_protocol_version7
wsrep_provider_nameGalerawsrep_provider_nameGalera
wsrep_provider_vendorCodership Oy <[email protected]>wsrep_provider_vendorCodership Oy <[email protected]>
wsrep_provider_version25.3.20(r3703)wsrep_provider_version25.3.20(r3703)
wsrep_readyONwsrep_readyON
wsrep_received827810wsrep_received51498818
wsrep_received_bytes3492240018wsrep_received_bytes866578987496
wsrep_repl_data_bytes801586481553wsrep_repl_data_bytes1399
wsrep_repl_keys725154316wsrep_repl_keys7
wsrep_repl_keys_bytes6969312016wsrep_repl_keys_bytes148
wsrep_repl_other_bytes0wsrep_repl_other_bytes0
wsrep_replicated50667876wsrep_replicated4
wsrep_replicated_bytes860983409659wsrep_replicated_bytes1803
wsrep_thread_count2wsrep_thread_count2

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.