Database

From QwikITedia

Contents 1 MySQL 1.1 Install MySQL & PHP Support on Ubuntu 1.2 Learn who is connected to MySQL 1.3 Remove MySQL 1.4 MySQL CLI (Command Line Drive MySQL Administration) 1.4.1 Login 1.4.2 Create a database 1.4.3 List databases on the sql server. 1.4.4 Switch to a database. 1.4.5 To see all the tables in the db. 1.4.6 To see database's field formats. 1.4.7 To delete a db. 1.4.8 To delete a table. 1.4.9 Show all data in a table. 1.4.10 Show unique records. 1.4.11 Show selected records sorted in an ascending (asc) or descending (desc). 1.4.12 Sum column. 1.4.13 Join tables on common columns. 1.4.14 Change a users password 1.4.15 Set a root password 1.4.16 Update a root password 1.4.17 To update info already in a table 1.4.18 Delete a row(s) from a table 1.4.19 Add a new column to db 1.4.20 Change column name 1.4.21 Make a unique column so you get no dupes. 1.4.22 Make a column bigger 1.4.23 Delete unique from table 1.4.24 Load a CSV file into a table 1.4.25 Dump all databases for backup 1.4.26 Dump one database for backup 1.4.27 Dump a table from a database 1.4.28 Restore database (or database table) from backup 1.4.29 Create Table - Example 1 1.4.30 Create Table - Example 2 2 PostGres 2.1 Remove PostGreSQL v8.2.27 2.2 Install PostgreSQL v8.3.x 2.3 Install PostgreSQL v8.4.x 2.4 Start or Stop Postgres 8.3 2.5 PostgreSQL v8.3.x 2.5.1 Set DB root user Password 2.5.2 Create a Database 2.5.3 Create a user 2.5.4 Setup DB privileges 2.5.5 Test using GUI tool 2.5.6 Connect as a user from the Command Line 2.5.7 Revoke privileges 2.5.8 Delete a user 2.5.9 Delete a Database 2.5.10 List Databases 2.5.11 Reset Password 2.5.12 Describe Commands 2.6 PHPPGADMIN install 2.7 CLI Session as PostGres User 2.8 LDAP Authentication 2.8.1 LDAPSEARCH is your friend 2.8.2 Host Based Authentication(hba) on PostGres 2.8.2.1 TEST 2.9 Local Authentication for Admins + LDAP Authentication for Remote Clients 2.10 Local Authentication for Admins + LDAP Authentication for Active Dir connections(REMOTE+LOCAL) 3 PostGres Versions 3.1 Server 3.2 Client 4 PostGresSQL FAQs

MySQL

Install MySQL & PHP Support on Ubuntu

• You need MySQL server and PHP support in a hurry

apt-get install mysql-server mysql-client libmysqlclient15-dev libapache2-mod-auth-mysql php5-mysql 

• Administer easily with a GUI client such as phpmyadmin

apt-get install phpmyadmin

Learn who is connected to MySQL

• Use the show processlist command:

diazepam htdocs # mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 82
Server version: 5.0.54-log Gentoo Linux mysql-5.0.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show processlist;
+----+-----------+-----------+-------+---------+------+-------+------------------+
| Id | User      | Host      | db    | Command | Time | State | Info             |
+----+-----------+-----------+-------+---------+------+-------+------------------+
| 79 | cactiuser | localhost | cacti | Sleep   |    0 |       | NULL             | 
| 82 | root      | localhost | NULL  | Query   |    0 | NULL  | show processlist | 
+----+-----------+-----------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql>

Remove MySQL

• Stop MySQL Server

sudo /etc/init.d/mysql stop

• Remove the sucka

apt-get --purge remove mysql-server-5.0

MySQL CLI (Command Line Drive MySQL Administration)

Login

• Use -h to specify a particular Host if necessary.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database

mysql> create database [databasename];

List databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

• Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

• Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

• Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

• Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

• Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

• Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

• Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
==== Return number of rows. ====
<pre>mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

• Creating a new user
• Login as root
• Switch to the MySQL db
• Make the user
• Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

• Change a users password from MySQL prompt
• Login as root
• Set the password
• Update privs

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;
* Recover a MySQL root password. Stop the MySQL server process. 
* Start again with no grant tables.
* Login to MySQL as root.
* Set new password.
* Exit MySQL and restart MySQL server.
<pre>
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password

• Applies when there is no root password;

# mysqladmin -u root password newpassword

Update a root password

# mysqladmin -u root -p oldpassword newpassword

• Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

• Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table

mysql> DELETE from [table name] where [field name] = 'whatever';
==== Update database permissions/privilages

.

mysql> flush privileges;
==== Delete a column ====
<pre>mysql> alter table [table name] drop column [column name];

Add a new column to db

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger

mysql> alter table [table name] modify [column name] VARCHAR(3)

;

Delete unique from table

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup

• Backup file in sql format to recreate all db's

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table - Example 1

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table - Example 2

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

PostGres

Remove PostGreSQL v8.2.27

apt-get --purge remove postgresql-8.2 postgresql-client-8.2 postgresql-client-common postgresql-common

Install PostgreSQL v8.3.x

apt-get install postgresql-8.3 postgresql-client-8.3 postgresql-client-common postgresql-common

Install PostgreSQL v8.4.x

• This assumes you are attempting an install on Ubuntu Hardy Heron
• First remove any 8.3x and God forbid 8.2x install or there might be horrendous Athena like nightmares:
• Next adjust the repo lookup on the Ubuntu server to include the back-ports repo as that's what currently harbors the 8.4.x packages for Ubuntu

sudo vi /etc/apt/sources.list

• Make sure the following lines feature

# Additionalrepos required to install PostgreSQL 8.4 on ubuntu for ICE - R.Esmonde 7/28/2010

deb http://us.archive.ubuntu.com/ubuntu hardy-backports main
deb http://us.archive.ubuntu.com/ubuntu hardy-updates main restricted universe
deb http://us.archive.ubuntu.com/ubuntu hardy-security main restricted universe

• Update the package pull

 apt-get update

• Install 8.4.(.4 at time of this wiki post):

apt-get install postgresql-8.4 postgresql-client-8.4 postgresql-client-common postgresql-common

Start or Stop Postgres 8.3

• Start Postgres 8.3

/etc/init.d/postgresql-8.3 start

• Stop Postgres 8.3

/etc/init.d/postgresql-8.3 stop

PostgreSQL v8.3.x

• Useful client side GUI admin tools

1. TOAD SQL
2. phppgadmin
3. pgAdmin111

Set DB root user Password

• For a PostGres install on Debian there is no password set when you install PGSQL so you need to connect to the Postgres DB as the postgres user(root) and set the password:

sudo -u postgres psql postgres

\password postgres

Create a Database

• From Command Line; Connect to PQSQL DB as root

root@gotham:/etc/postgresql-common# psql -d postgres
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

CREATE a NEW DB
postgres=# create database halle;
CREATE DATABASE

Create a user

• From Command Line

CREATE a NEW USER
postgres=# create user orinoco with password 'womble';
CREATE ROLE

Setup DB privileges

GRANT new USER some PRIVILEGES
postgres=# grant all privileges on database halle to orinoco;
GRANT

Test using GUI tool

• Connect via pgAdmin3 with :

1. Name=hal
2. Host=hal
3. Port=5432
4. MaintenaceDB=halle
5. Username=orinoco
6. Password=womble

Connect as a user from the Command Line

1. -h is for HOSTNAME
2. -p is for PORT
3. -d the database you want to connect to
4. -U the user you want to connect as
5. -W force a Password prompt for the user

root@hal:/etc/postgresql/8.3/main# psql -h localhost -p 5432 -d zulu -U resmonde -W
Password for user resmonde:

Revoke privileges

REVOKE ALL PRIVILEGES ON halle FROM orinoco;

Delete a user

DROP USER orinoco;

Delete a Database

DROP DATABASE halle;

List Databases

• From the commandline:

psql -l

Reset Password

• If you can’t remember your password you will need to disable authentication temporarly, login and reset the password.
• Go into your pgdata directory.
• Open pg_hba.conf

• The lines that say “md5” near the bottom, change to “trust”
• Restart your database instance.
• Login and set your password.

alter user vu2131 with password 'foobar';

• Quit psql.
• Change the lines back to md5 in your pg_hba.conf file
• Restart your instance again.
• Try logging in and see if your change worked!

Describe Commands

• Once connected:

\d ~= desc

\dt Show Tables
\dT list datatypes
\df list functions
\di list indexes
\dv list views

• You can use wildcards with \d, e.g. \df pg_* will show all functions beginning "pg_" Use specific objects name with \d to get the details.
• All this is explained in \?
• Better yet, use one of the GUI tools above.

PHPPGADMIN install

• Install phppgadmin( Postgres DB GUI ) in /usr/share/phppgadmin:

sudo apt-get install phppgadmin

• Set extra login security to false:

sudo vi /usr/share/phppgadmin/conf/config.inc.php

$conf['extra_login_security'] = false;

• Create symbolic link in /var/www to this DB GUI:

sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf

• Create users for DB:

# root@lapp:~# su -c 'createuser -P admin' postgres
# Enter password for new role: xxxxxxxxxx
# Enter it again:  xxxxxxxxxx
# Shall the new role be a superuser? (y/n) y
# CREATE ROLE

• Restart apache( /etc/init.d/apache2 restart )
• Postgres Conf files are here: /etc/postgresql/8.3/main
• Restart Postgres if you make any changes to it conf:

sudo /etc/init.d/postgres-8.3 restart

• To access phppgadmin from any other machine, you'll need to edit /etc/phppgadmin/apache.conf
• Comment out the "allow from 127.0.0.1/255.0.0.0" restriction and uncomment "allow from all", as per below:

#allow from 127.0.0.0/255.0.0.0
allow from all

• Now you should be able to access PGAdmin console from any host( Example http://myserver/phppgadmin )
• Finally, edit PostgreSQL's config to allow connections from IP addresses on the LAN:

sudo vi /etc/postgresql/8.3/main/pg_hba.conf

• Add the line:

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all     all     10.5.5.0 255.255.255.0  password

• Then restart the DB

sudo /etc/init.d/postgresql-8.3 restart

CLI Session as PostGres User

• ssh onto myserver
• Establish a Sudo session upon the Server:

resmonde@myserver:~$ sudo -i

• Change to postgres user:

root@myserver:~#su postgres
postgres@myserver:/root$

• Connect to PostGres Database Server as postgres User:

postgres@myserver:/root$ psql -d postgres -U postgres -W
Password for user postgres: 
Welcome to psql 8.3.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \q

LDAP Authentication

1. Ensure the user(s) that is trying to connect to the PostGres DB has an Active Directory (Archimedes Domain) account and that a user with this exact username exists within the PostGres database they are trying to connect to over LDAP - or this will NOT work.
   1. For example:: ddyve is a user in the Active directory and ddyve is a user with access to the PostGres database called datadyve

LDAPSEARCH is your friend

• Before you do anything ensure you can connect to your Active Directory using ldapsearch from the command line of the client harboring the PostGres Database

# ldapsearch -h ADServer1 -p 389 -b "DC=mycompany,DC=lan" -x -D "CN=LDAP Access,CN=Users,DC=mycompany,DC=lan" -w users-password -v
# ldapsearch -h ADServer2 -p 389 -b "DC=mycompany,DC=lan" -x -D "CN=Vertical Response,OU=Marketing,OU=MyCompany Users,DC=mycompany,DC=lan" -w userspassword -v

• This will save you hours and possibly days of trouble shooting.
• At the very least, it will guarantee you are using the correct Base DN and Bind URI for your future ldap URL.. this will prove critical.

Host Based Authentication(hba) on PostGres

• There is a hugely important configuration file for hba on Postgres, called pg_hba.conf
• It is located here:

/etc/postgresql/8.3/main/

• Before you touch it, move into its directory and back it up!

# sudo cp pg_hba.conf pg_hba.conf.safe

• Next you'll need to edit the file for both local connections and remote TCP/IP connections
• On the DB server edit this file and scroll to the bottom of the file where it speaks to IPv4 local connections
• Comment out the 127.0.0.1/32 md5 line and add your LDAP magic:

# IPv4 local connections:
# Prevent all local access even by admins and root users
# host    all         all         127.0.0.1/32          md5

• Now, add another config line for users connecting via remote clients such as pgAdmin111 for instance:

# Remote TCP/IP connection
host    all         all         10.5.5.0/16          ldap "ldap://ADServer1:389/OU=Mycompany Users,DC=mycompany,DC=lan;MYCOMPANY\"

• Your are finished configuring Postgres for HBA
• Restart PostGres DB or better yet do a full stop/start:

root@myserver:/etc/postgresql/8.3/main# /etc/init.d/postgresql-8.3 stop
 * Stopping PostgreSQL 8.3 database server
   ...done.
root@myserver:/etc/postgresql/8.3/main# /etc/init.d/postgresql-8.3 start
 * Starting PostgreSQL 8.3 database server
   ...done.
root@myserver:/etc/postgresql/8.3/main#

TEST

• From a command line prompt on the DB server, test your setup with one or two AD accounts that have also been createed within the PostGres server

root@myserver:/etc/postgresql-common# psql -h localhost -p 5432 -d projectdb -U user_a -W
Password for user user_a: 
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

projectdb=> \q
root@myserver:/etc/postgresql-common# psql -h localhost -p 5432 -d projectdb -U user_b -W
Password for user user_b: 
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

projectdb=# \q
root@myserver:/etc/postgresql-common#

Local Authentication for Admins + LDAP Authentication for Remote Clients

• If you want to allow local connections by PostGres admins and DBA's which depend upon their unique MD5 password as distinct from LDAP, you merely reinstate this functionality within the HBA conf file:

# IPv4 local connections:
# Permit local conenctions for admins, for example: admin, root and postgres accounts(DBAs and superusers)
host    all         all         127.0.0.1/32          md5

• Once the first line has been uncommented(as shown) local connections to the DB will be allowed but the user must ofcourse have been created by the DBA...for example admin, root....etc
• LDAP authentication remains untouched .. so your pg_hba.conf file should now look like:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

# IPv6 local connections:
host    all         all         ::1/128               md5

# Remote TCP/IP connection
# Force all remote connections to use LDAP authentication against SFDC1
host    all         all         10.5.5.0/16          ldap "ldap://ADServer1:389/OU=MyCompany Users,DC=mycompany,DC=lan;MYCOMPANY\"

• Don't forget to STOP and START PostGreSQL DB after any changes to this file or they will not be made effective:

root@myserver:/etc/postgresql/8.3/main# /etc/init.d/postgresql-8.3 stop
 * Stopping PostgreSQL 8.3 database server
   ...done.
root@myserver:/etc/postgresql/8.3/main# /etc/init.d/postgresql-8.3 start
 * Starting PostgreSQL 8.3 database server
   ...done.

Local Authentication for Admins + LDAP Authentication for Active Dir connections(REMOTE+LOCAL)

• Tell Postgres that local conenctions can be permitted based on who the local conenction is from/by( trust sameuser )
  • This is a kin to telling PostGres there will be some exceptions for local connections from superusers stipulated under Remote TCP/IP connections
• Add these user exceptions to the conf file under Remote TCP/IP connections:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
# Let admins and local superuser accounts connect to the DB using password
host    all         all         127.0.0.1/32         trust sameuser

# IPv6 local connections:
host    all         all         ::1/128               md5

# Remote TCP/IP connection
# Allow local connection exceptions .... by superusers(postgres,root,admin)
# Force all remote connections to use LDAP authentication against SFDC1
host    all     postgres,root,admin     10.5.5.0/16     password
host    all         all         10.5.5.0/16          ldap "ldap://ADServer1:389/OU=MyCompany Users,DC=mycompany,DC=lan;MYCOMPANY\"

• Don't forget to STOP and START PostGreSQL DB after any changes to this file or they will not be made effective:

root@myserver:/etc/postgresql/8.3/main# /etc/init.d/postgresql-8.3 stop
 * Stopping PostgreSQL 8.3 database server
   ...done.
root@myserver:/etc/postgresql/8.3/main# /etc/init.d/postgresql-8.3 start
 * Starting PostgreSQL 8.3 database server
   ...done.

PostGres Versions

• Quick means of determining versions of PostGres software from CLI

Server

• To determine what version of the server is running, connect to the DB server from cli as the postgres user and run the version query from the SQL prompt

user_b@dbproject:~$ sudo -u postgres psql postgres
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select version();
                                             version                                             
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)

postgres=# \q
user_b@dbproject:~$ 

Client

• SSH to the box and type:

user_b@dbproject:~$ psql --version
psql (PostgreSQL) 8.3.7
contains support for command-line editing
user_b@dbproject:~$ 

PostGresSQL FAQs

• where is PGSQL DB installed upon the system

locate psql

• Usually, /usr/lib/postgresql/8.3/bin/psql on Ubuntu