WEB HOSTING Y DISEñO DE PÁGINAS WEB

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.

Using InnoDB

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

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.

The General Query Log

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

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)

Regresar