Change root or a username password

# psql -d template1 -U pgsql

> alter user pgsql with password 'pgsql_password'; # Use username instead of "pgsql"

Create user and database

The commands createuser, dropuser, createdb and dropdb are convenient shortcuts

equivalent to the SQL commands. The new user is bob with database bobdb ; use as root with

pgsql the database super user:

# createuser -U pgsql -P bob # -P will ask for password

# createdb -U pgsql -O bob bobdb # new bobdb is owned by bob

# dropdb bobdb # Delete database bobdb

# dropuser bob # Delete user bob

The general database authentication mechanism is configured in pg_hba.conf

Grant remote access

The file $PGSQL_DATA_D/postgresql.conf specifies the address to bind to. Typically

listen_addresses = '*' for Postgres 8.x.

The file $PGSQL_DATA_D/pg_hba.conf defines the access control. Examples:

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

host bobdb bob 212.117.81.42 255.255.255.255 password

host all all 0.0.0.0/0 password

Backup and restore

The backups and restore are done with the user pgsql or postgres. Backup and restore a single

database:

# pg_dump --clean dbname > dbname_sql.dump

# psql dbname < dbname_sql.dump

Backup and restore all databases (including users):

# pg_dumpall --clean > full.dump

# psql -f full.dump postgres

In this case the restore is started with the database postgres which is better when reloading an empty cluster.

Change mysql root or username password

Method 1

# /etc/init.d/mysql stop

or

# killall mysqld

# mysqld --skip-grant-tables

# mysqladmin -u root password 'newpasswd'

# /etc/init.d/mysql start

Method 2

# mysql -u root mysql

mysql> UPDATE USER SET PASSWORD=PASSWORD("newpassword") where user='root';

mysql> FLUSH PRIVILEGES; # Use username instead of "root"

mysql> quit

Create user and database

# mysql -u root mysql

mysql> CREATE DATABASE bobdb;

mysql> GRANT ALL ON *.* TO 'bob'@'%' IDENTIFIED BY 'pwd'; # Use localhost instead of %

# to restrict the network access

mysql> DROP DATABASE bobdb; # Delete database

mysql> DROP USER bob; # Delete user

mysql> DELETE FROM mysql.user WHERE user='bob and host='hostname'; # Alt. command

mysql> FLUSH PRIVILEGES;

Grant remote access

Remote access is typically permitted for a database, and not all databases. The file /etc/my.cnf

contains the IP address to bind to. Typically comment the line bind-address = out.

# mysql -u root mysql

mysql> GRANT ALL ON bobdb.* TO bob@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'PASSWORD';

mysql> REVOKE GRANT OPTION ON foo.* FROM bar@'xxx.xxx.xxx.xxx';

mysql> FLUSH PRIVILEGES; # Use 'hostname' or also '%' for full access

Backup and restore

Backup and restore a single database:

# mysqldump -u root -psecret --add-drop-database dbname > dbname_sql.dump

# mysql -u root -psecret -D dbname < dbname_sql.dump

Backup and restore all databases:

# mysqldump -u root -psecret --add-drop-database --all-databases > full.dump

# mysql -u root -psecret < full.dump

Here is "secret" the mysql root password, there is no space after -p. When the -p option is used

alone (w/o password), the password is asked at the command prompt.

SQLite14 is a small powerfull self-contined, serverless, zero-configuration SQL database.

14.http://www.sqlite.org

— Databases —

44

Dump and restore

It can be useful to dump and restore an SQLite database. For example you can edit the dump

file to change a column attribute or type and then restore the database. This is easier than

messing with SQL commands. Use the command sqlite3 for a 3.x database.

# sqlite database.db .dump > dump.sql # dump

# sqlite database.db < dump.sql # restore

Convert 2.x to 3.x database

sqlite database_v2.db .dump | sqlite3 database_v3.db