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
Show the Status of the InnoDB Database
mysql> SHOW INNODB STATUS;
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> ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex
\
--enable-thread-safe-client
--enable-local-infile \
--with-unix-socket-path=/tmp/mysql.sock
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
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('xxxx');
or:
shell> mysqladmin --user=root password 'xxxx'
shell>
mysqladmin --user=root password ’’
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
--user=root --password=xxxx
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 processlist --user=root --password=xxxx
+----+--------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command |
Time | State |
Info |
+----+--------+-----------+------+---------+------+-------+------------------+
| 5 | amavis | localhost | maia | Sleep | 2124
|
|
|
| 19 | amavis | localhost | maia | Sleep | 2121
|
|
|
| 28 | root | localhost | | Query |
0 | | show processlist |
+----+--------+-----------+------+---------+------+-------+------------------+
shell> mysqladmin variables
--user=root --password=xxxx
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
using Load Data
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
';';
Exporting and Importing using Mysqldump
mysqldump --user=root --password=xxx --opt bugs >
bugs.sql
mysql> create database bugs;
mysql> exit;
mysql --user=root --password=xxx bugs < bugs.sql
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)
|
