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
(default)
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

Quick Info