welcome: please sign in

Diff for "DaemonAdmin/MySQL"

Differences between revisions 13 and 33 (spanning 20 versions)
⇤ ← Revision 13 as of 2007-02-05 14:44:06 →
Size: 2950
Editor: 213
Comment:
← Revision 33 as of 2011-04-23 00:08:19 → ⇥
Size: 97
Editor: ec2-122-248-244-127
Comment: BcENfl <a href="http://vilivwwawkml.com/">vilivwwawkml</a>
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
= DaemonAdmin/MySQL =

Because we wanted to have version 5 of MySQL running on our stable server, we used the [http://dotdeb.org] package. For this reason there should be mirrors from dotdeb.org listed in the file ''/etc/apt/sources.list'' on deleuze.

== Configuration details ==

my.cnf file modified to leave a port open over the network, default is local pipe only.

== Progress ==

As of Sat Jan 6 12:29:23 EST 2007, the MySQL 5.0 daemon and client libraries have been installed on deleuze. I also installed the mysql-common and mysql-client 5.0 packages on mire which should will allow for users to connect back to the main DB server.

== To Do ==

dbtool will need to be programmed to store data files in user home directories. It will also need to set up the appropriate GRANT permission on creation of each database to allow a connection from mire or other front-end servers by default.

== Steps to perform ==

Ok, logically, the steps are:

 1. Create user's database volume in AFS, if one isn't there already
 1. Create directory structure with proper permission within the AFS volume
 1. Create a symbolic link in /var/lib/mysql/ that points to the database
 1. Grant the user rights on the new DB

And, in terms of command line, the steps are:

(This is needed only once, for first DB - MySQL or any other):
 1. vos create -server afs -partition a -name db.USERNAME -maxquota 5000
 1. fs mkmount -dir /afs/.hcoop.net/common/databases/USERNAME -vol db.USERNAME -rw
 1. vos release root.cell
 1. fs setacl -dir /afs/hcoop.net/common/databases/USERNAME -acl mysql l

(And this for every mysql DB):
 1. $dir = /afs/hcoop.net/common/databases/USERNAME/mysql
 1. mkdir $dir
 1. fs setacl -dir /afs/hcoop.net/common/databases/USERNAME/mysql -acl mysql l
 1. mkdir $dir/DBNAME || exit # (Must not exist)
 1. chown mysql:mysql $dir/DBNAME
 1. chmod 770 $dir/DBNAME # (Just for visual impression)
 1. ln -sf $dir/DBNAME /var/lib/mysql/DBNAME
 1. fs setacl -dir $dir/DBNAME/ -acl mysql all
 1. mysql -e 'create database DBNAME'

Now, about users and granting permissions to the database, I would like to see users being able create new users
and passwords and their privileges (to their databases) themselves. This would allow fine-grained tuning
of what service uses which DB username/pw, and what access rights it has. Maybe a list of users/passwords,
which the user can edit, would be ok.

So anyway, the procedure for creating a user and giving privileges, executed on behalf of the admin user
(domtool2):

 1. CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'PASSWORD';
 1. GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,GRANT OPTION ON '''DBNAME''' TO USERNAME@'%.hcoop.net';
 1. FLUSH PRIVILEGES;

(Thing to note here: Wildcard '%' can be used in hostname part, for things like '%.hcoop.net'.)


There are two other things related to users, one is 		BcENfl <a href="http://vilivwwawkml.com/">vilivwwawkml</a>
----
CategorySystemAdministration

BcENfl <a href="http://vilivwwawkml.com/">vilivwwawkml</a>

CategorySystemAdministration

DaemonAdmin/MySQL (last edited 2012-04-23 04:19:38 by ClintonEbadi)