Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27947

Replication Seconds_Behind_Master strange values (0) while syncing

    XMLWordPrintable

Details

    Description

      Hello,
      I created a remote slave for a cluster mariadb.
      All works fine but i would like to survey the replication ( zabbix )
      The value of the variable is very strange.
      Why the variable return 0 between correct values.
      To reproduce à stop the slave for a long time and i restart the slave
      I use a script that show every second this variable

       
      host=localhost
      user=xxx
      password=xxx
       
      while true;
      do
          seconds=`mysql -h $host -u $user -p$password -e 'show slave status\G' 2>/dev/null |grep Seconds`
          echo `date "+%Y-%m-%d %H:%M:%S"`$seconds
          sleep 1
      done
      
      

      the output :

      2022-02-25 09:01:48 Seconds_Behind_Master: 0
      2022-02-25 09:01:49 Seconds_Behind_Master: 0
      2022-02-25 09:01:50 Seconds_Behind_Master: 0
      2022-02-25 09:01:51 Seconds_Behind_Master: 59118
      2022-02-25 09:01:52 Seconds_Behind_Master: 59050
      2022-02-25 09:01:53 Seconds_Behind_Master: 0
      2022-02-25 09:01:54 Seconds_Behind_Master: 0
      2022-02-25 09:01:55 Seconds_Behind_Master: 0
      2022-02-25 09:01:56 Seconds_Behind_Master: 0
      2022-02-25 09:01:57 Seconds_Behind_Master: 58379
      2022-02-25 09:01:58 Seconds_Behind_Master: 0
      2022-02-25 09:01:59 Seconds_Behind_Master: 58145
      2022-02-25 09:02:00 Seconds_Behind_Master: 0
      2022-02-25 09:02:01 Seconds_Behind_Master: 57864
      2022-02-25 09:02:02 Seconds_Behind_Master: 0
      2022-02-25 09:02:03 Seconds_Behind_Master: 0
      2022-02-25 09:02:04 Seconds_Behind_Master: 57462
      2022-02-25 09:02:05 Seconds_Behind_Master: 0
      2022-02-25 09:02:06 Seconds_Behind_Master: 0
      2022-02-25 09:02:07 Seconds_Behind_Master: 57316
      2022-02-25 09:02:08 Seconds_Behind_Master: 0
      2022-02-25 09:02:09 Seconds_Behind_Master: 0
      2022-02-25 09:02:10 Seconds_Behind_Master: 57006
      2022-02-25 09:02:11 Seconds_Behind_Master: 56995
      2022-02-25 09:02:12 Seconds_Behind_Master: 0
      2022-02-25 09:02:13 Seconds_Behind_Master: 56655
      2022-02-25 09:02:14 Seconds_Behind_Master: 0
      2022-02-25 09:02:15 Seconds_Behind_Master: 56470
      2022-02-25 09:02:16 Seconds_Behind_Master: 0
      2022-02-25 09:02:17 Seconds_Behind_Master: 0
      2022-02-25 09:02:18 Seconds_Behind_Master: 56094
      2022-02-25 09:02:19 Seconds_Behind_Master: 55983
      2022-02-25 09:02:20 Seconds_Behind_Master: 0
      2022-02-25 09:02:21 Seconds_Behind_Master: 55896
      2022-02-25 09:02:22 Seconds_Behind_Master: 0
      2022-02-25 09:02:23 Seconds_Behind_Master: 0
      2022-02-25 09:02:24 Seconds_Behind_Master: 0
      2022-02-25 09:02:25 Seconds_Behind_Master: 55591
      2022-02-25 09:02:26 Seconds_Behind_Master: 55553
      2022-02-25 09:02:27 Seconds_Behind_Master: 55445
      2022-02-25 09:02:29 Seconds_Behind_Master: 0
      2022-02-25 09:02:30 Seconds_Behind_Master: 0
      2022-02-25 09:02:31 Seconds_Behind_Master: 55146
      2022-02-25 09:02:32 Seconds_Behind_Master: 0
      2022-02-25 09:02:33 Seconds_Behind_Master: 54929
      2022-02-25 09:02:34 Seconds_Behind_Master: 54798
      2022-02-25 09:02:35 Seconds_Behind_Master: 54731
      2022-02-25 09:02:36 Seconds_Behind_Master: 0
      2022-02-25 09:02:37 Seconds_Behind_Master: 54472
      2022-02-25 09:02:38 Seconds_Behind_Master: 54421
      2022-02-25 09:02:39 Seconds_Behind_Master: 54270
      2022-02-25 09:02:40 Seconds_Behind_Master: 0
      2022-02-25 09:02:41 Seconds_Behind_Master: 0
      2022-02-25 09:02:42 Seconds_Behind_Master: 0
      2022-02-25 09:02:43 Seconds_Behind_Master: 0
      2022-02-25 09:02:44 Seconds_Behind_Master: 53867
      2022-02-25 09:02:45 Seconds_Behind_Master: 0
      2022-02-25 09:02:46 Seconds_Behind_Master: 0
      2022-02-25 09:02:47 Seconds_Behind_Master: 53656
      2022-02-25 09:02:48 Seconds_Behind_Master: 0
      2022-02-25 09:02:49 Seconds_Behind_Master: 0
      2022-02-25 09:02:50 Seconds_Behind_Master: 53324
      2022-02-25 09:02:51 Seconds_Behind_Master: 0
      2022-02-25 09:02:52 Seconds_Behind_Master: 0
      2022-02-25 09:02:53 Seconds_Behind_Master: 0
      2022-02-25 09:02:54 Seconds_Behind_Master: 0
      2022-02-25 09:02:55 Seconds_Behind_Master: 52819
      2022-02-25 09:02:56 Seconds_Behind_Master: 52682
      2022-02-25 09:02:57 Seconds_Behind_Master: 52618
      2022-02-25 09:02:58 Seconds_Behind_Master: 52515

      Why the variable return 0 between correct values.
      So impossible to use this variable to survey the replication state

      +Master conf +

      wsrep_gtid_mode          = on
      wsrep_gtid_domain_id     = 0
      server-id                = 01
      gtid_domain_id           = 3
      log_slave_updates        = on
      log-bin              = /var/log/mysql/binlog
      log-bin-index        = /var/log/mysql/binlog.index
      binlog_format=ROW
      expire-logs-days                         = 3
      max-binlog-size                          = 2048M
      sync-binlog                              = 0
      binlog-stmt-cache-size                   = 128K
      binlog-cache-size                        = 256K
      

      Slave conf

      server-id                = 02
      gtid_domain_id           = 9
      log_slave_updates=1
      relay-log-index = /var/log/mysql/slave-relay-bin.index
      relay-log       = /var/log/mysql/slave-relay-bin
      log-bin              = /var/log/mysql/binlog
      log-bin-index        = /var/log/mysql/binlog.index
      binlog_format=ROW
      expire-logs-days                         = 3
      max-binlog-size                          = 2048M
      sync-binlog                              = 0
      binlog-stmt-cache-size                   = 128K
      binlog-cache-size                        = 256K
      slave_net_timeout=60
      

      Replication is done by binary position (not gtid )

      Thanks

      Attachments

        Activity

          People

            bnestere Brandon Nesterenko
            slevieux Levieux stéphane
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.