Thursday, February 4, 2010

MySql Shortcut

If you access a MySql database from a linux machine here is a tip that can save you some time. In your home directory create a file named .my.cnf. That is dot-my-dot-cnf.

Then in that file you can create multiple entries like the following:
[client]
user=username
password=%!;*;^123
host=localhost

Now when you ssh in and type mysql, the client will automatically parse and use those credentials. You can specify multiple hosts if you want like this:

[client]
user=username
password=%$**^123
host=hosta.example.com

[client]
user=usernameB
password=%B^123
host=hostb.example.com

Then the MySql client will choose the proper set depending on the host you are connecting to. This feature served me well when using R scripts. In a R script that access a database you can specify the particular client group that you want to use.

In the .my.cnf file.

[Rdata]
user=Ruser
password=password
host=localhost

Then in the R file itself you can create the connection like this:

dbcon <- dbConnect( MySQL(), group="Rdata")

Now you can move the script around or share it without worrying about revealing your password information.

No comments:

Post a Comment