Difference between revisions of "SQWareRepository:mysql"

From Wiki_dbSQWare
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 
{{SQWareRepositoryPresSgbd|Mysql|database|instances|mys}}
 
{{SQWareRepositoryPresSgbd|Mysql|database|instances|mys}}
== Database content ==
+
== Specific database content for MySQL ==
{{SQWareRepositoryTables|tsqw_Repository|
 
<nowiki>Repository</nowiki>|
 
<pre>
 
/* 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));
 
 
 
</pre>
 
}}
 
  
{{SQWareRepositoryTables|tsqw_RepositoryOther|
+
{{SQWareRepositoryTables|tsqwmys_LogSlowQuery|
<nowiki>Repository other (passive node of cluster for example)</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
/* Repository autre (noeud passif de cluster par exemple)*/
+
Field Type
create table tsqw_RepositoryOther (
+
dbalias varchar(80)
mysql_sid varchar(30) not null,
+
gather_date datetime
virt_host_name varchar (64) not null,
+
status varchar(30)
host_name varchar(64) not null,
+
longquerytime int(11)
username  varchar(60) not null,
+
log_file varchar(800)
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;
 
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
{{SQWareRepositoryTables|tsqw_JobsExec|
+
{{SQWareRepositoryTables|tsqwmys_Parameters|
<nowiki>Jobs monitoring</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
/* Jobs monitoring */
+
Field Type
create table tsqw_JobsExec
+
dbalias varchar(80)
(
+
gather_date datetime
mysql_sid varchar(50) not null,
+
name varchar(255)
beginning datetime not null,
+
value varchar(4000)
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;
 
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
{{SQWareRepositoryTables|tsqw_VolFS|
+
{{SQWareRepositoryTables|tsqwmys_VolDb|
<nowiki>vol fs</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
/* vol fs */
+
Field Type
create table tsqw_VolFS (
+
dbalias varchar(80)
mysql_sid varchar(30) not null,
+
database_name varchar(255)
gather_date datetime not null,
+
gather_date datetime
host_name varchar (64) not null,
+
size_db decimal(12,2)
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));
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_VolFSThreshold|
 
<nowiki>Specific threshold for FS</nowiki>|
 
<pre>
 
/* 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));
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_VolFSThresholdDef|
 
<nowiki>Default threshold for FS</nowiki>|
 
<pre>
 
/* 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);
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_VolDb|
 
<nowiki>Volumetry of databases</nowiki>|
 
<pre>
 
/* 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));
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_VolInstance|
 
<nowiki>volumetry of instance</nowiki>|
 
<pre>
 
/* 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));
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_GenHisto|
 
<nowiki>Monthly informations </nowiki>|
 
<pre>
 
/* 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));
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_AlertFile|
 
<nowiki>Alert log location</nowiki>|
 
<pre>
 
/* 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));
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_LogSlowQuery|
 
<nowiki>Slow query</nowiki>|
 
<pre>
 
/* 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);
 
 
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_VolBackups|
 
<nowiki>Monitoring mysqldumps</nowiki>|
 
<pre>
 
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));
 
</pre>
 
}}
 
 
 
{{SQWareRepositoryTables|tsqw_VolBackupsExclude|
 
<nowiki>Exclude for check backup</nowiki>|
 
<pre>
 
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));
 
</pre>
 
}}
 
 
 
 
 
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeGlobalDb|
 
<nowiki>Global exclude of database for check backup db</nowiki>|
 
<pre>
 
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())
 
;
 
 
</pre>
 
</pre>
 
}}
 
}}
  
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeDb|
+
{{SQWareRepositoryTables|tsqwmys_VolTables|
<nowiki>Specific exclude of database for check backup db</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
create table tsqw_VolBackupsExcludeDb (
+
Field Type
  mysql_sid varchar(30)     not null,
+
dbalias varchar(80)
  database_name varchar(30)     not null,
+
gather_date datetime
  dbaname      varchar(50)     not null,
+
table_schema varchar(64)
  comments      varchar(50)     not null,
+
table_name varchar(64)
  upd_date      datetime
+
engine varchar(64)
)
+
table_rows int(11)
engine InnoDb
+
data_length decimal(12,2)
;
+
index_length decimal(12,2)
grant all on tsqw_VolBackupsExcludeDb to inddba;
 
alter table tsqw_VolBackupsExcludeDb add ( constraint isqw_tsqw_VolBackupsExcludeDb_pk primary key (mysql_sid,database_name));
 
 
</pre>
 
</pre>
 
}}
 
}}

Latest revision as of 18:13, 8 August 2015

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 tsqwmys_% or isqwmys_% are specific for Mysql.
Installation guide for SQWareRepository for Mysql

Naming convention

All specific objects for Mysql are named with prefixes:

  • tsqwmys_% : for tables
  • isqwmys_% : for index
  • isqwmys_%_u : for uniques
  • isqwmys_%_pk : for primary key


Nuvola apps important.png
Warning:
Do not change the standard tables of the tool. For your custom objects, use another name convention than dbSQWare.

This will facilitate version upgrades.


Specific database content for MySQL

tsqwmys_LogSlowQuery

This table contains : ...
Desc of tsqwmys_LogSlowQuery :

Field Type
dbalias varchar(80)
gather_date datetime
status varchar(30)
longquerytime int(11)
log_file varchar(800)


tsqwmys_Parameters

This table contains : ...
Desc of tsqwmys_Parameters :

Field Type
dbalias varchar(80)
gather_date datetime
name varchar(255)
value varchar(4000)


tsqwmys_VolDb

This table contains : ...
Desc of tsqwmys_VolDb :

Field Type
dbalias varchar(80)
database_name varchar(255)
gather_date datetime
size_db decimal(12,2)


tsqwmys_VolTables

This table contains : ...
Desc of tsqwmys_VolTables :

Field Type
dbalias varchar(80)
gather_date datetime
table_schema varchar(64)
table_name varchar(64)
engine varchar(64)
table_rows int(11)
data_length decimal(12,2)
index_length decimal(12,2)