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

Restoring files to Master and Replicas bypassing Binary Log

    XMLWordPrintable

Details

    Description

      I have a situation where I have several very large static tables that I wish to restore after a disaster or other event where the tables need to be restored, but at the same time, not impact production.

      When using the mysql or mysqlimport commands to restore these files, the Slaves immediately start getting very far behind the master due to the size of the tables. Which causes a log of concern.

      What I have found is a king of workaround due to some recent 10.5.x changes, but it does not really help as it appears to not be working as expected. For the purposes of this discussion, assume the 'master' has already had it's tables restored. Here the pseudocode:

      $files = array('/tmp/table1.sql', '/tmp/table2.sql', '/tmp/table3.sql');
      $replicas = array('host1', 'host2', 'host3');
      $master = array('alreadydone')

      foreach($replicas as $host)
      $cnn_id = db_connect($host);
      db_exec('SET @@session.sql_log_bin=0', $cnn_id);
      db_exec('SET @@global.read_only=0', $cnn_id);

      foreach($files as $file-table)

      { db_exec("SOURCE $file-table", $cnn_id); }

      db_exec('SET @@session.sql_log_bin=1', $cnn_id);
      db_exec('SET @@global.read_only=1', $cnn_id);
      }

      The problem I have is that the 'SOURCE' command is notoriously unreliable. I'm getting 1064 errors currently, and just scratching my head. The files are created using:

      mysqldump [options] database table > table.sql

      It would be nice if there were an option to run the mysqlimport or mysql command to bypass the binary log, and even possibly to take a list of hosts and perform a three way insert while avoiding the binary log and the overhead it would present.

      Attachments

        Activity

          People

            Unassigned Unassigned
            TheWitness Larry Adams
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.