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