SQWareRepository:mysql

Presentation

It is the repository module, and stores rdbms indicators for all RDBMS.
It is a MySQL database.
Objects prefix by tsqw_% or isqw_% are generic.
Objects prefix by tsqw{{{4}}}_% or isqw{{{4}}}_% are specific for Mysql.
Installation guide for SQWareRepository for Mysql

Naming convention

All specific objects for Mysql are named with prefixes:

• tsqw{{{4}}}_% : for tables
• isqw{{{4}}}_% : for index
• isqw{{{4}}}_%_u : for uniques
• isqw{{{4}}}_%_pk : for primary key

Database content

tsqw_Repository

This table contains : Repository
Desc of tsqw_Repository :

/* Repository */
create table tsqw_Repository (
 mysql_sid varchar(30) not null,
 virt_host_name varchar (64) not null,
 host_name varchar (64) not null,
 username  varchar (60) not null,
 port integer not null,
 comments varchar(500),
 contact varchar(100),
 status varchar(10) default 'ON',
 client varchar (60) default 'N.A',
 upd_date datetime,
 env  char(3)
)
engine InnoDb
;
grant all on tsqw_Repository to inddba;
alter table tsqw_Repository add ( constraint isqw_Repository_pk primary key (mysql_sid));

tsqw_RepositoryOther

This table contains : Repository other (passive node of cluster for example)
Desc of tsqw_RepositoryOther :

/* Repository autre (noeud passif de cluster par exemple)*/
create table tsqw_RepositoryOther (
 mysql_sid varchar(30) not null,
 virt_host_name varchar (64) not null,
 host_name varchar(64) not null,
 username  varchar(60) not null,
 port integer not null,
 comments varchar(500),
 contact varchar(100),
 status varchar(10) default 'XXX',
 client varchar (60) default 'N.A',
 upd_date datetime,
 env  char(3)
)
engine InnoDb
;
grant all on tsqw_RepositoryOther to inddba;

tsqw_JobsExec

This table contains : Jobs monitoring
Desc of tsqw_JobsExec :

/* Jobs monitoring */
create table tsqw_JobsExec
(
 mysql_sid varchar(50) not null,
 beginning datetime not null,
 script char(80) not null,
 parameters varchar(400) not null,
 end datetime,
 duration char(8),
 status integer
)
engine InnoDb
;
alter table tsqw_JobsExec add ( constraint isqw_JobsExec_pk primary key (mysql_sid, beginning, script) );
grant all on tsqw_JobsExec to inddba;

tsqw_VolFS

This table contains : vol fs
Desc of tsqw_VolFS :

/* vol fs */
create table tsqw_VolFS (
 mysql_sid varchar(30) not null,
 gather_date datetime not null,
 host_name varchar (64) not null,
 filesystem varchar (200) not null,
 size_fs integer not null,
 used integer not null,
 free integer not null,
 rate integer not null,
 mount varchar (200)
)
engine InnoDb
;
grant all on tsqw_VolFS to inddba;
alter table tsqw_VolFS add ( constraint isqw_VolFS_pk primary key (mysql_sid, gather_date,mount));

tsqw_VolFSThreshold

This table contains : Specific threshold for FS
Desc of tsqw_VolFSThreshold :

/* Specific threshold for FS */
create table tsqw_VolFSThreshold (
 mysql_sid varchar(30) not null,
 mount varchar (200),
 threshold_c integer not null,
 threshold_w integer not null
)
engine InnoDb
;
grant all on tsqw_VolFSThreshold to inddba;
alter table tsqw_VolFSThreshold add ( constraint isqw_VolFSThreshold_pk primary key (mysql_sid, mount));

tsqw_VolFSThresholdDef

This table contains : Default threshold for FS
Desc of tsqw_VolFSThresholdDef :

/* Default threshold for FS */
create table tsqw_VolFSThresholdDef (
 size_fs integer not null,
 threshold_c integer not null,
 threshold_w integer not null
)
engine InnoDb
;
grant all on tsqw_VolFSThresholdDef to inddba;
alter table tsqw_VolFSThresholdDef add ( constraint isqw_VolFSThreshold_pk primary key (size_fs));

insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (2048, 90, 80);
insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (10240, 92, 85);
insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (20480, 95, 90);
insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (102400000, 99, 97);

tsqw_VolDb

This table contains : Volumetry of databases
Desc of tsqw_VolDb :

/* volumetry of databases */
create table tsqw_VolDb (
 mysql_sid varchar(30) not null,
 database_name varchar(80) not null,
 gather_date datetime not null,
 size_db integer
)
engine InnoDb
;
grant all on tsqw_VolDb to inddba;
alter table tsqw_VolDb add ( constraint isqw_VolDb_pk primary key (mysql_sid, database_name, gather_date));

tsqw_VolInstance

This table contains : volumetry of instance
Desc of tsqw_VolInstance :

/* volumetry of instance */
create table tsqw_VolInstance (
 mysql_sid varchar(30) not null,
 gather_date datetime not null,
 size_instance integer,
 maj_version integer,
 version varchar(30)
)
engine InnoDb
;
grant all on tsqw_VolInstance to inddba;
alter table tsqw_VolInstance add ( constraint isqw_VolInstance_pk primary key (mysql_sid, gather_date));

tsqw_GenHisto

This table contains : Monthly informations
Desc of tsqw_GenHisto :

/* Monthly informations */
create table tsqw_GenHisto (
 mysql_sid varchar(30) not null,
 month datetime not null,
 gather_date datetime not null,
 size_instance integer,
 maj_version integer,
 client varchar (60),
 env  char(3)
)
engine InnoDb
;
grant all on tsqw_GenHisto to inddba;
alter table tsqw_GenHisto add ( constraint isqw_GenHisto_pk primary key (mysql_sid, month));

tsqw_AlertFile

This table contains : Alert log location
Desc of tsqw_AlertFile :

/* Alert log location*/
create table tsqw_AlertFile  (
 mysql_sid varchar(30) not null,
 gather_date datetime not null,
 alert_file  VARCHAR (800)
)
engine InnoDb
;
grant all on tsqw_AlertFile to inddba;
alter table tsqw_AlertFile add ( constraint isqw_AlertFile_pk primary key (mysql_sid));

tsqw_LogSlowQuery

This table contains : Slow query
Desc of tsqw_LogSlowQuery :

/* Slow query */
create table tsqw_LogSlowQuery  (
 mysql_sid varchar(30) not null,
 gather_date datetime not null,
 status varchar(30) not null,
 longquerytime integer not null,
 log_file  VARCHAR (800)
)
engine InnoDb
;
grant all on tsqw_LogSlowQuery to inddba;
create index isqw_LogSlowQuery on tsqw_LogSlowQuery (mysql_sid,gather_date);

tsqw_VolBackups

This table contains : Monitoring mysqldumps
Desc of tsqw_VolBackups :

create table tsqw_VolBackups (
  mysql_sid varchar(30)     not null,
  database_name varchar(80) not null,
  beginning datetime   not null,
  bck_type varchar(50)     not null,
  end_trt datetime   not null,
  duration char(8)         not null,
  size_bck integer
)
engine InnoDb
;
grant all on tsqw_VolBackups to inddba;
alter table tsqw_VolBackups add ( constraint isqw_VolBackups_pk primary key (mysql_sid, database_name, beginning));

tsqw_VolBackupsExclude

This table contains : Exclude for check backup
Desc of tsqw_VolBackupsExclude :

create table tsqw_VolBackupsExclude (
  mysql_sid varchar(30)     not null,
  dbaname varchar(50)     not null,
  comments varchar(50)     not null,
  upd_date datetime
)
engine InnoDb
;
grant all on tsqw_VolBackupsExclude to inddba;
alter table tsqw_VolBackupsExclude add ( constraint isqw_VolBackupsExclude_pk primary key (mysql_sid));

tsqw_VolBackupsExcludeGlobalDb

This table contains : Global exclude of database for check backup db
Desc of tsqw_VolBackupsExcludeGlobalDb :

create table tsqw_VolBackupsExcludeGlobalDb (
  database_name varchar(30)     not null,
  dbaname       varchar(50)     not null,
  comments      varchar(50)     not null,
  upd_date      datetime
)
engine InnoDb
;
grant all on tsqw_VolBackupsExcludeGlobalDb to inddba;
alter table tsqw_VolBackupsExcludeGlobalDb add ( constraint isqw_VolBackupsExcludeGlobalDb_pk primary key (database_name));

insert into tsqw_VolBackupsExcludeGlobalDb values ('information_schema','MP','Init edt',now())
;

tsqw_VolBackupsExcludeDb

This table contains : Specific exclude of database for check backup db
Desc of tsqw_VolBackupsExcludeDb :

create table tsqw_VolBackupsExcludeDb (
  mysql_sid varchar(30)     not null,
  database_name varchar(30)     not null,
  dbaname       varchar(50)     not null,
  comments      varchar(50)     not null,
  upd_date      datetime
)
engine InnoDb
;
grant all on tsqw_VolBackupsExcludeDb to inddba;
alter table tsqw_VolBackupsExcludeDb add ( constraint isqw_tsqw_VolBackupsExcludeDb_pk primary key (mysql_sid,database_name));