MySQL Utilites

Overview of the different MySQL programs

All MySQL clients that communicate with the server using the mysqlclient library use the following environment variables:

mysql

A SQL shell (with gnu readline). Supports interactive use and non-interactive querying. When used interactively, the result is given in an ascii-table format, but when used as a filter the result is a tab-separated output.

mysqlaccess

Script to check the privileges for a host, user and database combination.

mysqladmin

Administration utility. Create/Drop databases, reload (read new users) and refresh (flush tables to disk, reopen log files). Also gives version and process info. Try mysqladmin --help for all options

mysqld

The SQL daemon. This should always be running.

mysqldump

Dump a MySQL database into a file with SQL statements or tab separated text files. Enhanced freeware originally by Igor Romanenko.

mysqlimport

Imports one or more text files into respective tables. Can use all formats supported by LOAD DATA INFILE. See section LOAD DATA INFILE syntax

mysqlshow

Shows information about databases, tables, columns and indexes.

mysqlbug

This script should always be used when filing a bug report to the MySQL list.

mysql_install_db

Creates the MySQL grant tables with default privileges. This is usually only executed when installing the first MySQL release on a new system.

isamchk

Check, optimise and repair MySQL tables.

make_binary_release

Makes a binary release of a compiled MySQL. This could be sent by ftp to /pub/mysql/Incoming on www.tcx.se for the convenience of other MySQL users.

msql2mysql

A shell script to convert a mSQL program to MySQL. Doesn't handle all cases but gives a good start when converting.

replace

Binary used for msql2mysql. Utility program to change strings in place in files or on stdin. Uses a finite state machine to match longer strings first. Can be used to swap strings, for example replace a b b a -- files swaps a and b in the given files.

safe_mysqld

Starts the mysqld daemon with some safety features. Restarts on error and has logging of runtime information to a log file.

The MySQL table check, optimise and repair program

For information about how to use isamchk to repair a crashed table: See section How to repair tables..

isamchk memory use

isamchk doesn't use any more memory than you define with the -O options. The default is to use only about 2M to fix things.

But isamchk uses temporary files in TMPDIR. If TMPDIR points to a memory file system you may easily get out of memory errors.

Using -O sortbuffer=16M should probably be enough for most cases.

Getting low level table information

To get a description/statistics from a table use the methods below. We will explain some of the information in more detail later.

isamchk -d table_name

isamchk in 'describe mode'. If one uses --skip-locking isamchk may report an error for a table that is updated while isamchk runs, but there isn't any risk of destroying data.

isamchk -d -v table_name

A little more verbose.

isamchk -eis table_name

Shows only the most important information from a table. Slow since it must read the whole table.

isamchk -eiv table_name

Same as above but tells you what it is being done.

Example of isamchk -d output:

ISAM file:     company.ISM
Data records:           1403698  Deleted blocks:         0
Recordlength:               226
Record format: Fixed length

table description:
Key Start Len Index   Type
1   2     8   unique  double
2   15    10  multip. text packed stripped
3   219   8   multip. double
4   63    10  multip. text packed stripped
5   167   2   multip. unsigned short
6   177   4   multip. unsigned long
7   155   4   multip. text
8   138   4   multip. unsigned long
9   177   4   multip. unsigned long
    193   1           text

Example of isamchk -d -v output:

ISAM file:     company.ISM
Isam-version:  2
Creation time: 1996-08-28 11:44:22
Recover time:  1997-01-12 18:35:29
Data records:           1403698  Deleted blocks:              0
Datafile: Parts:        1403698  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length: 3791650815  Max keyfile length: 4294967294
Recordlength:               226
Record format: Fixed length
r
table description:
Key Start Len Index   Type                      Root Blocksize Rec/key
1   2     8   unique  double                15845376      1024       1
2   15    10  multip. text packed stripped  25062400      1024       2
3   219   8   multip. double                40907776      1024      73
4   63    10  multip. text packed stripped  48097280      1024       5
5   167   2   multip. unsigned short        55200768      1024    4840
6   177   4   multip. unsigned long         65145856      1024    1346
7   155   4   multip. text                  75090944      1024    4995
8   138   4   multip. unsigned long         85036032      1024      87
9   177   4   multip. unsigned long         96481280      1024     178
    193   1           text

Example of isamchk -eis output:

Checking ISAM file: company.ISM
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:   17%

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary contexts switches 639, Involuntary contexts switches 28966

Example of isamchk -eiv output:

Checking ISAM file: company.ISM
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
index  1:
index  2:
index  3:
index  4:
index  5:
index  6:
index  7:
index  8:
index  9:
No recordlinks
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 2
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
- check data record references index: 3
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
- check data record references index: 5
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 6
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 7
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 8
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 9
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:   9%  Packed:   17%

- check records and index references
[LOTS OF ROW NUMBERS DELETED]

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary contexts switches 10604, Involuntary contexts switches 122798

Here are the data file sizes of the table used above.

-rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.ISD
-rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.ISM

Explanations for the things isamchk prints:

ISAM file

Name of isam file.

Isam-version

Version of isam format. Currently always 2.

Creation time

When was the data file created.

Recover time

When was the index/data file last reconstructed.

Data records

How many records/rows.

Deleted blocks

How many deleted blocks still have reserved space. See section How to repair tables..

Datafile: Parts

For dynamic record format this shows how many data blocks there are. For an optimised table without splits this is the same as Data records.

Deleted data

How many bytes of non-reclaimed deleted data.

Datafile pointer

How many bytes the datafile pointer has. It is usually 2, 3 or 4 bytes. Most tables manage with 2 bytes but this cannot be controlled from MySQL yet. For fixed tables this is a record address. For dynamic tables this is a byte address.

Keyfile pointer

How many bytes has the datafile pointer. It is usually 1, 2 or 3 bytes. Most tables manage with 2 bytes but this is calculated automatically by MySQL. It is always a block address.

Max datafile length

How long (in bytes) can the table's data file (.ISD) get.

Max keyfile length

How long (in bytes) can the table's key file (.ISM) get.

Recordlength

How much space does each record/row take.

Record format

Which format does each record/row have. This example uses Fixed length.

table description

A list of all keys in the table. For each key some low level information is presented.

Key

This key's number.

Start

Where in the record/row does this index-part start.

Len

How long is this index part. For packed numbers this should always be the full length of the column. For strings it may be shorter than the full length.

Index

unique or multip.. If one value can exist multiple times in this index.

Type

What data-type does this index part have. This is a C data-type with the options packed, stripped or empty.

Root

Address of the root index block.

Blocksize

The size of each index block. This is by default 1024 but may be changed at compile time.

Rec/key

This is a statistical value used by the optimiser. It tells how many records there are per value for this key. A unique key always has a value of 1. This may be updated after a table is loaded (or greatly changed) with isamchk -a. If this is not updated at all, a default value of 30 is given.

The 9th key is a multi-part key with two parts.

Keyblocks used

How many percent of the keyblocks are used. Since this table has just been reorganised with isamchk the values are very high (very near theoretical maximum).

Packed

MySQL tries to pack keys with a common suffix. This can only be used for CHAR/VARCHAR/DECIMAL keys. For long strings like names, this can significantly reduce the space used. In the above example the 4 key is 10 characters long and gets a 60% reduction in space.

Max levels

How deep is the btree for this key. Large tables with long keys get high values.

Records

How many rows does the table have.

M.recordlength

Average recordlength. For fixed tables this is the recordlength.

Packed

MySQL strips spaces from the end of strings. What percentage did we save by doing this.

Recordspace used

What percentage of the datafile is used.

Empty space

What percentage of the datafile is unused.

Blocks/Record

How many blocks are there per record. This is always 1 for fixed format tables. This value should stay as close to 1.0 as possible. If it gets too big you can reorganise the table with isamchk. See section How to repair tables..

Recordblocks

How many blocks are used. For fixed format, this is the same as the number of records.

Deleteblocks

How many blocks are deleted.

Recorddata

How many bytes of actual user data there are in the datafile.

Deleted data

How many bytes of deleted data there are in the datafile.

Lost space

If a record is updated to a shorter length some space is lost. This is the sum of all such losses.

Linkdata

When the dynamic format is used, blocks are linked with pointers (length 4-7 bytes). This is the sum of all such pointers.

The MySQL compressed read only table generator

pack_isam is an extra that you get when ordering more that 10 licenses or extended support. Since pack_isam is distributed binary only, pack_isam is only available on some platforms.

Of course, all future updates to pack_isam is included in the price. pack_isam may at some time be included as standard when we get some kind of turnover for MySQL.

pack_isam works by compressing each column in the table separately. The information needed to decompress is read into memory when the table is opened. This gives a much better performance when accessing individual records as one only has to uncompress exactly one record, not a much larger disk block like when using Stacker on MSDOS.

MySQL uses memory mapping (mmap) on compressed tables and falls back to normal read/write file usage if mmap doesn't work.

Usually, pack_isam packs the datafile 40-70%.

There is currently two limitations with pack_isam:

After packing, the table is read only.
• It can't pack blobs, yet.

Fixing these limitations is on our TODO but with low priority.

pack_isam options:

> pack_isam --help
pack_isam Ver 5.0 for SOLARIS 2.5 on SPARCstation
Copyright (C) 1994-1997 TcX AB & Monty Program KB & Detron HB.
This is not free software. You must have a license to use this program
This software comes with ABSOLUTELY NO WARRANTY

Pack a ISAM-database to take much smaller space
Keys are not updated, one must run isamchk -rq on datafile afterwards
Usage: pack_isam [OPTIONS]

  -b, --backup          Make a backup of the table as table_name.OLD
  -f, --force           Force packing of table even if it's gets bigger or
                        tempfile exists.
  -j, --join=#          Join all given tables into table.
                        All tables MUST be identical.
  -p, --packlength=#    Force storage size of recordlength (1,2 or 3)
  -s, --silent          Be more silent.
  -t, --test            Don't pack table only test packing it
  -v, --verbose         Write info about progress and packing result
  -w, --wait            Wait and retry if table is in use
  -T, --tmp_dir=#       Use temporary directory to store temporary table
  -#, --debug=...       output debug log. Often this is 'd:t:o,filename`
  -?, --help            display this help and exit
  -V, --version         output version information and exit

Typical run:

(/my/monty/tmp) ls -l station.*
-rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.ISD
-rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.ISM
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

(/my/monty/tmp) isamchk -dvv station

ISAM 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
Datafilepointer (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

column 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

Compressing station.ISD: (1192 records)
- Calculating statistics
            
normal:     20  empty-space:      16  empty-zero:        12  empty-fill:  11
pre-space:   0  end-space:        12  intervall-fields:   5  zero:         7
Original trees:  57  After join: 17
- Compressing file
87.14%     

(/my/monty/tmp) ls -l station.*
-rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.ISD
-rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.ISM
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

(my/monty/tmp) isamchk -dvv station

ISAM 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-lockup                         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      allways zero                         2     9
21    336   4      allways zero                         2     9
22    340   1                                           3     9
23    341   8      table-lockup                         9     0
24    349   8      table-lockup                        10     0
25    357   8      allways 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-lockup                        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      allways zero                         2     9
34    392   4      table-lockup                        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      allways zero                         2     9
40    413   4      no zeros                             2     9
41    417   4      allways zero                         2     9
42    421   4      no zeros                             2     9
43    425   4      allways 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

Go to the first, previous, next, last section, table of contents.