Last access of a DB in Mysql











up vote
0
down vote

favorite












I have "inherited" a collection of Mysql DBs (~900) and I would like to know which of them are actually being used. I looked around and I can find some commands like:



SELECT from_unixtime(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='MY_DB' GROUP BY TABLE_SCHEMA;


but this does not really tell me if MY_DB is being accessed by some web service or users, right? It only informs about when it was last updated, unless I got it wrong. If so, is there a more accurate way to find out the last access of a DB?



Thank you!






mysql







share|improve this question






















  • enable general query log and then you can monitor it. Ref: dev.mysql.com/doc/refman/8.0/en/query-log.html
    – Madhur Bhaiya
    Nov 7 at 17:44










  • Yes but, if this is not enabled by now, will I have retrospective info? Or it counts AFTER I enable it?
    – bioplanet
    Nov 7 at 18:45










  • No, it will not have retrospective info. Also, you will need to setup some auto-cleanup for the logs also (after getting relevant summary points out of it); otherwise it gets big pretty fast
    – Madhur Bhaiya
    Nov 8 at 8:03

















up vote
0
down vote

favorite












I have "inherited" a collection of Mysql DBs (~900) and I would like to know which of them are actually being used. I looked around and I can find some commands like:



SELECT from_unixtime(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='MY_DB' GROUP BY TABLE_SCHEMA;


but this does not really tell me if MY_DB is being accessed by some web service or users, right? It only informs about when it was last updated, unless I got it wrong. If so, is there a more accurate way to find out the last access of a DB?



Thank you!






mysql







share|improve this question






















  • enable general query log and then you can monitor it. Ref: dev.mysql.com/doc/refman/8.0/en/query-log.html
    – Madhur Bhaiya
    Nov 7 at 17:44










  • Yes but, if this is not enabled by now, will I have retrospective info? Or it counts AFTER I enable it?
    – bioplanet
    Nov 7 at 18:45










  • No, it will not have retrospective info. Also, you will need to setup some auto-cleanup for the logs also (after getting relevant summary points out of it); otherwise it gets big pretty fast
    – Madhur Bhaiya
    Nov 8 at 8:03















up vote
0
down vote

favorite









up vote
0
down vote

favorite











I have "inherited" a collection of Mysql DBs (~900) and I would like to know which of them are actually being used. I looked around and I can find some commands like:



SELECT from_unixtime(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='MY_DB' GROUP BY TABLE_SCHEMA;


but this does not really tell me if MY_DB is being accessed by some web service or users, right? It only informs about when it was last updated, unless I got it wrong. If so, is there a more accurate way to find out the last access of a DB?



Thank you!






mysql







share|improve this question













I have "inherited" a collection of Mysql DBs (~900) and I would like to know which of them are actually being used. I looked around and I can find some commands like:



SELECT from_unixtime(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='MY_DB' GROUP BY TABLE_SCHEMA;


but this does not really tell me if MY_DB is being accessed by some web service or users, right? It only informs about when it was last updated, unless I got it wrong. If so, is there a more accurate way to find out the last access of a DB?



Thank you!






mysql




mysql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 7 at 15:02









bioplanet

132




132












  • enable general query log and then you can monitor it. Ref: dev.mysql.com/doc/refman/8.0/en/query-log.html
    – Madhur Bhaiya
    Nov 7 at 17:44










  • Yes but, if this is not enabled by now, will I have retrospective info? Or it counts AFTER I enable it?
    – bioplanet
    Nov 7 at 18:45










  • No, it will not have retrospective info. Also, you will need to setup some auto-cleanup for the logs also (after getting relevant summary points out of it); otherwise it gets big pretty fast
    – Madhur Bhaiya
    Nov 8 at 8:03




















  • enable general query log and then you can monitor it. Ref: dev.mysql.com/doc/refman/8.0/en/query-log.html
    – Madhur Bhaiya
    Nov 7 at 17:44










  • Yes but, if this is not enabled by now, will I have retrospective info? Or it counts AFTER I enable it?
    – bioplanet
    Nov 7 at 18:45










  • No, it will not have retrospective info. Also, you will need to setup some auto-cleanup for the logs also (after getting relevant summary points out of it); otherwise it gets big pretty fast
    – Madhur Bhaiya
    Nov 8 at 8:03


















enable general query log and then you can monitor it. Ref: dev.mysql.com/doc/refman/8.0/en/query-log.html
– Madhur Bhaiya
Nov 7 at 17:44




enable general query log and then you can monitor it. Ref: dev.mysql.com/doc/refman/8.0/en/query-log.html
– Madhur Bhaiya
Nov 7 at 17:44












Yes but, if this is not enabled by now, will I have retrospective info? Or it counts AFTER I enable it?
– bioplanet
Nov 7 at 18:45




Yes but, if this is not enabled by now, will I have retrospective info? Or it counts AFTER I enable it?
– bioplanet
Nov 7 at 18:45












No, it will not have retrospective info. Also, you will need to setup some auto-cleanup for the logs also (after getting relevant summary points out of it); otherwise it gets big pretty fast
– Madhur Bhaiya
Nov 8 at 8:03






No, it will not have retrospective info. Also, you will need to setup some auto-cleanup for the logs also (after getting relevant summary points out of it); otherwise it gets big pretty fast
– Madhur Bhaiya
Nov 8 at 8:03



















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53192093%2flast-access-of-a-db-in-mysql%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















 

draft saved


draft discarded



















































 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53192093%2flast-access-of-a-db-in-mysql%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

這個網誌中的熱門文章

Academy of Television Arts & Sciences

圖片
American television organization Academy of Television Arts & Sciences Founded 1946 ; 73 years ago  ( 1946 ) Location North Hollywood, California, United States Area served Television industry Product Emmy Awards Key people Frank Scherma ( Chairman and CEO ) Website televisionacademy.com [redirects to emmys.com , official website of the Emmys and the Television Academy The Academy of Television Arts & Sciences ( ATAS ), also colloquially known as the Television Academy , is a professional honorary organization dedicated to the advancement of the television industry in the United States. Founded in 1946, the organization presents the Primetime Emmy Awards, an annual ceremony honoring achievement in U.S. primetime television. Contents 1 History 2 Emmy Award 3 Publications and programs 4 Current governance 4.1 Board of Governors 5 Television Academy honors 5.1 1st Annual (2008) 5.2 2nd Annual (2009)...
閱讀完整內容

L'Équipe

圖片
For other uses, see Équipe (disambiguation). L'Équipe The front page of L'Équipe on 4 July 2011 Type Daily newspaper Format Tabloid Owner(s) Éditions Philippe Amaury Editor François Morinière Editor-in-chief Fabrice Jouhaud Founded 1946 Language French Headquarters Boulogne-Billancourt ISSN 0153-1069 Website www.lequipe.fr L'Équipe ( pronounced  [lekip] , French for "the team") is a French nationwide daily newspaper devoted to sport, owned by Éditions Philippe Amaury. The paper is noted for coverage of association football , rugby, motorsport and cycling. Its predecessor was L'Auto , a general sports paper whose name reflected not any narrow interest but the excitement of the time in car racing. L'Auto originated the Tour de France cycling stage race in 1903 as a circulation booster. The race leader's yellow jersey (French: maillot jaune ) was instituted in 1919, probably to reflect the dist...
閱讀完整內容

1995 France bombings

圖片
This article is a rough translation from French . It may have been generated by a computer or by a translator without dual proficiency. Please help to enhance the translation. The original article is under "français" in the "languages" sidebar. See this article's entry on Pages needing translation into English for discussion. 1995 France bombings Part of Algerian Civil War (spillover) Plaque commemorating the victims of the Saint-Michel station bombing on 25 July 1995 Location Paris and Lyon, France Date 25 July 1995  ( 1995-07-25 ) – 17 October 1995  ( 1995-10-17 ) Weapons Improvised explosive devices, school bombing Deaths 8 Non-fatal injuries 157 Perpetrator Armed Islamic Group Motive To induce the French government to withdraw support from the Algerian government during the Algerian Civil War The 1995 bombings in France were carried out by the Armed Islamic Group (GIA), who were broadening the Alger...
閱讀完整內容