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 will cause InnoDB to deliberately shut down a running server, it can be preferable to use this tool rather than waiting for a server in production usage to encounter the damaged pages.

innochecksum cannot be used on tablespace files that the server already has open. For such files, you should use CHECK TABLE to check tables within the tablespace.

If checksum mismatches are found, you would normally restore the tablespace from backup or start the server and attempt to use mysqldump to make a backup of the tables within the tablespace.

Invoke innochecksum like this:

shell> innochecksum [options] file_name

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

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 myisam_ftdump, be sure to issue a FLUSH TABLES statement first if the server is running.

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 myisam_ftdump like this:

shell> 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 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 mytexttablel 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 myisam_ftdump as follows:

shell> 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 myisam_ftdump in the database directory:

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

You can use myisam_ftdump to generate a list of index entries in order of frequency of occurrence like this:

shell> myisam_ftdump -c mytexttable 1 | sort -r

myisam_ftdump supports the following options:

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

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See Section 13.7.2.2, "CHECK TABLE Syntax", and Section 13.7.2.5, "REPAIR TABLE Syntax".

The use of myisamchk with partitioned tables is not supported.

Invoke myisamchk like this:

shell> myisamchk [options] tbl_name ...

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

With no options, myisamchk simply checks your table as the default operation. To get more information or to tell 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 myisamchk somewhere other than in the database directory, you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, 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 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:

shell> myisamchk *.MYI

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

shell> 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:

shell> myisamchk /path/to/datadir/*/*.MYI

The recommended way to quickly check all MyISAM tables is:

shell> 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:

shell> 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 myisamchk, see Section 4.6.3.6, "myisamchk Memory Usage".

For additional information about using myisamchk, see Section 7.6, "MyISAM Table Maintenance and Crash Recovery".

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

myisamchk supports the following options, which can be specified on the command line or in the [myisamchk] group of an option file. myisamchk also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling".

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

[mysqld]ft_min_word_len=3[myisamchk]ft_min_word_len=3

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;

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

MyISAM file: personRecord format:   PackedCharacter set:   latin1_swedish_ci (8)File-version: 1Creation time:   2009-08-19 16:47:41Recover time: 2009-08-19 16:47:56Status:  checked,analyzed,optimized keysAuto increment key:  1  Last value: 306688Data records:   306688  Deleted blocks: 0Datafile parts: 306688  Deleted data:   0Datafile pointer (bytes): 4  Keyfile pointer (bytes): 3Datafile length:   9347072  Keyfile length:   6066176Max datafile length: 4294967294  Max keyfile length:   17179868159Recordlength:   54table description:Key Start Len Index   Type Rec/key Root  Blocksize1   2 4   unique  long   1 99328   10242   6 20  multip. varchar prefix   512  3563520   1024 27 20  varchar  5123   48 3   multip. uint24 NULL   306688  6065152   1024Field Start Length Nullpos Nullbit Type1 1 12 2 4  no zeros3 6 21 varchar4 27 21 varchar5 48 3  1   1   no zeros6 51 3  1   2   no zeros

Checking MyISAM file: personData records:  306688   Deleted blocks:   0- check file-size- check record delete-chainNo recordlinks- check key delete-chainblock_size 1024:- check index reference- check data record references index: 1Key:  1:  Keyblocks used:  98%  Packed: 0%  Max levels:  3- check data record references index: 2Key:  2:  Keyblocks used:  99%  Packed:   97%  Max levels:  3- check data record references index: 3Key:  3:  Keyblocks used:  98%  Packed:  -14%  Max levels:  3Total: 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.00Record blocks:  306688  Delete blocks: 0Record data:   7934464  Deleted data:  0Lost space: 256512  Linkdata: 1156096User time 43.08, System time 1.68Maximum resident set size 0, Integral resident set size 0Non-physical pagefaults 0, Physical pagefaults 0, Swaps 0Blocks in 0 out 7, Messages in 0 out 0, Signals 0Voluntary context switches 0, Involuntary context switches 0Maximum memory usage: 1046926 bytes (1023k)

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

myisamlog processes the contents of a MyISAM log file.

Invoke myisamlog like this:

shell> myisamlog [options] [log_file [tbl_name] ...]shell> isamlog [options] [log_file [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 for myisamlog and isam.log for isamlog if no log_file argument is given. If tables are named on the command line, only those tables are updated.

myisamlog supports the following options:

The myisampack utility compresses MyISAM tables. myisampack works by compressing each column in the table separately. Usually, 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 myisampack like this:

shell> 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 myisampack, you should use myisamchk -rq to rebuild its indexes. Section 4.6.3, "myisamchk - MyISAM Table-Maintenance Utility".

myisampack supports the following options. It also reads option files and supports the options for processing them described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling".

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

shell> ls -l station.*-rw-rw-r--   1 monty my 994128 Apr 17 19:00 station.MYD-rw-rw-r--   1 monty my  53248 Apr 17 19:00 station.MYI-rw-rw-r--   1 monty my   5767 Apr 17 19:00 station.frmshell> myisamchk -dvv stationMyISAM file: stationIsam-version:  2Creation time: 1996-03-13 10:08:58Recover time:  1997-02-02  3:06:43Data records:  1192  Deleted blocks:  0Datafile parts: 1192  Deleted data: 0Datafile pointer (bytes): 2  Keyfile pointer (bytes): 2Max datafile length:   54657023  Max keyfile length:   33554431Recordlength:   834Record format: Fixed lengthtable description:Key Start Len Index   Type Root  Blocksize Rec/key1   2 4   unique  unsigned long 1024   1024  12   32 30  multip. text 10240   1024  1Field Start Length Type1 1 12 2 43 6 44 10 15 11 206 31 17 32 308 62 359 97 3510 132   3511 167   412 171   1613 187   3514 222   415 226   1616 242   2017 262   2018 282   2019 302   3020 332   421 336   422 340   123 341   824 349   825 357   826 365   227 367   228 369   429 373   430 377   131 378   232 380   833 388   434 392   435 396   436 400   437 404   138 405   439 409   440 413   441 417   442 421   443 425   444 429   2045 449   3046 479   147 480   148 481   7949 560   7950 639   7951 718   7952 797   853 805   154 806   155 807   2056 827   457 831   4shell> myisampack station.MYICompressing station.MYI: (1192 records)- Calculating statisticsnormal: 20  empty-space:   16  empty-zero: 12  empty-fill:  11pre-space:   0  end-space: 12  table-lookups:   5  zero: 7Original trees:  57  After join: 17- Compressing file87.14%Remember to run myisamchk -rq on compressed tablesshell> ls -l station.*-rw-rw-r--   1 monty my 127874 Apr 17 19:00 station.MYD-rw-rw-r--   1 monty my  55296 Apr 17 19:04 station.MYI-rw-rw-r--   1 monty my   5767 Apr 17 19:00 station.frmshell> myisamchk -dvv stationMyISAM file: stationIsam-version:  2Creation time: 1996-03-13 10:08:58Recover time:  1997-04-17 19:04:26Data records:   1192  Deleted blocks:  0Datafile parts: 1192  Deleted data: 0Datafile pointer (bytes):  3  Keyfile pointer (bytes): 1Max datafile length: 16777215  Max keyfile length: 131071Recordlength: 834Record format: Compressedtable description:Key Start Len Index   Type Root  Blocksize Rec/key1   2 4   unique  unsigned long   10240   1024  12   32 30  multip. text 54272   1024  1Field Start Length Type Huff tree  Bits1 1 1  constant 1 02 2 4  zerofill(1)  2 93 6 4  no zeros, zerofill(1) 2 94 10 1   3 95 11 20 table-lookup 4 06 31 1   3 97 32 30 no endspace, not_always  5 98 62 35 no endspace, not_always, no empty 6 99 97 35 no empty 7 910 132   35 no endspace, not_always, no empty 6 911 167   4  zerofill(1)  2 912 171   16 no endspace, not_always, no empty 5 913 187   35 no endspace, not_always, no empty 6 914 222   4  zerofill(1)  2 915 226   16 no endspace, not_always, no empty 5 916 242   20 no endspace, not_always  8 917 262   20 no endspace, no empty 8 918 282   20 no endspace, no empty 5 919 302   30 no endspace, no empty 6 920 332   4  always zero  2 921 336   4  always zero  2 922 340   1   3 923 341   8  table-lookup 9 024 349   8  table-lookup 10 025 357   8  always zero  2 926 365   2   2 927 367   2  no zeros, zerofill(1) 2 928 369   4  no zeros, zerofill(1) 2 929 373   4  table-lookup 11 030 377   1   3 931 378   2  no zeros, zerofill(1) 2 932 380   8  no zeros 2 933 388   4  always zero  2 934 392   4  table-lookup 12 035 396   4  no zeros, zerofill(1)   13 936 400   4  no zeros, zerofill(1) 2 937 404   1   2 938 405   4  no zeros 2 939 409   4  always zero  2 940 413   4  no zeros 2 941 417   4  always zero  2 942 421   4  no zeros 2 943 425   4  always zero  2 944 429   20 no empty 3 945 449   30 no empty 3 946 479   1  14 447 480   1  14 448 481   79 no endspace, no empty   15 949 560   79 no empty 2 950 639   79 no empty 2 951 718   79 no endspace 16 952 797   8  no empty 2 953 805   1  17 154 806   1   3 955 807   20 no empty 3 956 827   4  no zeros, zerofill(2) 2 957 831   4  no zeros, zerofill(1) 2 9

myisampack displays the following kinds of information:

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

After you run myisampack, you must run 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:

shell> myisamchk -rq --sort-index --analyze tbl_name.MYI

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

To unpack a packed table, use the --unpack option to myisamchk.

mysqlaccess is a diagnostic tool that Yves Carlier has provided for the MySQL distribution. It checks the access privileges for a host name, user name, and database combination. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table, column, or routine privileges specified in the tables_priv, columns_priv, or procs_priv tables.

Invoke mysqlaccess like this:

shell> mysqlaccess [host_name [user_name [db_name]]] [options]

mysqlaccess supports the following options.

If your MySQL distribution is installed in some nonstandard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the mysqlaccess script at approximately line 18. Search for a line that looks like this:

$MYSQL = '/usr/local/bin/mysql'; # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.

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 mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave 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 Section 5.2.4, "The Binary Log", and Section 16.2.2, "Replication Relay and Status Logs".

Invoke mysqlbinlog like this:

shell> mysqlbinlog [options] log_file ...

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

shell> mysqlbinlog binlog.0000003

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 Section 16.1.2, "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 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 slave 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 master server. On a slave, it is the difference of the end execution time on the slave minus the beginning execution time on the master. The difference serves as an indicator of how much replication lags behind the master. error_code indicates the result from executing the event. Zero means that no error occurred.

The output from mysqlbinlog can be re-executed (for example, by using it as input to mysql) to redo the statements in the log. This is useful for recovery operations after a server crash. For other usage examples, see the discussion later in this section and in Section 7.5, "Point-in-Time (Incremental) Recovery Using the Binary Log".

Normally, you use mysqlbinlog to read binary log files directly and apply them to the local MySQL server. It is also possible to 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; they are ignored except when you also use the --read-from-remote-server option.

mysqlbinlog supports the following options, which can be specified on the command line or in the [mysqlbinlog] and [client] groups of an option file. mysqlbinlog also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling".

You can also set the following variable by using --var_name=value syntax:

You can pipe the output of mysqlbinlog into the mysql client to execute the events contained in the binary log. This technique is used to recover from a crash when you have an old backup (see Section 7.5, "Point-in-Time (Incremental) Recovery Using the Binary Log"). For example:

shell> mysqlbinlog binlog.000001 | mysql -u root -p

Or:

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

You can also redirect the output of 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 mysql program:

shell> mysqlbinlog binlog.000001 > tmpfileshell> ... edit tmpfile ...shell> mysql -u root -p < tmpfile

When 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.").

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:

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!shell> 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 CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports "unknown table."

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

shell> 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:

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

mysqlbinlog can produce output that reproduces a LOAD DATA INFILE operation without the original data file. mysqlbinlog copies the data to a temporary file and writes a LOAD DATA LOCAL INFILE 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 mysqlbinlog converts LOAD DATA INFILE statements to LOAD DATA LOCAL INFILE 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 Section 6.1.6, "Security Issues with LOAD DATA LOCAL".

shell> mysqlbinlog --hexdump master-bin.000001

/*!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 startupROLLBACK;

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;

shell> mysqlbinlog log_file...# at 218#080828 15:03:08 server id 1  end_log_pos 258 Write_rows: table id 17 flags: STMT_END_FBINLOG 'fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ=='/*!*/;...# at 302#080828 15:03:08 server id 1  end_log_pos 356 Update_rows: table id 17 flags: STMT_END_FBINLOG 'fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP'/*!*/;...# at 400#080828 15:03:08 server id 1  end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_FBINLOG 'fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP'/*!*/;

shell> 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_FBINLOG '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_FBINLOG '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_FBINLOG 'fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP'/*!*/;### DELETE FROM test.t### WHERE###   @1=1###   @2='pear'###   @3='2009:01:01'

shell> 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_FBINLOG '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_FBINLOG '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_FBINLOG '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 */

shell> 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'

The MySQL slow query log contains information about queries that take a long time to execute (see Section 5.2.5, "The Slow Query Log"). mysqldumpslow parses MySQL slow query log files and prints a summary of their contents.

Normally, 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. The -a and -n options can be used to modify value abstracting behavior.

Invoke mysqldumpslow like this:

shell> mysqldumpslow [options] [log_file ...]

mysqldumpslow supports the following options.

Example of usage:

shell> mysqldumpslowReading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.logCount: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost insert into t2 select * from t1Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost insert into t2 select * from t1 limit NCount: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost insert into t1 select * from t1

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix.

To use mysqlhotcopy, you must have read access to the files for the tables that you are backing up, the SELECT privilege for those tables, the RELOAD privilege (to be able to execute FLUSH TABLES), and the LOCK TABLES privilege (to be able to lock the tables).

shell> mysqlhotcopy db_name [/path/to/new_directory]

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Back up tables in the given database that match a regular expression:

shell> mysqlhotcopy db_name./regex/

The regular expression for the table name can be negated by prefixing it with a tilde ("~"):

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy supports the following options, which can be specified on the command line or in the [mysqlhotcopy] and [client] groups of an option file. For information about option files, see Section 4.2.3.3, "Using Option Files".

Use perldoc for additional mysqlhotcopy documentation, including information about the structure of the tables needed for the --checkpoint and --record_log_pos options:

shell> perldoc mysqlhotcopy

mysql_convert_table_format converts the tables in a database to use a particular storage engine (MyISAM by default). mysql_convert_table_format is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see Section 2.14, "Perl Installation Notes").

Invoke mysql_convert_table_format like this:

shell> mysql_convert_table_format [options]db_name

The db_name argument indicates the database containing the tables to be converted.

mysql_convert_table_format supports the options described in the following list.

mysql_find_rows reads files containing SQL statements and extracts statements that match a given regular expression or that contain USE db_name or SET statements. The utility expects statements to be terminated with semicolon (;) characters.

Invoke mysql_find_rows like this:

shell> mysql_find_rows [options] [file_name ...]

Each file_name argument should be the name of file containing SQL statements. If no file names are given, mysql_find_rows reads the standard input.

Examples:

mysql_find_rows --regexp=problem_table --rows=20 < update.logmysql_find_rows --regexp=problem_table  update-log.1 update-log.2

mysql_find_rows supports the following options:

mysql_fix_extensions converts the extensions for MyISAM (or ISAM) table files to their canonical forms. It looks for files with extensions matching any lettercase variant of .frm, .myd, .myi, .isd, and .ism and renames them to have extensions of .frm, .MYD, .MYI, .ISD, and .ISM, respectively. This can be useful after transferring the files from a system with case-insensitive file names (such as Windows) to a system with case-sensitive file names.

Invoke mysql_fix_extensions like this, where data_dir is the path name to the MySQL data directory.

shell> mysql_fix_extensions data_dir

mysql_setpermission is a Perl script that was originally written and contributed by Luuk de Boer. It interactively sets permissions in the MySQL grant tables. mysql_setpermission is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see Section 2.14, "Perl Installation Notes").

Invoke mysql_setpermission like this:

shell> mysql_setpermission [options]

options should be either --help to display the help message, or options that indicate how to connect to the MySQL server. The account used when you connect determines which permissions you have when attempting to modify existing permissions in the grant tables.

mysql_setpermissions also reads options from the [client] and [perl] groups in the .my.cnf file in your home directory, if the file exists.

mysql_setpermission supports the following options:

mysql_waitpid signals a process to terminate and waits for the process to exit. It uses the kill() system call and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_waitpid like this:

shell> mysql_waitpid [options] pid wait_time

mysql_waitpid sends signal 0 to the process identified by pid and waits up to wait_time seconds for the process to terminate. pid and wait_time must be positive integers.

If process termination occurs within the wait time or the process does not exist, mysql_waitpid returns 0. Otherwise, it returns 1.

If the kill() system call cannot handle signal 0, mysql_waitpid() uses signal 1 instead.

mysql_waitpid supports the following options:

mysql_zap kills processes that match a pattern. It uses the ps command and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_zap like this:

shell> mysql_zap [-signal] [-?Ift] pattern

A process matches if its output line from the ps command contains the pattern. By default, mysql_zap asks for confirmation for each process. Respond y to kill the process, or q to exit mysql_zap. For any other response, mysql_zap does not attempt to kill the process.

If the -signal option is given, it specifies the name or number of the signal to send to each process. Otherwise, mysql_zap tries first with TERM (signal 15) and then with KILL (signal 9).

mysql_zap supports the following additional options:

Initially, the MySQL C API was developed to be very similar to that for the mSQL database system. Because of this, mSQL programs often can be converted relatively easily for use with MySQL by changing the names of the C API functions.

The msql2mysql utility performs the conversion of mSQL C API function calls to their MySQL equivalents. msql2mysql converts the input file in place, so make a copy of the original before converting it. For example, use msql2mysql like this:

shell> cp client-prog.c client-prog.c.origshell> msql2mysql client-prog.cclient-prog.c converted

Then examine client-prog.c and make any post-conversion revisions that may be necessary.

msql2mysql uses the replace utility to make the function name substitutions. See Section 4.8.2, "replace - A String-Replacement Utility".

mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL.

mysql_config supports the following options.

If you invoke mysql_config with no options, it displays a list of all options that it supports, and their values:

shell> mysql_configUsage: /usr/local/mysql/bin/mysql_config [options]Options:  --cflags [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro]  --include [-I/usr/local/mysql/include/mysql]  --libs   [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]  --libs_r [-L/usr/local/mysql/lib/mysql -lmysqlclient_r -lpthread -lz -lcrypt -lnsl -lm -lpthread]  --socket [/tmp/mysql.sock]  --port   [3306]  --version [4.0.16]  --libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz -lcrypt -lnsl -lm -lpthread -lrt]

You can use mysql_config within a command line to include the value that it displays for a particular option. For example, to compile a MySQL client program, use mysql_config as follows:

shell> CFG=/usr/local/mysql/bin/mysql_configshell> sh -c "gcc -o progname `$CFG --include` progname.c `$CFG --libs`"

When you use mysql_config this way, be sure to invoke it within backtick ("`") characters. That tells the shell to execute it and substitute its output into the surrounding command.

my_print_defaults displays the options that are present in option groups of option files. The output indicates what options will be used by programs that read the specified option groups. For example, the mysqlcheck program reads the [mysqlcheck] and [client] option groups. To see what options are present in those groups in the standard option files, invoke my_print_defaults like this:

shell> my_print_defaults mysqlcheck client--user=myusername--password=secret--host=localhost

The output consists of options, one per line, in the form that they would be specified on the command line.

my_print_defaults supports the following options.

resolve_stack_dump resolves a numeric stack dump to symbols.

Invoke resolve_stack_dump like this:

shell> resolve_stack_dump [options] symbols_file [numeric_dump_file]

The symbols file should include the output from the nm --numeric-sort mysqld command. The numeric dump file should contain a numeric stack track from mysqld. If no numeric dump file is named on the command line, the stack trace is read from the standard input.

resolve_stack_dump supports the following options.

For most system errors, MySQL displays, in addition to an internal text message, the system error code in one of the following styles:

message ... (errno: #)message ... (Errcode: #)

You can find out what the error code means by examining the documentation for your system or by using the perror utility.

perror prints a description for a system error code or for a storage engine (table handler) error code.

Invoke perror like this:

shell> perror [options] errorcode ...

Example:

shell> perror 13 64OS error code  13:  Permission deniedOS error code  64:  Machine is not on the network

To obtain the error message for a MySQL Cluster error code, invoke perror with the --ndb option:

shell> perror --ndb errorcode

Note that the meaning of system error messages may be dependent on your operating system. A given error code may mean different things on different operating systems.

perror supports the following options.

The replace utility program changes strings in place in files or on the standard input.

Invoke replace in one of the following ways:

shell> replace from to [from to] ... -- file_name [file_name] ...shell> replace from to [from to] ... < file_name

from represents a string to look for and to represents its replacement. There can be one or more pairs of strings.

Use the -- option to indicate where the string-replacement list ends and the file names begin. In this case, any file named on the command line is modified in place, so you may want to make a copy of the original before converting it. replace prints a message indicating which of the input files it actually modifies.

If the -- option is not given, replace reads the standard input and writes to the standard output.

replace uses a finite state machine to match longer strings first. It can be used to swap strings. For example, the following command swaps a and b in the given files, file1 and file2:

shell> replace a b b a -- file1 file2 ...

The replace program is used by msql2mysql. See Section 4.7.1, "msql2mysql - Convert mSQL Programs for Use with MySQL".

replace supports the following options.

The resolveip utility resolves host names to IP addresses and vice versa.

Invoke resolveip like this:

shell> resolveip [options] {host_name|ip-addr} ...

resolveip supports the following options.