Uploaded image for project: 'Percona XtraBackup'
  1. Percona XtraBackup
  2. PXB-1627

Support obtaining binary log coordinates from performance_schema.log_status

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0-2-alpha2
    • Fix Version/s: 8.0-2-alpha2
    • Component/s: None
    • Labels:

      Description

      Here is an example information from p_s.log_status table:

          SERVER_UUID: e1a4c2ec-b7a5-11e8-ab6f-9360249580c2
                LOCAL: {"gtid_executed": "", "binary_log_file": "mysql-bin.000002", "binary_log_position": 2541}
          REPLICATION: {"channels": [{"channel_name": "", "relay_log_file": "mysql-relay-bin.000003", "relay_log_position": 2523}]}
      STORAGE_ENGINES: {"InnoDB": {"LSN": 18926419, "LSN_checkpoint": 18602312}}
      

      Previous xtrabackup when backing up MySQL:

      1. FLUSH TABLES WITH READ LOCK to stop DDL, DML and freeze binary log and LSN.
      2. copy MyISAM tables
      3. if --slave-info, SHOW SLAVE STATUS and save master relay log position and file name
      4. run SHOW MASTER STATUS, save executed GTID set or binary log file and position (depending on GTID mode)
      5. finish redo log copying and save LSN
      6. UNLOCK TABLES

      when backing up PS:

      1. LOCK TABLES FOR BACKUP to stop all DDL and DML on transactional tables.
      2. copy MyISAM tables
      3. LOCK BINLOG FOR BACKUP to freeze binary log and LSN
      4. if --slave-info, SHOW SLAVE STATUS and save master relay log position and file name
      5. run SHOW MASTER STATUS, save executed GTID set or binary log file and position (depending on GTID mode)
      6. finish redo log copying and save LSN
      7. UNLOCK BINLOG
      8. UNLOCK TABLES

      Log copying and applying threads need to be changed to be able to stop at specific LSN. After that wew xtrabackup behaviour will become as follows.

      MySQL:

      1. FLUSH TABLES WITH READ LOCK (if MyISAM tables, or if --slave-info without GTID and auto-positioning)
      2. SELECT * FROM p_s.log_status, save LSN and binlog coordinates
      3. if --slave-info, then SHOW SLAVE STATUS and save master relay log position and file name
      4. stop log copying at LSN taken from p_s.log_status
      5. UNLOCK TABLES

      PS:

      1. LOCK TABLES FOR BACKUP (when MyISAM tables) or FTWRL (when --slave-info without GTID and auto-positioning)
      2. if --slave-info, then SHOW SLAVE STATUS and save master relay log position and file name
      3. SELECT * FROM p_s.log_status, save LSN and binlog coordinates
      4. stop log copying at LSN taken from p_s.log_status
      5. UNLOCK TABLES

      FTWRL is only needed in PS when --slave-info is specified and GTID mode is OFF or when GTID auto positioning is OFF. This will be fixed with PS-4843.

      New privilege BACKUP_ADMIN is needed by xtrabackup along with SELECT on performance_schema.log_status.

      Example:

      GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO pxb@'localhost';
      GRANT SELECT ON performance_schema.log_status TO pxb@'localhost';
      

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                sergei.glushchenko Sergei Glushchenko
                Reporter:
                sergei.glushchenko Sergei Glushchenko
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 week, 3 days, 5 hours, 12 minutes
                  1w 3d 5h 12m