(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>
}}
}}
Presentation It is the repository module, and stores rdbms indicators for all RDBMS. 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 tsqwmys_LogSlowQuery This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
status varchar(30)
longquerytime int(11)
log_file varchar(800)
tsqwmys_Parameters This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
name varchar(255)
value varchar(4000)
tsqwmys_VolDb This table contains :
...
Field Type
dbalias varchar(80)
database_name varchar(255)
gather_date datetime
size_db decimal(12,2)
tsqwmys_VolTables This table contains :
...
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)
