MYSQL: How to set a user DEFAULT DATABASE

[Useko Netsumi]

Is it possible to set a user's default database for MySQL database?

Instead of having an additional line of command, i.e:

mysql> use database <database_name>

Perhaps there are parameters I can set up a default database for a user when
she log on to mysql.

Thanks

[J.O. Aho]

No, mysql do not support this, but you could make a script which checks from a
list of some sort with uid/databasename and picks the databasename and uses
the --database option for mysql to select a "default" database for the user.

You rename the mysql binary say to "mysql.bin" and call the script for mysql,
this way nothing would change for the user.

//Aho

[Useko Netsumi]

Thanks and so sad to hear that. Perhaps this is one of my wish list for the
future release.

/useko

"J.O. Aho" <us...@example.net> wrote in message
news:btf0vd$6c0u9$1...@ID-130698.news.uni-berlin.de...

[J.O. Aho]

Useko Netsumi wrote:
> Thanks and so sad to hear that. Perhaps this is one of my wish list for the
> future release.

Why wait for the future, I dubt it will happen.

Create a file say /etc/mysql.users.db

--- /etc/mysql.users.db ---
username|databasename
user2|database2
user3|dbase
--- eof ---

We need to make it readable for everyone

chmod a+r -R /etc/mysql.users.db

Now move your mysql binary

mv /usr/bin/mysql /usr/bin/mysql.bin

and now time to create the next file /usr/bin/mysql

--- /usr/bin/mysql ---
#!/bin/sh
/usr/bin/mysql.bin -p -D `grep $USER /etc/mysql.users.db | sed 's/.*|//g'`
--- eof ---

Now you just have to make it executive

chmod 755 /usr/bin/mysql

So now you type mysql you will get a prompt for the password (thats the -p
option), the -D option is for the database and the stuff after it is to fetch
the name of the database from the mysql.users.db.

server user2 $ mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31199 to server version: 4.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+---------------------+
| Tables_in_database2 |
+---------------------+
| Table1 |
| Table2 |
| Table3 |
+---------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

So, you see you get to the "default" database, but you must set a default
database for everyone who has a database. If your users has another login to
the mysql than their account name, then you need to add a little bit more code
to the script, that takes care of the login name, eg: mysql username

//Aho