Architecture
MySQL has a lot in common with the Macintosh: both products grew out of their creators' early vision and passion to become the great products they are today; both have begun to fill very visible roles in the Open Source world; both have been popping up more and more in corporate settings; and both have managed to generate communities of vocal and loyal supporters -- communities that continue to grow and thrive.
MySQL is a small, fast and highly configurable DBMS. It supports a number of different table fileformats, depending on the requirements of the user. At last check MySQL does is not SQL92compliant and as such, will only support basic SQL statements. Also, unless compiled with InnoDB, MySQL is not ACID compliant.
These are the main MySQL clients and processes (mysqld):
-
mysqld - MySQL server daemon
-
safe_mysqld - Server process monitor
-
mysqlaccess - Tool for creating MySQL users
-
mysqladmin - Utility for administering MySQL
-
mysqldump - Tool for dumping the contents of a MySQL database
mysql - Command line interface to MySQL
mysqlshow - List all MySQL database
Overview
As shown in the next Figure, there are several layers to MySQL:

The top layer supplies basic
services like query parsing, networking, authentication, and logging.
Once MySQL has received and parsed a query, the second layer is responsible for analyzing, optimizing, and ultimately executing the query. Queries usually involve fetching data from one or more tables, and applying various SQL functions (such as DATE_FORMAT()) to the result.
Like the Linux kernel's virtual filesystem (VFS) layer, MySQL implements an abstraction layer between the query engine and the underlying data storage. The storage engine API
(shown as the third layer) provides generic routines for accessing data regardless of the underlying table type (BDB, MyISAM, etc.).
Modularity allows each storage engine in MySQL to use its own strategies for storing, locking, and retrieving data. For example, BDB tables provide transactions and page-level locking, while MyISAM tables have no transactions, use table locks, provide full-text search capabilities, and so on. The interface is generic enough that the upper layers in MySQL don't need to care where the tables exist on disk. In fact, HEAP tables don't use disk storage at all -- they're stored completely in memory.
InnoDB: Transactions and Row-level Locking
Long time MySQL users may recognize many of the storage
engines (formerly called "table handlers") listed at the bottom of the Figure.
However, one of them is a relative newcomer to MySQL. InnoDB (formerly
Innobase) was developed and is maintained by Heikki Tuuri of
Innobase Oy, the Finnish company that has partnered with MySQL AB to
provide commercial support for InnoDB in MySQL.
InnoDB was first released as part of MySQL in mid-2001.
For most of MySQL 3.23 development, InnoDB was considered experimental, and
was only included in the MySQL-Max binaries. Beginning with version 4.0,
InnoDB is a standard component of MySQL.
But just what is InnoDB? Without exaggeration, InnoDB is
the single most revolutionary addition to MySQL ever. InnoDB is a
storage engine that was modeled after Oracle, and it seeks to
provide Oracle-like functionality on many levels. The InnoDB storage
engine provides row-level locking, non-locking reads, multiple
isolation levels, referential integrity, automatic recovery, and full
ACID guarantees. And it does so with impressive performance and seamless
integrated with MySQL. In fact, MySQL/InnoDB recently tied Oracle for first
place in a widely publicized third party benchmark.
That's quite an impressive list of features. If you're
quite familiar with database technology, you may be a bit suspicious. InnoDB
sounds too good to be true. But InnoDB really does provide all that -- and
more. But if you're a little rusty on database theory and that list didn't
mean a lot to you, here's what those features actually do:
- In a database that supports transactions,
queries can be grouped into indivisible units of work which are either
applied completely or not applied at all. Applied transactions are
committed, and aborted transactions are rolled back. Like most other SQL
databases, MySQL uses BEGIN, COMMIT, and ROLLBACK commands to
begin, commit, and rollback transactions, respectively.
|
- Isolation Levels. In the traditional view
of transactions, all transactions should be isolated from each other.
One transaction may not affect another running transaction. However, a
number of edge cases and degrees of isolation have developed over the
years. A full explanation is beyond the scope of this article, but
InnoDB natively supports READ-COMMITTED, REPEATABLE-READ, and
SERIALIZABLE.
|
- By locking individual rows (and only when
necessary), InnoDB tables provide a high degree of concurrency. That
means you can have hundreds or even thousands of clients using a single
table without locking each other out unless they're all trying to change
the same data.
|
- In a database that supports referential
integrity, you can define relationships between fields in related
InnoDB tables to ensure that records cannot be removed from one table if
they are still being referenced from another. InnoDB will enforce
referential integrity. For example, you can't accidentally delete a
customer (say, in the customer table) who has pending orders (in the
orders table).
|
- If MySQL is shutdown abnormally, InnoDB will
automatically rollback uncommitted transactions and ensure that
committed transactions are applied. Unless you find a bug in InnoDB, a
system crash will never corrupt your tables or cause you to lose any
data.
|
- Until recently, database snobs didn't consider
MySQL to be anything more than a toy because it didn't have ACID
guarantees. ACID is an acronym for Atomicity, Consistency,
Isolation, and Durability -- the four critical characteristics of a
robust database server. InnoDB covers all of them. Transactions are
guaranteed to be atomic, indivisible units of work. InnoDB uses a
multi-versioning concurrency control (MVCC) system, much like Oracle,
that ensures that clients see consistent views of their data, and that
running transactions are isolated from each other. InnoDB's logging
ensures that committed transactions can survive system failures.
|
That all sounds great, but how easy is it to use in practice? Simple. Really. Simple.
You can convert an existing table to InnoDB with a
simple ALTER TABLE command.
ALTER TABLE mytable TYPE = InnoDB;
The conversion takes a little while MySQL copies the
data into InnoDB's tablespace and builds indices. When the conversion
is done, you can use the InnoDB tables just like any other table. Like
Oracle, InnoDB uses one or more large files on disk (tablespaces) to store
all of its data and indices. MyISAM, on the other hand, uses individual files
for each table, and groups databases into subdirectories. InnoDB doesn't rely
on the filesystem to help represent the database structure.
If you'd like to create new tables, simply add a Type =
InnoDB to the end of your CREATE TABLE statements:
CREATE TABLE mytable ( ... ) Type =
InnoDB;
That's all it takes. If you're already familiar with
Oracle, you'll find that the transition to InnoDB tables is relatively
straightforward. You can easily check, if InnoDB was started, if you look at
the error-log file:
030923 11:35:27
mysqld started
030923 11:35:28 InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306
Installling MySQL
Installing a MySQL Binary Distribution
The basic commands you must execute to
install and use a MySQL binary distribution are:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &
Installing a MySQL Source Distribution
The basic commands you must execute to
install a MySQL source distribution are:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors
-fno-exceptions \
-fno-rtti" ./configure \
--prefix=/usr/local/mysql
--with-extra-charsets=all \
--datadir=/u01/mysql/data \
--with-unix-socket-path=/var/lib/mysql/mysql.sock
\
--enable-thread-safe-client
--enable-local-infile \
--enable-assembler
--disable-shared \
--with-client-ldflags=-all-static
\
--with-mysqld-ldflags=-all-static
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R root /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> /usr/local/mysql/bin/mysqld_safe --user=mysql &
The MySQL Configuration File: my.cnf
It's very likely that the first task the administrator will want to
undertake is proper configuration of MySQL's configuration file. This
file, entitled my.cnf, stores default startup options for both the
server and for clients. Correct configuration of this file can go a long
way towards optimizing MySQL, as various memory buffer settings and
other valuable options can be set here.
Interestingly, the scope of this file can be set according to its
location. The settings will be considered global to all MySQL servers if
stored in /etc/my.cnf. It will be global to a specific server if located
in the directory where the MySQL databases are stored (/usr/local/mysql/data
for a binary installation, or /usr/local/var for a source installation).
Finally, its scope could be limited to a specific user if located in the
home directory of the MySQL user (~/.my.cnf). Keep in mind that even if
MySQL does locate a my.cnf file in /etc/my.cnf (global to all MySQL
servers on that machine), it will continue its search for a
server-specific file, and then a user-specific file. You can think of
the final configuration settings as being the result of the /etc/my.cnf,
mysql-data-dir/my.cnf, and ~/.my.cnf files.
# Akadia AG, Arvenweg 4, CH-3604 Thun
/etc/my.cf
#
--------------------------------------------------------------------------
# File: /etc/my.cf
#
# Autor: Martin Zahn, 25.09.2003
#
# Purpose: MySQL config file for very large
systems
#
# Location: /etc
#
# Certified: MySQL 4.0.15
#
--------------------------------------------------------------------------
# The following options will be passed to all MySQL clients
[client]
password = mysql
port =
3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port =
3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer
= 384M
max_allowed_packet = 1M
table_cache
= 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache
= 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security
enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named
pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
# skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by
quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1',
MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method,
then
# start replication for the first time (even
unsuccessfully, for example
# if you mistyped the password in master-password and
the slave fails to
# connect), the slave will create a master.info file,
and any later
# change in this file to the variables' values below
will be ignored and
# overridden by the content of the master.info file,
unless you shutdown
# the slave server, delete master.info and restart the
slaver server.
# For that reason, you may want to leave the lines
below untouched
# (commented) and instead use CHANGE MASTER TO (see
above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
# server-id = 2
#
# The replication master for this slave - required
# master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
# master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
# master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
# log-bin
# Point the following paths to different dedicated disks
# tmpdir
= /tmp/
# log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
# bdb_cache_size = 384M
# bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir
= /usr/local/mysql/data/
innodb_data_file_path
= ibdata1:500M;ibdata2:500M:autoextend
innodb_log_group_home_dir
= /usr/local/mysql/data/
innodb_log_arch_dir
= /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size
= 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size
= 5242880
innodb_log_buffer_size
= 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout
= 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Post-installation Setup and Testing
Use mysqladmin to verify that the server is running. The
following commands provide a simple test to check that the server is up
and responding to connections:
shell> mysqladmin version
mysqladmin Ver 8.40 Distrib 4.0.15, for pc-linux on
i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.15-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 6 hours 45 min 39 sec
shell> mysqladmin variables
Variable_name
Value
-------------------------------------------------------------------------------
back_log
50
basedir
/usr/local/mysql-standard-4.0.15-pc-linux-i686/
binlog_cache_size
32768
bulk_insert_buffer_size
8388608
character_set
latin1
character_sets
latin1 big5 czech euc_kr gb2312 ......
concurrent_insert
ON
connect_timeout
5
convert_character_set
datadir
/usr/local/mysql/data/
default_week_format
0
delay_key_write
ON
delayed_insert_limit
100
delayed_insert_timeout
300
delayed_queue_size
1000
flush
OFF
flush_time
0
ft_boolean_syntax
+ -><()~*:""&
ft_min_word_len
4
ft_max_word_len
254
ft_max_word_len_for_sort
20
ft_stopword_file
(built-in)
have_bdb
NO
have_crypt
YES
have_innodb
YES
have_isam
YES
have_raid
NO
have_symlink
YES
have_openssl
NO
have_query_cache
YES
init_file
innodb_additional_mem_pool_size 20971520
innodb_buffer_pool_size
402653184
innodb_data_file_path
ibdata1:500M;ibdata2:500M:autoextend
innodb_data_home_dir
/usr/local/mysql/data/
innodb_file_io_threads
4
innodb_force_recovery
0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown
ON
innodb_flush_method
innodb_lock_wait_timeout
50
innodb_log_arch_dir
/usr/local/mysql/data/
innodb_log_archive
OFF
innodb_log_buffer_size
8388608
innodb_log_file_size
5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir /usr/local/mysql/data/
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout
28800
join_buffer_size
131072
key_buffer_size
402653184
language
/usr/local/mysql-standard-4.0.15-pc-linux-i686/share/mysql/english/
large_files_support
ON
local_infile
ON
locked_in_memory
OFF
log
OFF
log_update
OFF
log_bin
ON
log_slave_updates
OFF
log_slow_queries
OFF
log_warnings
OFF
long_query_time
10
low_priority_updates
OFF
lower_case_table_names
OFF
max_allowed_packet
1047552
max_binlog_cache_size
4294967295
max_binlog_size
1073741824
max_connections
100
max_connect_errors
10
max_delayed_threads
20
max_heap_table_size
16777216
max_join_size
4294967295
max_relay_log_size
0
max_seeks_for_key
4294967295
max_sort_length
1024
max_user_connections
0
max_tmp_tables
32
max_write_lock_count
4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size
2147483647
myisam_repair_threads
1
myisam_recover_options
OFF
myisam_sort_buffer_size
67108864
net_buffer_length
16384
net_read_timeout
30
net_retry_count
10
net_write_timeout
60
new
OFF
open_files_limit
1134
pid_file
/usr/local/mysql/data/venus.hsz.akadia.com.pid
log_error
port
3306
protocol_version
10
query_cache_limit
1048576
query_cache_size
33554432
query_cache_type
ON
read_buffer_size
2093056
read_only
OFF
read_rnd_buffer_size
262144
rpl_recovery_rank
0
server_id
1
slave_net_timeout
3600
skip_external_locking
ON
skip_networking
OFF
skip_show_database
OFF
slow_launch_time
2
socket
/var/lib/mysql/mysql.sock
sort_buffer_size
2097144
sql_mode
0
table_cache
512
table_type
MYISAM
thread_cache_size
8
thread_stack
126976
tx_isolation
REPEATABLE-READ
timezone
CEST
tmp_table_size
33554432
tmpdir
/tmp/
version
4.0.15-standard-log
wait_timeout
28800
shell> mysqlshow --user=root --password=xxxx
+-----------+
| Databases |
+-----------+
| bugs |
| mysql |
| test |
+-----------+
shell> mysqlshow --user=root --password=xxxx bugs
Database: bugs
+-------------------+
| Tables
|
+-------------------+
| attachments |
| attachstatusdefs |
| attachstatuses |
| bugs
|
| bugs_activity |
| cc
|
| components |
| dependencies |
| duplicates |
| fielddefs |
| groups
|
| keyworddefs |
| keywords |
| logincookies |
| longdescs |
| milestones |
| namedqueries |
| products |
| profiles |
| profiles_activity |
| shadowlog |
| tokens
|
| versions |
| votes
|
| watch
|
+-------------------+
shell> mysql --user=root --password=xxxx -e "SELECT
host,db,user FROM db" mysql
+-----------+---------+------+
| host | db
| user |
+-----------+---------+------+
| % | test
| |
| % | test\_% |
|
| localhost | bugs | bugs |
+-----------+---------+------+
There is also a benchmark suite in the 'sql-bench' directory (under
the MySQL installation directory) that you can use to compare how MySQL
performs on different platforms. The benchmark suite is written in Perl,
using the Perl DBI module to provide a database-independent interface to
the various databases. The following additional Perl modules are
required to run the benchmark suite:
DBI
DBD-mysql
Data-Dumper
Data-ShowTable
shell> ./run-all-tests --user=root --password=xxxx
Benchmark DBD suite: 2.14
Date of test: 2003-09-22
11:33:17
Running tests on: Linux 2.4.18-14smp i686
Arguments:
Comments:
Limits from:
Server version: MySQL 4.0.15 standard log
Optimization: None
Hardware:
alter-table: Total time: 5 wallclock secs ( 0.05 usr 0.01
sys + 0.00 cusr 0.00 csys = 0.06 CPU)
ATIS: Total time: 11 wallclock secs ( 7.03 usr 2.61 sys +
0.00 cusr 0.00 csys = 9.64 CPU)
big-tables: Total time: 12 wallclock secs ( 4.78 usr 4.59 sys +
0.00 cusr 0.00 csys = 9.37 CPU)
connect: Total time: 123 wallclock secs (46.98 usr 23.79 sys +
0.00 cusr 0.00 csys = 70.77 CPU)
create: Total time: 104 wallclock secs ( 6.45 usr 1.33 sys +
0.00 cusr 0.00 csys = 7.78 CPU)
insert: Total time: 1341 wallclock secs (484.20 usr 125.03 sys +
0.00 cusr 0.00 csys = 609.23 CPU)
select: Total time: 105 wallclock secs (41.35 usr 10.26 sys + 0.00
cusr 0.00 csys = 51.61 CPU)
transactions: Test skipped because the database doesn't support
transactions
wisconsin: Total time: 7 wallclock secs ( 2.80 usr 0.87 sys
+ 0.00 cusr 0.00 csys = 3.67 CPU)
All 9 test executed successfully
Totals per operation:
Operation
seconds usr sys
cpu tests
alter_table_add
1.00 0.00 0.00
0.00 20
alter_table_drop
1.00 0.01 0.00
0.01 20
connect
8.00 5.11 0.58
5.69 10000
connect+select_1_row
10.00 6.45 1.07
7.52 10000
connect+select_simple
8.00 6.08 0.83
6.91 10000
count
14.00 0.03 0.00
0.03 100
count_distinct
0.00 0.17 0.03
0.20 1000
count_distinct_big
11.00 6.32 2.48
8.80 120
count_distinct_group
1.00 0.82 0.25
1.07 1000
count_distinct_group_on_key
0.00 0.27 0.01
0.28 1000
count_distinct_group_on_key_parts 2.00
0.84 0.26 1.10
1000
count_distinct_key_prefix
0.00 0.20 0.02
0.22 1000
count_group_on_key_parts
1.00 0.81 0.27
1.08 1000
count_on_key
29.00 8.10 1.47
9.57 50100
create+drop
10.00 1.59 0.33
1.92 10000
create_MANY_tables
73.00 1.65 0.24
1.89 10000
create_index
1.00 0.00 0.00
0.00 8
create_key+drop
12.00 1.96 0.32
2.28 10000
create_table
0.00 0.01 0.01
0.02 31
delete_all_many_keys
84.00 0.01 0.01
0.02 1
delete_big
0.00 0.00 0.00
0.00 1
delete_big_many_keys
84.00 0.01 0.01
0.02 128
delete_key
2.00 0.34 0.17
0.51 10000
delete_range
4.00 0.00 0.00
0.00 12
drop_index
2.00 0.00 0.00
0.00 8
drop_table
0.00 0.00 0.00
0.00 28
drop_table_when_MANY_tables
2.00 0.30 0.14
0.44 10000
insert
51.00 11.56 5.56 17.12
350768
insert_duplicates
11.00 3.30 1.47
4.77 100000
insert_key
175.00 8.03 1.78
9.81 100000
insert_many_fields
3.00 0.33 0.05
0.38 2000
insert_select_1_key
2.00 0.00 0.00
0.00 1
insert_select_2_keys
2.00 0.00 0.00
0.00 1
min_max
5.00 0.02 0.00
0.02 60
min_max_on_key
21.00 14.30 2.47 16.77
85000
multiple_value_insert
1.00 0.32 0.01
0.33 100000
order_by_big
33.00 18.75 8.55 27.30
10
order_by_big_key
28.00 19.05 8.51 27.56
10
order_by_big_key2
27.00 18.28 8.15 26.43
10
order_by_big_key_desc
29.00 19.23 8.39 27.62
10
order_by_big_key_diff
32.00 18.73 8.59 27.32
10
order_by_big_key_prefix
27.00 18.50 8.13 26.63
10
order_by_key2_diff
3.00 1.64 0.63
2.27 500
order_by_key_prefix
2.00 0.87 0.34
1.21 500
order_by_range
2.00 1.01 0.29
1.30 500
outer_join
2.00 0.00 0.00
0.00 10
outer_join_found
2.00 0.00 0.00
0.00 10
outer_join_not_found
2.00 0.00 0.00
0.00 500
outer_join_on_key
2.00 0.00 0.00
0.00 10
select_1_row
18.00 4.27 3.79
8.06 100000
select_1_row_cache
8.00 2.76 3.20
5.96 100000
select_2_rows
21.00 4.50 4.12
8.62 100000
select_big
27.00 18.47 8.19 26.66
80
select_big_str
15.00 7.66 1.65
9.31 10000
select_cache
2.00 1.63 0.31
1.94 10000
select_cache2
50.00 2.96 0.43
3.39 10000
select_column+column
19.00 4.14 3.18
7.32 100000
select_diff_key
65.00 0.24 0.03
0.27 500
select_distinct
2.00 1.30 0.49
1.79 800
select_group
4.00 0.98 0.26
1.24 2911
select_group_when_MANY_tables
7.00 0.95 0.30
1.25 10000
select_join
1.00 0.39 0.16
0.55 100
select_key
89.00 48.54 8.22 56.76
200000
select_key2
92.00 49.26 8.37 57.63
200000
select_key2_return_key
89.00 48.33 6.04 54.37
200000
select_key2_return_prim
91.00 48.70 6.97 55.67
200000
select_key_prefix
91.00 49.57 8.24 57.81
200000
select_key_prefix_join
4.00 3.43 1.38
4.81 100
select_key_return_key
87.00 47.68 6.92 54.60
200000
select_many_fields
9.00 4.45 4.54
8.99 2000
select_range
11.00 7.32 2.52
9.84 410
select_range_key2
5.00 3.52 0.83
4.35 25010
select_range_prefix
5.00 3.44 0.84
4.28 25010
select_simple
8.00 2.99 2.76
5.75 100000
select_simple_cache
8.00 3.02 2.61
5.63 100000
select_simple_join
1.00 0.47 0.16
0.63 500
update_big
14.00 0.01 0.00
0.01 10
update_of_key
10.00 1.95 0.73
2.68 50000
update_of_key_big
7.00 0.03 0.01
0.04 501
update_of_primary_key_many_keys
35.00 0.01 0.01
0.02 256
update_with_key
45.00 10.06 4.88 14.94
300000
update_with_key_prefix
16.00 5.93 1.70
7.63 100000
wisc_benchmark
2.00 1.74 0.41
2.15 114
TOTALS
1780.00 585.70 165.67 751.37 3224799
Database Administration
Exporting and Importing into and from ASCII Files
Pulling data from MySQL into an external, ascii File:
USE bugs;
mysql> SELECT * INTO OUTFILE 'users.dat'
FIELDS OPTIONALLY ENCLOSED BY '"'
TERMINATED BY ';'
FROM profiles;
Importing the external file:
mysql> CREATE DATABASE martin;
mysql> use martin;
mysql> CREATE TABLE profiles (
-> userid mediumint(9) NOT NULL auto_increment,
-> login_name varchar(255) NOT NULL default '',
-> cryptpassword varchar(34) default NULL,
-> realname varchar(255) default NULL,
-> groupset bigint(20) NOT NULL default '0',
-> disabledtext mediumtext NOT NULL,
-> mybugslink tinyint(4) NOT NULL default '1',
-> blessgroupset bigint(20) NOT NULL default '0',
-> emailflags mediumtext,
-> PRIMARY KEY (userid),
-> UNIQUE KEY login_name (login_name)
-> ) TYPE=InnoDB;
mysql> desc profiles;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type
| Null | Key | Default | Extra
|
+---------------+--------------+------+-----+---------+----------------+
| userid | mediumint(9) |
| PRI | NULL | auto_increment |
| login_name | varchar(255) |
| UNI | |
|
| cryptpassword | varchar(34) | YES |
| NULL |
|
| realname | varchar(255) | YES |
| NULL |
|
| groupset | bigint(20) |
| | 0 |
|
| disabledtext | mediumtext |
| |
|
|
| mybugslink | tinyint(4) |
| | 1 |
|
| blessgroupset | bigint(20) |
| | 0 |
|
| emailflags | mediumtext | YES |
| NULL |
|
+---------------+--------------+------+-----+---------+----------------+
mysql> LOAD DATA LOCAL INFILE 'users.dat'
-> INTO TABLE PROFILES
-> FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ';';
Setting Up the Initial MySQL Privileges
After installing MySQL, you set up the initial access privileges by
running scripts/mysql_install_db. The
mysql_install_db script starts up the
mysqld server, then initialises the grant tables to contain the
following set of privileges:
- The MySQL root user is created as a superuser who can do
anything. Connections must be made from the local host. Note: The
initial root password is empty, so anyone can connect as root without
a password and be granted all privileges.
- An anonymous user is created that can do anything with
databases that have a name of 'test' or starting with 'test_'.
Connections must be made from the local host. This means any local
user can connect without a password and be treated as the anonymous
user.
Because your installation is initially wide open, one of the
first things you should do is specify a password for the MySQL root user.
You can do this as follows (note that you specify the password using the
PASSWORD() function):
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
Adding New Users to MySQL
You can add users two different ways: by using
GRANT statements or by manipulating the MySQL grant tables directly.
The preferred method is to use GRANT statements, because they are more
concise and less error-prone.
First, use the mysql program to connect to the server
as the MySQL root user:
shell> mysql --user=root --password=xxxx mysql
Then you can add new users by issuing GRANT statements:
mysql> GRANT ALL PRIVILEGES ON *.* TO bugs@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO bugs@'%'
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
Recovering from Password Problems
If you garble your GRANT commands
or forget passwords and find that you don't have access to the critical
mysql table - even as the root user - don't panic. Bocome the superuser
on the operating system (e.g. the Unix root, not the MySQL root) and
kill the MySQL process. On a RedHat Linux System, you might be able to
end MySQL through the command:
shell> /etc/rc.d/init.d/mysqld
stop
Now start up MySQL again, bypassing the grant
tables and assign a new password for the MySQL root user:
shell> cd /usr/local/mysql/bin
shell> ./mysqld_safe --skip-grant-tables 1>/dev/null 2>&1 &
shell> mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') WHERE
user='root';
mysql> exit;
Now, find all MySQL Processes and
kill them explicitly as root:
shell> ps ax | grep mysql
shell> kill xxxx
Now, you can start MySQL again with
the normal startup parameters, the password is now changed:
shell> /etc/rc.d/init.d/mysqld
start
Database Backups
Because MySQL tables are stored as files, it is easy to do a backup.
mysqldump --user=root --password=xxxx --opt mysql
> mysql.sql
mysqldump --user=root --password=xxxx --quick mysql > mysql.dump
mysqlhotcopy --user=root --password=xxxx --allowold --keepold mysql /home/zahn/backup
The MySQL Log Files
MySQL has several different log
files that can help you find out what's going on inside mysqld:
-
The error log - Problems
encountering starting, running or stopping mysqld.
-
The query log - Established
connections and executed queries.
-
The binary log - Stores all
statements that changes something. Used also for replication
The error log file contains
information indicating when mysqld was started and stopped and also any
critical errors found when running.
If mysqld dies unexpectedly and
mysqld_safe needs to restart mysqld, mysqld_safe will write a restarted
mysqld row in this file. This log also holds a warning if mysqld notices
a table that needs to be automatically checked or repaired.
Beginning with MySQL 4.0.10 you can
specify where mysqld stores the error log file with the option
--log-error[=filename]. If no file name is given mysqld will use
mysql-data-dir/'hostname'.err on Unix.
If you want to know what happens
within mysqld, you should start it with --log[=file]. This will log all
connections and queries to the log file (by default named
mysql-data-dir/'hostname'.log. This log
can be very useful when you suspect an error in a client and want to
know exactly what mysqld thought the client sent to it.
The entries in this log are written
as mysqld receives the questions. This may be different from the order
in which the statements are executed. This is in contrast to the update
log and the binary log which are written after the query is executed,
but before any locks are released.
The binary log contains all information that is available in the
update log in a more efficient format. It also contains information
about how long each query took that updated the database. It doesn't
contain queries that don't modify any data.
You can examine the binary log file with the mysqlbinlog utility.
shell> mysqlbinlog hostname-bin.001
will print all queries contained in binlog 'hostname-bin.001',
together with information (time the query took, id of the thread which
issued it, timestamp when it was issued etc).
You can pipe the output of mysqlbinlog into a mysql client; this is
used to recover from a crash when you have an old backup:
shell> mysqlbinlog hostname-bin.001
hostname-bin.002 | mysql
or
shell> mysqlbinlog hostname-bin.001 > /tmp/queries.sql
shell> mysqlbinlog hostname-bin.002 >> /tmp/queries.sql
shell> mysql -e "source /tmp/queries.sql"
Starting and Stopping MySQL
Automatically
You can use the following script to start / stop
the server automatically at system startup time.
#!/bin/bash
prog="MySQL"
bindir="/usr/local/mysql/bin"
datadir="/usr/local/mysql/data"
lockfile="/var/lock/subsys/mysqld"
socket="/var/lib/mysql/mysql.sock"
mysqlhost="`uname -n`"
errlog="${datadir}/${mysqlhost}.err"
querylog="${datadir}/${mysqlhost}.log"
pidfile="${datadir}/${mysqlhost}.pid"
configfile="/etc/my.cnf"
mysqluser="mysql"
start(){
touch ${errlog}
chown mysql.mysql ${errlog}
chmod 0640 ${errlog}
touch ${querylog}
chown mysql.mysql ${querylog}
chmod 0640 ${querylog}
# DEBUG SQL
Queries
${bindir}/mysqld_safe --defaults-file=${configfile}
--user=${mysqluser} --log=${querylog} >/dev/null 2>&1 &
# Normal
Operation
# ${bindir}/mysqld_safe
--defaults-file=${configfile} --user=${mysqluser} >/dev/null 2>&1 &
ret=$?
if [ $ret -eq 0 ]; then
action $"Starting
$prog: " /bin/true
else
action $"Starting
$prog: " /bin/false
fi
[ $ret -eq 0 ] && touch ${lockfile}
return $ret
}
stop(){
/bin/kill `cat ${pidfile} 2> /dev/null
` > /dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
action $"Stopping
$prog: " /bin/true
else
action $"Stopping
$prog: " /bin/false
fi
[ $ret -eq 0 ] && rm -f ${lockfile}
[ $ret -eq 0 ] && rm -f ${socket}
return $ret
}
restart(){
stop
start
}
condrestart(){
[ -e /var/lock/subsys/mysqld ] && restart || :
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status mysqld
;;
restart)
restart
;;
condrestart)
condrestart
;;
*)
echo $"Usage: $0 {start|stop|status|condrestart|restart}"
exit 1
esac
exit $?
Using myisamchk for Table Maintenance
and Crash Recovery
To check/repair MyISAM tables ('.MYI') you should
use the myisamchk utility. To check/repair ISAM tables ('.ISM')
you should use the isamchk utility. In the following text we will
talk about myisamchk, but everything also applies to the old isamchk.
Even though the repair in myisamchk is quite
secure, it's always a good idea to make a backup before doing a repair (or
anything that could make a lot of changes to a table).
For example, if you are in a database directory,
you can check all the tables in the directory like this:
shell> myisamchk *.MYI
The recommended way to quickly check all tables is:
shell> myisamchk --silent
--fast *.MYI
If you want to check all tables and repair all
tables that are corrupted, you can use the following line:
shell> myisamchk --force --fast
--update-state -O key_buffer=64M \
-O sort_buffer=64M -O read_buffer=1M \
-O write_buffer=1M *.MYI
Note that if you get an error like:
myisamchk:
warning: 1 clients is using or hasn't closed the table properly
This means that you are trying to
check a table that has been updated by another program (like the mysqld
server) that hasn't yet closed the file or that has died without closing
the file properly. If mysqld is running, you must force a sync/close of
all tables with FLUSH TABLES and ensure that no one is using the tables
while you are running myisamchk.
Full-Text Search
Text searches in MySQL have long been a hit or miss proposition.
Prefix matches like "every name that starts with art" are easy. Given a
query like:
SELECT * FROM mytable WHERE name LIKE "art%"
MySQL can perform the search very quickly if the name column is
indexed. MySQL traverses the B-Tree index very quickly to locate all
possible matches, finding "art gallery," "art exhibit," and so on.
However, if the search is a bit less specific, things degrade. For
example, try to ask for "every name that contains art." You'd probably
write:
SELECT * FROM mytable WHERE name LIKE "%art%"
In this case, MySQL has a lot more work to do. Because the text is no
longer "anchored" to the beginning of the string, MySQL cannot simply
walk an index to find all possible matches. Instead, MySQL performs an
index scan. It reads every node in the index and performs a search on
each one. Since the index is much smaller than the table, this is a lot
faster than a full table scale, but it clearly doesn't scale well.
On the upside, of course, you'd find matches like "regional art
services" and "Bart Simpson." Oops. Maybe you didn't want to match
"Bart." You could use a complex regular expression and RLIKE instead of
LIKE to specify exactly what you mean. But MySQL can't really optimize
that query either -- for the same reasons.
Even if MySQL had a way of optimizing such queries, it would still
leave room for improvement. Why? Because most of the time, when a query
asks for all records where "foo" is in the name field, the query really
doesn't want all the records -- it probably wants only the most relevant
records. If "foo" occurs three times in one record, it's probably more
relevant than another where the pattern only occurs once at the very end
of the string.
Very often, that kind of query is initiated by someone who doesn't
know anything about MySQL at all. The user just types into a Web form
and expects it to be as smart as Google. The user may even type more
than one word, complicating the task even more. Obviously, a match is
more relevant if the words are found in close proximity to each other.
But standard SQL has no way of expressing such a query.
Luckily, MySQL provides full-text indexing and full-text search.
While not new in 4.0, MySQL's full-text search capabilities have
improved substantially in recent releases.
A full-text index can contain one or more "text" fields (CHAR,
VARCHAR, TEXT, etc.) It looks like this:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
INSERT INTO articles VALUES
(NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
(NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
(NULL,'Optimising MySQL','In this tutorial we will show ...'),
(NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
(NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
(NULL,'MySQL Security', 'When configured properly, MySQL ...');
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title
| body
|
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ...
|
| 1 | MySQL Tutorial | DBMS stands for DataBase
...
|
+----+-------------------+------------------------------------------+
Subqueries
One of the biggest hang-ups would-be MySQL users have had about
migrating their code is the lack of subqueries. In fact, it has probably
been the most frequently recurring topic on the mailing list for the
last year or two. Would-be users have been writing queries for so long
that they can't think of a way to do without them. As of MySQL 4.1,
there won't be any reason to -- from that release on, MySQL will handle
subqueries just fine.
SELECT * FROM used_cars
WHERE color = 'red' AND model IN
(SELECT DISTINCT(type) FROM crash_data WHERE death_rate < 10)
A subquery is a query within a query. The example shown asks MySQL to
fetch the list of distinct car types that ranked below 10 (whatever that
means) based on crash data. Then, using that list, find all the red,
used cars of the same model.
Multi-Table and Ordered Deletes
MySQL has always had a reputation for being practical. When enough
users need a new SQL extension, it generally gets implemented sooner or
later. The LIMIT clause is a great example. When you need only the first
20 records from a query, simply add a LIMIT:
SELECT * FROM mytable LIMIT 20;
And MySQL does what you'd expect. Want the next 10 records? No
problem:
SELECT * FROM mytable LIMIT 19, 10;
Yes, MySQL counts rows starting from 0 rather than 1. And the limit
can be applied to DELETE and UPDATE queries as well as SELECT.
The latest MySQL enhancements also improve DELETE queries. As of
MySQL 4.0, you can apply an ORDER BY as well as a LIMIT clause to a
DELETE query. That means it's easy to tell MySQL, "Delete the 500 oldest,
inactive records from the archive table," without getting the list of
records and deleting them individually. Instead, you can simply write:
DELETE FROM archive WHERE status = 'Inactive' ORDER
BY Time ASC LIMIT 500;
And, again, it does what you expect.
But it gets even better. As of 4.0, you can perform a relational
or multi-table delete. It only makes sense that you'd want to use a
join expression in the WHERE clause of a DELETE query. After all, in a
relational database, it's common for the information to be spread among
several tables (assuming it was properly normalized). Taking things a
step further, you can even delete records from multiple related tables
in a single query.
For example, to remove all Britney Spears albums from your nicely
organized and normalized CD/MP3 collection, you can ask MySQL to
simultaneously delete all of the album and track data, using her artist
information.
DELETE FROM album, track
USING album, track, artist
WHERE track.album_id = album.id
AND album.artist_id = artist.id
AND artist.name = 'Britney Spears';
Records will be removed from the album and track tables but not the
artist table. That's powerful stuff. Here's an alternative syntax that
some find easier to understand:
DELETE album, track FROM album, track, artist
WHERE track.album_id = album.id
AND album.artist_id = artist.id
AND artist.name = 'Britney Spears';
Notice that the USING clause is gone and the target tables are listed
right after DELETE, much as you'd list column names in a SELECT query.
Internationalization (New in Version 4.1)
MySQL (itself) has been internationalized for quite some time. The
messages produced by MySQL have been translated into over 20 languages.
By default, MySQL also provides as many character sets to choose from
when starting MySQL. By selecting the character set appropriate for your
data, you ensure that MySQL sorts records appropriately in queries that
use ORDER BY, and performs string comparisons as appropriate for the
character set.
However, if you need to store data in multiple character sets, MySQL
3.23 doesn't offer much help. You can change the server's default
character set using the --default-character-set option at start-up time,
but that's about it. Starting in version 4.1, you'll be able to
set the default character set on a per database, per table, and per
column basis.
To set the default character set on the database "beer" to German,
simple execute:
CREATE DATABASE beer DEFAULT CHARACTER SET
latin1;
To create a table to store Unicode text encoded as UTF-8, specify the
character set at the end of the CREATE TABLE statement:
CREATE TABLE unicode_stuff (
...
) CHARACTER SET utf8;
And to create a table to hold data in multiple character sets,
specify each field's character set explicitly. See Listing Two. Notice
that each text field uses a different character set.
CREATE TABLE intl_strings (
id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
german_string VARCHAR(255) CHARACTER SET latin1,
japanese_string VARCHAR(255) CHARACTER SET utf8,
enlish_us_string VARCHAR(255) CHARACTER SET latin1,
chinese_string VARCHAR(255) CHARACTER SET big5
);
If you don't specify a character set, all text columns automatically
inherit the table's character set. Tables inherit the database's
character set, and databases inherit the server's character set.
After upgrading to MySQL 4.1, you may need to adjust the character
sets of databases, tables, and columns. To do so, you can use the
appropriate ALTER command. For example, to change a database's default
character set:
ALTER DATABASE beer DEFAULT CHARACTER SET
latin1;
And to reset a database's character set back to the server's default:
ALTER DATABASE beer DEFAULT CHARACTER SET DEFAULT;
Similarly, you can use ALTER TABLE to set a table's character set:
ALTER TABLE messy_stuff CHARACTER SET big5;
And individual columns can be modified as well. Simply provide the
new column definition:
ALTER TABLE messy_stuff MODIFY chinese
VARCHAR(255) CHARACTER SET big5;
There is also a SHOW CHARACTER SET command to ask MySQL which
character sets it can support.
SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description
| Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese
| big5
| 2 |
| dec8 | DEC West European
| dec8_swedish_ci |
1 |
| cp850 | DOS West European
| cp850_general_ci | 1 |
| hp8 | HP West European
| hp8_english_ci |
1 |
| koi8r | KOI8-R Relcom Russian
| koi8r_general_ci | 1 |
| latin1 | ISO 8859-1 West European |
latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci
| 1 |
| swe7 | 7bit Swedish
| swe7_swedish_ci |
1 |
| ascii | US ASCII
| ascii_general_ci | 1 |
| ujis | EUC-JP Japanese
| ujis
| 3 |
| sjis | Shift-JIS Japanese
| sjis
| 2 |
| cp1251 | Windows Cyrillic
| cp1251_bulgarian_ci | 1 |
| hebrew | ISO 8859-8 Hebrew
| hebrew
| 1 |
| tis620 | TIS620 Thai
| tis620
| 1 |
| euckr | EUC-KR Korean
| euckr
| 2 |
| koi8u | KOI8-U Ukrainian
| koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312
| 2 |
| greek | ISO 8859-7 Greek
| greek
| 1 |
| cp1250 | Windows Central European |
cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese
| gbk
| 2 |
| latin5 | ISO 8859-9 Turkish
| latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian
| armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode
| utf8
| 3 |
| ucs2 | UCS-2 Unicode
| ucs2
| 2 |
| cp866 | DOS Russian
| cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2
| 1 |
| macce | Mac Central European
| macce
| 1 |
| macroman | Mac West European
| macroman
| 1 |
| cp852 | DOS Central European
| cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic
| latin7_general_ci | 1 |
| cp1256 | Windows Arabic
| cp1256_general_ci | 1 |
| cp1257 | Windows Baltic
| cp1257_ci_ai |
1 |
| binary | Binary pseudo charset
| binary
| 1 |
+----------+-----------------------------+---------------------+--------+
33 rows in set (0.00 sec)
|