MySQL: An Overview

Adam Haeder

Vice President of Information Technology

AIM Institute

adamh@omaha.org

What is MySQL?

multithreaded, multi-user SQL RDBMS

GPL or commercial license

Multiplatform

Maintained by Swedish firm MySQL AB

Widespread API support through popular programming languages, like C, Java, Perl

Features

Different storage options (MyISAM, InnoDB)

ACID (Atomic, Consistent, Isolated, Durable)-Compliant transactions

Row-level locking

Version 5 -> Triggers, views, stored
procedures, cursors

Indexes: single column, multi-column, unique, primary key. Also full-text

Master-slave replication

ODBC-compliant

Clustering support

Why is MySQL so popular?

Good 3rd-party language support via API

License

Many existing applications built on top of it

ANSI SQL Compliant

Good performance

Standard in most Linux distributions

Good support, both free and commercial

Alternatives

PostgreSQL

Interbase

Ingres

SQLite

IBM DB2

Oracle

MS SQL
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

Basic Setup

Initial security setup:
# /usr/bin/mysqladmin -u root password 'new-password'
# /usr/bin/mysqladmin -u root -h localhost password 'new-password'

Other mysql commands:
mysql	mysqldump
mysqladmin	mysqlshow
mysqlcheck	mysqlimport
mysqlaccess

Create a database

Connect to the database
# mysql -h localhost -u root -p

Create a database
mysql> create database olug;

Select the database
mysql> use olug;

Create a table

Create a table
mysql> create table olug_table (
-> name varchar(255) default NULL,
-> dob date default NULL,
-> job_title varchar(255) default NULL,
-> height int default NULL,
-> weight int default NULL,
-> primary key (name)
-> );
Query OK, 0 rows affected (0.04 sec)

Insert a row

mysql> insert into olug_table values
("Adam Haeder","10/17/1974","Head Peon",72,200);
Query OK, 1 row affected (0.00 sec)

mysql> select * from olug_table;
+-------------+------------+-----------+--------+--------+
| name | dob | job_title | height | weight |
+-------------+------------+-----------+--------+--------+
| Adam Haeder | 0000-00-00 | Head Peon | 72 | 200 |
+-------------+------------+-----------+--------+--------+
1 row in set (0.01 sec)

Replacing Data

mysql> replace into olug_table values ("Adam Haeder","1974-10-17","Head Peon",72,200);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from olug_table;
+-------------+------------+-----------+--------+--------+
| name | dob | job_title | height | weight |
+-------------+------------+-----------+--------+--------+
| Adam Haeder | 1974-10-17 | Head Peon | 72 | 200 |
+-------------+------------+-----------+--------+--------+
1 row in set (0.00 sec)

Filesystem

adam:/var/lib/mysql # ls -Al
total 9
drwx------ 2 mysql root 528 May 3 17:04 mysql
srwxrwxrwx 1 mysql daemon 0 May 3 17:04 mysql.sock
-rw-rw---- 1 mysql root 140 May 3 17:04 mysqld.log
-rw-rw---- 1 mysql daemon 6 May 3 17:04 mysqld.pid
drwx------ 2 mysql daemon 144 May 3 17:25 olug
drwx------ 2 mysql root 48 May 3 17:04 test

adam:/var/lib/mysql # ls -Al olug
total 20
-rw-rw---- 1 mysql daemon 56 May 3 17:38 olug_table.MYD
-rw-rw---- 1 mysql daemon 3072 May 3 17:38 olug_table.MYI
-rw-rw---- 1 mysql daemon 8666 May 3 17:25 olug_table.frm

GUI Tools

phpMyAdmin - www.phpmyadmin.net

MySQL Administrator – dev.mysql.com

MySQL Query Browser – dev.mysql.com

MacSQL – rtlabs.com

Navicat - www.navicat.com

2005-05-03T14:10:38