MySQL Client Environment Variables for USER and DB on Linux

MySQL

I see that http://dev.mysql.com/doc/refman/5.7/en/environment-variables.html has an option for setting the USER on Windows, but is it possible on Linux? You can do it via the MYSQL_TEST_LOGIN_FILE, but it's a bit convoluted, and requires setting up files with just the right permissions and one for every user you want to have access. Also, is there an environment variable for what database to start using?

Best Answer

Not that I know of, but you can make a file called my.cnf in a directory that you specify with the MYSQL_HOME environment variable. For example say you have ~/mysql_home/db/my.cnf:

[mysql]
user = username
password= ****
host = hostname
db = databasename

Then you can call the client like

MYSQL_HOME=~/mysql_home/db/ mysql

and it will use those options. Please keep in mind that your password will be readable by whomever can read that file and it isn't encrypted!

Related Solutions

Mysql – ny way to increase the thesql reserved pool for super users beyond 1

Is there some sort of sane upper limit any one user should never exceed? Maybe your busiest application has at most 20 open connections during peak normal operation. Let's assume you have 20 non root users.

set max_user_connections = 30 (to give a bit of a safe buffer)

set max_connections = max_user_connections * # users + extra buffer, 
   or 30 * 20 + 50 = 650 max connections.

This way your global max connections is safe from every getting reached and there should always be room for you to get in even if one particularly application or user is getting maxed out.

Thesql_install_db: How to set the root password

Upon installation, root@localhost does not have a password.

You should be able to connect without a password by just doing this:

$ mysql -uroot

Try the following:

$ mysqladmin --no-defaults --port=3308 --user=root password 'somenewpassword'

or you can do it the stubborn way:

$ service mysql restart --skip-grant-tables --skip-networking
$ mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
$ service mysql restart
$ mysql -uroot -p

UPDATE 2013-01-02 16:47 EDT

I am sorry, I overlooked the port number.

Here is the problem: you cannot use root@localhost against a non-3306 port unless you use the TCP/IP protocol. Please try this:

$ mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password 'somenewpassword'

Best Answer

Related Solutions

Mysql – ny way to increase the thesql reserved pool for super users beyond 1

Thesql_install_db: How to set the root password

UPDATE 2013-01-02 16:47 EDT

Related Question