sys_replication_status.sql

use sys;
DROP VIEW IF EXISTS replication_status;
CREATE
  ALGORITHM = MERGE
  SQL SECURITY INVOKER
  VIEW replication_status
AS
SELECT
  concat(conn_status.channel_name, ' (', worker_id,')') AS channel,
  conn_status.service_state AS io_state,
  applier_status.service_state AS sql_state,
  format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0" ,
      abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
      timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,  
  format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
                           LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,
  format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
                           LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,
  format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
                           LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,
  conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,
  applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction                             
FROM
  performance_schema.replication_connection_status AS conn_status
JOIN performance_schema.replication_applier_status_by_worker AS applier_status
  ON applier_status.channel_name = conn_status.channel_name
  ;


DROP VIEW IF EXISTS x$replication_status;
CREATE
  ALGORITHM = MERGE
  SQL SECURITY INVOKER
  VIEW x$replication_status
AS
SELECT
  conn_status.channel_name AS channel_name,
  worker_id,
  conn_status.service_state AS io_state,
  applier_status.service_state AS sql_state,
  CAST((if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0" ,
      abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
      timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now())))))*1000000000000) AS UNSIGNED INTEGER) latency,
  CAST(((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
                           LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)*1000000000000) AS UNSIGNED INTEGER) transport_time,
  CAST(((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
                           LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP)*1000000000000) AS UNSIGNED INTEGER) time_to_relay_log,
  CAST(((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
                           LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP)*1000000000000) AS UNSIGNED INTEGER) apply_time,      
  conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,
  applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction
FROM
  performance_schema.replication_connection_status AS conn_status
JOIN performance_schema.replication_applier_status_by_worker AS applier_status
  ON applier_status.channel_name = conn_status.channel_name
  ;