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: Pending Release
    • 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';
      

        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