MySQL

Compile-time options

Syntax

• [CFLAGS="-O3 -march=pentiumpro" CXX=gcc \
  CXXFLAGS="-O3 -march=pentiumpro -felide-constructors -fno-exceptions -fno-rtti" \]
  ./configure [options]*
  

• --prefix=<path>  (where to install MySQL)
• --localstatedir=<path>/data  (location of the database directories)
• --with-unix-socket-path=<path>/socket/mysql.sock  (location of the MySQL socket)

• --enable-assembler
• --with-mysqld-ldflags=-all-static

mysql

About

• Command-line tool for using Mysql

Syntax

mysql [options] [database]

Options

• -u name, --user=name
• -p[name], --password[=name]
• -h name, --host=name
• -D name, --database=name
• -S name, --socket=name   (socket file to use for connection)
• -P number, --port=number

• -e name, --execute=name   (execute command and quit)
• -B, --batch   (print results with a tab as separator, each row on new line)
• -r, --raw   (write fields without conversion; used with --batch)
• --column-names   (write column names in results)
• --skip-column-names

• -H, --html   (produce HTML output)
• -X, --xml   (produce XML output)
• -t, --table   (output in table format)

• --variable-name=value

Variables

• host
• user
• database
• table
• socket

• column-names
• raw
• html
• xml

Usage

• Can be used in interactive and non-interactive modes
• Host name is only required for remote hosts

mysqladmin

About

• Used for performing adminstrative tasks

Syntax

mysqladmin [options] [commands]

Options

• -u name, --user=name
• -p[name], --password[=name]
• -h name, --host=name
• -S name, --socket=name   (socket file to use for connection)
• -P number, --port=number

Commands

• shutdown
• ping   (check if mysqld is alive)

• create <database>
• drop <database>

Usage

• Host name is only required for remote hosts

mysqld_safe

• Shell script for running mysqld (<mysql-path>/libexec/mysqld)

cd <mysql-path>
bin/mysqld_safe &

mysql.user table

select host, user, password from mysql.user;

+-------------------------+------+----------
| host                    | user | password
+-------------------------+------+----------
| localhost               | root |
| computer_name.host_name | root |
| computer_name.host_name |      |            (anonymous account)
| localhost               |      |            (anonymous account)
+-------------------------+------+----------

# Delete anonymous accounts
delete from mysql.user where user = '';

+-------------------------+------+----------
| host                    | user | password
+-------------------------+------+----------
| localhost               | root |
| computer_name.host_name | root |
+-------------------------+------+----------

# Delete user accounts used for remote access
delete from mysql.user where Host = 'computer_name.host_name';

+-------------------------+------+----------
| host                    | user | password
+-------------------------+------+----------
| localhost               | root |
+-------------------------+------+----------

# Set the root password
set password for root@localhost = password('...');

+-------------------------+------+----------
| host                    | user | password
+-------------------------+------+----------
| localhost               | root | ...
+-------------------------+------+----------

# Create a guest account with limited privileges for the "test" database
# (privileges are stored in the mysql.db table).
grant select,insert,update,delete
    on test.* to guest@localhost identified by '...';

+-------------------------+-------+----------
| host                    | user  | password
+-------------------------+-------+----------
| localhost               | root  | ...
| localhost               | guest | ...
+-------------------------+-------+----------

mysql.db table

select host, db, user from mysql.db;

+------------------------+------------------+----------------
| host                   | db               | user
+------------------------+------------------+----------------
| %                      | test             |
| %                      | test\_%          |
| localhost              | test             | guest
+------------------------+------------------+----------------

# Prevent test databases from being world-writable
delete from mysql.db where host = '%';

+------------------------+------------------+----------------
| host                   | db               | user
+------------------------+------------------+----------------
| localhost              | test             | guest
+------------------------+------------------+----------------

Features

• Transactions are only available with InnoDB or Berkeley DB (BDB) engines
• Timeline

4.1

• Subqueries

5.0

• Stored procedures

5.0.1

• Views

5.0.2

• Triggers

MyISAM storage engine

Tables

• .MYD

    : Row data

• .MYI

    : Indexes and statistics.

• .frm

    : Information about the table structure; an internal representation of the CREATE TABLE statement.
  

SQL commands

• show databases;

• show tables;
• describe <table-name>;
• show create table table-name;
• show index from table-name [from database-name];

• explain select select-options;   (find out whether an index is used, and how tables are joined)

Importing data from a text file

load data infile '<file-name>' into table <table-name>
    [ignore <n> lines]
    fields terminated by ',' enclosed by '"'
    [(col1, col2, ...)];

Exporting data to a text file

select * into outfile '<file-name>'
    fields terminated by ',' enclosed by '"'
    from <table-name>;

Comments

• For double-hyphen comments, the double hyphens must be followed by a blank space or new line character