toshniba toshniba - 7 months ago 15
SQL Question

mysql: redirect SHOW STATUS into table

Is there a way to directly insert the output of

SHOW STATUS
into a table in mysql?

INSERT INTO showstatus (name, value) SHOW STATUS
or similar do not work.

Maybe the only alternative is to export query contents into textfile and then load contents of that file into the table?

Or maybe there is another way to continuously (daily) track mysql's status variables?

Answer

You could use INFORMATION_SCHEMA GLOBAL_STATUS/SESSION_STATUS tables:

CREATE TABLE showstatus(ID INT PRIMARY KEY AUTO_INCREMENT,
                        VARIABLE_NAME VARCHAR(1000),
                        VARIABLE_VALUE VARCHAR(1000),
                        tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO showstatus (variable_name, variable_value)
SELECT variable_name, variable_value
FROM  INFORMATION_SCHEMA.GLOBAL_STATUS 
UNION ALL 
SELECT variable_name, variable_value
FROM INFORMATION_SCHEMA.SESSION_STATUS;

SqlFiddleDemo

Note that you should add column to track timestamp when snapshot was created.

Comments