4.6 Administrative and Utility Programs

This section describes administrative programs and programs that perform miscellaneous utility operations.

 File: manual.info.tmp, Node: innochecksum, Next: myisam-ftdump, Prev: programs-admin-utils, Up: programs-admin-utils

4.6.1 innochecksum -- Offline InnoDB File Checksum Utility

*note 'innochecksum': innochecksum. prints checksums for 'InnoDB' files. This tool reads an 'InnoDB' tablespace file, calculates the checksum for each page, compares the calculated checksum to the stored checksum, and reports mismatches, which indicate damaged pages. It was originally developed to speed up verifying the integrity of tablespace files after power outages but can also be used after file copies. Because checksum mismatches cause 'InnoDB' to deliberately shut down a running server, it may be preferable to use this tool rather than waiting for an in-production server to encounter the damaged pages.

note 'innochecksum': innochecksum. cannot be used on tablespace files that the server already has open. For such files, you should use note 'CHECK TABLE': check-table. to check tables within the tablespace. Attempting to run *note 'innochecksum': innochecksum. on a tablespace that the server already has open results in an 'Unable to lock file' error.

If checksum mismatches are found, restore the tablespace from backup or start the server and attempt to use *note 'mysqldump': mysqldump. to make a backup of the tables within the tablespace.

Invoke *note 'innochecksum': innochecksum. like this:

 innochecksum [OPTIONS] FILE_NAME

innochecksum Options

*note 'innochecksum': innochecksum. supports the following options. For options that refer to page numbers, the numbers are zero-based.

Running innochecksum on Multiple User-defined Tablespace Files

The following examples demonstrate how to run *note 'innochecksum': innochecksum. on multiple user-defined tablespace files ('.ibd' files).

Run *note 'innochecksum': innochecksum. for all tablespace ('.ibd') files in the 'test' database:

 innochecksum ./data/test/*.ibd

Run *note 'innochecksum': innochecksum. for all tablespace files ('.ibd' files) that have a file name starting with 't':

 innochecksum ./data/test/t*.ibd

Run *note 'innochecksum': innochecksum. for all tablespace files ('.ibd' files) in the 'data' directory:

 innochecksum ./data/*/*.ibd

Note:

Running note 'innochecksum': innochecksum. on multiple user-defined tablespace files is not supported on Windows operating systems, as Windows shells such as 'cmd.exe' do not support glob pattern expansion. On Windows systems, note 'innochecksum': innochecksum. must be run separately for each user-defined tablespace file. For example:

 innochecksum.exe t1.ibd
 innochecksum.exe t2.ibd
 innochecksum.exe t3.ibd

Running innochecksum on Multiple System Tablespace Files

By default, there is only one 'InnoDB' system tablespace file ('ibdata1') but multiple files for the system tablespace can be defined using the 'innodb_data_file_path' option. In the following example, three files for the system tablespace are defined using the 'innodb_data_file_path' option: 'ibdata1', 'ibdata2', and 'ibdata3'.

 ./bin/mysqld --no-defaults --innodb-data-file-path="ibdata1:10M;ibdata2:10M;ibdata3:10M:autoextend"

The three files ('ibdata1', 'ibdata2', and 'ibdata3') form one logical system tablespace. To run note 'innochecksum': innochecksum. on multiple files that form one logical system tablespace, note 'innochecksum': innochecksum. requires the '-' option to read tablespace files in from standard input, which is equivalent to concatenating multiple files to create one single file. For the example provided above, the following *note 'innochecksum': innochecksum. command would be used:

 cat ibdata* | innochecksum -

Refer to the *note 'innochecksum': innochecksum. options information for more information about the '-' option.

Note:

Running note 'innochecksum': innochecksum. on multiple files in the same tablespace is not supported on Windows operating systems, as Windows shells such as 'cmd.exe' do not support glob pattern expansion. On Windows systems, note 'innochecksum': innochecksum. must be run separately for each system tablespace file. For example:

 innochecksum.exe ibdata1
 innochecksum.exe ibdata2
 innochecksum.exe ibdata3

 File: manual.info.tmp, Node: myisam-ftdump, Next: myisamchk, Prev: innochecksum, Up: programs-admin-utils

4.6.2 myisam_ftdump -- Display Full-Text Index information

note 'myisam_ftdump': myisam-ftdump. displays information about 'FULLTEXT' indexes in 'MyISAM' tables. It reads the 'MyISAM' index file directly, so it must be run on the server host where the table is located. Before using note 'myisam_ftdump': myisam-ftdump, be sure to issue a 'FLUSH TABLES' statement first if the server is running.

*note 'myisam_ftdump': myisam-ftdump. scans and dumps the entire index, which is not particularly fast. On the other hand, the distribution of words changes infrequently, so it need not be run often.

Invoke *note 'myisam_ftdump': myisam-ftdump. like this:

 myisam_ftdump [OPTIONS] TBL_NAME INDEX_NUM

The TBL_NAME argument should be the name of a 'MyISAM' table. You can also specify a table by naming its index file (the file with the '.MYI' suffix). If you do not invoke *note 'myisam_ftdump': myisam-ftdump. in the directory where the table files are located, the table or index file name must be preceded by the path name to the table's database directory. Index numbers begin with 0.

Example: Suppose that the 'test' database contains a table named 'mytexttable' that has the following definition:

 CREATE TABLE mytexttable
 (
   id   INT NOT NULL,
   txt  TEXT NOT NULL,
   PRIMARY KEY (id),
   FULLTEXT (txt)
 ) ENGINE=MyISAM;

The index on 'id' is index 0 and the 'FULLTEXT' index on 'txt' is index 1. If your working directory is the 'test' database directory, invoke *note 'myisam_ftdump': myisam-ftdump. as follows:

 myisam_ftdump mytexttable 1

If the path name to the 'test' database directory is '/usr/local/mysql/data/test', you can also specify the table name argument using that path name. This is useful if you do not invoke *note 'myisam_ftdump': myisam-ftdump. in the database directory:

 myisam_ftdump /usr/local/mysql/data/test/mytexttable 1

You can use *note 'myisam_ftdump': myisam-ftdump. to generate a list of index entries in order of frequency of occurrence like this on Unix-like systems:

 myisam_ftdump -c mytexttable 1 | sort -r

On Windows, use:

 myisam_ftdump -c mytexttable 1 | sort /R

*note 'myisam_ftdump': myisam-ftdump. supports the following options:

 File: manual.info.tmp, Node: myisamchk, Next: myisamlog, Prev: myisam-ftdump, Up: programs-admin-utils

4.6.3 myisamchk -- MyISAM Table-Maintenance Utility

The note 'myisamchk': myisamchk. utility gets information about your database tables or checks, repairs, or optimizes them. note 'myisamchk': myisamchk. works with 'MyISAM' tables (tables that have '.MYD' and '.MYI' files for storing data and indexes).

You can also use the note 'CHECK TABLE': check-table. and note 'REPAIR TABLE': repair-table. statements to check and repair 'MyISAM' tables. See note check-table::, and note repair-table::.

The use of *note 'myisamchk': myisamchk. with partitioned tables is not supported.

Caution:

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.

Invoke *note 'myisamchk': myisamchk. like this:

 myisamchk [OPTIONS] TBL_NAME ...

The OPTIONS specify what you want note 'myisamchk': myisamchk. to do. They are described in the following sections. You can also get a list of options by invoking note 'myisamchk --help': myisamchk.

With no options, note 'myisamchk': myisamchk. simply checks your table as the default operation. To get more information or to tell note 'myisamchk': myisamchk. to take corrective action, specify options as described in the following discussion.

TBL_NAME is the database table you want to check or repair. If you run note 'myisamchk': myisamchk. somewhere other than in the database directory, you must specify the path to the database directory, because note 'myisamchk': myisamchk. has no idea where the database is located. In fact, *note 'myisamchk': myisamchk. does not actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there.

You can name several tables on the note 'myisamchk': myisamchk. command line if you wish. You can also specify a table by naming its index file (the file with the '.MYI' suffix). This enables you to specify all tables in a directory by using the pattern '.MYI'. For example, if you are in a database directory, you can check all the 'MyISAM' tables in that directory like this:

 myisamchk *.MYI

If you are not in the database directory, you can check all the tables there by specifying the path to the directory:

 myisamchk /PATH/TO/DATABASE_DIR/*.MYI

You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:

 myisamchk /PATH/TO/DATADIR/*/*.MYI

The recommended way to quickly check all 'MyISAM' tables is:

 myisamchk --silent --fast /PATH/TO/DATADIR/*/*.MYI

If you want to check all 'MyISAM' tables and repair any that are corrupted, you can use the following command:

 myisamchk --silent --force --fast --update-state \
           --key_buffer_size=64M --myisam_sort_buffer_size=64M \
           --read_buffer_size=1M --write_buffer_size=1M \
           /PATH/TO/DATADIR/*/*.MYI

This command assumes that you have more than 64MB free. For more information about memory allocation with note 'myisamchk': myisamchk, see note myisamchk-memory::.

For additional information about using note 'myisamchk': myisamchk, see note myisam-table-maintenance::.

Important:

_You must ensure that no other program is using the tables while you are running *note 'myisamchk': myisamchk._. The most effective means of doing so is to shut down the MySQL server while running note 'myisamchk': myisamchk, or to lock all tables that note 'myisamchk': myisamchk. is being used on.

Otherwise, when you run *note 'myisamchk': myisamchk, it may display the following error message:

 warning: clients are using or haven't closed the table properly

This means that you are trying to check a table that has been updated by another program (such as the *note 'mysqld': mysqld. server) that hasn't yet closed the file or that has died without closing the file properly, which can sometimes lead to the corruption of one or more 'MyISAM' tables.

If note 'mysqld': mysqld. is running, you must force it to flush any table modifications that are still buffered in memory by using 'FLUSH TABLES'. You should then ensure that no one is using the tables while you are running note 'myisamchk': myisamchk.

However, the easiest way to avoid this problem is to use note 'CHECK TABLE': check-table. instead of note 'myisamchk': myisamchk. to check tables. See *note check-table::.

note 'myisamchk': myisamchk. supports the following options, which can be specified on the command line or in the '[myisamchk]' group of an option file. For information about option files used by MySQL programs, see note option-files::.

myisamchk Options

Option Name Description

-analyze Analyze the distribution of key values

-backup Make a backup of the .MYD file as file_name-time.BAK

-block-search Find the record that a block at the given offset belongs to

-character-sets-dir Directory where character sets can be found

-check Check the table for errors

-check-only-changed Check only tables that have changed since the last check

-correct-checksum Correct the checksum information for the table

-data-file-length Maximum length of the data file (when re-creating data file when it is full)

-debug Write debugging log

-decode_bits Decode_bits

-defaults-extra-file Read named option file in addition to usual option files

-defaults-file Read only named option file

-defaults-group-suffix Option group suffix value

-description Print some descriptive information about the table

-extend-check Do very thorough table check or repair that tries to recover every possible row from the data file

-fast Check only tables that haven't been closed properly

-force Do a repair operation automatically if myisamchk finds any errors in the table

-force Overwrite old temporary files. For use with the -r or -o option

-ft_max_word_len Maximum word length for FULLTEXT indexes

-ft_min_word_len Minimum word length for FULLTEXT indexes

-ft_stopword_file Use stopwords from this file instead of built-in list

-HELP Display help message and exit

-help Display help message and exit

-information Print informational statistics about the table that is checked

-key_buffer_size Size of buffer used for index blocks for MyISAM tables

-keys-used A bit-value that indicates which indexes to update

-max-record-length Skip rows larger than the given length if myisamchk cannot allocate memory to hold them

-medium-check Do a check that is faster than an -extend-check operation

-myisam_block_size Block size to be used for MyISAM index pages

-myisam_sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE

-no-defaults Read no option files

-parallel-recover Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads (beta)

-print-defaults Print default options

-quick Achieve a faster repair by not modifying the data file

-read_buffer_size Each thread that does a sequential scan allocates a buffer of this size for each table it scans

-read-only Do not mark the table as checked

-recover Do a repair that can fix almost any problem except unique keys that aren't unique

-safe-recover Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found

-set-auto-increment Force AUTO_INCREMENT numbering for new records to start at the given value

-set-collation Specify the collation to use for sorting table indexes

-silent Silent mode

-sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE

-sort-index Sort the index tree blocks in high-low order

-sort_key_blocks sort_key_blocks

-sort-records Sort records according to a particular index

-sort-recover Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large

-stats_method Specifies how MyISAM index statistics collection code should treat NULLs

-tmpdir Directory to be used for storing temporary files

-unpack Unpack a table that was packed with myisampack

-update-state Store information in the .MYI file to indicate when the table was checked and whether the table crashed

-verbose Verbose mode

-version Display version information and exit

-wait Wait for locked table to be unlocked, instead of terminating

-write_buffer_size Write buffer size

 File: manual.info.tmp, Node: myisamchk-general-options, Next: myisamchk-check-options, Prev: myisamchk, Up: myisamchk

4.6.3.1 myisamchk General Options .................................

The options described in this section can be used for any type of table maintenance operation performed by *note 'myisamchk': myisamchk. The sections following this one describe options that pertain only to specific operations, such as table checking or repairing.

You can also set the following variables by using '--VAR_NAME=VALUE' syntax:

Variable Default Value

'decode_bits' 9

'ft_max_word_len' version-dependent

'ft_min_word_len' 4

'ft_stopword_file' built-in list

'key_buffer_size' 523264

'myisam_block_size' 1024

'myisam_sort_key_blocks' 16

'read_buffer_size' 262136

'sort_buffer_size' 2097144

'sort_key_blocks' 16

'stats_method' nulls_unequal

'write_buffer_size' 262136

The possible note 'myisamchk': myisamchk. variables and their default values can be examined with note 'myisamchk --help': myisamchk.:

'myisam_sort_buffer_size' is used when the keys are repaired by sorting keys, which is the normal case when you use '--recover'. 'sort_buffer_size' is a deprecated synonym for 'myisam_sort_buffer_size'.

'key_buffer_size' is used when you are checking the table with '--extend-check' or when the keys are repaired by inserting keys row by row into the table (like when doing normal inserts). Repairing through the key buffer is used in the following cases:

Repairing through the key buffer takes much less disk space than using sorting, but is also much slower.

If you want a faster repair, set the 'key_buffer_size' and 'myisam_sort_buffer_size' variables to about 25% of your available memory. You can set both variables to large values, because only one of them is used at a time.

'myisam_block_size' is the size used for index blocks.

'stats_method' influences how 'NULL' values are treated for index statistics collection when the '--analyze' option is given. It acts like the 'myisam_stats_method' system variable. For more information, see the description of 'myisam_stats_method' in note server-system-variables::, and note index-statistics::.

'ft_min_word_len' and 'ft_max_word_len' indicate the minimum and maximum word length for 'FULLTEXT' indexes on 'MyISAM' tables. 'ft_stopword_file' names the stopword file. These need to be set under the following circumstances.

If you use *note 'myisamchk': myisamchk. to perform an operation that modifies table indexes (such as repair or analyze), the 'FULLTEXT' indexes are rebuilt using the default full-text parameter values for minimum and maximum word length and the stopword file unless you specify otherwise. This can result in queries failing.

The problem occurs because these parameters are known only by the server. They are not stored in 'MyISAM' index files. To avoid the problem if you have modified the minimum or maximum word length or the stopword file in the server, specify the same 'ft_min_word_len', 'ft_max_word_len', and 'ft_stopword_file' values to note 'myisamchk': myisamchk. that you use for note 'mysqld': mysqld. For example, if you have set the minimum word length to 3, you can repair a table with *note 'myisamchk': myisamchk. like this:

 myisamchk --recover --ft_min_word_len=3 TBL_NAME.MYI

To ensure that *note 'myisamchk': myisamchk. and the server use the same values for full-text parameters, you can place each one in both the '[mysqld]' and '[myisamchk]' sections of an option file:

 [mysqld]
 ft_min_word_len=3

 [myisamchk]
 ft_min_word_len=3

An alternative to using note 'myisamchk': myisamchk. is to use the note 'REPAIR TABLE': repair-table, note 'ANALYZE TABLE': analyze-table, note 'OPTIMIZE TABLE': optimize-table, or *note 'ALTER TABLE': alter-table. These statements are performed by the server, which knows the proper full-text parameter values to use.

 File: manual.info.tmp, Node: myisamchk-check-options, Next: myisamchk-repair-options, Prev: myisamchk-general-options, Up: myisamchk

4.6.3.2 myisamchk Check Options ...............................

*note 'myisamchk': myisamchk. supports the following options for table checking operations:

 File: manual.info.tmp, Node: myisamchk-repair-options, Next: myisamchk-other-options, Prev: myisamchk-check-options, Up: myisamchk

4.6.3.3 myisamchk Repair Options ................................

*note 'myisamchk': myisamchk. supports the following options for table repair operations (operations performed when an option such as '--recover' or '--safe-recover' is given):

 File: manual.info.tmp, Node: myisamchk-other-options, Next: myisamchk-table-info, Prev: myisamchk-repair-options, Up: myisamchk

4.6.3.4 Other myisamchk Options ...............................

*note 'myisamchk': myisamchk. supports the following options for actions other than table checks and repairs:

 File: manual.info.tmp, Node: myisamchk-table-info, Next: myisamchk-memory, Prev: myisamchk-other-options, Up: myisamchk

4.6.3.5 Obtaining Table Information with myisamchk ..................................................

To obtain a description of a 'MyISAM' table or statistics about it, use the commands shown here. The output from these commands is explained later in this section.

The TBL_NAME argument can be either the name of a 'MyISAM' table or the name of its index file, as described in *note myisamchk::. Multiple TBL_NAME arguments can be given.

Suppose that a table named 'person' has the following structure. (The 'MAX_ROWS' table option is included so that in the example output from *note 'myisamchk': myisamchk. shown later, some values are smaller and fit the output format more easily.)

 CREATE TABLE person
 (
   id         INT NOT NULL AUTO_INCREMENT,
   last_name  VARCHAR(20) NOT NULL,
   first_name VARCHAR(20) NOT NULL,
   birth      DATE,
   death      DATE,
   PRIMARY KEY (id),
   INDEX (last_name, first_name),
   INDEX (birth)
 ) MAX_ROWS = 1000000 ENGINE=MYISAM;

Suppose also that the table has these data and index file sizes:

 -rw-rw----  1 mysql  mysql  9347072 Aug 19 11:47 person.MYD
 -rw-rw----  1 mysql  mysql  6066176 Aug 19 11:47 person.MYI

Example of *note 'myisamchk -dvv': myisamchk. output:

 MyISAM file:         person
 Record format:       Packed
 Character set:       latin1_swedish_ci (8)
 File-version:        1
 Creation time:       2009-08-19 16:47:41
 Recover time:        2009-08-19 16:47:56
 Status:              checked,analyzed,optimized keys
 Auto increment key:              1  Last value:                306688
 Data records:               306688  Deleted blocks:                 0
 Datafile parts:             306688  Deleted data:                   0
 Datafile pointer (bytes):        4  Keyfile pointer (bytes):        3
 Datafile length:           9347072  Keyfile length:           6066176
 Max datafile length:    4294967294  Max keyfile length:   17179868159
 Recordlength:                   54

 table description:
 Key Start Len Index   Type                 Rec/key         Root  Blocksize
 1   2     4   unique  long                       1        99328       1024
 2   6     20  multip. varchar prefix           512      3563520       1024
     27    20          varchar                  512
 3   48    3   multip. uint24 NULL           306688      6065152       1024

 Field Start Length Nullpos Nullbit Type
 1     1     1
 2     2     4                      no zeros
 3     6     21                     varchar
 4     27    21                     varchar
 5     48    3      1       1       no zeros
 6     51    3      1       2       no zeros

Explanations for the types of information *note 'myisamchk': myisamchk. produces are given here. 'Keyfile' refers to the index file. 'Record' and 'row' are synonymous, as are 'field' and 'column.'

The initial part of the table description contains these values:

The 'table description' part of the output includes a list of all keys in the table. For each key, *note 'myisamchk': myisamchk. displays some low-level information:

The last part of the output provides information about each column:

The 'Huff tree' and 'Bits' fields are displayed if the table has been compressed with note 'myisampack': myisampack. See note myisampack::, for an example of this information.

Example of *note 'myisamchk -eiv': myisamchk. output:

 Checking MyISAM file: person
 Data records:  306688   Deleted blocks:       0
 - check file-size
 - check record delete-chain
 No recordlinks
 - check key delete-chain
 block_size 1024:
 - check index reference
 - check data record references index: 1
 Key:  1:  Keyblocks used:  98%  Packed:    0%  Max levels:  3
 - check data record references index: 2
 Key:  2:  Keyblocks used:  99%  Packed:   97%  Max levels:  3
 - check data record references index: 3
 Key:  3:  Keyblocks used:  98%  Packed:  -14%  Max levels:  3
 Total:    Keyblocks used:  98%  Packed:   89%

 - check records and index references
 *** LOTS OF ROW NUMBERS DELETED ***

 Records:            306688  M.recordlength:       25  Packed:            83%
 Recordspace used:       97% Empty space:           2% Blocks/Record:   1.00
 Record blocks:      306688  Delete blocks:         0
 Record data:       7934464  Deleted data:          0
 Lost space:         256512  Linkdata:        1156096

 User time 43.08, System time 1.68
 Maximum resident set size 0, Integral resident set size 0
 Non-physical pagefaults 0, Physical pagefaults 0, Swaps 0
 Blocks in 0 out 7, Messages in 0 out 0, Signals 0
 Voluntary context switches 0, Involuntary context switches 0
 Maximum memory usage: 1046926 bytes (1023k)

*note 'myisamchk -eiv': myisamchk. output includes the following information:

 File: manual.info.tmp, Node: myisamchk-memory, Prev: myisamchk-table-info, Up: myisamchk

4.6.3.6 myisamchk Memory Usage ..............................

Memory allocation is important when you run note 'myisamchk': myisamchk. note 'myisamchk': myisamchk. uses no more memory than its memory-related variables are set to. If you are going to use note 'myisamchk': myisamchk. on very large tables, you should first decide how much memory you want it to use. The default is to use only about 3MB to perform repairs. By using larger values, you can get note 'myisamchk': myisamchk. to operate faster. For example, if you have more than 512MB RAM available, you could use options such as these (in addition to any other options you might specify):

 myisamchk --myisam_sort_buffer_size=256M \
            --key_buffer_size=512M \
            --read_buffer_size=64M \
            --write_buffer_size=64M ...

Using '--myisam_sort_buffer_size=16M' is probably enough for most cases.

Be aware that note 'myisamchk': myisamchk. uses temporary files in 'TMPDIR'. If 'TMPDIR' points to a memory file system, out of memory errors can easily occur. If this happens, run note 'myisamchk': myisamchk. with the '--tmpdir=DIR_NAME' option to specify a directory located on a file system that has more space.

When performing repair operations, *note 'myisamchk': myisamchk. also needs a lot of disk space:

If you have a problem with disk space during repair, you can try '--safe-recover' instead of '--recover'.

 File: manual.info.tmp, Node: myisamlog, Next: myisampack, Prev: myisamchk, Up: programs-admin-utils

4.6.4 myisamlog -- Display MyISAM Log File Contents

*note 'myisamlog': myisamlog. processes the contents of a 'MyISAM' log file. To create such a file, start the server with a '--log-isam=log_file' option.

Invoke *note 'myisamlog': myisamlog. like this:

 myisamlog [OPTIONS] [FILE_NAME [TBL_NAME] ...]

The default operation is update ('-u'). If a recovery is done ('-r'), all writes and possibly updates and deletes are done and errors are only counted. The default log file name is 'myisam.log' if no LOG_FILE argument is given. If tables are named on the command line, only those tables are updated.

*note 'myisamlog': myisamlog. supports the following options:

 File: manual.info.tmp, Node: myisampack, Next: mysql-config-editor, Prev: myisamlog, Up: programs-admin-utils

4.6.5 myisampack -- Generate Compressed, Read-Only MyISAM Tables

The note 'myisampack': myisampack. utility compresses 'MyISAM' tables. note 'myisampack': myisampack. works by compressing each column in the table separately. Usually, *note 'myisampack': myisampack. packs the data file 40% to 70%.

When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.

MySQL uses 'mmap()' when possible to perform memory mapping on compressed tables. If 'mmap()' does not work, MySQL falls back to normal read/write file operations.

Please note the following:

Invoke *note 'myisampack': myisampack. like this:

 myisampack [OPTIONS] FILE_NAME ...

Each file name argument should be the name of an index ('.MYI') file. If you are not in the database directory, you should specify the path name to the file. It is permissible to omit the '.MYI' extension.

After you compress a table with note 'myisampack': myisampack, use note 'myisamchk -rq': myisamchk. to rebuild its indexes. *note myisamchk::.

note 'myisampack': myisampack. supports the following options. It also reads option files and supports the options for processing them described at note option-file-options::.

The following sequence of commands illustrates a typical table compression session:

 $> ls -l station.*
 -rw-rw-r--   1 jones    my         994128 Apr 17 19:00 station.MYD
 -rw-rw-r--   1 jones    my          53248 Apr 17 19:00 station.MYI
 -rw-rw-r--   1 jones    my           5767 Apr 17 19:00 station.frm

 $> 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

 $> 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%
 Remember to run myisamchk -rq on compressed tables

 $> myisamchk -rq station
 - check record delete-chain
 - recovering (with sort) MyISAM-table 'station'
 Data records: 1192
 - Fixing index 1
 - Fixing index 2

 $> mysqladmin -uroot flush-tables

 $> ls -l station.*
 -rw-rw-r--   1 jones    my         127874 Apr 17 19:00 station.MYD
 -rw-rw-r--   1 jones    my          55296 Apr 17 19:04 station.MYI
 -rw-rw-r--   1 jones    my           5767 Apr 17 19:00 station.frm

 $> 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
 Datafile pointer (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

*note 'myisampack': myisampack. displays the following kinds of information:

After a table has been compressed, the 'Field' lines displayed by *note 'myisamchk -dvv': myisamchk. include additional information about each column:

After you run note 'myisampack': myisampack, use note 'myisamchk': myisamchk. to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently:

 myisamchk -rq --sort-index --analyze TBL_NAME.MYI

After you have installed the packed table into the MySQL database directory, you should execute note 'mysqladmin flush-tables': mysqladmin. to force note 'mysqld': mysqld. to start using the new table.

To unpack a packed table, use the '--unpack' option to *note 'myisamchk': myisamchk.

 File: manual.info.tmp, Node: mysql-config-editor, Next: mysqlbinlog, Prev: myisampack, Up: programs-admin-utils

4.6.6 mysql_config_editor -- MySQL Configuration Utility

The *note 'mysql_config_editor': mysql-config-editor. utility enables you to store authentication credentials in an obfuscated login path file named '.mylogin.cnf'. The file location is the '%APPDATA%' directory on Windows and the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.

The unobfuscated format of the '.mylogin.cnf' login path file consists of option groups, similar to other option files. Each option group in '.mylogin.cnf' is called a 'login path,' which is a group that permits only certain options: 'host', 'user', 'password', 'port' and 'socket'. Think of a login path option group as a set of options that specify which MySQL server to connect to and which account to authenticate as. Here is an unobfuscated example:

 [client]
 user = mydefaultname
 password = mydefaultpass
 host = 127.0.0.1
 [mypath]
 user = myothername
 password = myotherpass
 host = localhost

When you invoke a client program to connect to the server, the client uses '.mylogin.cnf' in conjunction with other option files. Its precedence is higher than other option files, but less than options specified explicitly on the client command line. For information about the order in which option files are used, see *note option-files::.

To specify an alternate login path file name, set the 'MYSQL_TEST_LOGIN_FILE' environment variable. This variable is recognized by note 'mysql_config_editor': mysql-config-editor, by standard MySQL clients (note 'mysql': mysql, *note 'mysqladmin': mysqladmin, and so forth), and by the 'mysql-test-run.pl' testing utility.

Programs use groups in the login path file as follows:

note 'mysql_config_editor': mysql-config-editor. obfuscates the '.mylogin.cnf' file so it cannot be read as cleartext, and its contents when unobfuscated by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable. note 'mysql_config_editor': mysql-config-editor. provides a 'print' command for displaying the login path file contents, but even in this case, password values are masked so as never to appear in a way that other users can see them.

The obfuscation used by *note 'mysql_config_editor': mysql-config-editor. prevents passwords from appearing in '.mylogin.cnf' as cleartext and provides a measure of security by preventing inadvertent password exposure. For example, if you display a regular unobfuscated 'my.cnf' option file on the screen, any passwords it contains are visible for anyone to see. With '.mylogin.cnf', that is not true, but the obfuscation used is not likely to deter a determined attacker and you should not consider it unbreakable. A user who can gain system administration privileges on your machine to access your files could unobfuscate the '.mylogin.cnf' file with some effort.

The login path file must be readable and writable to the current user, and inaccessible to other users. Otherwise, *note 'mysql_config_editor': mysql-config-editor. ignores it, and client programs do not use it, either.

Invoke *note 'mysql_config_editor': mysql-config-editor. like this:

 mysql_config_editor [PROGRAM_OPTIONS] COMMAND [COMMAND_OPTIONS]

If the login path file does not exist, *note 'mysql_config_editor': mysql-config-editor. creates it.

Command arguments are given as follows:

The position of the command name within the set of program arguments is significant. For example, these command lines have the same arguments, but produce different results:

 mysql_config_editor --help set
 mysql_config_editor set --help

The first command line displays a general *note 'mysql_config_editor': mysql-config-editor. help message, and ignores the 'set' command. The second command line displays a help message specific to the 'set' command.

Suppose that you want to establish a 'client' login path that defines your default connection parameters, and an additional login path named 'remote' for connecting to the MySQL server the host 'remote.example.com'. You want to log in as follows:

To set up the login paths in the '.mylogin.cnf' file, use the following 'set' commands. Enter each command on a single line, and enter the appropriate passwords when prompted:

 $> mysql_config_editor set --login-path=client
          --host=localhost --user=localuser --password
 Enter password: ENTER PASSWORD "LOCALPASS" HERE
 $> mysql_config_editor set --login-path=remote
          --host=remote.example.com --user=remoteuser --password
 Enter password: ENTER PASSWORD "REMOTEPASS" HERE

*note 'mysql_config_editor': mysql-config-editor. uses the 'client' login path by default, so the '--login-path=client' option can be omitted from the first command without changing its effect.

To see what *note 'mysql_config_editor': mysql-config-editor. writes to the '.mylogin.cnf' file, use the 'print' command:

 $> mysql_config_editor print --all
 [client]
 user = localuser
 password = *****
 host = localhost
 [remote]
 user = remoteuser
 password = *****
 host = remote.example.com

The 'print' command displays each login path as a set of lines beginning with a group header indicating the login path name in square brackets, followed by the option values for the login path. Password values are masked and do not appear as cleartext.

If you do not specify '--all' to display all login paths or '--login-path=NAME' to display a named login path, the 'print' command displays the 'client' login path by default, if there is one.

As shown by the preceding example, the login path file can contain multiple login paths. In this way, *note 'mysql_config_editor': mysql-config-editor. makes it easy to set up multiple 'personalities' for connecting to different MySQL servers, or for connecting to a given server using different accounts. Any of these can be selected by name later using the '--login-path' option when you invoke a client program. For example, to connect to the remote server, use this command:

 mysql --login-path=remote

Here, *note 'mysql': mysql. reads the '[client]' and '[mysql]' option groups from other option files, and the '[client]', '[mysql]', and '[remote]' groups from the login path file.

To connect to the local server, use this command:

 mysql --login-path=client

Because *note 'mysql': mysql. reads the 'client' and 'mysql' login paths by default, the '--login-path' option does not add anything in this case. That command is equivalent to this one:

 mysql

Options read from the login path file take precedence over options read from other option files. Options read from login path groups appearing later in the login path file take precedence over options read from groups appearing earlier in the file.

note 'mysql_config_editor': mysql-config-editor. adds login paths to the login path file in the order you create them, so you should create more general login paths first and more specific paths later. If you need to move a login path within the file, you can remove it, then recreate it to add it to the end. For example, a 'client' login path is more general because it is read by all client programs, whereas a 'mysqldump' login path is read only by note 'mysqldump': mysqldump. Options specified later override options specified earlier, so putting the login paths in the order 'client', 'mysqldump' enables *note 'mysqldump': mysqldump.-specific options to override 'client' options.

When you use the 'set' command with *note 'mysql_config_editor': mysql-config-editor. to create a login path, you need not specify all possible option values (host name, user name, password, port, socket). Only those values given are written to the path. Any missing values required later can be specified when you invoke a client path to connect to the MySQL server, either in other option files or on the command line. Any options specified on the command line override those specified in the login path file or other option files. For example, if the credentials in the 'remote' login path also apply for the host 'remote2.example.com', connect to the server on that host like this:

 mysql --login-path=remote --host=remote2.example.com

mysql_config_editor General Options

note 'mysql_config_editor': mysql-config-editor. supports the following general options, which may be used preceding any command named on the command line. For descriptions of command-specific options, see note mysql-config-editor-commands::.

mysql_config_editor General Options

Option Name Description

-debug Write debugging log

-help Display help message and exit

-verbose Verbose mode

-version Display version information and exit

mysql_config_editor Commands and Command-Specific Options

This section describes the permitted *note 'mysql_config_editor': mysql-config-editor. commands, and, for each one, the command-specific options permitted following the command name on the command line.

In addition, note 'mysql_config_editor': mysql-config-editor. supports general options that can be used preceding any command. For descriptions of these options, see note mysql-config-editor-command-options::.

*note 'mysql_config_editor': mysql-config-editor. supports these commands:

 File: manual.info.tmp, Node: mysqlbinlog, Next: mysqldumpslow, Prev: mysql-config-editor, Up: programs-admin-utils

4.6.7 mysqlbinlog -- Utility for Processing Binary Log Files

The server's binary log consists of files containing 'events' that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the note 'mysqlbinlog': mysqlbinlog. utility. You can also use note 'mysqlbinlog': mysqlbinlog. to display the contents of relay log files written by a replica server in a replication setup because relay logs have the same format as binary logs. The binary log and relay log are discussed further in note binary-log::, and note replica-logs::.

Invoke *note 'mysqlbinlog': mysqlbinlog. like this:

 mysqlbinlog [OPTIONS] LOG_FILE ...

For example, to display the contents of the binary log file named 'binlog.000003', use this command:

 mysqlbinlog binlog.000003

The output includes events contained in 'binlog.000003'. For statement-based logging, event information includes the SQL statement, the ID of the server on which it was executed, the timestamp when the statement was executed, how much time it took, and so forth. For row-based logging, the event indicates a row change rather than an SQL statement. See *note replication-formats::, for information about logging modes.

Events are preceded by header comments that provide additional information. For example:

 # at 141
 #100309  9:28:36 server id 123  end_log_pos 245
   Query thread_id=3350  exec_time=11  error_code=0

In the first line, the number following 'at' indicates the file offset, or starting position, of the event in the binary log file.

The second line starts with a date and time indicating when the statement started on the server where the event originated. For replication, this timestamp is propagated to replica servers. 'server id' is the 'server_id' value of the server where the event originated. 'end_log_pos' indicates where the next event starts (that is, it is the end position of the current event + 1). 'thread_id' indicates which thread executed the event. 'exec_time' is the time spent executing the event, on a replication source server. On a replica, it is the difference of the end execution time on the replica minus the beginning execution time on the source. The difference serves as an indicator of how much replication lags behind the source. 'error_code' indicates the result from executing the event. Zero means that no error occurred.

Note:

When using event groups, the file offsets of events may be grouped together and the comments of events may be grouped together. Do not mistake these grouped events for blank file offsets.

The output from note 'mysqlbinlog': mysqlbinlog. can be re-executed (for example, by using it as input to note 'mysql': mysql.) to redo the statements in the log. This is useful for recovery operations after an unexpected server exit. For other usage examples, see the discussion later in this section and in *note point-in-time-recovery::.

You can use *note 'mysqlbinlog': mysqlbinlog. to read binary log files directly and apply them to the local MySQL server. You can also read binary logs from a remote server by using the '--read-from-remote-server' option. To read remote binary logs, the connection parameter options can be given to indicate how to connect to the server. These options are '--host', '--password', '--port', '--protocol', '--socket', and '--user'.

When running note 'mysqlbinlog': mysqlbinlog. against a large binary log, be careful that the filesystem has enough space for the resulting files. To configure the directory that note 'mysqlbinlog': mysqlbinlog. uses for temporary files, use the 'TMPDIR' environment variable.

*note 'mysqlbinlog': mysqlbinlog. sets the value of 'pseudo_slave_mode' to true before executing any SQL statements. This system variable affects the handling of XA transactions.

note 'mysqlbinlog': mysqlbinlog. supports the following options, which can be specified on the command line or in the '[mysqlbinlog]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqlbinlog Options

Option Name Description IntroducedDeprecated

-base64-output

Print binary log entries using base-64 encoding

-bind-address

Use specified network interface to connect to MySQL Server

-binlog-row-event-max-size

Binary log max event size

-character-sets-dir

Directory where character sets are installed

-connection-server-id

Used for testing and debugging. See text for applicable default values and other particulars

-database

List entries for just this database

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-disable-log-bin

Disable binary logging

-exclude-gtids

Do not show any of the groups in the GTID set provided

-force-if-open

Read binary log files even if open or not closed properly

-force-read

If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-hexdump

Display a hex dump of the log in comments

-host

Host on which MySQL server is located

-idempotent

Cause the server to use idempotent mode while processing binary log updates from this session only

-include-gtids

Show only the groups in the GTID set provided

-local-load

Prepare local temporary files for LOAD DATA in the specified directory

-login-path

Read login path options from .mylogin.cnf

-no-defaults

Read no option files

-offset

Skip the first N entries in the log

-open-files-limit

Specify the number of open file descriptors to reserve

-password

Password to use when connecting to server

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-raw

Write events in raw (binary) format to output files

-read-from-remote-master

Read the binary log from a MySQL replication source server rather than reading a local log file

-read-from-remote-server

Read binary log from MySQL server rather than local log file

-result-file

Direct output to named file

-rewrite-db

Create rewrite rules for databases when playing back from logs written in row-based format. Can be used multiple times

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-id

Extract only those events created by the server having the given server ID

-server-id-bits

Tell mysqlbinlog how to interpret server IDs in binary log when log was written by a mysqld having its server-id-bits set to less than the maximum; supported only by MySQL Cluster version of mysqlbinlog

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-set-charset

Add a SET NAMES charset_name statement to the output

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-short-form

Display only the statements contained in the log

-skip-gtids

Do not include the GTIDs from the binary log files in the output dump file

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-start-datetime

Read binary log from first event with timestamp equal to or later than datetime argument

-start-position

Decode binary log from first event with position equal to or greater than argument

-stop-datetime

Stop reading binary log at first event with timestamp equal to or greater than datetime argument

-stop-never

Stay connected to server after reading last binary log file

-stop-never-slave-server-id

Slave server ID to report when connecting to server

-stop-position

Stop decoding binary log at first event with position equal to or greater than argument

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-to-last-log

Do not stop at the end of requested binary log from a MySQL server, but rather continue printing to end of last binary log

-user

MySQL user name to use when connecting to server

-verbose

Reconstruct row events as SQL statements

-verify-binlog-checksum

Verify checksums in binary log

-version

Display version information and exit

You can pipe the output of note 'mysqlbinlog': mysqlbinlog. into the note 'mysql': mysql. client to execute the events contained in the binary log. This technique is used to recover from an unexpected exit when you have an old backup (see *note point-in-time-recovery::). For example:

 mysqlbinlog binlog.000001 | mysql -u root -p

Or:

 mysqlbinlog binlog.[0-9]* | mysql -u root -p

If the statements produced by note 'mysqlbinlog': mysqlbinlog. may contain note 'BLOB': blob. values, these may cause problems when note 'mysql': mysql. processes them. In this case, invoke note 'mysql': mysql. with the '--binary-mode' option.

You can also redirect the output of note 'mysqlbinlog': mysqlbinlog. to a text file instead, if you need to modify the statement log first (for example, to remove statements that you do not want to execute for some reason). After editing the file, execute the statements that it contains by using it as input to the note 'mysql': mysql. program:

 mysqlbinlog binlog.000001 > tmpfile
 ... EDIT TMPFILE ...
 mysql -u root -p < tmpfile

When *note 'mysqlbinlog': mysqlbinlog. is invoked with the '--start-position' option, it displays only those events with an offset in the binary log greater than or equal to a given position (the given position must match the start of one event). It also has options to stop and start when it sees an event with a given date and time. This enables you to perform point-in-time recovery using the '--stop-datetime' option (to be able to say, for example, 'roll forward my databases to how they were today at 10:30 a.m.').

Processing multiple files

If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:

 mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
 mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using multiple connections to the server causes problems if the first log file contains a note 'CREATE TEMPORARY TABLE': create-table. statement and the second log contains a statement that uses the temporary table. When the first note 'mysql': mysql. process terminates, the server drops the temporary table. When the second *note 'mysql': mysql. process attempts to use the table, the server reports 'unknown table.'

To avoid problems like this, use a single *note 'mysql': mysql. process to execute the contents of all binary logs that you want to process. Here is one way to do so:

 mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file:

 mysqlbinlog binlog.000001 >  /tmp/statements.sql
 mysqlbinlog binlog.000002 >> /tmp/statements.sql
 mysql -u root -p -e "source /tmp/statements.sql"

note 'mysqlbinlog': mysqlbinlog. can produce output that reproduces a note 'LOAD DATA': load-data. operation without the original data file. note 'mysqlbinlog': mysqlbinlog. copies the data to a temporary file and writes a note 'LOAD DATA LOCAL': load-data. statement that refers to the file. The default location of the directory where these files are written is system-specific. To specify a directory explicitly, use the '--local-load' option.

Because note 'mysqlbinlog': mysqlbinlog. converts note 'LOAD DATA': load-data. statements to note 'LOAD DATA LOCAL': load-data. statements (that is, it adds 'LOCAL'), both the client and the server that you use to process the statements must be configured with the 'LOCAL' capability enabled. See note load-data-local-security::.

Warning:

The temporary files created for *note 'LOAD DATA LOCAL': load-data. statements are not automatically deleted because they are needed until you actually execute those statements. You should delete the temporary files yourself after you no longer need the statement log. The files can be found in the temporary file directory and have names like ORIGINAL_FILE_NAME-#-#.

 File: manual.info.tmp, Node: mysqlbinlog-hexdump, Next: mysqlbinlog-row-events, Prev: mysqlbinlog, Up: mysqlbinlog

4.6.7.1 mysqlbinlog Hex Dump Format ...................................

The '--hexdump' option causes *note 'mysqlbinlog': mysqlbinlog. to produce a hex dump of the binary log contents:

 mysqlbinlog --hexdump master-bin.000001

The hex output consists of comment lines beginning with '#', so the output might look like this for the preceding command:

 /*!40019 SET @@SESSION.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 # at 4
 #051024 17:24:13 server id 1  end_log_pos 98
 # Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
 # 00000004 9d fc 5c 43   0f   01 00 00 00   5e 00 00 00   62 00 00 00   00 00
 # 00000017 04 00 35 2e 30 2e 31 35  2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
 # 00000027 6f 67 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |og..............|
 # 00000037 00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |................|
 # 00000047 00 00 00 00 9d fc 5c 43  13 38 0d 00 08 00 12 00 |.......C.8......|
 # 00000057 04 04 04 04 12 00 00 4b  00 04 1a                |.......K...|
 #       Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
 #       at startup
 ROLLBACK;

Hex dump output currently contains the elements in the following list. This format is subject to change. For more information about binary log format, see MySQL Internals: The Binary Log (https://dev.mysql.com/doc/internals/en/binary-log.html).

 File: manual.info.tmp, Node: mysqlbinlog-row-events, Next: mysqlbinlog-backup, Prev: mysqlbinlog-hexdump, Up: mysqlbinlog

4.6.7.2 mysqlbinlog Row Event Display .....................................

The following examples illustrate how *note 'mysqlbinlog': mysqlbinlog. displays row events that specify data modifications. These correspond to events with the 'WRITE_ROWS_EVENT', 'UPDATE_ROWS_EVENT', and 'DELETE_ROWS_EVENT' type codes. The '--base64-output=DECODE-ROWS' and '--verbose' options may be used to affect row event output.

Suppose that the server is using row-based binary logging and that you execute the following sequence of statements:

 CREATE TABLE t
 (
   id   INT NOT NULL,
   name VARCHAR(20) NOT NULL,
   date DATE NULL
 ) ENGINE = InnoDB;

 START TRANSACTION;
 INSERT INTO t VALUES(1, 'apple', NULL);
 UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;
 DELETE FROM t WHERE id = 1;
 COMMIT;

By default, note 'mysqlbinlog': mysqlbinlog. displays row events encoded as base-64 strings using note 'BINLOG': binlog. statements. Omitting extraneous lines, the output for the row events produced by the preceding statement sequence looks like this:

 $> mysqlbinlog LOG_FILE
 ...
 # at 218
 #080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
 '/*!*/;
 ...
 # at 302
 #080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
 '/*!*/;
 ...
 # at 400
 #080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
 '/*!*/;

To see the row events as comments in the form of 'pseudo-SQL' statements, run *note 'mysqlbinlog': mysqlbinlog. with the '--verbose' or '-v' option. The output contains lines beginning with '###':

 $> mysqlbinlog -v LOG_FILE
 ...
 # at 218
 #080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
 '/*!*/;
 ### INSERT INTO test.t
 ### SET
 ###   @1=1
 ###   @2='apple'
 ###   @3=NULL
 ...
 # at 302
 #080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
 '/*!*/;
 ### UPDATE test.t
 ### WHERE
 ###   @1=1
 ###   @2='apple'
 ###   @3=NULL
 ### SET
 ###   @1=1
 ###   @2='pear'
 ###   @3='2009:01:01'
 ...
 # at 400
 #080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
 '/*!*/;
 ### DELETE FROM test.t
 ### WHERE
 ###   @1=1
 ###   @2='pear'
 ###   @3='2009:01:01'

Specify '--verbose' or '-v' twice to also display data types and some metadata for each column. The output contains an additional comment following each column change:

 $> mysqlbinlog -vv LOG_FILE
 ...
 # at 218
 #080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
 '/*!*/;
 ### INSERT INTO test.t
 ### SET
 ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
 ###   @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
 ...
 # at 302
 #080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
 '/*!*/;
 ### UPDATE test.t
 ### WHERE
 ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
 ###   @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
 ### SET
 ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
 ###   @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
 ...
 # at 400
 #080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

 BINLOG '
 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
 '/*!*/;
 ### DELETE FROM test.t
 ### WHERE
 ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
 ###   @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */

You can tell note 'mysqlbinlog': mysqlbinlog. to suppress the note 'BINLOG': binlog. statements for row events by using the '--base64-output=DECODE-ROWS' option. This is similar to '--base64-output=NEVER' but does not exit with an error if a row event is found. The combination of '--base64-output=DECODE-ROWS' and '--verbose' provides a convenient way to see row events only as SQL statements:

 $> mysqlbinlog -v --base64-output=DECODE-ROWS LOG_FILE
 ...
 # at 218
 #080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F
 ### INSERT INTO test.t
 ### SET
 ###   @1=1
 ###   @2='apple'
 ###   @3=NULL
 ...
 # at 302
 #080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F
 ### UPDATE test.t
 ### WHERE
 ###   @1=1
 ###   @2='apple'
 ###   @3=NULL
 ### SET
 ###   @1=1
 ###   @2='pear'
 ###   @3='2009:01:01'
 ...
 # at 400
 #080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F
 ### DELETE FROM test.t
 ### WHERE
 ###   @1=1
 ###   @2='pear'
 ###   @3='2009:01:01'

Note:

You should not suppress note 'BINLOG': binlog. statements if you intend to re-execute note 'mysqlbinlog': mysqlbinlog. output.

The SQL statements produced by '--verbose' for row events are much more readable than the corresponding *note 'BINLOG': binlog. statements. However, they do not correspond exactly to the original SQL statements that generated the events. The following limitations apply:

Proper interpretation of row events requires the information from the format description event at the beginning of the binary log. Because note 'mysqlbinlog': mysqlbinlog. does not know in advance whether the rest of the log contains row events, by default it displays the format description event using a note 'BINLOG': binlog. statement in the initial part of the output.

If the binary log is known not to contain any events requiring a *note 'BINLOG': binlog. statement (that is, no row events), the '--base64-output=NEVER' option can be used to prevent this header from being written.

 File: manual.info.tmp, Node: mysqlbinlog-backup, Next: mysqlbinlog-server-id, Prev: mysqlbinlog-row-events, Up: mysqlbinlog

4.6.7.3 Using mysqlbinlog to Back Up Binary Log Files .....................................................

By default, note 'mysqlbinlog': mysqlbinlog. reads binary log files and displays their contents in text format. This enables you to examine events within the files more easily and to re-execute them (for example, by using the output as input to note 'mysql': mysql.). note 'mysqlbinlog': mysqlbinlog. can read log files directly from the local file system, or, with the '--read-from-remote-server' option, it can connect to a server and request binary log contents from that server. note 'mysqlbinlog': mysqlbinlog. writes text output to its standard output, or to the file named as the value of the '--result-file=FILE_NAME' option if that option is given.

mysqlbinlog Backup Capabilities

note 'mysqlbinlog': mysqlbinlog. can read binary log files and write new files containing the same content--that is, in binary format rather than text format. This capability enables you to easily back up a binary log in its original format. note 'mysqlbinlog': mysqlbinlog. can make a static backup, backing up a set of log files and stopping when the end of the last file is reached. It can also make a continuous ('live') backup, staying connected to the server when it reaches the end of the last log file and continuing to copy new events as they are generated. In continuous-backup operation, note 'mysqlbinlog': mysqlbinlog. runs until the connection ends (for example, when the server exits) or note 'mysqlbinlog': mysqlbinlog. is forcibly terminated. When the connection ends, note 'mysqlbinlog': mysqlbinlog. does not wait and retry the connection, unlike a replica server. To continue a live backup after the server has been restarted, you must also restart note 'mysqlbinlog': mysqlbinlog.

mysqlbinlog Backup Options

Binary log backup requires that you invoke *note 'mysqlbinlog': mysqlbinlog. with two options at minimum:

Along with '--read-from-remote-server', it is common to specify other options: '--host' indicates where the server is running, and you may also need to specify connection options such as '--user' and '--password'.

Several other options are useful in conjunction with '--raw':

Static and Live Backups

To back up a server's binary log files with note 'mysqlbinlog': mysqlbinlog, you must specify file names that actually exist on the server. If you do not know the names, connect to the server and use the note 'SHOW BINARY LOGS': show-binary-logs. statement to see the current names. Suppose that the statement produces this output:

 mysql> SHOW BINARY LOGS;
 +---------------+-----------+
 | Log_name      | File_size |
 +---------------+-----------+
 | binlog.000130 |     27459 |
 | binlog.000131 |     13719 |
 | binlog.000132 |     43268 |
 +---------------+-----------+

With that information, you can use *note 'mysqlbinlog': mysqlbinlog. to back up the binary log to the current directory as follows (enter each command on a single line):

Output File Naming

Without '--raw', note 'mysqlbinlog': mysqlbinlog. produces text output and the '--result-file' option, if given, specifies the name of the single file to which all output is written. With '--raw', note 'mysqlbinlog': mysqlbinlog. writes one binary output file for each log file transferred from the server. By default, *note 'mysqlbinlog': mysqlbinlog. writes the files in the current directory with the same names as the original log files. To modify the output file names, use the '--result-file' option. In conjunction with '--raw', the '--result-file' option value is treated as a prefix that modifies the output file names.

Suppose that a server currently has binary log files named 'binlog.000999' and up. If you use *note 'mysqlbinlog --raw': mysqlbinlog. to back up the files, the '--result-file' option produces output file names as shown in the following table. You can write the files to a specific directory by beginning the '--result-file' value with the directory path. If the '--result-file' value consists only of a directory name, the value must end with the pathname separator character. Output files are overwritten if they exist.

'--result-file' Option Output File Names

'--result-file=x' 'xbinlog.000999' and up

'--result-file=/tmp/' '/tmp/binlog.000999' and up

'--result-file=/tmp/x' '/tmp/xbinlog.000999' and up

Example: mysqldump + mysqlbinlog for Backup and Restore

The following example describes a simple scenario that shows how to use note 'mysqldump': mysqldump. and note 'mysqlbinlog': mysqlbinlog. together to back up a server's data and binary log, and how to use the backup to restore the server if data loss occurs. The example assumes that the server is running on host HOST_NAME and its first binary log file is named 'binlog.000999'. Enter each command on a single line.

Use *note 'mysqlbinlog': mysqlbinlog. to make a continuous backup of the binary log:

 mysqlbinlog --read-from-remote-server --host=HOST_NAME --raw
   --stop-never binlog.000999

Use *note 'mysqldump': mysqldump. to create a dump file as a snapshot of the server's data. Use '--all-databases', '--events', and '--routines' to back up all data, and '--master-data=2' to include the current binary log coordinates in the dump file.

 mysqldump --host=HOST_NAME --all-databases --events --routines --master-data=2> DUMP_FILE

Execute the *note 'mysqldump': mysqldump. command periodically to create newer snapshots as desired.

If data loss occurs (for example, if the server unexpectedly exits), use the most recent dump file to restore the data:

 mysql --host=HOST_NAME -u root -p < DUMP_FILE

Then use the binary log backup to re-execute events that were written after the coordinates listed in the dump file. Suppose that the coordinates in the file look like this:

 -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284;

If the most recent backed-up log file is named 'binlog.001004', re-execute the log events like this:

 mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004
   | mysql --host=HOST_NAME -u root -p

You might find it easier to copy the backup files (dump file and binary log files) to the server host to make it easier to perform the restore operation, or if MySQL does not allow remote 'root' access.

mysqlbinlog Backup Restrictions

Binary log backups with *note 'mysqlbinlog': mysqlbinlog. are subject to these restrictions:

 File: manual.info.tmp, Node: mysqlbinlog-server-id, Prev: mysqlbinlog-backup, Up: mysqlbinlog

4.6.7.4 Specifying the mysqlbinlog Server ID ............................................

When invoked with the '--read-from-remote-server' option, note 'mysqlbinlog': mysqlbinlog. connects to a MySQL server, specifies a server ID to identify itself, and requests binary log files from the server. You can use note 'mysqlbinlog': mysqlbinlog. to request log files from a server in several ways:

With '--read-from-remote-server' only, *note 'mysqlbinlog': mysqlbinlog. connects using a server ID of 0, which tells the server to disconnect after sending the last requested log file.

With '--read-from-remote-server' and '--stop-never', *note 'mysqlbinlog': mysqlbinlog. connects using a nonzero server ID, so the server does not disconnect after sending the last log file. The server ID is 65535 by default, but this can be changed with '--stop-never-slave-server-id'.

Thus, for the first two ways of requesting files, the server disconnects because note 'mysqlbinlog': mysqlbinlog. specifies a server ID of 0. It does not disconnect if '--stop-never' is given because note 'mysqlbinlog': mysqlbinlog. specifies a nonzero server ID.

 File: manual.info.tmp, Node: mysqldumpslow, Prev: mysqlbinlog, Up: programs-admin-utils

4.6.8 mysqldumpslow -- Summarize Slow Query Log Files

The MySQL slow query log contains information about queries that take a long time to execute (see note slow-query-log::). note 'mysqldumpslow': mysqldumpslow. parses MySQL slow query log files and summarizes their contents.

Normally, *note 'mysqldumpslow': mysqldumpslow. groups queries that are similar except for the particular values of number and string data values. It 'abstracts' these values to 'N' and ''S'' when displaying summary output. To modify value abstracting behavior, use the '-a' and '-n' options.

Invoke *note 'mysqldumpslow': mysqldumpslow. like this:

 mysqldumpslow [OPTIONS] [LOG_FILE ...]

Example output with no options given:

 Reading mysql slow query log from /usr/local/mysql/data/mysqld57-slow.log
 Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into t2 select * from t1

 Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into t2 select * from t1 limit N

 Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into t1 select * from t1

*note 'mysqldumpslow': mysqldumpslow. supports the following options.

mysqldumpslow Options

Option Name Description

-a Do not abstract all numbers to N and strings to 'S'

-n Abstract numbers with at least the specified digits

-debug Write debugging information

-g Only consider statements that match the pattern

-help Display help message and exit

-h Host name of the server in the log file name

-i Name of the server instance

-l Do not subtract lock time from total time

-r Reverse the sort order

-s How to sort output

-t Display only first num queries

-verbose Verbose mode

 File: manual.info.tmp, Node: programs-development, Next: programs-miscellaneous, Prev: programs-admin-utils, Up: programs