Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Keeping MySQL connection parameters in a safe place

by wfsp (Abbot)
on Dec 27, 2005 at 15:00 UTC ( #519337=perlmeditation: print w/replies, xml ) Need Help??

Your Mother alerted me to a neat trick for keeping MySQL connection parameters in a safe place by using the mysql_read_default_file connection option.

A couple of points to note:

  1. You can omit the name of the database from the data source name (dsn) but you need the trailing colon.
  2. In the connect call the two undefs are needed (on the remote server at least, it worked ok locally without them).
See below for version info.

my $my_cnf = 'path_to_somewhere_safe/my_cnf.cnf'; my $dsn = "DBI:mysql:;" . "mysql_read_default_file=$my_cnf"; my $dbh = DBI->connect( $dsn, undef, undef, {RaiseError => 1} ) or die "DBI::errstr: $DBI::errstr";
Note there is no connection info in the script at all. It's tucked away nicely in a cnf file. For example:

# my_cnf.cnf [client] host = hostname database = my_db user = my_usr password = my_pwd

The DBD::mysql docs give an indication on how to use option files but more can be found on the MySQL site:

To see which options are available:

There is also an interesting article at:

The last link points out that you need the two undef parameters in the connect statement.

Version info:

MySQL -> 4.0.17 (remote)
MySQL -> 4.1.15 (local)

DBI 1.32 (remote)
DBI 1.49 (local)

DBD::MySQL 2.0419 (remote)
DBD::MySQL 3.0002 (local)

Hope this is of some use and many thanks to Your Mother for the pointer.



The 'interesting article' linked to above should be credited to gmax (see below).
++ to gmax

Replies are listed 'Best First'.
Re: Keeping MySQL connection parameters in a safe place
by gmax (Abbot) on Dec 27, 2005 at 16:26 UTC

    One more trick usable with DBD::mysql is mysql_read_default_group ,an option to have a configuration file with credentials for different applications.

    First, you need to create a configuration file, with different [label]s.

    # $HOME/.my.cnf # Here are some general options [client] socket=/tmp/mysql.sock port=3306 # the following are specific to each application [mysqldump] user=dumpuser password=not_my_pwd [backup] user=bkpuser password=not_my_real_one [usage] user=simpleguy password=not_this_one [readonly] user=poorguy password=don_t_try_it [myapp] user=specialguy password=something_different

    Then, in your code you can refer to such labels this way:

    my $dbh = DBI->connect("DBI:mysql:test" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf" .';mysql_read_default_group=myapp', undef, undef ) or die "something went wrong ($DBI::errstr)";

    This code will use the label [myapp] from the file $ENV{HOME}/.my.cnf.

    To use a backup application, replace myapp with backup in the above code and your application will use that username and password under [backup].

    You can also use this trick to test the same application with different users having different access profiles. (Update. - I mean database users, not O.S. users)

    my $profile = shift || 'usage'; my $dbh = DBI->connect("DBI:mysql:test" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf" .";mysql_read_default_group=$profile", undef, undef ) or die "something went wrong ($DBI::errstr)";

    BTW, the article you were referring to is mine, also published in my blog.

    Update - While mysql_read_default_file adds to security, because you won't leave your password hardcoded in your script and you can store it outside the document tree in web applications, using mysql_read_default_group is only a matter of convenience. Using it does not add to security, but just to tidiness.

     _  _ _  _  
    (_|| | |(_|><
      Doesn't the user the app runs under still have to have read access to the file or does the mysql user have to have read access to the file? I'm wondering where the additional safety benefits are coming in over using your own config file.

      My criteria for good software:
      1. Does it work?
      2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Re: Keeping MySQL connection parameters in a safe place
by leocharre (Priest) on Dec 27, 2005 at 19:22 UTC

    Ok this is pretty cool.. I think I need to using something like this. My question.. this hits the disk, so.. performance - If I need to query a table every time a user logs in, etc- this creates an expensive disk operation, right?

    If 20 people log in per minute, or if every time a user does something I need to perform some kind of query to the db, then.. won't reading from disk for a db connection sort of slow things?

    Maybe I'm missing something here- maybe I should be looking into opening a db connection for a user's session and not closing it somehow ?

      Where do you think your database retrieves its data from, thin air? ;-) No, the db reads its data from disk as well, and that's a much more complicated operation than a simple text file read. Luckily (for both cases) the OS will cache often-accessed data in RAM, and therefore the expensive disk operation turns into a simple memory lookup. Which is why database servers can have too little RAM even if only a small percentage of it is being used by the DB process.

      A computer is a state machine. Threads are for people who can't program state machines. -- Alan Cox

        Now, if I have a text file with the word 'soup' in it. And I have an infinite loop that makes a system call to a script that reads the content into memory and then exits.. . Are you saying at some point the text file will be coming from memory instead of disk ?!

        What if it's an infinite loop and it's spaced at a random 1 to 10 seconds appart?

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://519337]
Approved by bart
Front-paged by dbwiz
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (1)
As of 2021-10-23 05:53 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (87 votes). Check out past polls.