SQWareRepository:mysql: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
Replaced content with '{{SQWareRepositoryPresSgbd|Mysql|database|instances|mys}} == Specific database content for MySQL =='
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|
<nowiki>Repository other (passive node of cluster for example)</nowiki>|
<pre>
/* 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;
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_JobsExec|
<nowiki>Jobs monitoring</nowiki>|
<pre>
/* 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;
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolFS|
<nowiki>vol fs</nowiki>|
<pre>
/* 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));
 
</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>
}}
 
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeDb|
<nowiki>Specific exclude of database for check backup db</nowiki>|
<pre>
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));
</pre>
}}

Revision as of 19:03, 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


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

Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:mysql&oldid=7254"