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