LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This 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


Reply
  Search this Thread
Old 05-07-2014, 11:30 AM   #1
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Rep: Reputation: 49
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?
 
Old 05-08-2014, 02:18 AM   #2
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu, mint, suse
Posts: 1,591

Rep: Reputation: 145Reputation: 145
grep datadir /etc/my.cnf

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,...)
 
1 members found this post helpful.
Old 05-08-2014, 02:43 AM   #3
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,460

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
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.
 
1 members found this post helpful.
Old 05-08-2014, 07:42 AM   #4
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
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.
 
Old 05-08-2014, 08:09 AM   #5
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,460

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
By default replication files are named mysql-bin.xxxxxx for outbound replication and mysqld-relay-bin.xxxxxx for inbound replication.

So it would appear that at one point your server was a replication "slave".

Look in the MySQL data folder for a file "master.info", the 4th line of which would be the "master" server name/ip

You could also log in to MySQL on the server and use this to show master info.

Code:
mysql -u <yourmysqlusername> -p
show slave status\G
The small size of the files would indicate that nothing is being sent to your server.
 
1 members found this post helpful.
Old 05-08-2014, 08:18 AM   #6
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
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?
 
Old 05-08-2014, 08:27 AM   #7
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,460

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
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!
 
Old 05-08-2014, 08:35 AM   #8
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
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?
 
Old 05-08-2014, 08:43 AM   #9
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,460

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
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.
 
Old 05-08-2014, 08:45 AM   #10
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
Oh ok, wasn't sure which way it went.

So I need to log into the "real" server, then log into MySQL somehow, then run this

select Host,User from mysql.user where Repl_slave_priv != "N";

and that should tell us what slave servers are reading from the "real" server as their masters?
 
Old 05-08-2014, 08:49 AM   #11
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,460

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
Quote:
Originally Posted by rjo98 View Post
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:

Code:
show slave status\G
 
Old 05-08-2014, 08:53 AM   #12
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
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?
 
Old 05-08-2014, 09:02 AM   #13
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
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'

when I do it as root though, I get some output, but the hosts are all either localhost, 127.0.0.1, or the server name itself.

Last edited by anon091; 05-08-2014 at 09:05 AM. Reason: adding the "as root" part
 
Old 05-08-2014, 09:04 AM   #14
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,460

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
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 View Post
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.


Code:
mysql -u root -p
 
Old 05-08-2014, 09:05 AM   #15
anon091
Senior Member
 
Registered: Jun 2009
Posts: 1,795

Original Poster
Rep: Reputation: 49
I get some output, but the hosts are all either localhost, 127.0.0.1, or the server name itself.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Databases/ MySQL... spoovy General 9 04-26-2010 12:43 AM
LXer: How To Back Up MySQL Databases Without Interrupting MySQL LXer Syndicated Linux News 0 05-14-2007 09:16 AM
List databases in MySQL vital_101 Linux - Server 2 11-08-2006 06:26 PM
restoring mysql databases? and mysql permissions... armegeden Linux - Software 0 03-13-2003 11:04 AM
mysql databases k3v0 Linux - Software 2 12-03-2002 07:41 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 03:05 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration