MySQL Databases Overview

MySQL is written in C and C++. The SQL parser uses yacc and a home-brewed lexer, sql_lex.cc

MySQL works on many different system platforms, including AIX, BSDi, FreeBSD, HP-UX, i5/OS, Linux, Mac OS X, NetBSD, Novell NetWare, OpenBSD, OpenSolaris, eComStation, OS/2 Warp, QNX, IRIX, Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos, Tru64 and Microsoft Windows. A port of MySQL to OpenVMS is also available.

Libraries for accessing MySQL databases are available in all major programming languages with language-specific APIs. In addition, an ODBC interface called MyODBC allows additional programming languages that support the ODBC interface to communicate with a MySQL database, such as ASP or ColdFusion. The MySQL server and official libraries are mostly implemented in ANSI C/ANSI C++.

To administer MySQL databases one can use the included command-line tool (commands: mysql and mysqladmin). Also downloadable from the MySQL site are GUI administration tools: MySQL Administrator and MySQL Query Browser. Both of the GUI tools are now included in one package called tools/5.0.html MySQL GUI Tools.

In addition to the above-mentioned tools developed by MySQL AB, there are several other commercial and non-commercial tools available. Examples include Navicat Free Lite Edition, Database Master (Freeware) or SQLyog Community Edition, they are free desktop based GUI tools and phpMyAdmin, a free Web-based administration interface implemented in PHP.bdb_cache_size The bu er that is allocated to cache index and rows for BDB tables.

If you don’t use BDB tables, you should start mysqld with --skip-bdb to not waste
memory for this cache.

bdb_log_buffer_size The bu er that is allocated to cache index and rows for BDB
tables. If you don’t use BDB tables, you should set this to 0 or start mysqld with
--skip-bdb to not waste memory for this cache.
bdb_home The value of the --bdb-home option.
bdb_max_lock The maximum number of locks (1000 by default) you can have active on
a BDB table. You should increase this if you get errors of type bdb: Lock table is out
of available locks or Got error 12 from ... when you have do long transactions or
when mysqld has to examine a lot of rows to calculate the query.
bdb_logdir The value of the --bdb-logdir option.
bdb_shared_data Is ON if you are using --bdb-shared-data.
bdb_tmpdir The value of the --bdb-tmpdir option.
binlog_cache_size. The size of the cache to hold the SQL statements for the binary
log during a transaction. If you often use big, multi-statement transactions you can
increase this to get more performance.
character_set The default character set.
character_sets The supported character sets.
concurrent_inserts If ON (the default), MySQL will allow you to use INSERT on
MyISAM tables at the same time as you run SELECT queries on them. You can turn this
option o by starting mysqld with --safe or --skip-new.
connect_timeout The number of seconds the mysqld server is waiting for a connect
packet before responding with Bad handshake.
datadir The value of the --datadir option.
delay_key_write If enabled (is on by default), MySQL will honor the delay_key_
write option CREATE TABLE. This means that the key bu er for tables with this option
will not get ushed on every index update, but only when a table is closed. This will
speed up writes on keys a lot, but you should add automatic checking of all tables with
myisamchk --fast --force if you use this. Note that if you start mysqld with the
--delay-key-write-for-all-tables option this means that all tables will be treated
as if they were created with the delay_key_write option. You can clear this ag by
starting mysqld with --skip-new or --safe-mode.
delayed_insert_limit After inserting delayed_insert_limit rows, the INSERT
DELAYED handler will check if there are any SELECT statements pending. If so, it allows
these to execute before continuing.
delayed_insert_timeout How long a INSERT DELAYED thread should wait for INSERT
statements before terminating.
delayed_queue_size What size queue (in rows) should be allocated for handling
INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will
wait until there is room in the queue again.
flush This is ON if you have started MySQL with the --flush option.
flush_time If this is set to a non-zero value, then every flush_time seconds all tables
will be closed (to free up resources and sync things to disk). We only recommend this
option on Win95, Win98, or on systems where you have very little resources.
ft_min_word_len The minimum length of the word to be included in a FULLTEXT
index. Note: FULLTEXT index have to be rebuilt after changing this variable.
ft_max_word_len The maximum length of the word to be included in a FULLTEXT
index. Note: FULLTEXT index have to be rebuilt after changing this variable.
ft_max_word_len_sort The maximum length of the word in a FULLTEXT index to be
used in fast index recreation method in REPAIR, CREATE INDEX, or ALTER TABLE. Longer
words are inserted the slow way. The rule of the thumb is as follows: with ft_max_
word_len_sort increasing, MySQL will create bigger temporary files (thus slowing
the process down, due to disk I/O), and will put fewer keys in one sort block (againg,
decreasing the e ciency). When ft_max_word_len_sort is too small, instead, MySQL
will insert a lot of words into index the slow way - but short words will be inserted
very fast. It applies only to index recreation during REPAIR, CREATE INDEX, or ALTER
TABLE.
have_innodb YES if mysqld supports InnoDB tables. DISABLED if --skip-innodb is
used.
have_bdb YES if mysqld supports Berkeley DB tables. DISABLED if --skip-bdb is used.
have_raid YES if mysqld supports the RAID option.
have_openssl YES if mysqld supports SSL (encryption) on the client/server protocol.
init_file The name of the file specified with the --init-file option when you start
the server. This is a file of SQL statements you want the server to execute when it
starts.
interactive_timeout The number of seconds the server waits for activity on an inter-
active connection before closing it. An interactive client is defined as a client that uses
the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.
join_buffer_size The size of the bu er that is used for full joins (joins that do not use
indexes). The bu er is allocated one time for each full join between two tables. Increase
this value to get a faster full join when adding indexes is not possible. (Normally the
best way to get fast joins is to add indexes.)

key_buffer_size Index blocks are bu ered and are shared by all threads. key_
buffer_size is the size of the bu er used for index blocks.
Increase this to get better index handling (for all reads and multiple writes) to as much
as you can a ord; 64M on a 256M machine that mainly runs MySQL is quite common.
If you, however, make this too big (more than 50% of your total memory?) your system
may start to page and become extremely slow. Remember that because MySQL does
not cache data read, that you will have to leave some room for the OS filesystem cache.
You can check the performance of the key bu er by doing show status and exam-
ine the variables Key_read_requests, Key_reads, Key_write_requests, and Key_
writes. The Key_reads/Key_read_request ratio should normally be < 0 =" no"> SHOW GRANTS FOR root@localhost;

+-----------------------------------
----------------------------------+
| Grants for root@localhost
|

Chapter 4: MySQL Database Administration
243
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ’root’@’localhost’ WITH GRANT OPTION |
+---------------------------------------------------------------------+
4.5.5.8 SHOW CREATE TABLE
Shows a CREATE TABLE statement that will create the given table:
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id int(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM
SHOW CREATE TABLE will quote table and column names according to SQL_QUOTE_SHOW_
CREATE option. Section 5.5.6 [SET OPTION SQL_QUOTE_SHOW_CREATE], page 334.
4.6 MySQL Localisation and International Usage
4.6.1 The Character Set Used for Data and Sorting
By default, MySQL uses the ISO-8859-1 (Latin1) character set with sorting according to
Swedish/Finnish. This is the character set suitable in the USA and western Europe.
All standard MySQL binaries are compiled with --with-extra-charsets=complex. This
will add code to all standard programs to be able to handle latin1 and all multi-byte
character sets within the binary. Other character sets will be loaded from a character-set
definition file when needed.
The character set determines what characters are allowed in names and how things are
sorted by the ORDER BY and GROUP BY clauses of the SELECT statement.
You can change the character set with the --default-character-set option when you start
the server. The character sets available depend on the --with-charset=charset and --
with-extra-charset= list-of-charset | complex | all options to configure, and the
character set configuration files listed in ‘SHAREDIR/charsets/Index’.
If you change the character set when running MySQL (which may also change the sort
order), you must run myisamchk -r -q on all tables. Otherwise your indexes may not be
ordered correctly.
When a client connects to a MySQL server, the server sends the default character set in
use to the client. The client will switch to use this character set for this connection.

Mostrare tutte le immagini di questo Slideshow

Google

Custom Search

Adserver 610x250

If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog:

One should use mysql_real_escape_string() when escaping strings for a SQL query.
mysql_real_escape_string() is identical to the old mysql_escape_string() function,
except that it takes the MYSQL connection handle as the first parameter.
If the client is compiled with di erent paths than where the server is installed and the user
who configured MySQL didn’t included all character sets in the MySQL binary, one must
specify for the client where it can find the additional character sets it will need if the server
runs with a di erent character set than the client.
One can specify this by putting in a MySQL option file:
[client]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
where the path points to where the dynamic MySQL character sets are stored.
One can force the client to use specific character set by specifying:
[client]
default-character-set=character-set-name
but normally this is never needed.

German character set
To get German sorting order, you should start mysqld with --default-character-
set=latin_de. This will give you the following characteristics.
When sorting and comparing string’s the following mapping is done on the strings before
doing the comparison:
-> ae
-> oe
-> ue
-> ss
All accented characters, are converted to their un-accented uppercase counterpart. All
letters are converted to uppercase.
When comparing strings with LIKE the one -> two character mapping is not done. All
letters are converted to uppercase. Accent are removed from all letters except: , , , , and .

Non-English Error Messages
mysqld can issue error messages in the following languages: Czech, Danish, Dutch, English
(the default), Estonian, French, German, Greek, Hungarian, Italian, Japanese, Korean,
Norwegian, Norwegian-ny, Polish, Portuguese, Romanian, Russian, Slovak, Spanish, and
Swedish.
To start mysqld with a particular language, use either the --language=lang or -L lang
options. For example:
shell> mysqld --language=swedish
or:

shell> mysqld --language=/usr/local/share/swedish
Note that all language names are specified in lowercase.
The language files are located (by default) in ‘mysql base dir/share/LANGUAGE/’.
To update the error message file, you should edit the ‘errmsg.txt’ file and execute the
following command to generate the ‘errmsg.sys’ file:
shell> comp_err errmsg.txt errmsg.sys
If you upgrade to a newer version of MySQL, remember to repeat your changes with the
new ‘errmsg.txt’ file.

Adding a New Character Set
To add another character set to MySQL, use the following procedure.
Decide if the set is simple or complex. If the character set does not need to use special
string collating routines for sorting and does not need multi-byte character support, it is
simple. If it needs either of those features, it is complex.
For example, latin1 and danish are simple charactersets while big5 or czech are complex
character sets.
In the following section, we have assumed that you name your character set MYSET.
For a simple character set do the following:
1. Add MYSET to the end of the ‘sql/share/charsets/Index’ file Assign an unique
number to it.
2. Create the file ‘sql/share/charsets/MYSET.conf’. (You can use ‘sql/share/charsets/latin1.conf’
as a base for this.)
The syntax for the file very simple:
• Comments start with a ’#’ character and proceed to the end of the line.
• Words are separated by arbitrary amounts of whitespace.
• When defining the character set, every word must be a number in hexadecimal
format
• The ctype array takes up the first 257 words. The to_lower, to_upper and
sort_order arrays take up 256 words each after that.
See Section 4.6.4 [Character arrays], page 246.
3. Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists
in configure.in.
4. Reconfigure, recompile, and test.
For a complex character set do the following:
1. Create the file ‘strings/ctype-MYSET.c’ in the MySQL source distribution.
2. Add MYSET to the end of the ‘sql/share/charsets/Index’ file. Assign an unique
number to it.
3. Look at one of the existing ‘ctype-*.c’ files to see what needs to be defined, for
example ‘strings/ctype-big5.c’. Note that the arrays in your file must have names
like ctype_MYSET, to_lower_MYSET, and so on. This corresponds to the arrays in the
simple character set. See Section 4.6.4 [Character arrays], page 246. For a complex
character set
4. Near the top of the file, place a special comment like this:
/*
* This comment is parsed by configure to create ctype.c,
* so don’t change it unless you know what you are doing.
*
* .configure. number_MYSET=MYNUMBER
* .configure. strxfrm_multiply_MYSET=N
* .configure. mbmaxlen_MYSET=N
*/
The configure program uses this comment to include the character set into the MySQL
library automatically.
The strxfrm multiply and mbmaxlen lines will be explained in the following sections.
Only include them if you the string collating functions or the multi-byte character set
functions, respectively.
5. You should then create some of the following functions:
• my_strncoll_MYSET()
• my_strcoll_MYSET()
• my_strxfrm_MYSET()
• my_like_range_MYSET()

6. Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists in configure.in.
7. Reconfigure, recompile, and test.
The file ‘sql/share/charsets/README’ includes some more instructions.
If you want to have the character set included in the MySQL distribution, mail a patch to
internals@lists.mysql.com.
4.6.4 The character definition arrays
to_lower[] and to_upper[] are simple arrays that hold the lowercase and uppercase char-
acters corresponding to each member of the character set. For example:
to_lower[’A’] should contain ’a’
to_upper[’a’] should contain ’A’
sort_order[] is a map indicating how characters should be ordered for comparison and
sorting purposes. For many character sets, this is the same as to_upper[] (which means
sorting will be case insensitive). MySQL will sort characters based on the value of sort_
order[character]. For more complicated sorting rules, see the discussion of string collating
below. See Section 4.6.5 [String collating], page 247.
ctype[] is an array of bit values, with one element for one character. (Note that to_
lower[], to_upper[], and sort_order[] are indexed by character value, but ctype[] is
indexed by character value + 1. This is an old legacy to be able to handle EOF.)
You can find the following bitmask definitions in ‘m_ctype.h’:
#define _U 01 /* Uppercase */
#define _L 02 /* Lowercase */
#define _N 04 /* Numeral (digit) */
#define _S 010 /* Spacing character */
#define _P 020 /* Punctuation */
#define _C 040 /* Control character */
#define _B 0100 /* Blank */
#define _X 0200 /* heXadecimal digit */
The ctype[] entry for each character should be the union of the applicable bitmask values
that describe the character. For example, ’A’ is an uppercase character (_U) as well as a
hexadecimal digit (_X), so ctype[’A’+1] should contain the value:
_U + _X = 01 + 0200 = 0201

Mostrare tutte le immagini di questo Slideshow Google Custom Search Adserver 610x250 If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog:
Custom Search If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog: