|
|
| 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>
| |
| − | }}
| |
