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 doc
http://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
SQLite
http://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
