MySQL Administrative and Utility Programs

MySQLMySQLi Database

Let us look at the administrative and utility programs in MySQL and understand how they can be used −

ibd2sdi

It is a utility to extract serialized dictionary information (SDI) from InnoDB tablespace files. SDI data is present all persistent InnoDB tablespace files. ibd2sdi can be used at runtime or when the server is offline.

innochecksum

It prints the checksums for InnoDB files. It reads an InnoDB tablespace file, calculates the checksum for every page, compares the calculated checksum to the stored checksum, and reports mismatches, which show the damaged pages. It was originally developed to speed up the verification of the integrity of tablespace files after power outages but it can also be used after file copies

It can be invoked as follows −

shell> innochecksum [options] file_name

myisam_ftdump

It displays information about the FULLTEXT indexes in MyISAM tables. It reads the MyISAM index file directly, hence it must be run on the server host where the table is located. Before using myisam_ftdump, the user has to ensure that they issue a FLUSH TABLES statement first if the server is running.

It can be invoked as shown below −

shell > myisam_ftdump [options] tbl_name index_num

myisamchk

It gets information about the user’s database tables or checks, repairs, or optimizes them. It works with MyISAM tables, i.e. the tables that have .MYD and .MYI files for storing data and indexes. Using myisamchk with partitioned tables is not supported.

It can be invoked as shown below −

shell> myisamchk [options] tbl_name ...

myisamlog

It processes the contents of a MyISAM log file. To create a file of this sort, start the server with a --log-isam=log_file option.

It can be invoked as shown below −

shell> myisamlog [options] [file_name [tbl_name] ...]

myisampack

It compresses MyISAM tables. It works by compressing every column in the table separately. In general, myisampack packs the data file 40% to 70%.

It can be invoked as shown below −

shell> myisampack [options] file_name ...

mysqlbinlog

The server's binary log consists of files which contain “events” that help describe the modifications done to database contents. The server writes these files in binary format. To display the contents in text format, the mysqlbinlog utility is used.

It can be invoked as shown below −

shell> mysqlbinlog [options] log_file ...

mysqldumpslow

The MySQL slow query log contains information about queries which would take a long time to execute. This utility parses MySQL slow query log files and summarizes their contents.

It can be invoked as shown below −

shell> mysqldumpslow [options] [log_file ...]
raja
Published on 10-Mar-2021 17:36:35
Advertisements