You are here: Hello world / Howto / SQL-DBMS   SQL-Database hints.

SQL-DBMS

Some helpful hints on administrating and working with major linux sql-databases.

In the examples below, user input is formatted bold.

Administration

I use the following names as examples for hosts, users, databases, etc. They are all written in itallic capitals.

Creating a database

PostgreSQL mySQL 
$ su - postgres
$ createdb -h DBHOST -U DBADMIN -W MYDB
CREATE DATABASE
$ exit
 
$ mysql -h DBHOST -u DBADMIN -p
mysql> create database MYDB ;
Query OK, 1 row affected (0.16 sec)

mysql> quit
Notes
  • If you omit -h DBHOST, the mysql client connects to the localhost.

Deleting a database

PostgreSQL mySQL 
$ su - postgres
$ dropdb -h DBHOST -U DBADMIN -W MYDB
DROP DATABASE
$ exit
 
$ mysql -h DBHOST -u DBADMIN -p
mysql> drop database MYDB ;
Query OK, 0 row affected (0.56 sec)

mysql> quit

Listing available databases

PostgreSQL mySQL 
$ su - postgres
$ psql -l
...
 
$ mysql -h DBHOST -u DBADMIN -p
mysql> show databases ;
...
mysql> quit

Creating a user

PostgreSQL mySQL 
$ su - postgres
$ createuser -h DBHOST -U DBADMIN -W -P DBUSER
Enter password for user "DBUSER": DBPASS
Enter it again: DBPASS
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
$ exit
 
$ mysql -h DBHOST -u DBADMIN -p
mysql> grant all privileges on MYDB.*
    -> to DBUSER@localhost
    -> identified by 'DBPASS' ;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Notes
To allow password authentication for local connections, you have to add something like this to pg_hba.conf:
local  all                        password
host   all  127.0.0.1  255.0.0.0  password
 Notes
  • The above statement creates a DBUSER which has access to the database MYDB when connecting from the database server machine itself (in contrast to connecting from a remote host).
  • For more information see also www.mysql.com/doc/A/d/Adding_users.html.

Changing password of a user

PostgreSQL mySQL 
$ su - postgres
$ psql -h DBHOST -W template1
template1=# alter user DBUSER with password 'DBPASS' ;
ALTER USER
template1=# \q
$ exit
 
$ mysql -h DBHOST -u DBADMIN -p mysql
mysql> update user set password=password('DBPASS')
-> where user='DBUSER' ;
Query OK, 1 rows affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges ;
Query OK, 0 rows affected (0.35 sec)

mysql> quit
Notes
  • The above statement changes the password of DBUSER to DBPASS (the new password) regarding all databases. If you want to change the password for this user and a single database, change the where clause.

Deleting a user

PostgreSQL mySQL 
$ su - postgres
$ dropuser -h DBHOST -U DBADMIN -W DBUSER
DROP USER
$ exit
 
$ mysql -h DBHOST -u DBADMIN -p mysql
mysql> delete from user
-> where user='DBUSER ;
Query OK, 1 rows affected (0.01 sec)

mysql> delete from db
-> where user='DBUSER ;
Query OK, 1 rows affected (0.01 sec)

mysql> flush privileges ;
Query OK, 0 rows affected (0.35 sec)

mysql> quit

Modifying data

(n/a)

Querying data

(n/a)

Author: Tobias Jahn
Public key fingerprint: 0556 B7D8 93FB 0304 B868 9FBA 3A71 E4F9 AE03 4AAD 		Last modification of this page: 2003-04-19 10:57 GMT
Last generation of this page: 2007-06-16 20:26 GMT
Valid HTML 4.0!