Thursday, November 06, 2008

More database commands

I mentioned a few articles ago that I am getting some real quality MySQL database experience at my job and I wasn't kidding.  I am loving it and having fun. 

After writing a Perl function to audit our databases to ensure they were defined correctly, per our pre-defined schemas, I had to start making changes to the databases in question.  The first thing that I took care of was the deletions of whole tables and databases.  That was easy through the use of the 'drop' command, but now I am into the modifications. 

There are fields in tables that have either the wrong name or the wrong defined type.  So, I have to change them.  The wrong defined type is easy and modified using the following command:

          alter table <table_name> modify column <column_name> type;

<table_name>  => replace with the name of the table with fields you are fixing.
<column_name> => replace with the column / field that you are fixing.
type  =>  replace with the type that you want to change to (ie: int, bigint, float, varchar(??), etc)

The other type of change is where the name of a field/column is incorrect.  In that case, you would use the following command sequence:

           alter table <table_name> change column <old_column_name> <new_column_name> type;

<table_name>  =>  the table that has fields needing fixing.
<old_column_name>  =>  the present name of the column you want to fix.
<new_column_name> =>  the new name for the column you want to fix.
type  =>  while you are not changing it, you still have to specify the type for the column/field.  Just look at what it is presently defined as using "describe <table_name>" to see all field definitions.

Now, with the last one, I am running into a bunch of issues where two fields need their names switched.  The data is in the correct place, but the names aren't.  In that case, you would move one of them to a temporary name (like the new name with "_temp" added on to it) and then moved to its final name after the other field has been moved to its new name.

As I said, its all very interesting and fun to learn, while being quite involving.  I have become very acquainted with the MySQL online documentation, that is for sure. 

Useful, stupid Unix tricks

Today on Slashdot there is an article that asks people to submit their stupid, yet useful unix tricks.  While there are a number of good ones on there, I did pick up a couple of interesting ones. 

One of the things I picked up was to type "ssh username@" and then hit tab and you would get a list of available hostnames to connect to.  These are pulled from the machine's hosts file.  Now, one thing it doesn't do is get a list from your local, internal DNS, so don't get discouraged if you don't see your host name on there but know you can connect it.  That is the reason.

There was also a bit of a heated exchange regarding the use or lack of use of the 'sudo' command.  There are a bunch of people that claim, and I quote...  "Pshaw! All 1337 sysadmins just live as root!".    This is obviously someone who only administers his own system and nobody elses.  I would personally be very scared to have this person as a sysadmin.  I have done sysadmin work a number of times and at my present job we maintain our own machines, so root is part of the curriculum.  But, you won't see us exclusively "su -" into the root account all day.  If I need to do anything as root, I either use sudo for a quick job or if its more intensive, I use 'sudo bash', which makes you root as well.  But once done, I get out of it and back to my account. 

See, what these apparent noobs to the sysadmin scene don't realize is that the root account was not meant to be logged into all day doing all of your work.  That is what you have a user account for and the reason you should use your own account whenever you can.  Granted, I know that there are people I have dealt with who have systems that ONLY have a root account and that's it, but that would be one of the few exceptions to the rule. To run as root all the time just "because you can" is juvenile, inexperienced and immature.  Most sysadmins who have earned their titles know the rules of root and abide by them for a reason.  I yearn to be there when one of those "pshaw" kids makes a wrong move as root and looses a TON of needed data that makes them drop to their knees and cry.  I don't wish extensive data loss on anyone, but my hopes would be that they would learn their lesson.

Another command I recently discovered before seeing this list was the screen command, which is used during remote access to execute commands that may take a while to run.  You can then disconnect from the console and then reconnect later and reconnect to the screen session you had running.  Its pretty sweet really and allows your machine to not be tied up on a process.  Unix is so sweet like that, always a way to do something!

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.