Diamond Notes

Just another WordPress weblog

Tip of the Day — the .my.cnf File

February 27th, 2008 | Category: MySQL, Tip of the Day

Here is somethingthat you might be interested in.  If you are using Linux on your servers and you ssh to the server and then run the mysql client to access the server you can use a .my.cnf file to speed up access.  This is put in your home directory and is automatically parsed by the mysql client when it is invoked.  For example here is a really simple one:

[client]
user =username

password =x&r%@ldc

host = localhost

Now you can just type ‘mysql’ and it connnects you up automatically.  There are many other options you can put in here.  Not sure how this works on Windows — I don’t think it does.  Of course if you are using Windows as a desktop OS you can ssh to the server and then go from there.  The reference page for this is: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

6 Comments so far

  1. Ronald Bradford February 27th, 2008 1:19 pm

    This of course is extremely dangerous. You should be restricting your MySQL ‘root’ access for many reasons.

    You should always access MySQL with a user with less privileges, much like you use a normal Linux user, and only ‘root’ when necessary, e.g. for installing software. Normal users also should not have full access, never SUPER and never ALL on *.* to ‘%’.

    If you also say ~/.my.cnf it also best describes the home directory, even with subsequent comment.

  2. admin February 27th, 2008 1:47 pm

    Ronald,

    I agree with you. Actually this is a holdover from days of “yore”. Believe it or not (and you probably don’t at this point) I am very security conscious. I edited the post to show another username as I wouldn’t want to lead people astray.

  3. castro1688 February 27th, 2008 4:37 pm

    While I see how that is a cool feature, even that is dangerous.

    If someone got ANY access to your machine they would have instant access to your DB.

  4. admin February 27th, 2008 8:54 pm

    Thanks for the comment. However, this I am going to disagree with. Why? If I had set this up I would execute ‘chmod 600 .my.cnf’. Then you would have to either have my username/account or the root password to read it. Not “any” access as you say. And if someone gets root access to the server you are screwed anyways. So stop worrying about access to the database at this point since as root I can just copy off the data files. End of story.

    I should have pointed out the chmod command to do this possibly. Sorry, trying to keep the tip points short.

  5. Vid Luther February 27th, 2008 10:52 pm

    Is there a way to specify different users or databases this way? I was wondering if I could create sections for different hosts, or even different users, something akin to the ssh ~/.ssh/config file.

    so if I said something like mysql devphpcult, it would look for a user under a devphpcult section etc?

  6. sapphirecat February 28th, 2008 6:42 am

    After a bit of experiment, my ~/.my.cnf looks like this:

    username=foo
    password

    This will prompt for the password, without requiring ‘-p’ on the command line.

Leave a reply