Chapter 4 Overview of MySQL Utilities

Table of Contents

4.1 Binary Log Operations
4.2 Database Operations
4.3 General Operations
4.4 High Availability Operations
4.5 Server Operations
4.6 Specialized Operations

This chapter presents an brief overview of each of the available utilities. The utilities are grouped into sections based on the type of administrative function that they perform.

4.1 Binary Log Operations

These utilities are designed to perform operations on binary log files.

4.2 Database Operations

These utilities are those designed to work at the database-level. They include utilities that can used to manage databases on one or more servers.

  • mysqldbcompare

    • Compare databases on two servers or the same server

    • Compare definitions and data

    • Generate a difference report

    • Generate SQL transformation statements

  • mysqldbcopy

    • Copy databases between servers

    • Clone databases on the same server

    • Supports rename

  • mysqldbexport

    • Export metadata and/or data from one or more databases

    • Formats: SQL, CSV, TAB, Grid, Vertical

  • mysqldbimport

    • Import metadata and data from one or more files

    • Reads all formats from mysqldbexport

  • mysqldiff

    • Compare object definitions

    • Generate a difference report

4.3 General Operations

These utilities are those designed to perform general operations such as reporting and searching.

  • mysqldiskusage

    • Show disk usage for databases

    • Generate reports in SQL, CSV, TAB, Grid, Vertical

  • mysqlfrm

    • Reads .frm files, optionally in byte-by-byte diagnostic mode

    • Generates CREATE statements from table definition data

  • mysqlgrants

    • Displays grants per object.

    • Produce reports by user, user with grants, and GRANT statements.

  • mysqlindexcheck

    • Read indexes for one or more tables

    • Check for redundant and duplicate indexes

    • Generate reports in SQL, CSV, TAB, Grid, Vertical

  • mysqlmetagrep

    • Search metadata

    • Regexp, database search

    • Generate SQL statement for search query

  • mysqlprocgrep

    • Search process information

    • Generate SQL statement for search

    • Kill processes that match query

  • mysqluserclone

    • Clone a user account, to the same or different server

    • Show user grants

  • mysqluc

    • Command line client for running MySQL Utilities

    • Allows a persistent connection to a MySQL Server

    • Tab completion for utility names and options

    • Allows calling the commands with shorter names, such as using "serverinfo" instead of mysqlserverinfo

4.4 High Availability Operations

These utilities are those designed to support replication and high availability operations for MySQL servers.

  • mysqlfailover

    • Provides automatic failover on a replication topology

    • Uses Global Transaction Identifiers (GTID, MySQL Server 5.6.5+)

  • mysqlreplicate

    • Setup replication

    • Start from beginning, current, specific binlog, pos

  • mysqlrplms

    • Provides round-robin multi-source replication (a slave server continually cycles through multiple masters in order to store a consolidated data set)

    • Uses Global Transaction Identifiers (GTID, MySQL Server 5.6.9+)

  • mysqlrpladmin

    • Administers the replication topology

    • Allows recovery of the master

    • Commands include elect, failover, gtid, health, start, stop, and switchover

  • mysqlrplcheck

    • Check replication configuration

    • Tests binary logging on master

  • mysqlrplshow

    • Show slaves attached to master

    • Can search recursively

    • Show the replication topology as a graph or list

  • mysqlrplsync

    • Check data consistency between servers in a replicated setup

    • Uses Global Transaction Identifiers (GTID)

    • Requires MySQL Server 5.6.14 and higher

  • mysqlslavetrx

    • Skip multiple transaction on slaves

    • Uses Global Transaction Identifiers (GTID)

4.5 Server Operations

These utilities are used to perform server-wide operations.

  • mysqlserverclone

    • Start a new instance of a running server

  • mysqlserverinfo

    • Show server information

    • Can search for running servers on a host

    • Access online or offline servers

4.6 Specialized Operations

These utilities are designed to be used with a specific commercial extension. In this case, these utilities require the Audit Log Plugin.

  • mysqlauditadmin

    • Monitor the audit log

    • Copy, rotate, and configure the audit log

  • mysqlauditgrep

    • Search the audit log

    • Output results to different formats