MySQL Server-Side Scripts and Utilities
=======================================
Overview of the Server-Side Scripts and Utilities
-------------------------------------------------
`mysql
--help'.
You can override default options for all standard programs with an
option file. *Note Option files::.
The following list briefly describes the server-side MySQL programs:
`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.
`mysql_install_db'
Creates the MySQL grant tables with default privileges. This is
usually executed only once, when first installing MySQL on a
system.
`mysqld_safe', The Wrapper Around `mysqld'
------------------------------------------
`mysqld_safe' is the recommended way to start a `mysqld' daemon on
Unix. `mysqld_safe' adds some safety features such as restarting the
server when an error occurs and logging run-time information to a log
file.
*Note:* Before MySQL 4.0, `mysqld_safe' is named `safe_mysqld'. To
preserve backward compatibility, MySQL binary distributions for some
time will include `safe_mysqld' as a symbolic link to `mysqld_safe'.
If you don't use `--mysqld=#' or `--mysqld-version=#' `mysqld_safe'
will use an executable named `mysqld-max' if it exists. If not,
`mysqld_safe' 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 `mysqld_safe' script, but instead
put the options to `mysqld_safe' in the `[mysqld_safe]' section in the
`my.cnf' file. `mysqld_safe' reads all options from the `[mysqld]',
`[server]' and `[mysqld_safe]' sections from the option files. (For
backward compatibility, it also reads the `[safe_mysqld]' sections.)
*Note Option files::.
Note that all options on the command-line to `mysqld_safe' are passed
to `mysqld'. If you wants to use any options in `mysqld_safe' that
`mysqld' doesn't support, you must specify these in the option file.
Most of the options to `mysqld_safe' are the same as the options to
`mysqld'. *Note Command-line options::.
`mysqld_safe' 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 (this is marked obsolete in 4.0; Use --log-error instead)'
`--log-error=path'
Write the error log to the above file. *Note Error log::.
`--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 suffix for
`mysqld'. For example if you use `--mysqld-version=max',
`mysqld_safe' will start the `ledir/mysqld-max' version. If the
argument to `--mysqld-version' is empty, `ledir/mysqld' will be
used.
`--nice=# (added in MySQL 4.0.14)'
`--no-defaults'
`--open-files-limit=#'
Number of files `mysqld' should be able to open. Passed to `ulimit
-n'. Note that you need to start `mysqld_safe' 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 `mysqld_safe' 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 different
locations. `mysqld_safe' expects one of these conditions to be true:
* The server and databases can be found relative to the directory
from which `mysqld_safe' is invoked. `mysqld_safe' 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
`mysqld_safe' from your MySQL installation directory (for example,
`/usr/local/mysql' for a binary distribution).
* If the server and databases cannot be found relative to the
working directory, `mysqld_safe' 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 `mysqld_safe' comes. They
should be correct if MySQL was installed in a standard location.
Because `mysqld_safe' 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 `mysqld_safe' from the MySQL
installation directory:
shell> cd mysql_installation_directory
shell> bin/mysqld_safe &
If `mysqld_safe' 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 `mysqld_safe'
will be overwritten, so you should make a copy of your edited version
that you can reinstall.
`mysqld_multi', A Program for Managing Multiple MySQL Servers
-------------------------------------------------------------
`mysqld_multi' is meant for managing several `mysqld' processes that
listen for connections on different Unix sockets and TCP/IP ports.
The program will search for group(s) named `[mysqld#]' from `my.cnf'
(or the file named by the `--config-file=...' option), where `#' can be
any positive number starting from 1. This number is referred to in the
following discussion as the option group number, or GNR. Group numbers
distinquish option groups from one another and are used as arguments to
`mysqld_multi' to specify which servers you want to start, stop, or
obtain status for. Options listed in these groups should be the same
as you would use in the usual `[mysqld]' group used for starting
`mysqld'. (See, for example, *Note Automatic start::.) However, for
`mysqld_multi', be sure that each group includes options for values
such as the port, socket, etc., to be used for each individual `mysqld'
process.
`mysqld_multi' is invoked using the following syntax:
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
shell> mysqld_multi --example
The GNR values in the list can be comma-separated or combined with a
dash; in the latter case, all the GNRs between GNR1-GNR2 will be
affected. With no GNR argument, all groups listed in the option file
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:
will be searched from the ordinary
`my.cnf' file.
`--example'
Display an example option 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.
variable `PATH' or fix
`mysqld_safe'.
`--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 affects 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, connections are made using
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' program) have the same
password and username for all the data directories accessed (to the
`mysql' database) And make sure that the user has the `SHUTDOWN'
privilege! If you have many data directories and many different
`mysql' databases with different 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'"
*Note Privileges::. 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 `mysqld_safe' to
start `mysqld' (for example, `--mysqld=mysqld_safe') Every
`mysqld' should have its own `pid-file'. The advantage using
`mysqld_safe' instead of `mysqld' directly here is, that
`mysqld_safe' "guards" every `mysqld' process and will restart it,
if a `mysqld' process terminates due to a signal sent using `kill
-9', or for other reasons such as a segmentation fault (which
MySQL should never do, of course;). Please note that the
`mysqld_safe' 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 `mysqld_safe' 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
--------------------------------------------------------------------------
*Note `mysqld_safe': mysqld_safe. The above test should be
successful, or you may encounter problems.
* Beware of the dangers starting multiple `mysqld's 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 different 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 flexibility. 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. For
example, the GNR for a group named `[mysqld17]' is 17.
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 `mysqld's and *why one would
want* to have separate `mysqld' processes. Starting multiple
`mysqld's in one data directory *will not* give you extra
performance in a threaded system!
*Note Multiple servers::.
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/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = multi_admin
password = multipass
user = john
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
*Note Option files::.
`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'.
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 datafile 40%-70%.
MySQL uses memory mapping (`mmap()') on compressed tables and falls
back to normal read/write file usage if `mmap()' doesn't work.
Please note the following:
* After packing, the table is read-only. This is generally intended
(such as when accessing packed tables on a CD). Also allowing
writes to a packed table is on our TODO list but with low priority.
* `myisampack' can also pack `BLOB' or `TEXT' columns. The older
`pack_isam' (for `ISAM' tables) can not do this.
`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.
might be updated
during the packing process.
The sequence of commands shown here 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
36 400 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
- 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
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 here:
`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.
`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 different values, which were
converted to an `ENUM' before Huffman compression.
`zero'
The number of columns for which all values are zero.
`Original trees'
The initial number of Huffman trees.
`After join'
The number of distinct Huffman 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 Huffman tree associated with the field.
`Bits'
The number of bits used in the Huffman 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 efficiently:
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:
for Berkeley DB (BDB) tables
CFLAGS=-DUSE_SYMDIR Symbolic link support for Windows
The option for enabling InnoDB support is needed only in MySQL 3.23. In
MySQL 4 and up, InnoDB is included by default.
You can find the MySQL-Max binaries at
`http://www.mysql.com/downloads/mysql-max-4.0.html'.
Note that as BerkeleyDB (BDB) is not available for all platforms, so
some of the `Max' binaries may not have support for it. You can check
which table types are supported by doing the following query:
mysql> SHOW VARIABLES LIKE "have_%";
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | DISABLED |
| have_openssl | NO |
| have_query_cache | YES |
+------------------+----------+
The meanings of the values in the second column are:
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 indicating why
the option is disabled.
*Note*: To be able to create InnoDB tables in MySQL version 3.23 you
*must* edit your startup options to include at least the
`innodb_data_file_path' option. *Note InnoDB in MySQL 3.23::.
To get better performance for BDB tables, you should add some
configuration options for these, too. *Note BDB start::.
`mysqld_safe' automatically tries to start any `mysqld' binary with the
`-max' suffix. This makes it very easy to test out 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.
*Note `mysqld_safe': mysqld_safe.
On Linux, the `MySQL-Max' RPM uses the above mentioned `mysqld_safe'
feature. (It just installs the `mysqld-max' executable, so
`mysqld_safe' automatically uses this executable when `mysqld_safe' is
restarted.)
The following table shows which table types our MySQL-Max binaries
include:
Y
Solaris-SPARC Y Y
SCO OSR5 Y Y
UnixWare Y Y
Mac OS X N Y
Note that as of MySQL 4, you do not need a MySQL Max server for InnoDB,
because InnoDB is included by default.
[Назад] [Содержание] [Вперед]
| Главная |