|
|
(5 intermediate revisions by the same user not shown) |
Line 1: |
Line 1: |
− | {{SQWareRepositoryPresSgbd|Mysql|d'une database|instances}} | + | {{SQWareRepositoryPresSgbd|Mysql|database|instances|mys}} |
− | == Contenu de la database == | + | == 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 autre (noeud passif de cluster par exemple)</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|tsqwmys_LogSlowQuery| |
− | }}
| + | <nowiki>...</nowiki>| |
− | | |
− | {{SQWareRepositoryTables|tsqw_VolFS| | |
− | <nowiki>vol fs</nowiki>| | |
| <pre> | | <pre> |
− | /* vol fs */
| + | Field Type |
− | create table tsqw_VolFS (
| + | dbalias varchar(80) |
− | mysql_sid varchar(30) not null,
| + | gather_date datetime |
− | gather_date datetime not null,
| + | status varchar(30) |
− | host_name varchar (64) not null,
| + | longquerytime int(11) |
− | filesystem varchar (200) not null,
| + | log_file varchar(800) |
− | 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> | | </pre> |
| }} | | }} |
| | | |
− | {{SQWareRepositoryTables|tsqw_VolFSThreshold| | + | {{SQWareRepositoryTables|tsqwmys_Parameters| |
− | <nowiki>threshold spécifique pour FS</nowiki>| | + | <nowiki>...</nowiki>| |
| <pre> | | <pre> |
− | /* threshold spécifique pour FS */
| + | Field Type |
− | create table tsqw_VolFSThreshold (
| + | dbalias varchar(80) |
− | mysql_sid varchar(30) not null,
| + | gather_date datetime |
− | mount varchar (200),
| + | name varchar(255) |
− | threshold_c integer not null,
| + | value varchar(4000) |
− | 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> | | </pre> |
| }} | | }} |
| | | |
− | {{SQWareRepositoryTables|tsqw_VolFSThresholdDef| | + | {{SQWareRepositoryTables|tsqwmys_VolDb| |
− | <nowiki>threshold par défaut pour FS</nowiki>| | + | <nowiki>...</nowiki>| |
| <pre> | | <pre> |
− | /* threshold par défaut pour FS */
| + | Field Type |
− | create table tsqw_VolFSThresholdDef (
| + | dbalias varchar(80) |
− | size_fs integer not null,
| + | database_name varchar(255) |
− | threshold_c integer not null,
| + | gather_date datetime |
− | threshold_w integer not null
| + | size_db decimal(12,2) |
− | )
| |
− | 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> | | </pre> |
| }} | | }} |
| | | |
− | {{SQWareRepositoryTables|tsqw_VolDb| | + | {{SQWareRepositoryTables|tsqwmys_VolTables| |
− | <nowiki>volumétrie databases</nowiki>| | + | <nowiki>...</nowiki>| |
| <pre> | | <pre> |
− | /* volumétrie databases */
| + | Field Type |
− | create table tsqw_VolDb (
| + | dbalias varchar(80) |
− | mysql_sid varchar(30) not null,
| + | gather_date datetime |
− | database_name varchar(80) not null,
| + | table_schema varchar(64) |
− | gather_date datetime not null,
| + | table_name varchar(64) |
− | size_db integer
| + | engine varchar(64) |
− | )
| + | table_rows int(11) |
− | engine InnoDb
| + | data_length decimal(12,2) |
− | ;
| + | index_length decimal(12,2) |
− | 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>volumétrie instance</nowiki>|
| |
− | <pre>
| |
− | /* volumétrie 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>informations mensuelles</nowiki>|
| |
− | <pre>
| |
− | /* informations mensuelles */
| |
− | 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> | | </pre> |
| }} | | }} |