HCoop Git - hcoop/zz_old/ikiwiki/blob - UsingDatabases.mdwn

   1 ## page was renamed from UseDatabases
   2 Previously, database creation needed admin intervention. We now wrote 'dbtool', however, to leave database management to the users themselves. This is possible because database files, kept in /home/<dbtype> (such as /home/mysql/), have proper filesystem permissions. They are set-gid to the users' primary group, so the sizes of users' databases enter their filesystem quota calculation.
   3
   4 = MySQL =
   5 == Database creation ==
   6 The databases you select for creation will be prefixed with your username, to avoid any name clashes. In other words, if you ask for a database 'gis', the actual database created will be '<username>_gis'.
   7
   8 When the database is created, all privileges on it are granted to the user owning them, except the privilege to grant permissions to other users.
   9
  10 Let's see an invocation of dbtool:
  11
  12 ''' dbtool create <dbtype> <password> <database ...> '''
  13
  14 Here's an example:
  15
  16 ''' dbtool create mysql myPass12.4 gis '''
  17
  18 At this point, MySQL database '<username>_gis' has been created, and <username>@'''localhost''' is given permission to connect to it using password 'myPass12.4'. An important thing to  remember here is that there is, by default, one password for all databases! If you create two MySQL databases, pick the same password for them or the second one will overwrite the first database password.
  19
  20 == Load database ==
  21 To load a database, use:
  22 '''mysql -p <username>_gis < gis.sql'''
  23 == Connecting to the database ==
  24 You can always connect to the databases using appropriate command line tools:
  25
  26 ''' mysql -p <dbname> '''
  27
  28 Upon typing in the password, mysql client tool will start up and connect to <dbname>. You can, however, create ~/.my.cnf to automate the "login":
  29
  30 {{{
  31 [mysql]
  32 user = <username>
  33 password = myPass12.4 }}}
  34 Just make sure the file is mode 0600: '''chmod 0600 ~/.my.cnf''', and omit ''-p'' from further {{{mysql}}} invocations.
  35
  36 == Database removal ==
  37 You can remove your own databases by invoking appropriate databases' command line tools.  Here's a MySQL example:
  38
  39 ''' mysql -e 'drop database <username>_gis' '''
  40
  41 Keep in mind that database permissions are not removed. In other words, once you drop the database, you can re-create it without invoking dbtool:
  42
  43 ''' mysql -e 'create database <username>_gis' '''
  44
  45 = PostgreSQL =
  46 You can create Postgres databases in a similar way.  The main difference is that you never provide a password to {{{dbtool}}}.  While MySQL doesn't provide any reasonable non-password-based authentication option, PostgreSQL has the nice ident scheme, where processes authenticate to databases based on their owning UNIX user.  Since this avoids adding an extra opportunity for password-guessing, we only allow Postgres authentication via this method.
  47
  48 The following command line would create database {{{<username>_test}}}:
  49
  50 ''' dbtool create postgres test '''
  51
  52 Then you could connect to your database:
  53
  54 ''' psql <username>_test '''