|
Creating New
Databases and New Database Users on your Unix/Linux Server
You will use the command
/usr/bin/mysql_setpermissions
to create database users and databases.
First a quick overview of MySQL users and databases:
Each mysql database access must be made by a user, but this user is *not*
the same as the Unix user (login name), nor is it created when you create
a Unix user (login name). So, typically, the best way to create databases
(this is what we do at Netmar) is to create a database user in mysql with
the same name as the person's login name (if the login name is jsmith,
then we create a mysql user jsmith). Then we also create a database with
the name of the user, since we only allow one database per client (so,
a database named jsmith). It really doesn't matter that we only allow
one database per client, because intelligent clients will realize that,
with one database, you can use different database prefixes on your tables
and effectively have multiple databases anyway.
So, this way, our steps are to create a user jsmith, then create a database
jsmith, and then to assign that database permissions for jsmith, as well
as set jsmith's password.
So, we will use the mysql_setpermissions command. If it is not found,
you may get output like that below. In this case, check your /etc/bashrc
or ~/.bashrc files for "/usr/bin" in your $PATH. In the meantime,
you will have to invoke the program by typing the absolute path (/usr/bin/mysql_setpermission).
------- Code Sample 1
- mysql_setpermission -------
[root@server root]# mysql_setpermission
bash: mysql_setpermission: command not found
[root@server root]# /usr/bin/mysql_setpermission
|
In order to create
a database, you will need to be root on your system. Enter the mysql_setpermissions
interface (it's a nice script to help you out with creating databases).
It will ask you for a password; this should be your root password.
The script gives you several options. We will be starting from scratch,
creating both a mysql user and a database. So, pick option 2. (Add database
+ user permission for that db, all except admin functions)
It will ask you what database to create. In this example, I'll use "testerdb".
After you input that, it will ask what user you'd like it to create. Enter
a username (I'll use testerdb). You should set a password when it asks.
When asked for a host, % is the best option. When asked about adding another
host, you should say no unless you plan to access the db from multiple
hosts. It will ask you to confirm before committing the actions you've
asked.
-------
Code Sample 2 - mysql_setpermission -------
[root@server root]# mysql_setpermission
Password for user to connect to MySQL:
##############################################################
## Welcome to the permission setter 1.2 for MySQL.
## made by Luuk de Boer
##############################################################
What would you like to do:
1. Set password for a user.
2. Add a database + user privilege for that database.
- user can do all except all admin functions
3. Add user privilege for an existing database.
- user can do all except all admin functions
4. Add user privilege for an existing database.
- user can do all except all admin functions + no create/drop
5. Add user privilege for an existing database.
- user can do only selects (no update/delete/insert etc.)
0. exit this program
Make your choice [1,2,3,4,5,0]: 2
Which database would you like to add: testerdb
The new database testerdb will be created
What username is to be created: testerdb
Username = testerdb
Would you like to set a password for [y/n]: y
What password do you want to specify for :
Type the password again:
We now need to know from what host(s) the user will connect.
Keep in mind that % means 'from any host' ...
The host please: %
Would you like to add another host [yes/no]: n
Okay we keep it with this ...
The following host(s) will be used: %.
############################################################
That was it ... here is an overview of what you gave to me:
The database name : testerdb
The username : testerdb
The host(s) : %
############################################################
Are you pretty sure you would like to implement this [yes/no]: y
Okay ... let's go then ...
Everything is inserted and mysql privileges have been reloaded.
|
If you would like
to test your new database, do so with the mysql command like so:
------- Code Sample 3-
mysql ----------------------
[root@server root]# mysql -u testerdb -p testerdb
Enter Password:
Welcome to the MySQL monitor. Commands end with.....
|
The syntax for this
command is:
mysql [-u mysqlusername] [-p [databasepassword]] databasename
So, you can put the password on the commandline if you want, but it's
not reccomended. If you just use -p and don't specify a password, then
it will prompt you for one.
Press (control-C) to get out of the mysql user interface. |
