How to tell when mysql databases were last used or updated
Linux - NewbieThis Linux forum is for members that are new to Linux.
Just starting out and have a question?
If it is not in the man pages or the how-to's this is the place!
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
How to tell when mysql databases were last used or updated
I found another abandoned mysql server, which according to webmin has about 20 databases in it, none of which i'm sure are actually used/needed/updated still.
doing some googling trying to figure this out myself, I see that this command will tell me where the data files for mysql are
grep datadir /etc/my.cnf
When I look in that folder, there is a TON of files, most of which are mysqld-relay-bin.###### files. I'm not a mysql person so don't know what those are.
At this point I'm in over my head. Am I on the right path to finding out if these databases are even used/accessed/updated anymore?
will show you where the database files reside. In this directory (/var/lib/mysql ?)
you can
ls -ls
This will give out when the databases were changed the last time (inserts, updates, deletes,...)
The relay files are most likely recording change operations on databases so if these are all 0 then you can take a guess that there's no updates being done on the databases.
If it's an old server it's likely that the database(s) will be using MyISAM tables, so you'll find a folder for each database. Within that folder you'll find .MYI and .MYD files that are the index and data files. Check the dates on these and they'll give you an indication about usage.
So are the relay files showing changes that are being made to the local databases, or changes being sent from another MySQL server to this one through some kind of log shipping or something? or is there a way to tell? The vast majority of the relay files are 285 bytes, but every once in a while there is a bigger one, like 10kb or less. I would like to clean these up if that's an option, once we figure out where they're coming from.
It does appear that there is a folder for each database. I'll look inside those for the modify times.
OK, the fourth line of that file definitely is one of our "real" live MySQL servers that I know we still use at least one database on. So does the sheer quantity of those relay files mean that it's not processing any of them? I would think that it would process and delete them, unless that's configurable and not configured.
Side question, on that "real" MySQL server, how can I tell where all it is shipping transactions too?
Make use of the show slave status on the slave, you may find errors that mean sql is broken.
As for finding out which slaves are taking updates from your master you can try the following in mysql:
Code:
select Host,User from mysql.user where Repl_slave_priv != "N";
Which will give you a list of users that have the slave replication privilege. Hopefully whoever set up your replication made separate grants for each host and the "Host" column shouldn't contain wildcards!
I probably should have mentioned something before, but I'm pretty clueless when it comes to how to handle MySQL in Linux. I do have webmin on the server which shows me whatever that shows me, but outside of that I've never really done much of anything with MySQL.
Going in through webmin to the MySQL users part on the "real" server, I don't see any users who's host is set to this other servers name specifically, but I do see a user account for the guy who used to admin this stuff and his hosts column is set to any, so I wonder if that is what's sending the logs to this server. Also through webmin, if I look in the "database connections" area, I see the user account for that guy, with client host set to the server in my original post, in "Binlog Dump" mode.
Does that help at all?
The way MySQL replication works nothing "sends" TO the slave server, the slave server is configured to read FROM the master server.
Looking at the users on the slave won't show you anything meaningful about replication, only looking at the users on the master will be meaningful. On the master look for "Slave" in the privilege column.
You're much better off using shell access and mysql to look at this on the slave in any detail, webmin just isn't going to be enough.
and that should tell us what slave servers are reading from the "real" server as their masters?
No, it'll tell you what user names were set up with replication privileges.
If you're lucky someone will have set up each slave server with a host/user combination. If you're unlucky then the host will contain a "%" wildcard.
This is still no guarantee that those servers are still checking for replication. You'd still need to check each one individually to see if they are up, running and replicating properly using the output of:
OK. Well let me see if I can figure out how to even log into MySQL on the "real" server.
So since the slaves read from the master, if I turned off the server from my original post for a little while (just as a test to see if anything breaks), would that have any effect on the "real" server that we still use? since the slave reads from the master, i'm guessing files wouldn't build up filling up a volume on the "real"/master server, right?
The master will log replication information regardless of the status of slaves. Turning off the slave will have absolutely no effect on the master, the master will continue to log replication regardless. There will be no indication on the master that a slave isn't processing replication requests. When the slave is restarted it basically goes "Oh, I was at point XXXXX in log file YYYYY" and sends requests to start reading from that point in the master.
---------- Post added 8th May 2014 at 03:05 PM ----------
Quote:
Originally Posted by rjo98
ok, I tried running that command on the "real" server (or at least I think I did), it gave me an error though
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
You'll probably have to log in as the MySQL "root" user for this.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.