8 Replies - 396 Views - Last Post: 13 November 2018 - 08:19 AM

#1 ktsirig   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 14-September 05

Last access of a DB in Mysql

Posted 07 November 2018 - 08:44 AM

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!

Is This A Good Question/Topic? 1
  • +

Replies To: Last access of a DB in Mysql

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,583
  • Joined: 12-June 08

Re: Last access of a DB in Mysql

Posted 07 November 2018 - 09:02 AM

That's the last update so no.. It is not like the 'sys.dm_db_index_usage_stats' on MSSQL. Popping into the server and see the actual DB file modified dates may help, but for the most part, I do not believe there is a good way at all. Maybe sift through some sort of query logging? (Obv turn that on if it is ofF)
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4244
  • View blog
  • Posts: 13,449
  • Joined: 08-June 10

Re: Last access of a DB in Mysql

Posted 07 November 2018 - 09:56 AM

Quote

but this does not really tell me if MY_DB is being accessed by some web service or users, right?

How should the DB differentiate the two? Eventually, it is a server script (whether that is written in PHP, C#, or whatever) that is connecting to the DB and so long as these script(s) connect using the same credentials, the DB has zero chance of seeing a difference.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2997
  • View blog
  • Posts: 11,542
  • Joined: 03-December 12

Re: Last access of a DB in Mysql

Posted 07 November 2018 - 10:35 AM

Production fun. Turn the server off and see who complains about it.
Was This Post Helpful? 2
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,583
  • Joined: 12-June 08

Re: Last access of a DB in Mysql

Posted 07 November 2018 - 10:35 AM

Not going to lie.. I've done that a few times.
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2997
  • View blog
  • Posts: 11,542
  • Joined: 03-December 12

Re: Last access of a DB in Mysql

Posted 07 November 2018 - 10:37 AM

It sounds like a joke, but we do it routinely. We have so many systems and no idea whom or what is using it. That's the only way to know if it is still being used a lot of times.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: Last access of a DB in Mysql

Posted 07 November 2018 - 11:21 AM

That's the first thing that occurred to me as well ;)

We have lots of objects that are probably not being used; I want to just append _DEL and wait a few weeks 'til someone cries.
Was This Post Helpful? 0
  • +
  • -

#8 Sheepings   User is offline

  • D.I.C Lover
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,260
  • Joined: 05-December 13

Re: Last access of a DB in Mysql

Posted 12 November 2018 - 05:52 PM

You can use SHOW [FULL] PROCESSLIST and you should get something like this ::
            Id: 15
         User: sheepings
         Host: localhost
           db: NULL
      Command: Query
         Time: 0
        State: NULL
         Info: SHOW FULL PROCESSLIST
    Rows_sent: 0
Rows_examined: 0
    Rows_read: 48

You're all wrong lol. There is a way, one way, well a way that I know. That command will output the user, the db, and the query being executed regardless if its update, select from * or something else. It's a very gray area and not many ways to find out if its actually being accessed. Hope this helps you guys out next time, before you upset the babies. :)
Was This Post Helpful? 1
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2997
  • View blog
  • Posts: 11,542
  • Joined: 03-December 12

Re: Last access of a DB in Mysql

Posted 13 November 2018 - 08:19 AM

Nice Query. I use SQL Server primarily, and it is more fun to just turn things off anyway!!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1