Simple howtos

This collection of mini howtos is a dynamic copy of the Unix Toolbox. This page extracts the XML content directly from the original XHTML DOM and displays only the requested node. Source code here (use "save as").
Unix Toolbox revision 14.4
Copyright (c) 2007-2012 Colin Barschel. Some rights reserved under Creative Commons [Attribution - Share Alike]
System
Processes
File System
Network
SSH SCP
VPN with SSH
RSYNC
SUDO
Encrypt Files
Encrypt Partitions
SSL Certificates
CVS
SVN
Useful Commands
Install Software
Convert Media
Printing
Databases
Disk Quota
Shells
Scripting
Programming
Online Help

Databases

PostgreSQL

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.

MySQL

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 (see MySQL dochttp://dev.mysql.com/doc/refman/5.1/en/adding-users.html)

# mysql -u root mysql
mysql> CREATE USER 'bob'@'localhost' IDENTIFIED BY 'pwd'; # create only a user
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. (On FreeBSD my.cnf not created per fedault, copy one .cnf file from /usr/local/share/mysql to /usr/local/etc/my.cnf) 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.

SQLite

SQLitehttp://www.sqlite.org is a small powerful self-contained, serverless, zero-configuration SQL database.

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