welcome: please sign in

The following 347 words could not be found in the dictionary of 7 words (including 7 LocalSpellingWords) and are highlighted below:
able   about   access   acl   Adam   admin   Administration   afs   after   ahead   all   allow   already   also   always   an   And   and   Another   any   anymore   anyway   apply   appropriate   apt   are   as   As   associated   at   back   backup   be   Because   been   behalf   behavior   being   both   Bugzilla   but   can   case   Category   changing   Chlipala   chmod   chown   clever   client   cnf   command   commands   common   Configuration   connect   consider   cool   could   Create   create   created   creating   creation   daemon   Database   database   databases   db   dbtool   default   deleting   deleuze   details   dir   directories   directory   Do   do   doesn   Dom   domtool2   don   done   dotdeb   drop   dropped   duplicated   empty   enough   etc   even   exactly   examine   executed   exist   existence   existing   exit   external   figure   file   fine   fixperms   For   for   from   fs   fyodor   giving   go   grained   Grant   granting   has   have   hcoop   here   his   host   hostname   how   http   if   If   implement   implemented   impression   in   inherited   Initialize   initialized   installed   instead   interface   interfaces   is   isn   issue   it   Jan   Just   just   keep   last   leave   let   letting   lib   libraries   lid   like   line   link   list   listed   listening   lives   ll   ln   local   localhost   Logical   main   make   map   mask   match   maxquota   Maybe   Mikulovsky   mire   mirrors   mkdir   mkmount   modified   mounted   must   Must   My   my   mysql   name   need   Needs   needs   net   network   never   new   No   no   none   not   note   Now   now   of   on   one   only   open   Or   or   org   other   our   out   Outdated   over   package   packages   parent   part   particular   partition   password   passwords   perform   permission   permissions   pipe   points   port   possible   prefixed   present   privileges   problems   procedure   Progress   proper   pw   reason   recreate   related   release   requested   restrict   retain   right   rights   rl   routine   rule   run   running   runs   rw   Ryan   Sat   says   script   see   server   service   set   setacl   sf   should   simply   Since   So   so   sole   solution   sources   space   specified   specify   stable   Steps   steps   stick   still   structure   sudo   support   sure   symbolic   system   System   table   tables   tell   terms   that   The   the   their   them   themselves   then   There   there   These   these   they   thing   Thing   things   this   This   ticket   time   to   To   Tool   try   tuning   two   up   use   used   user   username   users   uses   using   var   version   visual   vol   volume   vos   want   wanted   way   We   we   were   what   when   where   which   who   Wildcard   will   with   within   without   work   Work   would   wouldn   You   you  

Clear message
Edit

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.

1. Configuration details

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

2. 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.

3. To Do

The new dbtool implemented as part of DomTool can now be used to create MySQL users and databases and the associated AFS directories. We still need to figure out how to allow users to drop tables from their databases without letting them drop the databases themselves. Since users retain permissions on a database even after it's dropped, the user could drop his database and recreate it on the partition where /var/lib/mysql lives, instead of in AFS.

We also need to work out exactly what hostname mask to use in creating users and granting them privileges.

Bugzilla says this isn't an issue anymore. dbtool runs mysql-fixperms now right? Or must a user tell dbtool to do this? -- RyanMikulovsky

No, dbtool doesn't run mysql-fixperms. We would never have created that script if it were possible to set up a database ahead of time so that these problems wouldn't apply to it. mysql-fixperms needs to do things to particular tables, and dbtool isn't run on table creation. --AdamChlipala

4. Steps to perform

4.1. Logical steps

  1. Create user's database volume in AFS, if one isn't there already
  2. Create directory structure with proper permission within the AFS volume (the sole existence of the directory is enough for MySQL to consider it a database, even if just an empty one). NOTE: If we stick to existing behavior on fyodor, requested database name should be prefixed with USERNAME_ .
  3. Create a symbolic link in /var/lib/mysql/ that points to the database
  4. Grant the user rights on the new DB

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

4.2. Initialize DB space for any DB

You need to perform this as any user who has AFS admin permissions:

  1. $dir = /afs/hcoop.net/common/.databases/USERNAME

If vos examine db.USER says there's no volume created:

  1. vos create -server afs -partition a -name db.USERNAME -maxquota 5000

If db.USER volume is there, but $dir is not present (volume isn't mounted):

  1. fs mkmount -dir /afs/hcoop.net/common/.databases/USERNAME -vol db.USERNAME -rw

  2. vos release common.databases

And this can be done always:

  1. fs setacl -dir $dir -acl databases l
  2. fs setacl -dir $dir -acl system:backup rl

4.3. Database creation routine when the db space has been initialized

You need to perform this as any user who has AFS admin permissions:

  1. $dir = /afs/hcoop.net/common/databases/USERNAME/mysql

  2. mkdir -p $dir
  3. fs setacl -dir $dir -acl mysql lid
  4. fs setacl -dir $dir -acl databases none # (keep out other databases, just in case)
  5. fs setacl -dir $dir -acl system:backup rl # (should be inherited from parent dir)

  6. sudo mkdir $dir/DBNAME || exit # (Must not exist)

  7. sudo chown mysql:mysql $dir/DBNAME

  8. sudo chmod 770 $dir/DBNAME # (Just for visual impression)

  9. sudo ln -sf $dir/DBNAME /var/lib/mysql/DBNAME

  10. fs setacl -dir $dir/DBNAME/ -acl mysql all

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, or an appropriate support ticket would be cool.

So anyway, the procedure for creating a user and giving privileges, executed on behalf of the admin user (domtool2), which can be specified as sudo -H mysql -e "<COMMAND HERE>" mysql:

  1. CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'PASSWORD';

  2. GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,GRANT OPTION ON DBNAME.* TO USERNAME@'%.hcoop.net';

  3. 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 changing password and the other is deleting users. These simply map to mysql SET PASSWORD and DROP USER commands, if you go to implement them.

And one last thing; mysql is listening both on localhost and network interfaces (deleuze external IP). Maybe we want to restrict it to run on just one of them. Or if not, if it will run on both, then the access rule ( USERNAME@'%.hcoop.net' ) has to be duplicated in a way (for USERNAME@localhost), OR the users will always have to specify mysql host "deleuze" instead of "localhost". Another solution to this is that we don't try to be clever at all, but simply let users make sure the hostname part in their support ticket will match the interface they'll be using to connect.

CategorySystemAdministration CategoryNeedsWork CategoryOutdated

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