James Skemp's StrivingLife

Another Ubuntu Quickie, this time on the default passwords for MySQL and PostgreSQL.

MySQL

mysql -u root
UPDATE mysql.user SET Password = OLD_PASSWORD('***password***') WHERE User = 'root';
FLUSH PRIVILEGES;
\q

PostgreSQL

sudo -u postgres psql template1
ALTER USER postgres WITH PASSWORD '***password***';
\q

Below are the PostgreSQL 8.2 commands on Ubuntu.

These are listed here primarily for my own benefit.

General
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "template1")
\cd [DIR]      change the current working directory
\copyright     show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding
\h [NAME]      help on syntax of SQL commands, * for all commands
\q             quit psql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing        toggle timing of commands (currently off)
\unset NAME    unset (delete) internal variable
\! [COMMAND]   execute command in shell or start interactive shell

Query Buffer
\e [FILE]      edit the query buffer (or file) with external editor
\g [FILE]      send query buffer to server (and results to file or |pipe)
\p             show the contents of the query buffer
\r             reset (clear) the query buffer
\s [FILE]      display history or save it to file
\w FILE        write query buffer to file

Input/Output
\echo [STRING] write string to standard output
\i FILE        execute commands from file
\o [FILE]      send all query results to file or |pipe
\qecho [STRING]
write string to query output stream (see \o)

Informational
\d [NAME]      describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN]  list aggregate functions
\db [PATTERN]  list tablespaces (add "+" for more detail)
\dc [PATTERN]  list conversions
\dC            list casts
\dd [PATTERN]  show comment for object
\dD [PATTERN]  list domains
\df [PATTERN]  list functions (add "+" for more detail)
\dg [PATTERN]  list groups
\dn [PATTERN]  list schemas (add "+" for more detail)
\do [NAME]     list operators
\dl            list large objects, same as \lo_list
\dp [PATTERN]  list table, view, and sequence access privileges
\dT [PATTERN]  list data types (add "+" for more detail)
\du [PATTERN]  list users
\l             list all databases (add "+" for more detail)
\z [PATTERN]   list table, view, and sequence access privileges (same as \dp)

Formatting
\a             toggle between unaligned and aligned output mode
\C [STRING]    set table title, or unset if none
\f [STRING]    show or set field separator for unaligned query output
\H             toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t             show only rows (currently off)
\T [STRING]    set HTML <table> tag attributes, or unset if none
\x             toggle expanded output (currently off)

Copy, Large Object
\copy ...      perform SQL COPY with data stream to the client host
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID    large object operations

For SQL on Ubuntu Linux, I decided it was easiest to just use the repositories to just install MySQL and PostgreSQL.

There was a couple of reasons for this. First, MySQL docs recommend it.

Second, there's not that much that I really want to configure in SQL.

So ...

MySQL

sudo apt-get install mysql-server

At the time of this writing, this will install;

libdbd-mysql-perl
libdbi-perl
libnet-daemon-perl
libplrpc-perl
mysql-client-5.0
mysql-server
mysql-server-5.0

Suggested packages:

dbishell
libcompress-zlib-perl
tinyca

Recommended packages:

mailx

There's some official GUIs that you can also install:

sudo apt-get install mysql-admin mysql-query-browser

At the time of this writing, this will install;

libcairomm-1.0-1
libglibmm-2.4-1c2a
libgtkhtml3.8-15
libgtkmm-2.4-1c2a
mysql-admin
mysql-admin-common
mysql-query-browser
mysql-query-browser-common

Suggested packages:

libgtkhtml3.8-dbg

PostgreSQL

For PostgreSQL, it's a bit longer of a string, since there's four packages, but it's still easy enough.

sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-client-common postgresql-common

You may as well also go from pgAdmin III, which "is the most popular and feature rich Open Source administration and development platform for PostgreSQL."

sudo apt-get install pgadmin3

At the time of this writing, this will install:

libpq4
libwxbase2.6-0
libwxgtk2.6-0
pgadmin3
pgadmin3-data

Recommended packages:

pgagent (adds functionality to schedule PostgreSQL jobs)

Next time ...

Next time we'll configure databases for both MySQL and PostgreSQL on Ubuntu.