“Show” Output Store Into Table

Is there any way through which can I store my “show slave status” output into any permanent/temporary table. After storing output into table I will took some decisions against their error number.

Disclaimer: The below solution is probably unnecessarily complicated – the obvious solution to this would perhaps involve a cron job or maybe a database event running at some interval to execute SHOW SLAVE STATUS; and extracting the output into your table.

Nevertheless, here is what I think is an interesting solution which gives you instant access to the SHOW SLAVE STATUS output through a SELECT query. However, I’ve only been tested this with MariaDB (version 10.1.32), and it may or may not be portable to MySQL. It requires the CONNECT storage engine, and I’m not sure you can get that for MySQL.

First install the connect storage engine package in your OS. Then in the mysql client:

INSTALL SONAME 'ha_connect';
CREATE DATABASE IF NOT EXISTS `_` 
  DEFAULT CHARACTER SET = 'utf8' 
  DEFAULT COLLATE = 'utf8_general_ci';
USE _
CREATE OR REPLACE SERVER `_` 
  FOREIGN DATA WRAPPER MYSQL OPTIONS ( 
    HOST 'localhost', 
    USER 'root', PASSWORD 'your_password_here', 
    PORT 3306, 
    DATABASE '_' 
  )
;
CREATE TABLE show_slave_status 
  ENGINE = CONNECT 
  TABLE_TYPE = MYSQL 
  SRCDEF = 'SHOW SLAVE STATUS' 
  CONNECTION = '_';

Now you can get all the values you usually get from SHOW SLAVE STATUS with a SELECT: SELECT * FROM _.show_slave_status;

Credits: I borrowed almost all of this from Federico Razzoli’s materialize.sql.