As of April 2009[update], MySQL offers MySQL 5.1 in two different variants: the MySQL Community Server and Enterprise Server. They have a common code base and include the following features:* A broad subset of ANSI SQL 99, as well as extensions
* Cross-platform support
* Stored procedures
* Triggers
* Cursors
* Updatable Views
* True VARCHAR support
* INFORMATION_SCHEMA
* Strict mode
* X/Open XA distributed transaction processing (DTP) support; two phase commit as part of this, using Oracle's InnoDB engine
* Independent storage engines (MyISAM for read speed, InnoDB for transactions and referential integrity, MySQL Archive for storing historical data in little space)
* Transactions with the InnoDB, BDB and Cluster storage engines; savepoints with InnoDB
* SSL support
* Query caching
* Sub-SELECTs (i.e. nested SELECTs)
* Replication with one master per slave, many slaves per master, no automatic support for multiple masters per slave.
* Full-text indexing and searching using MyISAM engine
* Embedded database library
* Partial Unicode support (UTF-8 sequences longer than 3 bytes are not supported; UCS-2 encoded strings are also limited to the BMP)
* ACID compliance using the InnoDB, BDB and Cluster engines
* Shared-nothing clustering through MySQL Cluster
The MySQL Enterprise Server is released once per month and the sources can be obtained either from MySQL's customer-only Enterprise site or from MySQL's Bazaar repository, both under the GPL license. The MySQL Community Server is published on an unspecified schedule under the GPL and contains all bug fixes that were shipped with the last MySQL Enterprise Server release. Binaries are no longer provided by MySQL for every release of the Community Server.
If the sorting rules for your language are too complex to be handled with the simple sort_
order[] table, you need to use the string collating functions.
Right now the best documentation on this is the character sets that are already imple-
mented. Look at the big5, czech, gbk, sjis, and tis160 character sets for examples.
You must specify the strxfrm_multiply_MYSET=N value in the special comment at the top
of the file. N should be set to the maximum ratio the strings may grow during my_strxfrm_
MYSET (it must be a positive integer).
4.6.6 Multi-byte Character Support
If your want to add support for a new character set that includes multi-byte characters,
you need to use the multi-byte character functions.
Right now the best documentation on this is the character sets that are already imple-
mented. Look at the euc kr, gb2312, gbk, sjis and ujis character sets for examples. These
are implemented in the ctype-’charset’.c files in the ‘strings’ directory.
You must specify the mbmaxlen_MYSET=N value in the special comment at the top of the
source file. N should be set to the size in bytes of the largest character in the set.
4.6.7 Problems With Character Sets
If you try to use a character set that is not compiled into your binary, you can run into a
couple of di erent problems:
‘/usr/local/mysql/share/mysql/charsets’). This can be fixed by using the --
character-sets-dir option to the program in question.
• The character set is a multi-byte-character set that can’t be loaded dynamically. In
this case you have to recompile the program with the support for the character set.
• The character set is a dynamic character set, but you don’t have a configure file for
it. In this case you should install the configure file for the character set from a new
MySQL distribution.
• Your ‘Index’ file doesn’t contain the name for the character set.
ERROR 1105: File ’/usr/local/share/mysql/charsets/?.conf’ not found
(Errcode: 2)
In this case you should either get a new Index file or add by hand the name of any
missing character sets.
For MyISAM tables, you can check the character set name and number for a table with
myisamchk -dvv table_name.
4.7 MySQL Server-Side Scripts and Utilities
4.7.1 Overview of the Server-Side Scripts and Utilities
All MySQL clients that communicate with the server using the mysqlclient library use
the following environment variables:
Name Description
MYSQL_UNIX_PORT The default socket; used for connections to localhost
MYSQL_TCP_PORT The default TCP/IP port
MYSQL_PWD The default password
MYSQL_DEBUG Debug-trace options when debugging
TMPDIR The directory where temporary tables/files are created
Use of MYSQL_PWD is insecure. See Section 4.2.7 [Connecting], page 183.
The ‘mysql’ client uses the file named in the MYSQL_HISTFILE environment variable to save
the command-line history. The default value for the history file is ‘$HOME/.mysql_history’,
where $HOME is the value of the HOME environment variable. See Appendix F [Environment
variables], page 695.
All MySQL programs take many di erent options. However, every MySQL program pro-
vides a --help option that you can use to get a full description of the program’s di erent
options. For example, try mysql --help.
You can override default options for all standard client programs with an option file. Sec-
tion 4.1.2 [Option files], page 168.
The list below brie y describes the MySQL programs:
myisamchk
Utility to describe, check, optimise, and repair MySQL tables. Because
myisamchk has many functions, it is described in its own chapter. See Chapter 4
[MySQL Database Administration], page 164.
make_binary_distribution
Makes a binary release of a compiled MySQL. This could be sent by FTP to
‘/pub/mysql/Incoming’ on support.mysql.com for the convenience of other
MySQL users.
msql2mysql
A shell script that converts mSQL programs to MySQL. It doesn’t handle all
cases, but it gives a good start when converting.
mysqlaccess
A script that checks the access privileges for a host, user, and database combi-
nation.
mysqladmin
Utility for performing administrative operations, such as creating or dropping
databases, reloading the grant tables, ushing tables to disk, and reopening
log files. mysqladmin can also be used to retrieve version, process, and status
information from the server. See Section 4.8.3 [mysqladmin], page 269.
mysqlbug The MySQL bug report script. This script should always be used when filing a
bug report to the MySQL list.
mysqld The SQL daemon. This should always be running.
mysqldump
Dumps a MySQL database into a file as SQL statements or as tab-separated
text files. Enhanced freeware originally by Igor Romanenko. See Section 4.8.5
[mysqldump], page 273.
mysqlimport
Imports text files into their respective tables using LOAD DATA INFILE. See
Section 4.8.7 [mysqlimport], page 277.
mysqlshow
Displays information about databases, tables, columns, and indexes.
mysql_install_db
Creates the MySQL grant tables with default privileges. This is usually exe-
cuted only once, when first installing MySQL on a system.
replace A utility program that is used by msql2mysql, but that has more general ap-
plicability as well. replace changes strings in place in files or on the standard
input. Uses a finite state machine to match longer strings first. Can be used to
swap strings. For example, this command swaps a and b in the given files:
shell> replace a b b a -- file1 file2 ...
safe_mysqld is the recommended way to start a mysqld daemon on Unix. safe_mysqld
adds some safety features such as restarting the server when an error occurs and logging
run-time information to a log file.
If you don’t use --mysqld=# or --mysqld-version=# safe_mysqld will use an executable
named mysqld-max if it exists. If not, safe_mysqld will start mysqld. This makes it very
easy to test to use mysqld-max instead of mysqld; Just copy mysqld-max to where you have
mysqld and it will be used.
Normally one should never edit the safe_mysqld script, but instead put the options to
safe_mysqld in the [safe_mysqld] section in the my.cnf file. safe_mysqld will read all
options from the [mysqld], [server] and [safe_mysqld] sections from the option files.
See Section 4.1.2 [Option files], page 168.
Note that all options on the command line to safe_mysqld are passed to mysqld. If you
wants to use any options in safe_mysqld that mysqld doesn’t support, you must specify
these in the option file.
Most of the options to safe_mysqld are the same as the options to mysqld. See Section 4.1.1
[Command-line options], page 164.
safe_mysqld supports the following options:
–basedir=path
–core-file-size=# Size of the core file mysqld should be able to create. Passed to ulimit
-c.
–datadir=path
–defaults-extra-file=path
–defaults-file=path
–err-log=path
–ledir=path Path to mysqld
–log=path
–mysqld=mysqld-version Name of the mysqld version in the ledir directory you want
to start.
–mysqld-version=version Similar to --mysqld= but here you only give the su x for
mysqld. For example if you use --mysqld-version=max, safe_mysqld will start
the ledir/mysqld-max version. If the argument to --mysqld-version is empty,
ledir/mysqld will be used.
–no-defaults
–open-files-limit=# Number of files mysqld should be able to open. Passed to ulimit
-n. Note that you need to start safe_mysqld as root for this to work properly!
–pid-file=path
–port=#
–socket=path
–timezone=# Set the timezone (the TZ) variable to the value of this parameter.
–user=#
The safe_mysqld script is written so that it normally is able to start a server that was
installed from either a source or a binary version of MySQL, even if these install the server
in slightly di erent locations. safe_mysqld expects one of these conditions to be true:
• The server and databases can be found relative to the directory from which safe_
mysqld is invoked. safe_mysqld looks under its working directory for ‘bin’ and ‘data’
directories (for binary distributions) or for ‘libexec’ and ‘var’ directories (for source
distributions). This condition should be met if you execute safe_mysqld from your
MySQL installation directory (for example, ‘/usr/local/mysql’ for a binary distribu-
tion).
• If the server and databases cannot be found relative to the working directory, safe_
mysqld attempts to locate them by absolute pathnames. Typical locations are
‘/usr/local/libexec’ and ‘/usr/local/var’. The actual locations are determined
when the distribution was built from which safe_mysqld comes. They should be
correct if MySQL was installed in a standard location.
Because safe_mysqld will try to find the server and databases relative to its own working
directory, you can install a binary distribution of MySQL anywhere, as long as you start
safe_mysqld from the MySQL installation directory:
shell> cd mysql_installation_directory
shell> bin/safe_mysqld &
If safe_mysqld fails, even when invoked from the MySQL installation directory, you can
modify it to use the path to mysqld and the pathname options that are correct for your
system. Note that if you upgrade MySQL in the future, your modified version of safe_
mysqld will be overwritten, so you should make a copy of your edited version that you can
reinstall.
Mysqld multi, program for managing multiple MySQL servers
mysqld_multi is meant for managing several mysqld processes running in di erent Unix
sockets and TCP/IP ports.
The program will search for group(s) named [mysqld#] from my.cnf (or the given –config-
file=...), where # can be any positive number starting from 1. These groups should be the
same as the usual [mysqld] group (e.g. options to mysqld, see MySQL manual for detailed
information about this group), but with those port, socket etc. options that are wanted
for each separate mysqld processes. The number in the group name has another function;
it can be used for starting, stopping, or reporting some specific mysqld servers with this
program. See the usage and options below for more information.
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
The GNR above means the group number. You can start, stop or report any GNR, or
several of them at the same time. (See –example) The GNRs list can be comma separated,
or a dash combined, of which the latter means that all the GNRs between GNR1-GNR2 will
be a ected. Without GNR argument all the found groups will be either started, stopped,
or reported. Note that you must not have any white spaces in the GNR list. Anything after
a white space is ignored.
mysqld_multi supports the following options:
–config-file=... Alternative config file. Note: This will not a ect this program’s own
options (group [mysqld_multi]), but only groups [mysqld#]. Without this option
everything will be searched from the ordinary my.cnf file.
–example Give an example of a config file.
–help Print this help and exit.
–log=... Log file. Full path to and the name for the log file. Note: If the file exists,
everything will be appended.
–mysqladmin=... mysqladmin binary to be used for a server shutdown.
–mysqld=... mysqld binary to be used. Note that you can give safe_mysqld to this
option also. The options are passed to mysqld. Just make sure you have mysqld in
your environment variable PATH or fix safe_mysqld.
–no-log Print to stdout instead of the log file. By default the log file is turned on.
–password=... Password for user for mysqladmin.
–tcp-ip Connect to the MySQL server(s) via the TCP/IP port instead of the Unix
socket. This a ects stopping and reporting. If a socket file is missing, the server may
still be running, but can be accessed only via the TCP/IP port. By default connecting
is done via the Unix socket.
–user=... MySQL user for mysqladmin.
–version Print the version number and exit.
Some notes about mysqld_multi:
• Make sure that the MySQL user, who is stopping the mysqld services (e.g using the
mysqladmin) have the same password and username for all the data directories ac-
cessed (to the ’mysql’ database) And make sure that the user has the ’Shutdown priv’
privilege! If you have many data- directories and many di erent ’mysql’ databases
with di erent passwords for the MySQL ’root’ user, you may want to create a common
’multi admin’ user for each using the same password (see below). Example how to do
it:
shell> mysql -u root -S /tmp/mysql.sock -proot_password -e
"GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY ’multipass’"
See Section 4.2.5 [Privileges], page 178.
You will have to do the above for each mysqld running in each data directory, that you
have (just change the socket, -S=...).
• pid-file is very important, if you are using safe_mysqld to start mysqld (e.g. –
mysqld=safe mysqld) Every mysqld should have its own pid-file. The advantage
using safe_mysqld instead of mysqld directly here is, that safe_mysqld ’guards’ every
mysqld process and will restart it, if a mysqld process fails due to signal kill -9, or
similar. (Like segmentation fault, which MySQL should never do, of course ;) Please
note that safe_mysqld script may require that you start it from a certain place. This
means that you may have to cd to a certain directory, before you start the mysqld_
multi. If you have problems starting, please see the safe_mysqld script. Check
especially the lines:
--------------------------------------------------------------------
MY_PWD=‘pwd‘ Check if we are starting this relative (for the binary
release) if test -d /data/mysql -a -f ./share/mysql/english/errmsg.sys
-a -x ./bin/mysqld
--------------------------------------------------------------------------
See Section 4.7.2 [ safe_mysqld], page 250.
The above test should be successful, or you may encounter problems.
• Beware of the dangers starting multiple mysqlds in the same data directory. Use
separate data directories, unless you know what you are doing!
• The socket file and the TCP/IP port must be di erent for every mysqld.
• The first and fifth mysqld group were intentionally left out from the example. You
may have ’gaps’ in the config file. This gives you more exibility. The order in which
the mysqlds are started or stopped depends on the order in which they appear in the
config file.
• When you want to refer to a certain group using GNR with this program, just use the
number in the end of the group name ([mysqld# <== ). • You may want to use option ’–user’ for mysqld, but in order to do this you need to be root when you start the mysqld_multi script. Having the option in the config file doesn’t matter; you will just get a warning, if you are not the superuser and the mysqlds are started under your Unix account. Important: Make sure that the pid- file and the data directory are read+write(+execute for the latter one) accessible for that Unix user, who the specific mysqld process is started as. Do not use the Unix root account for this, unless you know what you are doing! • Most important: Make sure that you understand the meanings of the options that are passed to the mysqlds and why one would want to have separate mysqld processes. Starting multiple mysqlds in one data directory will not give you extra performance in a threaded system! See Section 4.1.4 [Multiple servers], page 172. This is an example of the config file on behalf of mysqld_multi. # This file should probably be in your home dir (~/.my.cnf) or /etc/my.cnf # Version 2.1 by Jani Tolonen [mysqld_multi] mysqld = /usr/local/bin/safe_mysqld mysqladmin = /usr/local/bin/mysqladmin user = multi_admin password = multipass [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/var2/hostname.pid2 datadir = /usr/local/mysql/var2 language = /usr/local/share/mysql/english user = john 254 MySQL Technical Reference for Version 4.0.1-alpha [mysqld3] socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/var3/hostname.pid3 datadir = /usr/local/mysql/var3 language = /usr/local/share/mysql/swedish user = monty [mysqld4] socket = /tmp/mysql.sock4 port = 3309 pid-file = /usr/local/mysql/var4/hostname.pid4 datadir = /usr/local/mysql/var4 language = /usr/local/share/mysql/estonia user = tonu [mysqld6] socket = /tmp/mysql.sock6 port = 3311 pid-file = /usr/local/mysql/var6/hostname.pid6 datadir = /usr/local/mysql/var6 language = /usr/local/share/mysql/japanese user = jani See Section 4.1.2 [Option files], page 168. 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator myisampack is used to compress MyISAM tables, and pack_isam is used to compress ISAM tables. Because ISAM tables are deprecated, we will only discuss myisampack here, but everything said about myisampack should also be true for pack_isam. myisampack works by compressing each column in the table separately. The information needed to decompress columns is read into memory when the table is opened. This results in much better performance when accessing individual records, because you only have to uncompress exactly one record, not a much larger disk block as when using Stacker on MS-DOS. Usually, myisampack packs the data file 40%-70%. MySQL uses memory mapping (mmap()) on compressed tables and falls back to normal read/write file usage if mmap() doesn’t work. There are currently two limitations with myisampack: • After packing, the table is read-only. • myisampack can also pack BLOB or TEXT columns. The older pack_isam could not do this. Fixing these limitations is on our TODO list but with low priority. myisampack is invoked like this: shell> myisampack [options] filename ...
Each filename should be the name of an index (‘.MYI’) file. If you are not in the database
directory, you should specify the pathname to the file. It is permissible to omit the ‘.MYI’
extension.
myisampack supports the following options:
• -b, –backup Make a backup of the table as tbl_name.OLD.
• -#, –debug=debug options Output debug log. The debug_options string often is
’d:t:o,filename’.
• -f, –force Force packing of the table even if it becomes bigger or if the temporary file
exists. myisampack creates a temporary file named ‘tbl_name.TMD’ while it compresses
the table. If you kill myisampack, the ‘.TMD’ file may not be deleted. Normally,
myisampack exits with an error if it finds that ‘tbl_name.TMD’ exists. With --force,
myisampack packs the table anyway.
• -?, –help Display a help message and exit.
• -j big tbl name, –join=big tbl name Join all tables named on the command line into a
single table big_tbl_name. All tables that are to be combined must be identical (same
column names and types, same indexes, etc.).
• -p #, –packlength=# Specify the record length storage size, in bytes. The value should
be 1, 2, or 3. (myisampack stores all rows with length pointers of 1, 2, or 3 bytes. In
most normal cases, myisampack can determine the right length value before it begins
packing the file, but it may notice during the packing process that it could have used a
shorter length. In this case, myisampack will print a note that the next time you pack
the same file, you could use a shorter record length.)
• -s, –silent Silent mode. Write output only when errors occur.
• -t, –test Don’t actually pack table, just test packing it.
• -T dir name, –tmp dir=dir name Use the named directory as the location in which to
write the temporary table.
• -v, –verbose Verbose mode. Write information about progress and packing result.
• -V, –version Display version information and exit.
• -w, –wait
Wait and retry if table is in use. If the mysqld server was invoked with the --skip-
locking option, it is not a good idea to invoke myisampack if the table might be
updated during the packing process.
The sequence of commands shown below illustrates a typical table compression session:
shell> ls -l station.*
-rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-02-02 3:06:43
Data records: 1192 Deleted blocks: 0
Datafile: Parts: 1192 Deleted data: 0
Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
Max datafile length: 54657023 Max keyfile length: 33554431
Recordlength: 834
Record format: Fixed length
table description:
Key Start Len Index Type
Root Blocksize Rec/key
1 2 4 unique unsigned long 1024 1024 1
2 32 30 multip. text
10240 1024 1
Field Start Length Type
1 1 1
2 2 4
3 6 4
4 10 1
5 11 20
6 31 1
7 32 30
8 62 35
9 97 35
10 132 35
11 167 4
12 171 16
13 187 35
14 222 4
15 226 16
16 242 20
17 262 20
18 282 20
19 302 30
20 332 4
21 336 4
22 340 1
23 341 8
24 349 8
25 357 8
26 365 2
27 367 2
28 369 4
29 373 4
30 377 1
31 378 2
32 380 8
33 388 4
34 392 4
35 396 4
37 404 1
38 405 4
39 409 4
40 413 4
41 417 4
42 421 4
43 425 4
44 429 20
45 449 30
46 479 1
47 480 1
48 481 79
49 560 79
50 639 79
51 718 79
52 797 8
53 805 1
54 806 1
55 807 20
56 827 4
57 831 4
shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics
normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
Original trees: 57 After join: 17
- Compressing file
87.14%
shell> ls -l station.*
-rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-04-17 19:04:26
Data records: 1192 Deleted blocks: 0
Datafile: Parts: 1192 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 1
Max datafile length: 16777215 Max keyfile length: 131071
Recordlength: 834
Record format: Compressed
table description:
Key Start Len Index Type
Root Blocksize Rec/key
1 2 4 unique unsigned long 10240 1024 1
2 32 30 multip. text
54272 1024 1
Field Start Length Type
Huff tree Bits
1 1 1 constant
1 0
2 2 4 zerofill(1)
2 9
3 6 4 no zeros, zerofill(1) 2 9
4 10 1
3 9
5 11 20 table-lookup
4 0
6 31 1
3 9
7 32 30 no endspace, not_always 5 9
8 62 35 no endspace, not_always, no empty 6 9
9 97 35 no empty
7 9
10 132 35 no endspace, not_always, no empty 6 9
11 167 4 zerofill(1)
2 9
12 171 16 no endspace, not_always, no empty 5 9
13 187 35 no endspace, not_always, no empty 6 9
14 222 4 zerofill(1)
2 9
15 226 16 no endspace, not_always, no empty 5 9
16 242 20 no endspace, not_always 8 9
17 262 20 no endspace, no empty 8 9
18 282 20 no endspace, no empty 5 9
19 302 30 no endspace, no empty 6 9
20 332 4 always zero
2 9
21 336 4 always zero
2 9
22 340 1
3 9
23 341 8 table-lookup
9 0
24 349 8 table-lookup
10 0
25 357 8 always zero
2 9
26 365 2
2 9
27 367 2 no zeros, zerofill(1) 2 9
28 369 4 no zeros, zerofill(1) 2 9
29 373 4 table-lookup
11 0
30 377 1
3 9
31 378 2 no zeros, zerofill(1) 2 9
32 380 8 no zeros
2 9
33 388 4 always zero
2 9
34 392 4 table-lookup
12 0
35 396 4 no zeros, zerofill(1) 13 9
36 400 4 no zeros, zerofill(1) 2 9
37 404 1
2 9
38 405 4 no zeros
2 9
39 409 4 always zero
2 9
40 413 4 no zeros
2 9
41 417 4 always zero
2 9
42 421 4 no zeros
2 9
43 425 4 always zero
2 9
44 429 20 no empty
3 9
45 449 30 no empty
3 9
46 479 1
14 4
47 480 1
14 4
48 481 79 no endspace, no empty 15 9
49 560 79 no empty
2 9
50 639 79 no empty
2 9
51 718 79 no endspace
16 9
52 797 8 no empty
2 9
53 805 1
17 1
54 806 1
3 9
55 807 20 no empty
3 9
56 827 4 no zeros, zerofill(2) 2 9
57 831 4 no zeros, zerofill(1) 2 9
The information printed by myisampack is described below:
normal The number of columns for which no extra packing is used.
empty-space
The number of columns containing values that are only spaces; these will occupy
1 bit.
empty-zero
The number of columns containing values that are only binary 0’s; these will
occupy 1 bit.
empty-fill
The number of integer columns that don’t occupy the full byte range of their
type; these are changed to a smaller type (for example, an INTEGER column
may be changed to MEDIUMINT).
pre-space
The number of decimal columns that are stored with leading spaces. In this
case, each value will contain a count for the number of leading spaces.
end-space
The number of columns that have a lot of trailing spaces. In this case, each
value will contain a count for the number of trailing spaces.
table-lookup
The column had only a small number of di erent values, which were converted
to an ENUM before Hu man compression.
zero The number of columns for which all values are zero.
Original trees
The initial number of Hu man trees.
After join
The number of distinct Hu man trees left after joining trees to save some header
space.
After a table has been compressed, myisamchk -dvv prints additional information about
each field:
Type The field type may contain the following descriptors:
constant All rows have the same value.
no endspace
Don’t store endspace.
no endspace, not_always
Don’t store endspace and don’t do end space compression for all
values.
no endspace, no empty
Don’t store endspace. Don’t store empty values.
table-lookup
The column was converted to an ENUM.
zerofill(n)
The most significant n bytes in the value are always 0 and are not
stored.
no zeros Don’t store zeros.
always zero
0 values are stored in 1 bit.
Huff tree The Hu man tree associated with the field.
Bits The number of bits used in the Hu man tree.
After you have run pack_isam/myisampack you must run isamchk/myisamchk to re-create
the index. At this time you can also sort the index blocks and create statistics needed for
the MySQL optimiser to work more e ciently:
myisamchk -rq --analyze --sort-index table_name.MYI
isamchk -rq --analyze --sort-index table_name.ISM
After you have installed the packed table into the MySQL database directory you should
do mysqladmin flush-tables to force mysqld to start using the new table.
If you want to unpack a packed table, you can do this with the --unpack option to isamchk
or myisamchk.
Mysqld-max, An extended mysqld server
mysqld-max is the MySQL server (mysqld) configured with the following configure options:
Option
Comment
–with-server-su x=-max Add a su x to the mysqld version string.
–with-innodb Support for InnoDB tables.
–with-bdb Support for Berkeley DB (BDB) tables
CFLAGS=-DUSE SYMDIR Symbolic links support for Windows.
You can find the MySQL-max binaries at http://www.mysql.com/downloads/mysql-max-3.23.html.
The Windows MySQL 3.23 binary distribution includes both the standard mysqld.exe bi-
nary and the mysqld-max.exe binary. http://www.mysql.com/downloads/mysql-3.23.html.
See Section 2.1.2 [Windows installation], page 52.
Note that as InnoDB and Berkeley DB are not available for all platforms, some of the Max
binaries may not have support for both of these. You can check which table types are
supported by doing the following query:
mysql> show variables like "have_%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_bdb | YES |
| have_innodb | NO |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
+---------------+-------+
The meaning of the values are:
Value
Meaning.
YES
The option is activated and usable.
NO
MySQL is not compiled with support for this option.
DISABLED
The xxxx option is disabled because one started mysqld with --skip-xxxx or because one didn’t start mysqld with all needed options to enable the option. In this case the hostname.err file should contain a reason for why the option is disabled.
Note: To be able to create InnoDB tables you must edit your startup options to include at
least the innodb_data_file_path option. See Section 7.5.2 [InnoDB start], page 453.
To get better performance for BDB tables, you should add some configuration options for
these too.
safe_mysqld will automatically try to start any mysqld binary with the -max prefix. This
makes it very easy to test out a another mysqld binary in an existing installation. Just run
configure with the options you want and then install the new mysqld binary as mysqld-
max in the same directory where your old mysqld binary is.
The mysqld-max RPM uses the above mentioned safe_mysqld feature. It just installs
the mysqld-max executable and safe_mysqld will automatically use this executable when
safe_mysqld is restarted.
0 commenti:
Post a Comment