mysql abandoned, need to figure it out in a hurry

anon091 · 04-25-2014, 08:32 AM

Let me first preface this by saying I've used Windows MS SQL in the past, but never MySQL, and never any db software in RHEL. But here's my situation.

Our Linux/DB guy has ditched us, so now we're between a rock and a hard place. We have some software where supposedly we need to "clear out some tables" to reset it to blank so it can be used for it's next cycle. Only problem is, I have no idea where to start.

As root, I su'd to that guy's account on the server it's running on. I see in his history he has the following line
mysql WhatImGuessingIsTheDatabaseName -pWhatImGuessingIsTheDatabasePassword

I'm afraid to run that command because I'm not clear on what to do. Also, if that allows me to get into MySQL, does it have any "history" type command where I could possible determine which tables and how he has cleared them out in the past?

Or is there something I can install on my Windows machine which could help with this? (and not let me mess this database up).

vmccord · 04-25-2014, 09:03 AM

1. Don't panic. (Large friendly letters.
2. mysql WhatImGuessingIsTheDatabaseName -pWhatImGuessingIsTheDatabasePassword
You are right in your guessing. It opens up the SQL interface for mysql for that particular database. And once you have, you may be able to see the history. The up arrow will display the same way that it does in the bash command line interface.
3. sql is sql. So, "Show tables;" Will show you the tables in that particular database.
4. There is gui interface available, but that's all I know about it. Think of this as an excellent time to learn mysql.

anon091 · 04-25-2014, 09:10 AM

1. haha, thanks, I needed that
2. Knowing MySQL CLI has the up arrow history makes me feel a LOT better. Now I just have to get the nerve to actually run that command.
3. For my prior experience with MS SQL, I always had a GUI for about 95% of the stuff, and only had to do command line when things got ugly.
4. Yeah, I wouldn't mind sticking with the command line. Maybe I can find a cheat sheet of frequent commands. Heck, at this point, I wouldn't even know how to exit out of MySQL CLI haha.

Also, as I'm digging through this persons notes/files, I did find one that just had three random lines in it, all saying "truncate NameOfSomething;". Those sound very database-ish to me, and the names after truncate logically sound like what could be table names in a database.

And probably most importantly what I need to figure out how to do, is how I would backup and restore that WhatImGuessingIsTheDatabaseName.

szboardstretcher · 04-25-2014, 09:12 AM

to view his history you can open the file .mysql_history in his home directory... assuming root:

Code:

less /root/.mysql_history

this part is more tricky. I am only generalizing here, but youll want to find lines like this:

Code:

use somedatabasename;
delete from sometablename

That will give you a clue of what he did and where he did it. Luckily mysql saves all command history unless you disable it.

anon091 · 04-25-2014, 09:19 AM

Looking at root's .MySQL_history this is all that's in it:
show master status;
SHOW MASTER STATUS;
show databases
;
SHOW MASTER STATUS;
show databases
;
SHOW MASTER STATUS;
show master status;

Looking under his account, all I get is this
show databases;
FLUSH TABLES WITH READ LOCK;

Maybe he used yet another account on the server?

szboardstretcher · 04-25-2014, 09:26 AM

Correct. You'll want to check all the .mysql_history files then.

Anyone happen to know where the history goes if you log in through the client remotely? I don't think it keeps history in that case.

YankeePride13 · 04-25-2014, 09:30 AM

Depending on how you have your ACLs set up, you might be able to use this MySQL workbench on a windows machine to give you a GUI look at the database:

http://www.mysql.com/products/workbench/

anon091 · 04-25-2014, 09:36 AM

Is there a slick way to more all the users on my server's .MySQL_history files? or do I have to manually look for them all?

Thanks for the gui suggestion Yankee.

szboardstretcher · 04-25-2014, 09:46 AM

Something like:

Code:

find / -name ".mysql_history" -exec more {} \;

YankeePride13 · 04-25-2014, 09:53 AM

Also, make sure you back up the database before you do anything. The command is:

Code:

mysqldump --password='$password' $database [$table] > /some/path/to/backup/backup.sql

szboardstretcher · 04-25-2014, 09:59 AM

Be wary of making a backup of a production database!

Keep in mind that a backup can be huge, so check the size of your drive before you do this.

Also, you might have to wait until later in the day if this is a production server so you don't affect services by locking tables or anything, especially if you are using isam tables.

anon091 · 04-25-2014, 10:00 AM

That command only came up with the same stuff
show databases;
FLUSH TABLES WITH READ LOCK;
show master status;
SHOW MASTER STATUS;
show databases
;
SHOW MASTER STATUS;
show databases
;
SHOW MASTER STATUS;
show master status;

thanks for the backup command too Yankee

---------- Post added 04-25-14 at 11:01 AM ----------

Yeah, i'd try a backup during the off hours. but how can I tell how big the database is anyway?

szboardstretcher · 04-25-2014, 10:03 AM

I use this to get database sizes:

Code:

SELECT table_schema AS "Data Base Name", 
sum( data_length + index_length ) / 1024 / 1024 AS "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ;

anon091 · 04-25-2014, 10:55 AM

ok, thanks. after I make time to make a backup, and get brave enough to actually log in to MySQL, I will let you know.

or I wonder if there's a way I can back it up then restore it to my machine to test with. that might be good.

Thanks for all the help everyone, I'm feeling a lot better than I was when I originally posted.

YankeePride13 · 04-25-2014, 10:56 AM

Quote:

Originally Posted by rjo98

or I wonder if there's a way I can back it up then restore it to my machine to test with. that might be good.

You sure could. Just install MySQL locally on your machine or in a VM. But if the data is sensative, be sure to put it in a safe place and keep your system updated.