Chapter 5 Manual Pages

Table of Contents

5.1 mysqlauditadmin — Allows users to perform maintenance actions on the audit log
5.2 mysqlauditgrep — Allows users to search the current or an archived audit log
5.3 mysqlbinlogmove — Binary log relocate utility
5.4 mysqlbinlogpurge — Binary log purge utility
5.5 mysqlbinlogrotate — Binary log rotate utility
5.6 mysqldbcompare — Compare Two Databases and Identify Differences
5.7 mysqldbcopy — Copy Database Objects Between Servers
5.8 mysqldbexport — Export Object Definitions or Data from a Database
5.9 mysqldbimport — Import Object Definitions or Data into a Database
5.10 mysqldiff — Identify Differences Among Database Objects
5.11 mysqldiskusage — Show Database Disk Usage
5.12 mysqlfailover — Automatic replication health monitoring and failover
5.13 mysqlfrm — File reader for .frm files.
5.14 mysqlgrants — Display grants by object
5.15 mysqlindexcheck — Identify Potentially Redundant Table Indexes
5.16 mysqlmetagrep — Search Database Object Definitions
5.17 mysqlprocgrep — Search Server Process Lists
5.18 mysqlreplicate — Set Up and Start Replication Between Two Servers
5.19 mysqlrplms — Set Up and Start Replication from a Slave to Multiple Masters
5.20 mysqlrpladmin — Administration utility for MySQL replication
5.21 mysqlrplcheck — Check Replication Prerequisites
5.22 mysqlrplshow — Show Slaves for Master Server
5.23 mysqlrplsync — Replication synchronization checker
5.24 mysqlserverclone — Clone Existing Server to Create New Server
5.25 mysqlserverinfo — Display Common Diagnostic Information from a Server
5.26 mysqlslavetrx — Slave transaction skip utility
5.27 mysqluc — Command line client for running MySQL Utilities
5.28 mysqluserclone — Clone Existing User to Create New User

This chapter includes the manual pages for each of the utilities. Each manual page is formatted similar to a typical Unix man page.

5.1 mysqlauditadmin — Allows users to perform maintenance actions on the audit log

This utility allows you to maintain the audit log including the ability to view and modify a subset of audit log control variables, display the audit log file status, perform on-demand rotation of the log file, and copy files to other locations. These features enable you to easily monitor the audit log file growth and control its rotation (automatically based on the defined file size threshold, or manually by a on-demand command).

Rotation refers to the action of replacing the current audit log file by a new one for continuous use, renaming (with a timestamp extension) and copying the previously used audit log file to a defined location for archival purposes.

The available actions include the following:

  • copy

    This command copies the audit log specified by --audit-log-name to the destination path specified by --copy-to. The --remote-login option can be used to copy log files from a remote location. Note: the destination path must be locally accessible by the current user.

  • policy

    The policy command is used to change the audit logging policy. The accepted values include the following, which are set using the --value option.

    Note

    The --server option is also required to execute this command.

    Starting from MySQL server 5.6.20 and 5.7.5, the value is read only for the audit_log_policy variable. MySQL server 5.7.9 introduced two new variables: audit_log_connection_policy and audit_log_statement_policy whose values are determined based on the presence and value of the audit_log_policy startup variable. See the MySQL reference manual for more information about how the policy variables are set. These changes are supported starting from MySQL Utilities 1.5.2.

    • ALL: log all events

    • NONE: log nothing

    • LOGINS: only log login events

    • QUERIES: only log query events

    • DEFAULT: sets the default log policy

  • rotate_on_size

    This command sets the file size threshold for automatic rotation of the audit log (the audit_log_rotate_on_size variable). The value is set using the --value option, and must be in the range (0, 4294967295). This command also requires the --server option to be specified. Note: if the variable is set with a value that is not a multiple of 4096, then it is truncated to the nearest multiple.

  • rotate

    This command is used to perform an on-demand audit log rotation, and only requires the --server option to be passed. Note: this command has no effect if the audit log file size is smaller than 4096, which is the minimum value allowed that is greater than 0 for the audit_log_rotate_on_size variable).

OPTIONS

mysqlauditadmin accepts the following command-line options:

  • --audit-log-name=AUDIT_LOG_FILE

    Full path and filename for the audit log file. Used by the --file-stats option, and the copy command.

  • --copy-to=COPY_DESTINATION

    The location to copy the specified audit log file. The path must be locally accessible for the current user.

  • --file-stats

    Display the audit log file statistics.

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --remote-login=REMOTE_LOGIN

    User name and host to be used for the remote login, for copying log files. It is defined using the following format: user:host or IP. The utility displays a prompt for the password.

  • --server=SERVER

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --show-options

    Display the audit log system variables.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --value=VALUE

    Value used to set variables based on the specified commands, such as policy and rotate_on_size.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

This utility can only be applied to servers with the audit log plugin enabled. And the audit log plugin is available as of MySQL Server versions 5.5.28 and 5.6.10.

This utility requires Python version 2.6 or higher, but does not support Python 3.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This allows the utility to use the my_print_defaults tools, which is required to read the login-path values from the login configuration file (.mylogin.cnf). This feature exists as of MySQL Server 5.6.6, see mysql_config_editor — MySQL Configuration Utility.

Changes to MySQL Enterprise Audit are not documented here, so your output might be different than the examples shown. For example, a new (or removed) MySQL Enterprise Audit option might affect the output.

LIMITATIONS

The --remote-login option is not supported on Microsoft Windows platforms. For Microsoft Windows, use UNC paths and perform a local copy operation, omitting the --remote-login option.

EXAMPLES

To display the audit log system variables, run the following command:

shell> mysqlauditadmin --show-options --server=root@localhost:3310

#
# Showing options after command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | NONE          |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

To perform a (manual) rotation of the audit log file, use the following command:

shell> mysqlauditadmin --server=root@localhost:3310 rotate

#
# Executing ROTATE command.
#

To display the audit log file statistics, run the following command:

shell> mysqlauditadmin --file-stats --audit-log-name=../SERVER/data/audit.log

  +------------------------------+--------+---------------------------+---------------------------+
  | File                         | Size   | Created                   | Last Modified             |
  +------------------------------+--------+---------------------------+---------------------------+
  | audit.log                    | 3258   | Wed Sep 26 11:07:43 2012  | Wed Sep 26 11:07:43 2012  |
  | audit.log.13486539046497235  | 47317  | Wed Sep 26 11:05:04 2012  | Wed Sep 26 11:05:04 2012  |
  +------------------------------+--------+---------------------------+---------------------------+

To change the audit log policy to log only query events, and show the system variables before and after the execution of the policy command, use the following command:

shell> mysqlauditadmin --show-options --server=root@localhost:3310 policy \
       --value=QUERIES

#
# Showing options before command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

#
# Executing POLICY command.
#

#
# Showing options after command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | NONE          |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

To change the audit log automatic file rotation size (audit_log_rotate_on_size) to 32535, and show the system variables before and after the execution of the rotate_on_size command, use the following command. (Notice that the value set is actually 28672 because the specified rotate_on_size value is truncated to a multiple of 4096):

shell> mysqlauditadmin --show-options --server=root@localhost:3310 rotate_on_size \
       --value=32535

#
# Showing options before command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

#
# Executing POLICY command.
#

#
# Showing options after command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | NONE          |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 28672         |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

To perform a copy of a audit log file to another location, use the following command:

shell> mysqlauditadmin --audit-log-name=../SERVER/data/audit.log.13486539046497235 \
       copy --copy-to=/BACKUP/Audit_Logs

To copy a audit log file from a remote server/location to the current location (a prompt is issued for the user password), use the following command:

shell> mysqlauditadmin --audit-log-name=audit.log.13486539046497235 \
       copy --remote-login=user:host --copy-to=.

PERMISSIONS REQUIRED

The user must have permissions to read the audit log file(s) on disk and write the file(s) to the remote location.

5.2 mysqlauditgrep — Allows users to search the current or an archived audit log

This utility allows you to search the current or archived audit logs permitting you to display data from the audit log file according to the defined search criterion. It also allows you to output the results in different formats, namely GRID (default), TAB, CSV, VERTICAL, and RAW (the original XML format).

This utility allows you to search and filter the returned audit log records by: users (--users), date and time ranges (--start-date and --end-date), SQL query types (--query-type), logged event and record types (--event-type), status (--status), and matching patterns (--pattern). Any of these search options can be combined and used together, with the retrieved records resulting from all options evaluated as an and condition (all must be true).

The --pattern supports two types of pattern matching: standard SQL, used with the SQL LIKE operator (SQL patterns), and standard REGEXP (POSIX regular expression patterns).

This utility always requires an audit log file. The AUDIT_LOG_FILE argument should contain the a full path and filename for the audit log file. If not specified, a notification concerning this requirement is displayed. If --format is specified without search parameters, all of the records of the audit log are displayed in the specified format. Thus, you can use this feature to view the audit log file in the supported formats.

The --file-stats option is not considered a search criteria and is used to display the file statistics of a specified audit log. Other search options are ignored when the --file-stats option is used, except the --format option, which formats the results accordingly.

To specify the format of the generated results, use one of the following values with the --format option:

  • GRID (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • CSV

    Display output in comma-separated values format.

  • VERTICAL

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

  • RAW

    Display output results in the original raw format of the audit log records, which is written in XML.

Standard SQL Pattern Matching

The simple patterns defined by the SQL standard enables users to use two characters with special meanings: % (percent) matches zero or more characters, and _ (underscore) matches exactly one arbitrary character. In standard SQL, these types of patterns are used with the LIKE comparison operator, and they are case-insensitive by default. This utility assumes that they are case-insensitive.

For example:

  • "audit%"

    Match any string that starts with "audit".

  • "%log%"

    Match any string containing the word "log".

  • "%_"

    Match any string consisting of one or more characters.

For documentation about the standard SQL pattern matching syntax, see Pattern Matching.

REGEXP Pattern Matching (POSIX)

Standard REGEXP patterns are more powerful than the simple patterns defined in the SQL standard. A regular expression is a string of ordinary and special characters specified to match other strings. Unlike SQL Patterns, REGEXP patterns are case-sensitive. The REGEXP syntax defines the following characters with special meaning:

  • .

    Match any character.

  • ^

    Match the beginning of a string.

  • $

    Match the end of a string.

  • \

    Match zero or more repetitions of the preceding regular expression.

  • +

    Match one or more repetitions of the preceding regular expression.

  • ?

    Match zero or one repetition of the preceding regular expression.

  • |

    Match either the regular expressions from the left or right of |.

  • []

    Indicates a set of characters to match.

    Note

    Special characters lose their special meaning inside sets. In particular, the caret symbol (^) acquires a different meaning if it is the first character of the set, matching the complementary set (i.e., all the characters that are not in the set are matched).

  • {m}

    Match m repetitions of the preceding regular expression.

  • {m,n}

    Match from m to n repetitions of the preceding regular expression.

  • ()

    Define a matching group, and matches the regular expression inside the parentheses.

For example:

  • "a\*"

    Match a sequence of zero or more a.

  • "a+"

    Match a sequence of one or more a.

  • "a?"

    Match zero or one a.

  • "ab|cd"

    Match ab or cd.

  • "[axy]"

    Match a, x or y.

  • "[a-f]"

    Match any character in the range a to f (that is, a, b, c, d, e, or f).

  • "[^axy]"

    Match any character except a, x or y.

  • "a{5}"

    Match exactly five copies of a.

  • "a{2,5}"

    Match from two to five copies of a.

  • "(abc)+"

    Match one or more repetitions of abc.

This is a brief overview of regular expressions that can be used to define this type of patterns. The full syntax is described in the Python "re" module docs, supporting the definition of much more complex pattern matching expression.

OPTIONS

mysqlauditgrep accepts the following command-line options:

  • --end-date=END_DATE

    End date/time to retrieve log entries until the specified date/time range. If not specified or the value is 0, all entries to the end of the log are displayed. Accepted formats: "yyyy-mm-ddThh:mm:ss" or "yyyy-mm-dd".

  • --event-type=EVENT_TYPE

    Comma-separated list of event types to search in all audit log records matching the specified types. Supported values are: Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time.

  • --file-stats

    Display the audit log file statistics.

  • --format=FORMAT, -f FORMAT

    Output format to display the resulting data. Supported format values: GRID (default), TAB, CSV, VERTICAL and RAW.

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --pattern=PATTERN, -e PATTERN

    Search pattern to retrieve all entries with at least one attribute value matching the specified pattern. By default the standard SQL LIKE patterns are used for matching. If the --regexp option is set, then REGEXP patterns must be specified for matching.

  • --query-type=QUERY_TYPE

    Comma-separated list of SQL statements/commands to search for and match. Supported values: CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE, SELECT, INSERT, UPDATE, DELETE, COMMIT, SHOW, SET, CALL, PREPARE, EXECUTE, DEALLOCATE.

  • --regexp, --basic-regexp, -G

    Indicates that pattern matching is performed using a regular expression REGEXP (from the Python re module). By default, the simple standard SQL LIKE patterns are used for matching. This affects how the value specified by the --pattern option is interpreted.

  • --start-date=START_DATE

    Starting date/time to retrieve log entries from the specified date/time range. If not specified or the value is 0, all entries from the start of the log are displayed. Accepted formats: yyyy-mm-ddThh:mm:ss or yyyy-mm-dd.

  • --status=STATUS

    Comma-separated list of status values or intervals to search for all audit log records with a matching status. Status values are non-negative integers (corresponding to MySQL error codes). Status intervals are closed (i.e., include both endpoints) and defined simply using a dash between its endpoints. For Example: 1051,1068-1075,1109,1146.

    The --status option is available as of MySQL Utilities 1.2.4 / 1.3.3.

  • --users=USERS, -u USERS

    Comma-separated list of user names, to search for their associated log entries. For example: "dan,jon,john,paul,philip,stefan".

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

This utility is available as of MySQL Utilities 1.2.0.

This utility can only be applied to servers with the audit log plugin enabled. And the audit log plugin is available as of MySQL Server versions 5.5.28 and 5.6.10.

This utility support both of the existing audit log file formats (old and new). The new audit log format is supported as of MySQL Utilities 1.4.3. See The Audit Log File, for more information about available file formats.

This utility requires the use of Python version 2.6 or higher, but does not support Python 3.

Single or double quote characters (respectively, ' or ") can be used around option values. In fact, quotes are required to set some options values correctly, such as values with whitespace. For example, to specify the event types Create DB and Drop DB for the --event-type option, the following syntax must be used: --event-type='Create DB,Drop DB' or --event-type="Create DB,Drop DB".

EXAMPLES

To display the audit log file statistics and output the results in CSV format, run the following command:

shell> mysqlauditgrep --file-stats --format=CSV /SERVER/data/audit.log

  #
  # Audit Log File Statistics:
  #
  File,Size,Created,Last Modified
  audit.log,9101,Thu Sep 27 13:33:11 2012,Thu Oct 11 17:40:35 2012

  #
  # Audit Log Startup Entries:
  #

  SERVER_ID,STARTUP_OPTIONS,NAME,TIMESTAMP,MYSQL_VERSION,OS_VERSION,VERSION
  1,/SERVER/sql/mysqld --defaults-file=/SERVER/my.cnf,Audit,2012-09-27T13:33:11,5.5.29-log,x86_64-Linux,1

To display the audit log entries of specific users, use the following command:

shell> mysqlauditgrep --users=tester1,tester2 /SERVER/data/audit.log

To display the audit log file statistics, run the following command:

shell> mysqlauditgrep --users=tester1,tester2 /SERVER/data/audit.log

  +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+
  | STATUS  | SERVER_ID  | NAME     | TIMESTAMP            | CONNECTION_ID  | HOST       | USER     | PRIV_USER  | IP         | SQLTEXT                          |
  +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+
  | 0       | 1          | Connect  | 2012-09-28T11:26:50  | 9              | localhost  | root     | tester1    | 127.0.0.1  | None                             |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | SET @@session.autocommit = OFF   |
  | 0       | 1          | Ping     | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | None                             |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | SHOW VARIABLES LIKE 'READ_ONLY'  |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | COMMIT                           |
  | 0       | 1          | Ping     | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | None                             |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | COMMIT                           |
  | 0       | 1          | Quit     | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | None                             |
  | 0       | 1          | Connect  | 2012-10-10T15:55:55  | 11             | localhost  | tester2  | root       | 127.0.0.1  | None                             |
  | 0       | 1          | Query    | 2012-10-10T15:55:55  | 11             | None       | tester2  | root       | None       | select @@version_comment limit 1 |
  | 0       | 1          | Query    | 2012-10-10T15:56:10  | 11             | None       | tester2  | root       | None       | show databases                   |
  | 1046    | 1          | Query    | 2012-10-10T15:57:26  | 11             | None       | tester2  | root       | None       | show tables test                 |
  | 1046    | 1          | Query    | 2012-10-10T15:57:36  | 11             | None       | tester2  | root       | None       | show tables test                 |
  | 0       | 1          | Query    | 2012-10-10T15:57:51  | 11             | None       | tester2  | root       | None       | show tables in test              |
  | 0       | 1          | Quit     | 2012-10-10T15:57:59  | 11             | None       | tester2  | root       | None       | None                             |
  | 0       | 1          | Connect  | 2012-10-10T17:35:42  | 12             | localhost  | tester2  | root       | 127.0.0.1  | None                             |
  | 0       | 1          | Query    | 2012-10-10T17:35:42  | 12             | None       | tester2  | root       | None       | select @@version_comment limit 1 |
  | 0       | 1          | Quit     | 2012-10-10T17:47:22  | 12             | None       | tester2  | root       | None       | None                             |
  +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+

To display the audit log entries for a specific date/time range, use the following command:

shell> mysqlauditgrep --start-date=2012-09-27T13:33:47 --end-date=2012-09-28 /SERVER/data/audit.log

  +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+
  | STATUS  | TIMESTAMP            | NAME   | CONNECTION_ID  | SQLTEXT                                                                   |
  +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+
  | 0       | 2012-09-27T13:33:47  | Ping   | 7              | None                                                                      |
  | 0       | 2012-09-27T13:33:47  | Query  | 7              | SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'  |
  | 0       | 2012-09-27T13:33:47  | Query  | 7              | COMMIT                                                                    |
  | 0       | 2012-09-27T13:34:48  | Quit   | 7              | None                                                                      |
  | 0       | 2012-09-27T13:34:48  | Quit   | 8              | None                                                                      |
  +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+

To display the audit log entries matching a specific SQL LIKE pattern, use the following command:

shell> mysqlauditgrep --pattern="% = ___"; /SERVER/data/audit.log

  +---------+----------------------+--------+---------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                         | CONNECTION_ID  |
  +---------+----------------------+--------+---------------------------------+----------------+
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 8              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 10             |
  +---------+----------------------+--------+---------------------------------+----------------+

To display the audit log entries matching a specific REGEXP pattern, use the following command:

shell> mysqlauditgrep --pattern=".* = ..." --regexp /SERVER/data/audit.log

  +---------+----------------------+--------+---------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                         | CONNECTION_ID  |
  +---------+----------------------+--------+---------------------------------+----------------+
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 8              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 10             |
  +---------+----------------------+--------+---------------------------------+----------------+

To display the audit log entries of specific query types, use the following command:

shell> mysqlauditgrep --query-type=show,SET /SERVER/data/audit.log

  +---------+----------------------+--------+-------------------------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                                         | CONNECTION_ID  |
  +---------+----------------------+--------+-------------------------------------------------+----------------+
  | 0       | 2012-09-27T13:33:39  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF                  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'datadir'                   | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'basedir'                   | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 8              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF                  | 8              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 8              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'basedir'                   | 8              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF                  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF                  | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@GLOBAL.audit_log_flush = ON               | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'audit_log_policy'          | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'audit_log_rotate_on_size'  | 10             |
  | 0       | 2012-10-10T15:56:10  | Query  | show databases                                  | 11             |
  | 1046    | 2012-10-10T15:57:26  | Query  | show tables test                                | 11             |
  | 1046    | 2012-10-10T15:57:36  | Query  | show tables test                                | 11             |
  | 0       | 2012-10-10T15:57:51  | Query  | show tables in test                             | 11             |
  +---------+----------------------+--------+-------------------------------------------------+----------------+

To display the audit log entries of specific event types, use the following command:

shell> mysqlauditgrep --event-type="Ping,Connect" /SERVER/data/audit.log

  +---------+----------+----------------------+----------------+------------+---------+------------+------------+
  | STATUS  | NAME     | TIMESTAMP            | CONNECTION_ID  | HOST       | USER    | PRIV_USER  | IP         |
  +---------+----------+----------------------+----------------+------------+---------+------------+------------+
  | 0       | Connect  | 2012-09-27T13:33:39  | 7              | localhost  | root    | root       | 127.0.0.1  |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Connect  | 2012-09-27T13:33:39  | 8              | localhost  | root    | root       | 127.0.0.1  |
  | 0       | Ping     | 2012-09-27T13:33:39  | 8              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 8              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:47  | 7              | None       | None    | None       | None       |
  | 0       | Connect  | 2012-09-28T11:26:50  | 9              | localhost  | root    | tester     | 127.0.0.1  |
  | 0       | Ping     | 2012-09-28T11:26:50  | 9              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 9              | None       | None    | None       | None       |
  | 0       | Connect  | 2012-09-28T11:26:50  | 10             | localhost  | root    | root       | 127.0.0.1  |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Connect  | 2012-10-10T15:55:55  | 11             | localhost  | tester  | root       | 127.0.0.1  |
  | 0       | Connect  | 2012-10-10T17:35:42  | 12             | localhost  | tester  | root       | 127.0.0.1  |
  +---------+----------+----------------------+----------------+------------+---------+------------+------------+

To display the audit log entries with a specific status, use the following command:

shell> mysqlauditgrep --status=1100-1199,1046 /SERVER/data/audit.log

  +---------+----------------------+--------+---------------------------------------------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                                                             | CONNECTION_ID  |
  +---------+----------------------+--------+---------------------------------------------------------------------+----------------+
  | 1046    | 2012-10-10T15:57:26  | Query  | show tables test                                                    | 11             |
  | 1046    | 2012-10-10T15:57:36  | Query  | show tables test                                                    | 11             |
  | 1146    | 2012-10-10T17:44:55  | Query  | select * from test.employees where salary > 500 and salary < 1000   | 12             |
  | 1046    | 2012-10-10T17:47:17  | Query  | select * from test_encoding where value = '<>"&'                    | 12             |
  +---------+----------------------+--------+---------------------------------------------------------------------+----------------+
Note

You can view all successful commands with --status=0, and all unsuccessful commands with --status=1-9999.

To display the audit log entries matching several search criteria, use the following command:

shell> mysqlauditgrep --users=root --start-date=0 --end-date=2012-10-10 --event-type=Query \
       --query-type=SET --status=0 --pattern="%audit_log%" /SERVER/data/audit.log

  +---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
  | STATUS  | SERVER_ID  | NAME   | TIMESTAMP            | CONNECTION_ID  | USER  | PRIV_USER  | SQLTEXT                            |
  +---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
  | 0       | 1          | Query  | 2012-09-28T11:26:50  | 10             | root  | root       | SET @@GLOBAL.audit_log_flush = ON  |
  +---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+

PERMISSIONS REQUIRED

The user must have permissions to read the audit log file(s) on disk.

5.3 mysqlbinlogmove — Binary log relocate utility

This utility allows binary logs to be relocated to a different location in a simple and easy way. In particular, it moves existing binary logs to a specified location and updates the necessary server files (i.e., binary log index files) with the new location.

From a practical point of view, the use of this utility is recommended before you change the binary log base directory to move all binary log files to the target location, avoiding errors on the server when started with the new --log-bin location. It is also useful to archive older binary log files to a different location, in order to save disk space in the current partition.

Note

In order to relocate all binary log files, the MySQL server must be stopped. This requirement is not needed if binary log files that are not in use are moved.

The user must provide the destination directory to move the binary log files as an argument and the server connection parameters with the --server option or the source location of the binary log files using the option --binlog-dir. When the --server option is used, the utility determines the binary logs base filename and index file location from the server (depending on its version) and all binary log files are moved except the ones currently in use (with the higher sequence number). In order to move all binary logs including those in use, the --binlog-dir option must be used, which requires the MySQL server to be stopped.

By default, the utility only moves binary log files. If the server is performing the role of a slave or if you want to move both binary and relay log files, you must use the --log-type option with the desired value.

When the server --server is used, binary logs are flushed at the end of the move operation to refresh the cache. Users can skip this step using the --skip-flush-binlogs option.

The utility always attempts to determine the necessary information (base filename, binary logs and index location) based on the available server's data or the default values. Nevertheless, custom values might be used and some variables might not be available for older server versions or simply the server connection might not provided. If custom filenames are used, the user can specify them using the options --bin-log-index, --bin-log-basename, --relay-log-index, and --relay-log-basename, respectively for binary log and relay log files.

By default, all of the binary log files found that are not in use are moved (except when the --server option is used). The --sequence option can be used to restrict the files to move based on their sequence number. It is also possible to filter the files to move based on their modification date using the --modified-before option.

The utility displays the list of binary files that are moved. Users can also use the --verbose option to see additional information when the utility executes (e.g., used values for server variables).

Note

This utility was added in MySQL Utilities 1.6.0.

OPTIONS

mysqlbinlogmove accepts the following command-line options:

  • --binlog-dir=binlog_dir

    Source directory (full path) for the binary log files to move.

  • --bin-log-basename=binlog_basename

    Basename for the binary log files. If not available, it is assumed to be any name ended with '-bin'.

  • --bin-log-index=binlog_index

    Location (full path) of the binary log index file. If not specified, it is assumed to be located in the binary log directory.

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --log-type=log_type

    Type of the binary log files to move, i.e. binary log or relay log files. Supported values: 'bin' for binary log files, 'relay' for relay log files, 'all' for both binary log and relay log files.

    Default = bin.

  • --modified-before=modified_before

    Specify the datetime or number of days to move binary log files with a modified date prior to the specified value. Accepts a datetime in the format yyyy-mm-ddThh:mm:ss or yyyy-mm-dd, or an non-negative integer indicating the number of elapsed days.

  • --relay-log-basename=relay_log_basename

    Basename for the relay log files. If not available, it is assumed to be any name ended with '-relay-bin'.

  • --relay-log-index=relay_log_index

    Location (full path) of the relay log index file. If not specified, it is assumed to be located in the binary log directory.

  • --sequence=sequence_number_list

    Comma-separated list of non-negative sequence integers or intervals to move binary files with a matching sequence number. Specified sequence number intervals are closed (i.e., include both endpoints) and defined simply using a dash between its endpoints. For Example: 3,5-12,16,21.

  • --server=server_connection

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --skip-flush-binlogs

    Skip the binary log flush operation to refresh server's internal information after moving the binary log files.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

By default, binary logs are flushed after moving the files when the --server option is used. In particular, FLUSH BINARY LOGS is executed after moving all binary log files and FLUSH RELAY LOGS after moving all relay log files. This flush operation is required to refresh the binary log data on the server, otherwise errors might occur or inconsistent information might be displayed regarding the moved files (without restarting the server). For example, when executing the following statements: SHOW BINLOG EVENTS and SHOW BINARY LOGS. Nevertheless, the flush operation also closes and reopens the binary log files. See FLUSH Syntax, for more information about the FLUSH statement. Recall that the --skip-flush-binlogs option can be used to skip the flush operation.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools, which is required to read the login-path values from the login configuration file (.mylogin.cnf).

LIMITATIONS

This utility does not support remote access to binary log files and must be executed on the local server.

EXAMPLES

Move available binary log files from a running server:

shell> mysqlbinlogmove --server=user:pass@localhost:3310 \
          /archive/binlog_dir
#
# Moving bin-log files...
# - server-bin.000001
# - server-bin.000002
# - server-bin.000003
# - server-bin.000004
# - server-bin.000005
#
# Flushing binary logs...
#
#...done.
#

Move all binary log files from a stopped server specifying the source binary log directory:

shell> mysqlbinlogmove --binlog-dir=/server/data \
          /new/binlog_dir
#
# Moving bin-log files...
# - server-bin.000001
# - server-bin.000002
# - server-bin.000003
# - server-bin.000004
# - server-bin.000005
# - server-bin.000006
#
#...done.
#

Move available relay log files from a running slave:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=relay /archive/slave/binlog_dir
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
# - slave-relay-bin.000011
# - slave-relay-bin.000012
# - slave-relay-bin.000013
# - slave-relay-bin.000014
# - slave-relay-bin.000015
# - slave-relay-bin.000016
#
# Flushing relay logs...
#
#...done.
#

Move available binary log and relay log files from a running slave skipping the flush step:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --skip-flush-binlogs \
          /archive/slave/binlog_dir
#
# Moving bin-log files...
# - slave-bin.000001
# - slave-bin.000002
# - slave-bin.000003
# - slave-bin.000004
# - slave-bin.000005
#
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
# - slave-relay-bin.000011
# - slave-relay-bin.000012
# - slave-relay-bin.000013
# - slave-relay-bin.000014
# - slave-relay-bin.000015
# - slave-relay-bin.000016
#
#...done.
#

Move available binary log files from a running slave matching the specified sequence numbers:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --sequence=2,4-7,11,13 \
          /archive/slave/binlog_dir
#
# Applying sequence filter to bin-log files...
#
# Moving bin-log files...
# - slave-bin.000002
# - slave-bin.000004
# - slave-bin.000005
# - slave-bin.000006
#
# Flushing binary logs...
#
#
# Applying sequence filter to relay-log files...
#
# Moving relay-log files...
# - slave-relay-bin.000002
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000011
# - slave-relay-bin.000013
#
# Flushing relay logs...
#
#...done.
#

Move available binary log files modified two days ago from a running slave:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --modified-before=2 \
          /archive/slave/binlog_dir
#
# Applying modified date filter to bin-log files...
#
# Moving bin-log files...
# - slave-bin.000001
# - slave-bin.000002
# - slave-bin.000003
#
# Flushing binary logs...
#
#
# Applying modified date filter to relay-log files...
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
#
# Flushing relay logs...
#
#...done.

Move available binary log files modified prior to the specified date from a running slave:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --modified-before=2014-08-31 \
          /archive/slave/binlog_dir
#
# Applying modified date filter to bin-log files...
#
# Moving bin-log files...
# - slave-bin.000001
# - slave-bin.000002
# - slave-bin.000003
#
# Flushing binary logs...
#
#
# Applying modified date filter to relay-log files...
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
#
# Flushing relay logs...
#
#...done.

PERMISSIONS REQUIRED

By default, the user account used to connect to the server must have permissions to flush the binary logs, more precisely the RELOAD privilege is required, except if the flush step is skipped.

Additionally, the user account used to execute the utility must have read and write access to the location of the binary log and index files as well as the destination directory to move the files.

5.4 mysqlbinlogpurge — Binary log purge utility

This utility enables you to safely purge (delete) binary logs by ensuring that any files which are in use or required by any of the slaves in a replication topology are not deleted. This is achieved by checking which binary logs have been read on each slave. This determines the minimal set of binary log files that can be purged.

Note

In order to determine which binary logs can be purged, mysqlbinlogpurge connects to the master. If the specified server is not the active master, mysqlbinlogpurge cannot determine which binary logs are still needed by the slaves.

You must provide the master's connection parameters with the --master option and each slave's connection parameters with the --slaves option. Alternatively, use the --discover-slaves-login option configured with the user name and password to connect to the slaves. In case the server is not a master, you must provide the connection parameters with the --server option.

The discover slaves option requires all slaves use the --report-host and --report-port server startup variables with the correct hostname and port. If these are missing or report the incorrect information, the slave may not be detected and thus not included in the operation of the utility. The discover slaves option ignores any slaves to which it cannot connect.

mysqlbinlogpurge attempts to determine the binary logs to purge by logging in to each server. If a slave is not actively participating in a replication topology, mysqlbinlogpurge does not purge any logs.

By default, mysqlbinlogpurge purges all the binary log files that are not in use. Use the --binlog option to override this behavior and configure the first binary log file to not purge.

mysqlbinlogpurge displays the list of binary log files that were purged. Use the --verbose option to see a list of the remaining available binary log files on the server and to display additional information when mysqlbinlogpurge executes, such as status of the I/O and SQL threads of each slave.

OPTIONS

mysqlbinlogpurge provides the following command-line options:

  • --version

    Show the program's version number.

  • --help

    Display the help message.

  • --server=server_connection

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl

    Specifies if the server connection requires SSL. If an encrypted connection cannot be established, the connection attempt fails. By default set to 0, indicating that SSL is not required.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL certificate authorities.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --binlog=binlog

    Binary log filename to not to purge. All the binary log files prior to the specified file are removed.

  • --dry-run

    Run mysqlbinlogpurge without purging any binary log files, instead displaying a list of the unused binary log files which would be purged.

  • --discover-slaves-login=slave-login

    Supply a user name and password, in the form user[:passwd] or login-path, used for discovering slaves and relay slaves in the replication topology. For example, --discover=joe:secret uses 'joe' as the user name and 'secret' as the password for attempting to log in to each discovered slave.

  • --slaves=slave connections

    Connection information for slave servers. List multiple slaves in a comma-separated list. The list is evaluated literally, where each server is considered a slave of the master listed. Thus, all servers specified in this option must be slaves of the current master.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --master=connection

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v is verbose, -vv is more verbose, -vvv is debug level.

NOTES

If the server specified using the --server option is a master server and there are slaves connected, mysqlbinlogpurge displays an error and does not purge the binary logs that match the criteria specified.

LIMITATIONS

mysqlbinlogpurge cannot verify slaves that are finds a slave which is not actively replicating from the master.

EXAMPLES

Purge all binary log files not in use from a master, specifying the slaves to check:

shell> mysqlbinlogpurge --master=root:root@localhost:3310 \
          --slaves=root:root@localhost:3311,root:root@localhost:3312,root:root@localhost:3313 \
          -vv
exec_util command=python -u ../scripts/mysqlbinlogpurge.py --master=root:root@localhost:3310  --slaves=root:root@localhost:3311,root:root@localhost:33
12,root:root@localhost:3313  -vv
# Checking user permission to purge binary logs...
#
# Master active binlog file: mysql-bin.000021
# Checking slave: localhost@3311
# I/O thread is currently reading: mysql-bin.000021
# File position of the I/O thread: 120
# Master binlog file with last event executed by the SQL thread: mysql-bin.000021
# I/O thread running: Yes
# SQL thread running: Yes
# Checking slave: localhost@3312
# I/O thread is currently reading: mysql-bin.000021
# File position of the I/O thread: 120
# Master binlog file with last event executed by the SQL thread: mysql-bin.000021
# I/O thread running: Yes
# SQL thread running: Yes
# Checking slave: localhost@3313
# I/O thread is currently reading: mysql-bin.000021
# File position of the I/O thread: 120
# Master binlog file with last event executed by the SQL thread: mysql-bin.000021
# I/O thread running: Yes
# SQL thread running: Yes
# Range of binlog files available: from mysql-bin.000016 to mysql-bin.000021
# Latest binlog file replicated by all slaves: mysql-bin.000020
# Latest not active binlog file: mysql-bin.000020
# Executing query PURGE BINARY LOGS TO 'mysql-bin.000021'
# Binlog file available: mysql-bin.000021
# Range of binlog files purged: from mysql-bin.000016 to mysql-bin.000020

Purge all binary log files not in use prior to a specific binary log file:

shell> mysqlbinlogpurge --master=root:root@localhost:3310 \
          --slaves=root:root@localhost:3311,root:root@localhost:3312,root:root@localhost:3313 \
          --binlog=mysql-bin.000027 -v
# Checking user permission to purge binary logs...
#
# Master active binlog file: mysql-bin.000031
# Checking slave: localhost@3311
# I/O thread is currently reading: mysql-bin.000031
# Checking slave: localhost@3312
# I/O thread is currently reading: mysql-bin.000031
# Checking slave: localhost@3313
# I/O thread is currently reading: mysql-bin.000031
# Range of binlog files available: from mysql-bin.000023 to mysql-bin.000031
# Latest binlog file replicated by all slaves: mysql-bin.000030
# Purging binary logs prior to 'mysql-bin.000027'
# Range of binlog files available: from mysql-bin.000027 to mysql-bin.000031
# Range of binlog files purged: from mysql-bin.000023 to mysql-bin.000026

Display a query statement you could use to manually purge all binary log files not in use from a server, without actually purging them by using the --dry-run option:

shell> mysqlbinlogpurge --server=root:root@localhost:3310 --dry-run
# To manually purge purge the binary logs Execute the following query:
PURGE BINARY LOGS TO 'mysql-bin.000004'

PERMISSIONS REQUIRED

By default, the user name you specified to connect to the server must have SUPER and REPLICATION SLAVE permissions to be able to purge the binary logs.

5.5 mysqlbinlogrotate — Binary log rotate utility

This utility rotates the binary log by closing the active binary log file and opening a new binary log file.

The user must provide the server connection parameters with the --server option.

The user can also use the --min-size option to conditionally rotate the active binary log file only if the file size exceeds the specified value in bytes.

OPTIONS

mysqlbinlogrotate accepts the following command-line options:

  • --version

    Shows the program's version number.

  • --help

    Displays the help message.

  • --server=server_connection

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --min-size=min-size

    Rotate the active binary log file only if the file size exceeds the specified value in bytes.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

NOTES

By default, the utility rotates only the active binary log file, but for MySQL Server versions prior to 5.5.3, other log files (i.e, error log, relay log, slow log) are also rotated.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

Rotate the active binary log file and display the new filename.

shell> mysqlbinlogrotate --server=root:root@localhost:3310 -v
# Checking user permission to rotate binary logs...
#
# Active binlog file: 'mysql-bin.000002' (size: 32054 bytes)
# The binlog file has been rotated.
# New active binlog file: 'mysql-bin.000003'

Rotate the active binary log file only if his size exceeds 1GB (1073741824 bytes) and display new filename.

shell> mysqlbinlogrotate --server=root:root@localhost:3310 \
          --min-size=1073741824 -v
# Checking user permission to rotate binary logs...
#
# Active binlog file: 'mysql-bin.000002' (size: 1610612736 bytes)
# The binlog file has been rotated.
# New active binlog file: 'mysql-bin.000003'

PERMISSIONS REQUIRED

By default, the user used to connect to the server must have permissions to rotate the binary logs, more precisely the SUPER and REPLICATION SLAVE privileges are required.

5.6 mysqldbcompare — Compare Two Databases and Identify Differences

This utility compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.

Use the notation db1:db2 to name two databases to compare, or, alternatively just db1 to compare two databases with the same name. The latter case is a convenience notation for comparing same-named databases on different servers.

The comparison may be run against two databases of different names on a single server by specifying only the --server1 option. The user can also connect to another server by specifying the --server2 option. In this case, db1 is taken from server1 and db2 from server2.

All databases between two servers can also be compared using the --all option. In this case, only the databases in common (with the same name) between the servers are successively compared. Therefore, no databases need to be specified but the --server1 and --server2 options are required. Users can skip the comparison of some of the databases using the --exclude option.

Note

The data must not be changed during the comparison. Unexpected errors may occur if data is changed during the comparison.

The objects considered in the database include tables, views, triggers, procedures, functions, and events. A count for each object type can be shown with the -vv option.

The check is performed using a series of steps called tests. By default, the utility stops on the first failed test, but you can specify the --run-all-tests option to cause the utility to run all tests regardless of their end state.

Note

Using --run-all-tests may produce expected cascade failures. For example, if the row counts differ among two tables being compared, the data consistency also fails.

The tests include the following:

  1. Check database definitions

    A database existence precondition check ensures that both databases exist. If they do not, no further processing is possible and the --run-all-tests option is ignored.

  2. Check existence of objects in both databases

    The test for objects in both databases identifies those objects missing from one or another database. The remaining tests apply only to those objects that appear in both databases. To skip this test, use the --skip-object-compare option. That can be useful when there are known missing objects among the databases.

  3. Compare object definitions

    The definitions (the CREATE statements) are compared and differences are presented. To skip this test, use the --skip-diff option. That can be useful when there are object name differences only that you want to ignore.

  4. Check table row counts

    This check ensures that both tables have the same number of rows. This does not ensure that the table data is consistent. It is merely a cursory check to indicate possible missing rows in one table or the other. The data consistency check identifies the missing rows. To skip this test, use the --skip-row-count option.

  5. Check table data consistency

    This check identifies both changed rows as well as missing rows from one or another of the tables in the databases. Changed rows are displayed as a diff-style report with the format chosen (GRID by default) and missing rows are also displayed using the format chosen. This check is divided in two steps: first the full table checksum is compared between the tables, then if this step fails (or is skipped) the algorithm to find rows differences is executed. To skip the preliminary checksum table step in this test, use the --skip-checksum-table option. To skip this full test, use the --skip-data-check option.

You may want to use the --skip-xxx options to run only one of the tests. This might be helpful when working to bring two databases into synchronization, to avoid running all of the tests repeatedly during the process.

Each test completes with one of the following states:

  • pass

    The test succeeded.

  • FAIL

    The test failed. Errors are displayed following the test state line.

  • SKIP

    The test was skipped due to a missing prerequisite or a skip option.

  • WARN

    The test encountered an unusual but not fatal error.

  • -

    The test is not applicable to this object.

To specify how to display diff-style output, use one of the following values with the --difftype option:

  • unified (default)

    Display unified format output.

  • context

    Display context format output.

  • differ

    Display differ-style format output.

  • sql

    Display SQL transformation statement output.

To specify how to display output for changed or missing rows, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

The --changes-for option controls the direction of the difference (by specifying the object to be transformed) in either the difference report (default) or the transformation report (designated with the --difftype=sql option). Consider the following command:

shell> mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql db1:dbx

The leftmost database (db1) exists on the server designated by the --server1 option (host1). The rightmost database (dbx) exists on the server designated by the --server2 option (host2).

  • --changes-for=server1: Produce output that shows how to make the definitions of objects on server1 like the definitions of the corresponding objects on server2.

  • --changes-for=server2: Produce output that shows how to make the definitions of objects on server2 like the definitions of the corresponding objects on server1.

The default direction is server1.

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects in the operation.

If the utility is to be run on a server that has binary logging enabled, and you do not want the comparison steps logged, use the --disable-binary-logging option.

OPTIONS

mysqldbcompare accepts the following command-line options:

  • --all, -a

    Compare all database in common (with the same name) between two servers.

    The --all option ignores the following databases: INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, mysql, and sys.

    Note

    The sys database is ignored as of Utilities 1.6.2.

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --changes-for=direction

    Specify the server to show transformations to match the other server. For example, to see the transformation for transforming object definitions on server1 to match the corresponding definitions on server2, use --changes-for=server1. Permitted values are server1 and server2. The default is server1.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --difftype=difftype, -ddifftype

    Specify the difference display format. Permitted format values are unified, context, differ, and sql. The default is unified.

  • --disable-binary-logging

    If binary logging is enabled, disable it during the operation to prevent comparison operations from being written to the binary log. Note: Disabling binary logging requires the SUPER privilege.

  • --exclude=exclude, -xexclude

    Exclude one or more databases from the operation using either a specific name such as db1 or a search pattern. Use this option multiple times to specify multiple exclusions. By default, patterns use database patterns such as LIKE. With the --regexp option, patterns use regular expressions for matching names.

    Note

    The utility attempts to determine if the pattern supplied has any special characters (such as an asterisks), which may indicate that the pattern could be a REGEXP pattern. If there are special, non-SQL LIKE pattern characters and the user has not specified the --regexp option, a warning is presented to suggest the user check the pattern for possible use with the --regexp option.

  • --format=format, -fformat

    Specify the display format for changed or missing rows. Permitted format values are grid, csv, tab, and vertical. The default is grid.

  • --compact

    Compacts the output by reducing the number of control lines that are displayed in the diff results. This option should be used together with one of the following difference types: unified or context. It is most effective when used with the unified difference type and the grid format.

  • --quiet, -q

    Do not print anything. Return only an exit code of success or failure.

  • --regexp, --basic-regexp, -G

    Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching.

  • --run-all-tests, -t

    Do not halt at the first difference found. Process all objects.

  • --server1=source

    Connection information for the first server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --server2=source

    Connection information for the second server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --show-reverse

    Produce a transformation report containing the SQL statements to conform the object definitions specified in reverse. For example, if --changes-for is set to server1, also generate the transformation for server2. Note: The reverse changes are annotated and marked as comments.

  • --skip-checksum-table

    Skip the CHECKSUM TABLE step in the data consistency check. Added in release-1.4.3.

  • --skip-data-check

    Skip the data consistency check.

  • --skip-diff

    Skip the object definition difference check.

  • --skip-object-compare

    Skip the object comparison check.

  • --skip-row-count

    Skip the row count check.

  • --span-key-size=number of bytes to use for key

    Change the size of the key used for compare table contents. A higher value can help to get more accurate results comparing large databases, but may slow the algorithm.

    Default value is 8.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

  • --use-indexes

    List the index to use. Use this option to select the index to use if the table has no primary key or it has more than one unique index without null columns. Use this option in the format: --use-indexes="table1.indexA[;table2.indexB;]"

  • --width=number

    Change the display width of the test report. The default is 75 characters.

NOTES

The login user must have the appropriate permissions to read all databases and tables listed.

For the --difftype option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --difftype=d specifies the differ type. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

If any database identifier specified as an argument contains special characters or is a reserved word, then it must be appropriately quoted with backticks (`). In turn, names quoted with backticks must also be quoted with single or double quotes depending on the operating system, i.e. (") in Windows or (') in non-Windows systems, in order for the utilities to read backtick quoted identifiers as a single argument. For example, to compare a database with the name weird`db.name with other:weird`db.name, the database pair must be specified using the following syntax (in non-Windows): '`weird``db.name`:`other:weird``db.name`'.

When comparing two databases of different names, the utility suppresses differences in the CREATE DATABASE statements when only the names differ. If any of the decorators differ, the differences in the statements is shown.

EXAMPLES

Use the following command to compare the emp1 and emp2 databases on the local server, and run all tests even if earlier tests fail:

shell> mysqldbcompare --server1=root@localhost emp1:emp2 --run-all-tests
# server1 on localhost: ... connected.
# Checking databases emp1 on server1 and emp2 on server2
#
# WARNING: Objects in server2:emp2 but not in server1:emp1:
#   TRIGGER: trg
# PROCEDURE: p1
#     TABLE: t1
#      VIEW: v1
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# ---------------------------------------------------------------------------
# FUNCTION  f1                                      pass    -       -
# TABLE     departments                             pass    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Data differences found among rows:
--- emp1.departments
+++ emp2.departments
@@ -1,4 +1,4 @@
 *************************       1. row *************************
    dept_no: d002
- dept_name: dunno
+ dept_name: Finance
 1 rows.

# Rows in emp1.departments not in emp2.departments
*************************       1. row *************************
   dept_no: d008
 dept_name: Research
1 rows.

# Rows in emp2.departments not in emp1.departments
*************************       1. row *************************
   dept_no: d100
 dept_name: stupid
1 rows.

# TABLE     dept_manager                            pass    pass    -
#           - Compare table checksum                                pass

# Database consistency check failed.
#
# ...done

Given: two databases with the same table layout. Data for each table contains:

mysql> select * from db1.t1;
+---+---------------+
| a | b             |
+---+---------------+
| 1 | Test 789      |
| 2 | Test 456      |
| 3 | Test 123      |
| 4 | New row - db1 |
+---+---------------+
4 rows in set (0.00 sec)

mysql> select * from db2.t1;
+---+---------------+
| a | b             |
+---+---------------+
| 1 | Test 123      |
| 2 | Test 456      |
| 3 | Test 789      |
| 5 | New row - db2 |
+---+---------------+
4 rows in set (0.00 sec)

To generate the SQL statements for data transformations to make db1.t1 the same as db2.t1, use the --changes-for=server1 option. We must also include the -a option to ensure that the data consistency test is run. The following command illustrates the options used and an excerpt from the results generated:

shell> mysqldbcompare --server1=root:root@localhost \
    --server2=root:root@localhost db1:db2 --changes-for=server1 -a \/
    --difftype=sql

[...]

#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
#-------------------------------------------------------------------------
# TABLE     t1                                      pass    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Transformation for --changes-for=server1:
#

# Data differences found among rows:
UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1';
UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3';
DELETE FROM db1.t1 WHERE a = '4';
INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2');


# Database consistency check failed.
#
# ...done

Similarly, when the same command is run with --changes-for=server2 and --difftype=sql, the following report is generated:

shell> mysqldbcompare --server1=root:root@localhost \
    --server2=root:root@localhost db1:db2 --changes-for=server2 -a \
    --difftype=sql

[...]

#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
#-------------------------------------------------------------------------
# TABLE     t1                                      pass    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Transformation for --changes-for=server2:
#

# Data differences found among rows:
UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1';
UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3';
DELETE FROM db2.t1 WHERE a = '5';
INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1');


# Database consistency check failed.
#
# ...done

With the --difftype=sql SQL generation option set, --show-reverse shows the object transformations in both directions. Here is an excerpt of the results:

shell> mysqldbcompare --server1=root:root@localhost \
          --server2=root:root@localhost db1:db2 --changes-for=server1 \
          --show-reverse -a --difftype=sql

[...]

#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     t1                                      pass    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Transformation for --changes-for=server1:
#

# Data differences found among rows:
UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1';
UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3';
DELETE FROM db1.t1 WHERE a = '4';
INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2');

#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1';
# UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3';
# DELETE FROM db2.t1 WHERE a = '5';
# INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1');


# Database consistency check failed.
#
# ...done

LIMITATIONS

The utility reads the primary key of each row into a data structure, which is then used to generate checksums for each row. The primary key and checksum are then sorted and compared to detect which rows differ. Due to this design, the utility may exhibit slower performance for very large tables (many rows) especially for tables with wide primary keys. Use of this utility with tables that have blob fields as part of the primary key is not recommended.

PERMISSIONS REQUIRED

The user must have the SELECT, CREATE TEMPORARY TABLES and INSERT privileges for the databases being compared on both connections. The user must also have SELECT privilege on the mysql database. If the binary log is enabled and the --disable-binary-logging option is used, the user must also have the SUPER privilege.

5.7 mysqldbcopy — Copy Database Objects Between Servers

This utility copies a database on a source server to a database on a destination server. If the source and destination servers are different, the database names can be the same or different. If the source and destination servers are the same, the database names must be different.

The utility accepts one or more database pairs on the command line. To name a database pair, use db_name:new_db_name syntax to specify the source and destination names explicitly. If the source and destination database names are the same, db_name can be used as shorthand for db_name:db_name.

By default, the operation copies all objects (tables, views, triggers, events, procedures, functions, and database-level grants) and data to the destination server. There are options to turn off copying any or all of the objects as well as not copying the data.

To exclude specific objects by name, use the --exclude option with a name in db.*obj* format, or you can supply a search pattern. For example, --exclude=db1.trig1 excludes the single trigger and --exclude=trig_ excludes all objects from all databases having a name that begins with trig and has a following character.

By default, the utility creates each table on the destination server using the same storage engine as the original table. To override this and specify the storage engine to use for all tables created on the destination server, use the --new-storage-engine option. If the destination server supports the new engine, all tables use that engine.

To specify the storage engine to use for tables for which the destination server does not support the original storage engine on the source server, use the --default-storage-engine option.

The --new-storage-engine option takes precedence over --default-storage-engine if both are given.

If the --new-storage-engine or --default-storage-engine option is given and the destination server does not support the specified storage engine, a warning is issued and the server's default storage engine setting is used instead.

By default, the operation uses a consistent snapshot to read the source databases. To change the locking mode, use the --locking option with a locking type value. Use a value of no-locks to turn off locking altogether or lock-all to use only table locks. The default value is snapshot. Additionally, the utility uses WRITE locks to lock the destination tables during the copy.

You can include replication statements for copying data among a master and slave or between slaves. The --rpl option permits you to select from the following replication statements to include in the export.

  • master

    Create and execute a CHANGE MASTER statement to make the destination server a slave of the server specified in the --source option. This executes the appropriate STOP and START slave statements. The STOP SLAVE statement is executed at the start of the copy and the CHANGE MASTER followed by the START SLAVE statements are executed after the copy.

  • slave

    Create and execute a CHANGE MASTER statement to make the destination server a slave connected to the same master as the server specified in the --source option. This executes the appropriate STOP and START slave statements. The STOP SLAVE statement is executed at the start of the copy and the CHANGE MASTER followed by the START SLAVE statements after the copy.

To include the replication user in the CHANGE MASTER statement, use the --rpl-user option to specify the user and password. If this option is omitted, the utility attempts to identify the replication user. In the event that there are multiple candidates or the user requires a password, the utility aborts with an error.

If you attempt to copy databases on a server with GTIDs enabled (GTID_MODE = ON), a warning is generated if the copy does not include all databases. This is because the GTID statements generated include the GTIDs for all databases and not only those databases in the export.

The utility also generates a warning if you copy databases on a GTID enabled server but use the --skip-gtid option.

To make the most use of GTIDs, you should copy all of the databases on the server with the --all option.

OPTIONS

mysqldbcopy accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --default-storage-engine=def_engine

    The engine to use for tables if the destination server does not support the original storage engine on the source server.

  • --destination=destination

    Connection information for the destination server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --exclude=exclude, -xexclude

    Exclude one or more objects from the operation using either a specific name such as db1.t1 or a search pattern. Use this option multiple times to specify multiple exclusions. By default, patterns use LIKE matching. With the --regexp option, patterns use REGEXP matching.

    This option does not apply to grants.

    Note

    The utility attempts to determine if the pattern supplied has any special characters (such as an asterisks), which may indicate that the pattern could be a REGEXP pattern. If there are special, non-SQL LIKE pattern characters and the user has not specified the --regexp option, a warning is presented to suggest the user check the pattern for possible use with the --regexp option.

  • --drop-first

    Drop each database to be copied if exists before copying anything into it. Without this option, an error occurs if you attempt to copy objects into an existing database.

    Note

    Before MySQL Utilities 1.4.2, this option was named --force.

  • --locking=locking

    Choose the lock type for the operation. Permitted lock values are no-locks (do not use any table locks), lock-all (use table locks but no transaction and no consistent read), and snapshot (consistent read using a single transaction). The default is snapshot.

  • --multiprocess

    Specify the number of processes to concurrently copy the specified databases. Special values: 0 (number of processes equal to the number of detected CPUs) and 1 (default - no concurrency). Multiprocessing works at the database level for Windows and at the table level for Non-Windows (POSIX) systems.

  • --new-storage-engine=new_engine

    The engine to use for all tables created on the destination server.

  • --not-null-blobs

    Allow conversion of blob fields marked as NOT NULL to NULL before copy then restore NOT NULL after the copy. May cause indexes to be rebuilt if the affected blob fields are used in indexes.

  • --quiet, -q

    Turn off all messages for quiet execution.

  • --regexp, --basic-regexp, -G

    Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching.

  • --rpl=dump_option, --replication=dump_option

    Include replication information. Permitted values are master (make destination a slave of the source server) and slave (make destination a slave of the same master as the source - only works if the source server is a slave).

  • --rpl-user=replication_user

    The user and password for the replication user requirement in the form: user[:password] or login-path. E.g. rpl:passwd Default = None.

  • l --skip-gtid

    Skip creation and execution of GTID statements during the copy operation.

  • --all

    Copy all of the databases on the server.

  • --skip=objects

    Specify objects to skip in the operation as a comma-separated list (no spaces). Permitted values are CREATE_DB, DATA, EVENTS, FUNCTIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.

  • --source=source

    Connection information for the source server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects in the operation.

On the source to copy all objects from the database, the user must have these privileges: SELECT for tables, SHOW VIEW for views, EVENT for events and TRIGGER for triggers. Additionally, the SELECT privilege is also required for the mysql database.

On the destination to copy all objects, the user must have these privileges: CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP if --drop-first option is used, SUPER when binary logging is enabled, CREATE VIEW for views, CREATE ROUTINE, EXECUTE for procedures and functions, EVENT for events, TRIGGER for triggers and GRANT OPTION to copy grants. The SUPER privilege might also be required for some objects (views, procedures, functions, events and triggers), depending on their DEFINER value.

Actual privileges required may differ from installation to installation depending on the security privileges present and whether the database contains certain objects such as views or events and whether binary logging is enabled.

The --new-storage-engine and --default-storage-engine options apply to all destination tables in the operation.

Some option combinations may result in errors during the operation. For example, eliminating tables but not views may result in an error a the view is copied.

The --rpl option is not valid for copying databases on the same server. If used in this manner, an error is generated.

When copying data and including the GTID commands, you may encounter an error similar to "GTID_PURGED can only be set when GTID_EXECUTED is empty". This occurs because the destination server is not in a clean replication state. To alleviate this problem, you can issue a "RESET MASTER" command on the destination prior to executing the copy.

Cloning databases that contain foreign key constraints does not change the constraint in the cloned table. For example, if table db1.t1 has a foreign key constraint on table db1.t2, when db1 is cloned to db2, table db2.t1 contains a foreign key constraint on db1.t2.

Copying tables that contain 0 in auto_increment columns is accomplished by enabling the NO_AUTO_VALUE_ON_ZERO SQL_MODE prior to the copy and disabling it after.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

If any database identifier specified as an argument contains special characters or is a reserved word, then it must be appropriately quoted with backticks (`). In turn, names quoted with backticks must also be quoted with single or double quotes depending on the operating system, i.e. (") in Windows or (') in non-Windows systems, in order for the utilities to read backtick quoted identifiers as a single argument. For example, to copy a database with the name weird`db.name with other:weird`db.name, the database pair must be specified using the following syntax (in non-Windows): '`weird``db.name`:`other:weird``db.name`'.

Keep in mind that you can only take advantage of multiprocessing if your system has multiple CPUs available for concurrent execution. Also note that multiprocessing is applied at a different level according to the operating system where the mysqldbcopy utility is executed (due to python limitations). In particular, it is applied at the database level for Windows (i.e., different databases are concurrently copied) and at the table level for Non-Windows (POSIX) systems (i.e., different tables within the same database are concurrently copied).

EXAMPLES

The following example demonstrates how to use the utility to copy a database named util_test to a new database named util_test_copy on the same server:

shell> mysqldbcopy \
  --source=root:pass@localhost:3310:/test123/mysql.sock \
  --destination=root:pass@localhost:3310:/test123/mysql.sock \
  util_test:util_test_copy
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database util_test renamed as util_test_copy
# Copying TABLE util_test.t1
# Copying table data.
# Copying TABLE util_test.t2
# Copying table data.
# Copying TABLE util_test.t3
# Copying table data.
# Copying TABLE util_test.t4
# Copying table data.
# Copying VIEW util_test.v1
# Copying TRIGGER util_test.trg
# Copying PROCEDURE util_test.p1
# Copying FUNCTION util_test.f1
# Copying EVENT util_test.e1
# Copying GRANTS from util_test
#...done.

If the database to be copied does not contain only InnoDB tables and you want to ensure data integrity of the copied data by locking the tables during the read step, add a --locking=lock-all option to the command:

shell> mysqldbcopy \
  --source=root:pass@localhost:3310:/test123/mysql.sock \
  --destination=root:pass@localhost:3310:/test123/mysql.sock \
  util_test:util_test_copy --locking=lock-all
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database util_test renamed as util_test_copy
# Copying TABLE util_test.t1
# Copying table data.
# Copying TABLE util_test.t2
# Copying table data.
# Copying TABLE util_test.t3
# Copying table data.
# Copying TABLE util_test.t4
# Copying table data.
# Copying VIEW util_test.v1
# Copying TRIGGER util_test.trg
# Copying PROCEDURE util_test.p1
# Copying FUNCTION util_test.f1
# Copying EVENT util_test.e1
# Copying GRANTS from util_test
#...done.

To copy one or more databases from a master to a slave, you can use the following command to copy the databases. Use the master as the source and the slave as the destination:

shell> mysqldbcopy --source=root@localhost:3310 \
  --destination=root@localhost:3311 test123 --rpl=master \
  --rpl-user=rpl
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Source on localhost: ... connected.
# Stopping slave
# Copying database test123
# Copying TABLE test123.t1
# Copying data for TABLE test123.t1
# Connecting to the current server as master
# Starting slave
#...done.

To copy a database from one slave to another attached to the same master, you can use the following command using the slave with the database to be copied as the source and the slave where the database needs to copied to as the destination:

shell> mysqldbcopy --source=root@localhost:3311 \
  --destination=root@localhost:3312 test123 --rpl=slave \
  --rpl-user=rpl

# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Source on localhost: ... connected.
# Stopping slave
# Copying database test123
# Copying TABLE test123.t1
# Copying data for TABLE test123.t1
# Connecting to the current server's master
# Starting slave
#...done.

LIMITATIONS

When copying tables with blob fields defined as NOT NULL and the --not-null-blobs option is not specified, the copy operation stops with an error suggesting how to manually remove the restriction.

However, you can use the --not-null-blobs option to automatically convert the fields. In this case, the utility prints a warning that the fields were temporarily altered on the destination to remove the NOT NULL option. The NOT NULL restriction is restored after the copy.

This special processing is needed because the copy attempts to use a bulk insert technique to copy the data in two passes; one to copy the data without blob field data, and another to update the rows with the blob data. Thus, having blob fields defined as NOT NULL fails on the first pass unless the restriction is removed before the data is copied.

PERMISSIONS REQUIRED

The user must have SELECT, SHOW VIEW, EVENT and TRIGGER privileges for the database(s) on the source server. On the destination server, the user must have the following privileges for the copied database(s): CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP if --drop-first option is used, and SUPER depending on the objects DEFINER value. When copying tables that include foreign keys, the user must also have the REFERENCES privilege.

5.8 mysqldbexport — Export Object Definitions or Data from a Database

This utility exports metadata (object definitions) or data or both from one or more databases. By default, the export includes only definitions.

mysqldbexport differs from mysqldump in that it can produce output in a variety of formats to make your data extraction/transport much easier. It permits you to export your data in the format most suitable to an external tool, another MySQL server, or other use without the need to reformat the data.

To exclude specific objects by name, use the --exclude option with a name in db.*obj* format, or you can supply a search pattern. For example, --exclude=db1.trig1 excludes the single trigger and --exclude=trig_ excludes all objects from all databases having a name that begins with trig and has a following character.

To skip objects by type, use the --skip option with a list of the objects to skip. This enables you to extract a particular set of objects, say, for exporting only events (by excluding all other types). Similarly, to skip creation of UPDATE statements for BLOB data, specify the --skip-blobs option.

To specify how to display output, use one of the following values with the --format option:

  • sql (default)

    Display output using SQL statements. For definitions, this consists of the appropriate CREATE and GRANT statements. For data, this is an INSERT statement (or bulk insert if the --bulk-insert option is specified).

  • grid

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

To specify how much data to display, use one of the following values with the --display option:

  • brief

    Display only the minimal columns for recreating the objects.

  • full

    Display the complete column list for recreating the objects.

  • names

    Display only the object names.

Note

The --display option is ignored when combined with the SQL-format output type.

To turn off the headers for csv or tab display format, specify the --no-headers option.

To turn off all feedback information, specify the --quiet option.

To write the data for individual tables to separate files, use the --file-per-table option. The name of each file is composed of the database and table names followed by the file format. For example, the following command produces files named db1.*table_name*.csv:

mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data

By default, the operation uses a consistent snapshot to read the source databases. To change the locking mode, use the --locking option with a locking type value. Use a value of no-locks to turn off locking altogether or lock-all to use only table locks. The default value is snapshot. Additionally, the utility uses WRITE locks to lock the destination tables during the copy.

You can include replication statements for exporting data among a master and slave or between slaves. The --rpl option permits you to select from the following replication statements to include in the export.

  • master

    Include the CHANGE MASTER statement to make the destination server a slave of the server specified in the --server option. This places the appropriate STOP and START slave statements in the export whereby the STOP SLAVE statement is placed at the start of the export and the CHANGE MASTER followed by the START SLAVE statements are placed after the export stream.

  • slave

    Include the CHANGE MASTER statement to make the destination server a slave connected to the same master as the server specified in the --server option. It only works if the current server is a slave. This places the appropriate STOP and START slave statements in the export whereby the STOP SLAVE statement is placed at the start of the export and the CHANGE MASTER followed by the START SLAVE statements are placed after the export stream.

  • both

    Include both the 'master' and 'slave' information for CHANGE MASTER statements for either spawning a new slave with the current server's master or using the current server as the master. All statements generated are labeled and commented to enable the user to choose which to include when imported.

To include the replication user in the CHANGE MASTER statement, use the --rpl-user option to specify the user and password. If this option is omitted, the utility attempts to identify the replication user. In the event that there are multiple candidates or the user requires a password, these statements are placed inside comments for the CHANGE MASTER statement.

You can also use the --comment-rpl option to place the replication statements inside comments for later examination.

If you specify the --rpl-file option, the utility writes the replication statements to the file specified instead of including them in the export stream.

Exporting Data with GTIDs

If you attempt to export databases on a server with GTIDs enabled (GTID_MODE = ON), a warning is generated if the export does not include all databases. This is because the GTID statements generated include the GTIDs for all databases and not only those databases in the export.

The utility also generates a warning if you export databases on a GTID enabled server but use the --skip-gtid option.

To make the most use of GTIDs and export/import, you should export all of the databases on the server with the --all option. This action generates an export file with all of the databases and the GTIDs executed to that point.

Importing this file on another server ensures that server has all of the data as well as all of the GTIDs recorded correctly in its logs.

OPTIONS

mysqldbexport accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --bulk-insert, -b

    Use bulk insert statements for data.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --comment-rpl

    Place the replication statements in comment statements. Valid only with the --rpl option.

  • --display=display, -ddisplay

    Control the number of columns shown. Permitted display values are brief (minimal columns for object creation), full* (all columns), and **names (only object names; not valid for --format=sql). The default is brief.

  • --exclude=exclude, -xexclude

    Exclude one or more objects from the operation using either a specific name such as db1.t1 or a search pattern. Use this option multiple times to specify multiple exclusions. By default, patterns use LIKE matching. With the --regexp option, all patterns specified use REGEXP matching.

    This option does not apply to grants.

    Note

    The utility attempts to determine if the pattern supplied has any special characters (such as an asterisks), which may indicate that the pattern could be a REGEXP pattern. If there are special, non-SQL LIKE pattern characters and the user has not specified the --regexp option, a warning is presented to suggest the user check the pattern for possible use with the --regexp option.

  • --export=export, -eexport

    Specify the export format. Permitted format values include the following. The default is definitions.

    Table 5.1 mysqldbexport Export Types

    Export TypeDefinition
    definitions (default)Only export the definitions (metadata) for the objects in the database list
    dataOnly export the table data for the tables in the database list
    bothExport both the definitions (metadata) and data

  • --file-per-table

    Write table data to separate files. This is valid only if the export output includes data (that is, if --export=data or --export=both are given). This option produces files named db_name.*tbl_name*.*format*. For example, a csv export of two tables named t1 and t2 in database d1, results in files named db1.t1.csv and db1.t2.csv. If table definitions are included in the export, they are written to stdout as usual.

  • --format=format, -fformat

    Specify the output display format. Permitted format values are sql, grid, tab, csv, and vertical. The default is sql.

  • --locking=locking

    Choose the lock type for the operation. Permitted lock values are no-locks (do not use any table locks), lock-all (use table locks but no transaction and no consistent read), and snapshot (consistent read using a single transaction). The default is snapshot.

  • --multiprocess

    Specify the number of processes to concurrently export the specified databases. Special values: 0 (number of processes equal to the number of detected CPUs) and 1 (default - no concurrency). Multiprocessing works at the database level for Windows and at the table level for Non-Windows (POSIX) systems.

  • --no-headers, -h

    Do not display column headers. This option applies only for csv and tab output.

  • --output-file

    Specify the path and filename to store the generated export output. By default the standard output is used (no file).

  • --quiet, -q

    Turn off all messages for quiet execution.

  • --regexp, --basic-regexp, -G

    Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching.

  • --rpl=rpl_mode, --replication=rpl_mode

    Include replication information. Permitted values are master (make destination a slave of the source server), slave (make destination a slave of the same master as the source - only works if the source server is a slave), and both (include the master and slave options where applicable).

  • --rpl-file=RPL_FILE, --replication-file=RPL_FILE

    The path and filename where the generated replication information should be written. Valid only with the --rpl option.

  • --rpl-user=replication_user

    The user and password for the replication user requirement, in the format: user[:password] or login-path. For example, rpl:passwd. The default is None.

  • --server=server

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --skip=skip-objects

    Specify objects to skip in the operation as a comma-separated list (no spaces). Permitted values are CREATE_DB, DATA, EVENTS, FUNCTIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.

  • --skip-blobs

    Do not export BLOB data.

  • --skip-gtid

    Skip creation of GTID_PURGED statements.

  • --all

    Generate an export file with all of the databases and the GTIDs executed to that point.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access (e.g., SELECT) all objects in the operation.

To export all objects from a source database, the user must have these privileges: SELECT and SHOW VIEW on the database as well as SELECT on the mysql database.

Actual privileges needed may differ from installation to installation depending on the security privileges present and whether the database contains certain objects such as views, events, and stored routines.

Some combinations of the options may result in errors when the export is imported later. For example, eliminating tables but not views may result in an error when a view is imported on another server.

For the --format, --export, and --display options, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

When exporting table data that contain a 0 in the auto_increment column, the export prints a warning that you must enable the NO_AUTO_VALUE_ON_ZERO SQL_MODE in order to import the data. A sample SQL statement is provided to illustrate how to set the mode.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This allows the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

If any database identifier specified as an argument contains special characters or is a reserved word, then it must be appropriately quoted with backticks (`). In turn, names quoted with backticks must also be quoted with single or double quotes depending on the operating system, i.e. (") in Windows or (') in non-Windows systems, in order for the utilities to read backtick quoted identifiers as a single argument. For example, to export a database with the name weird`db.name, it must be specified as argument using the following syntax (in non-Windows): '`weird``db.name`'.

Keep in mind that you can only take advantage of multiprocessing if your system has multiple CPUs available for concurrent execution. Also note that multiprocessing is applied at a different level according to the operating system where the mysqldbexport utility is executed (due to python limitations). In particular, it is applied at the database level for Windows (i.e., different databases are concurrently exported) and at the table level for Non-Windows (POSIX) systems (i.e., different tables within the same database are concurrently exported).

EXAMPLES

To export the definitions of the database dev from a MySQL server on the local host via port 3306, producing output consisting of CREATE statements, use this command:

shell> mysqldbexport --server=root:pass@localhost \
  --skip=GRANTS --export=DEFINITIONS util_test
# Source on localhost: ... connected.
# Exporting metadata from util_test
DROP DATABASE IF EXISTS `util_test`;
CREATE DATABASE `util_test`;
USE `util_test`;
# TABLE: `util_test`.`t1`
CREATE TABLE `t1` (
  `a` char(30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
# TABLE: util_test.t2
CREATE TABLE `t2` (
  `a` char(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# TABLE: util_test.t3
CREATE TABLE `t3` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` char(30) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
# TABLE: util_test.t4
CREATE TABLE `t4` (
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  KEY `ref_t3` (`c`),
  CONSTRAINT `ref_t3` FOREIGN KEY (`c`) REFERENCES `t3` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# VIEW: `util_test`.`v1`
[...]
#...done.

Similarly, to export the data of the database util_test, producing bulk insert statements, use this command:

shell> mysqldbexport --server=root:pass@localhost \
          --export=DATA --bulk-insert util_test
# Source on localhost: ... connected.
USE `util_test`;
# Exporting data from `util_test`
# Data for table `util_test`.`t1`:
INSERT INTO `util_test`.`t1` VALUES ('01 Test Basic database example'),
  ('02 Test Basic database example'),
  ('03 Test Basic database example'),
  ('04 Test Basic database example'),
  ('05 Test Basic database example'),
  ('06 Test Basic database example'),
  ('07 Test Basic database example');
# Data for table `util_test`.`t2`:
INSERT INTO `util_test`.`t2` VALUES ('11 Test Basic database example'),
  ('12 Test Basic database example'),
  ('13 Test Basic database example');
# Data for table `util_test`.`t3`:
INSERT INTO util_test.t3 VALUES (1, '14 test fkeys'),
  (2, '15 test fkeys'),
  (3, '16 test fkeys');
# Data for table `util_test`.`t4`:
INSERT INTO `util_test`.`t4` VALUES (3, 2);
#...done.

If the database to be exported does not contain only InnoDB tables and you want to ensure data integrity of the exported data by locking the tables during the read step, add a --locking=lock-all option to the command:

shell> mysqldbexport --server=root:pass@localhost \
  --export=DATA --bulk-insert util_test --locking=lock-all
# Source on localhost: ... connected.
USE `util_test`;
# Exporting data from `util_test`
# Data for table `util_test`.`t1`:
INSERT INTO `util_test`.`t1` VALUES ('01 Test Basic database example'),
  ('02 Test Basic database example'),
  ('03 Test Basic database example'),
  ('04 Test Basic database example'),
  ('05 Test Basic database example'),
  ('06 Test Basic database example'),
  ('07 Test Basic database example');
# Data for table `util_test`.`t2`:
INSERT INTO `util_test`.`t2` VALUES ('11 Test Basic database example'),
  ('12 Test Basic database example'),
  ('13 Test Basic database example');
# Data for table `util_test`.`t3`:
INSERT INTO `util_test`.`t3` VALUES (1, '14 test fkeys'),
  (2, '15 test fkeys'),
  (3, '16 test fkeys');
# Data for table `util_test`.`t4`:
INSERT INTO `util_test`.`t4` VALUES (3, 2);
#...done.

To export a database and include the replication commands to use the current server as the master (for example, to start a new slave using the current server as the master), use the following command:

shell> mysqldbexport --server=root@localhost:3311 util_test \
          --export=both --rpl-user=rpl:rpl --rpl=master -v
# Source on localhost: ... connected.
#
# Stopping slave
STOP SLAVE;
#
# Source on localhost: ... connected.
# Exporting metadata from `util_test`
DROP DATABASE IF EXISTS `util_test`;
CREATE DATABASE `util_test`;
USE `util_test`;
# TABLE: `util_test`.`t1`
CREATE TABLE `t1` (
  `a` char(30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
#...done.
# Source on localhost: ... connected.
USE `util_test`;
# Exporting data from `util_test`
# Data for table `util_test`.`t1`:
INSERT INTO `util_test`.`t1` VALUES ('01 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('02 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('03 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('04 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('05 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('06 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('07 Test Basic database example');
#...done.
#
# Connecting to the current server as master
CHANGE MASTER TO MASTER_HOST = 'localhost',
  MASTER_USER = 'rpl',
  MASTER_PASSWORD = 'rpl',
  MASTER_PORT = 3311,
  MASTER_LOG_FILE = 'clone-bin.000001' ,
  MASTER_LOG_POS = 106;
#
# Starting slave
START SLAVE;
#

Similarly, to export a database and include the replication commands to use the current server's master (for example, to start a new slave using the same the master), use the following command:

shell> mysqldbexport --server=root@localhost:3311 util_test \
          --export=both --rpl-user=rpl:rpl --rpl=slave -v
# Source on localhost: ... connected.
#
# Stopping slave
STOP SLAVE;
#
# Source on localhost: ... connected.
# Exporting metadata from util_test
DROP DATABASE IF EXISTS `util_test`;
CREATE DATABASE `util_test`;
USE `util_test`;
# TABLE: `util_test`.`t1`
CREATE TABLE `t1` (
  `a` char(30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
#...done.
# Source on localhost: ... connected.
USE `util_test`;
# Exporting data from `util_test`
# Data for table `util_test`.`t1`:
INSERT INTO `util_test`.`t1` VALUES ('01 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('02 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('03 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('04 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('05 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('06 Test Basic database example');
INSERT INTO `util_test`.`t1` VALUES ('07 Test Basic database example');
#...done.
#
# Connecting to the current server's master
CHANGE MASTER TO MASTER_HOST = 'localhost',
  MASTER_USER = 'rpl',
  MASTER_PASSWORD = 'rpl',
  MASTER_PORT = 3310,
  MASTER_LOG_FILE = 'clone-bin.000001' ,
  MASTER_LOG_POS = 1739;
#
# Starting slave
START SLAVE;
#

PERMISSIONS REQUIRED

The user account specified must have permission to read all databases listed including access to any objects exported. For example, if the export includes stored routines, the user specified must be able to access and view stored routines.

5.9 mysqldbimport — Import Object Definitions or Data into a Database

This utility imports metadata (object definitions), data, or both for one or more databases from one or more files.

If an object exists on the destination server with the same name as an imported object, it may be dropped first by using the --drop-first option.

To skip objects by type, use the --skip option with a list of the objects to skip. This enables you to extract a particular set of objects, say, for importing only events (by excluding all other types). Similarly, to skip creation of UPDATE statements for BLOB data, specify the --skip-blobs option.

To specify the input format, use one of the following values with the --format option. These correspond to the output formats of the mysqldbexport utility:

  • sql (default)

    Input consists of SQL statements. For definitions, this consists of the appropriate CREATE and GRANT statements. For data, this is an INSERT statement (or bulk insert if the --bulk-insert option is specified).

  • grid

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Input is formatted in comma-separated values format.

  • raw_csv

    Input is a simple CSV file containing uniform rows with values separated with commas. The file can contain a header (the first row) that lists the table columns. The option --table is required to use this format.

  • tab

    Input is formatted in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

To indicate that input in csv or tab format does not contain column headers, specify the --no-headers option.

To turn off all feedback information, specify the --quiet option.

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects in the operation. For details, see NOTES.

Changing Storage Engines

By default, the utility creates each table on the destination server using the same storage engine as the original table. To override this and specify the storage engine to use for all tables created on the destination server, use the --new-storage-engine option. If the destination server supports the new engine, all tables use that engine.

To specify the storage engine to use for tables for which the destination server does not support the original storage engine on the source server, use the --default-storage-engine option.

The --new-storage-engine option takes precedence over --default-storage-engine if both are given.

If the --new-storage-engine or --default-storage-engine option is given and the destination server does not support the specified storage engine, a warning is issued and the server's default storage engine setting is used instead.

Importing Data with GTIDs

If you attempt to import databases on a server with GTIDs enabled (GTID_MODE = ON), a warning is generated if the import file did not include the GTID statements generated by mysqldbexport.

The utility also generates a warning if you import databases on a server without GTIDs enabled and there are GTID statements present in the file. Use the --skip-gtid option to ignore the GTID statements.

To make the most use of GTIDs and export/import, you should export all of the databases on the server with the --all option. This action generates an export file with all of the databases and the GTIDs executed to that point. Importing this file on another server ensures that server has all of the data as well as all of the GTIDs recorded correctly in its logs.

OPTIONS

mysqldbimport accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --autocommit

    Enable autocommit for data import. By default, autocommit is off and data changes are only committed once at the end of each imported file.

  • --bulk-insert, -b

    Use bulk insert statements for data.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --default-storage-engine=def_engine

    The engine to use for tables if the destination server does not support the original storage engine on the source server.

  • --drop-first, -d

    Drop each database to be imported if exists before importing anything into it.

  • --dryrun

    Import the files and generate the statements but do not execute them. This is useful for testing input file validity.

  • --format=format, -fformat

    Specify the input format. Permitted format values are sql (default), grid, tab, csv, raw_csv, and vertical.

  • --import=import_type, -iimport_type

    Specify the import format. Permitted format values are:

    Table 5.2 mysqldbimport Import Types

    Import TypeDefinition
    definitions (default)Only import the definitions (metadata) for the objects in the database list
    dataOnly import the table data for the tables in the database list
    bothImport both the definitions (metadata) and data

    If you attempt to import objects into an existing database, the result depends on the import format. If the format is definitions or both, an error occurs unless --drop-first is given. If the format is data, imported table data is added to existing table data.

  • --max-bulk-insert

    Specify the maximum number of INSERT statements to bulk, by default 30000. This option is only used with --bulk-insert.

  • --multiprocess

    Specify the number of processes to concurrently import the specified files. Special values: 0 (number of processes equal to the number of detected CPUs) and 1 (default - no concurrency). Multiprocessing works at the files level for any operating systems.

  • --new-storage-engine=new_engine

    The engine to use for all tables created on the destination MySQL server.

  • --no-headers, -h

    Input does not contain column headers. This option only applies to the csv and tab file formats.

  • --quiet, -q

    Turn off all messages for quiet execution.

  • --server=server

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --skip=skip_objects

    Specify objects to skip in the operation as a comma-separated list (no spaces). Permitted values for this list are; CREATE_DB, DATA, EVENTS, FUNCTIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.

  • --skip-blobs

    Do not import BLOB data.

  • --skip-gtid

    Skip execution of GTID_PURGED statements.

  • --skip-rpl

    Do not execute replication commands.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --table=db,table

    Specify the table for importing. This option is required while using --format=raw_csv.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The login user must have the appropriate permissions to create new objects, access (read) the mysql database, and grant privileges. If a database to be imported already exists, the user must have read permission for it, which is needed to check the existence of objects in the database.

Actual privileges needed may differ from installation to installation depending on the security privileges present and whether the database contains certain objects such as views or events and whether binary logging is enabled.

Some combinations of the options may result in errors during the operation. For example, excluding tables but not views may result in an error when a view is imported.

The --new-storage-engine and --default-storage-engine options apply to all destination tables in the operation.

For the --format and --import options, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

When importing table data that contain a 0 in the auto_increment column, the import fails unless the NO_AUTO_VALUE_ON_ZERO SQL_MODE SQL_MODE is set. You can view the SQL_MODE with the show variables like 'sql_mode' command and change the SQL_MODE with the set sql_mode command.

When importing data and including the GTID commands, you may encounter an error similar to "GTID_PURGED can only be set when GTID_EXECUTED is empty". This occurs because the destination server is not in a clean replication state. To solve this problem, you can issue a "RESET MASTER" command on the destination prior to executing the import.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

Keep in mind that you can only take advantage of multiprocessing if your system has multiple CPUs available for concurrent execution. Also note that multiprocessing is applied at the file level for the mysqldbimport utility, which means that only different files can be concurrently imported.

EXAMPLES

To import the metadata from the util_test database to the server on the local host using a file in CSV format, use this command:

shell> mysqldbimport --server=root@localhost --import=definitions \
          --format=csv data.csv
# Source on localhost: ... connected.
# Importing definitions from data.csv.
#...done.

Similarly, to import the data from the util_test database to the server on the local host, importing the data using bulk insert statements, use this command:

shell> mysqldbimport --server=root@localhost --import=data \
          --bulk-insert --format=csv data.csv
# Source on localhost: ... connected.
# Importing data from data.csv.
#...done.

To import both data and definitions from the util_test database, importing the data using bulk insert statements from a file that contains SQL statements, use this command:

shell> mysqldbimport --server=root@localhost --import=both --bulk-insert --format=sql data.sql

# Source on localhost: ... connected.
# Importing definitions and data from data.sql.
#...done.

PERMISSIONS REQUIRED

You also need permissions to create the new data directory and write data to it including permissions to create all objects in the import stream such as views, events, and stored routines. Thus, actual permissions vary based on the contents of the import stream.

5.10 mysqldiff — Identify Differences Among Database Objects

This utility reads the definitions of objects and compares them using a diff-like method to determine whether they are the same. The utility displays the differences for objects that are not the same.

Use the notation db1:db2 to name two databases to compare, or, alternatively just db1 to compare two databases with the same name. The latter case is a convenience notation for comparing same-named databases on different servers.

The comparison may be executed against two databases of different names on a single server by specifying only the --server1 option. The user can also connect to another server by specifying the --server2 option. In this case, db1 is taken from server1 and db2 from server2.

When a database pair is specified, all objects in one database are compared to the corresponding objects in the other. Objects not appearing in either database produce an error.

To compare a specific pair of objects, add an object name to each database name using the db.obj format. For example, use the db1.obj1:db2.obj2 format to compare two named objects, or db1.obj1 to compare an object with the same name in databases with the same name. It is not permitted to mix a database name with an object name. For example, db1.obj1:db2 and db1:db2.obj2 are illegal formats.

The comparison may be run against a single server for comparing two databases of different names on the same server by specifying only the --server1 option. Alternatively, you can also connect to another server by specifying the --server2 option. In this case, the first object to compare is taken from server1 and the second from server2.

By default, the utility generates object differences as a difference report. However, you can generate a transformation report containing SQL statements for transforming the objects for conformity instead. Use the 'sql' value for the --difftype option to produce a listing that contains the appropriate ALTER commands to conform the object definitions for the object pairs specified. If a transformation cannot be formed, the utility reports the diff of the object along with a warning statement. See important limitations in the NOTES section.

To specify how to display the diff styled output, use one of the following values with the --difftype option:

  • unified (default)

    Display unified format output.

  • context

    Display context format output.

  • differ

    Display differ-style format output.

  • sql

    Display SQL transformation statement output.

The --changes-for option controls the direction of the difference (by specifying the object to be transformed) in either the difference report (default) or the transformation report (designated with the --difftype=sql option). Consider the following command:

shell> mysqldiff --server1=root@host1 --server2=root@host2 --difftype=sql \
          db1.table1:dbx.table3

The leftmost database (db1) exists on the server designated by the --server1 option (host1). The rightmost database (dbx) exists on the server designated by the --server2 option (host2).

  • --changes-for=server1: Produces output that shows how to make the definitions of objects on server1 like the definitions of the corresponding objects on server2.

  • --changes-for=server2: Produces output that shows how to make the definitions of objects on server2 like the definitions of the corresponding objects on server1.

The default direction is server1.

For the sql difference format, you can also see the reverse transformation by specifying the --show-reverse option.

The utility stops at the first occurrence of missing objects or when an object does not match. To override this behavior, specify the --force option to cause the utility to attempt to compare all objects listed as arguments.

OPTIONS

mysqldiff accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --changes-for=direction

    Specify the server to show transformations to match the other server. For example, to see the transformation for transforming object definitions on server1 to match the corresponding definitions on server2, use --changes-for=server1. Permitted values are server1 and server2. The default is server1.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --difftype=difftype, -ddifftype

    Specify the difference display format. Permitted format values are unified (default), context, differ, and sql.

  • --compact

    Compacts the output by reducing the control lines that are displayed in the diff results. This option should be used together with one of the following difference types: unified or context.

  • --force

    Do not halt at the first difference found. Process all objects to find all differences.

  • --quiet, -q

    Do not print anything. Return only an exit code of success or failure.

  • --server1=source

    Connection information for the first server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --server2=source

    Connection information for the second server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --show-reverse

    Produce a transformation report containing the SQL statements to conform the object definitions specified in reverse. For example, if --changes-for is set to server1, also generate the transformation for server2.

    Note

    The reverse changes are annotated and marked as comments.

  • --skip-table-options

    Ignore the differences between all table options, such as AUTO_INCREMENT, ENGINE, CHARSET, etc.). A warning is issued if the --skip-table-options option is used and table option differences are found.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

  • --width=number

    Change the display width of the test report. The default is 75 characters.

SQL TRANSFORMATION LIMITATIONS

The SQL transformation feature has these known limitations:

  • When tables with partition differences are encountered, the utility generates the ALTER TABLE statement for all other changes but prints a warning and omits the partition differences.

  • If the transformation detects table options in the source table (specified with the --changes-for option) that are not changed or do not exist in the target table, the utility generates the ALTER TABLE statement for all other changes but prints a warning and omits the table option differences.

  • Rename for events is not supported. This is because mysqldiff compares objects by name. In this case, depending on the direction of the diff, the event is identified as needing to be added or a DROP EVENT statement is generated.

  • Changes in the definer clause for events are not supported.

  • SQL extensions specific to MySQL Cluster are not supported.

NOTES

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects to be compared.

For the --difftype option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --difftype=d specifies the differ type. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

If any database object identifier specified as an argument contains special characters or is a reserved word, then it must be appropriately quoted with backticks (`). In turn, names quoted with backticks must also be quoted with single or double quotes depending on the operating system, i.e. (") in Windows or (') in non-Windows systems, in order for the utilities to read backtick quoted identifiers as a single argument. For example, to show the difference between table weird`table1 from database weird`db.name and table weird`table2 from database other:weird`db.name, the objects pair must be specified using the following syntax (in non-Windows): '`weird``db.name`.`weird``table1`:`other:weird``db.name`.`weird``table2`'.

EXAMPLES

To compare the employees and emp databases on the local server, use this command:

shell> mysqldiff --server1=root@localhost employees:emp1
# server1 on localhost: ... connected.
WARNING: Objects in server1:employees but not in server2:emp1:
  EVENT: e1
Compare failed. One or more differences found.

shell> mysqldiff --server1=root@localhost \
          employees.t1:emp1.t1 employees.t3:emp1.t3

# server1 on localhost: ... connected.
# Comparing employees.t1 to emp1.t1                                [PASS]
# server1 on localhost: ... connected.
# Comparing employees.t3 to emp1.t3                                [PASS]
Success. All objects are the same.

shell> mysqldiff --server1=root@localhost \
          employees.salaries:emp1.salaries --differ

# server1 on localhost: ... connected.
# Comparing employees.salaries to emp1.salaries                    [FAIL]
# Object definitions are not the same:
  CREATE TABLE `salaries` (
    `emp_no` int(11) NOT NULL,
    `salary` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`from_date`),
    KEY `emp_no` (`emp_no`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
?           ^^^^^
+ ) ENGINE=MyISAM DEFAULT CHARSET=latin1
?          ++ ^^^
Compare failed. One or more differences found.

The following examples show how to generate a transformation report. Assume the following object definitions:

Host1:

CREATE TABLE db1.table1 (num int, misc char(30));

Host2:

CREATE TABLE dbx.table3 (num int, notes char(30), misc char(55));

To generate a set of SQL statements that transform the definition of db1.table1 to dbx.table3, use this command:

shell> mysqldiff --server1=root@host1 --server2=root@host2 \
          --changes-for=server1 --difftype=sql \
          db1.table1:dbx.table3

# server1 on host1: ... connected.
# server2 on host2: ... connected.
# Comparing db1.table1 to dbx.table3                               [FAIL]
# Transformation statements:

ALTER TABLE db1.table1
  ADD COLUMN notes char(30) AFTER a,
  CHANGE COLUMN misc misc char(55);

Compare failed. One or more differences found.

To generate a set of SQL statements that transform the definition of dbx.table3 to db1.table1, use this command:

shell> mysqldiff --server1=root@host1 --server2=root@host2 \
      --changes-for=server2 --difftype=sql \
      db1.table1:dbx.table3

# server1 on host1: ... connected.
# server2 on host2: ... connected.
# Comparing db1.table1 to dbx.table3                               [FAIL]
# Transformation statements:

ALTER TABLE dbx.table3
  DROP COLUMN notes,
  CHANGE COLUMN misc misc char(30);

Compare failed. One or more differences found.

To generate a set of SQL statements that transform the definitions of dbx.table3 and db1.table1 in both directions, use this command:

shell> mysqldiff --server1=root@host1 --server2=root@host2 \
          --show-reverse --difftype=sql \
          db1.table1:dbx.table3

# server1 on host1: ... connected.
# server2 on host2: ... connected.
# Comparing db1.table1 to dbx.table3                               [FAIL]
# Transformation statements:

# --destination=server1:
ALTER TABLE db1.table1
  ADD COLUMN notes char(30) AFTER a,
  CHANGE COLUMN misc misc char(55);

# --destination=server2:
# ALTER TABLE dbx.table3
#   DROP COLUMN notes,
#   CHANGE COLUMN misc misc char(30);

Compare failed. One or more differences found.

PERMISSIONS REQUIRED

The user must have SELECT privileges for both objects on both servers as well as SELECT on the mysql database.

5.11 mysqldiskusage — Show Database Disk Usage

This utility displays disk space usage for one or more databases. The utility optionally displays disk usage for the binary log, slow query log, error log, general query log, relay log, and InnoDB tablespaces. The default is to only show database disk usage.

If the command-line lists no databases, the utility shows the disk space usage for all databases.

Sizes displayed without a unit indicator (such as MB) are in bytes.

The utility determines the location of the data directory by requesting it from the server. For a local server, the utility obtains size information directly from files in the data directory and InnoDB home directory. In this case, you must have file system access to read those directories. Disk space usage shown includes the sum of all storage engine- specific files such as the .MYI and .MYD files for MyISAM and the tablespace files for InnoDB.

If the file system read fails, or if the server is not local, the utility cannot determine exact file sizes. It is limited to information that can be obtained from the system tables, which therefore should be considered an estimate. For information read from the server, the account used to connect to the server must have the appropriate permissions to read any objects accessed during the operation.

If information requested requires file system access but is not available that way, the utility prints a message that the information is not accessible. This occurs, for example, if you request log usage but the server is not local and the log files cannot be examined directly.

To specify how to display output, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

To turn off the headers for grid, csv, or tab display format, specify the --no-headers option.

OPTIONS

mysqldiskusage accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --all, -a

    Display all disk usage. This includes usage for databases, logs, and InnoDB tablespaces.

  • --binlog, -b

    Display binary log usage.

  • --empty, -m

    Include empty databases.

  • --format=format, -fformat

    Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid.

  • --innodb, -i

    Display InnoDB tablespace usage. This includes information about the shared InnoDB tablespace as well as .idb files for InnoDB tables with their own tablespace.

  • --logs, -l

    Display general query log, error log, and slow query log usage.

  • --no-headers, -h

    Do not display column headers. This option applies only for grid, csv, and tab output.

  • --quiet, -q

    Suppress informational messages.

  • --relaylog, -r

    Display relay log usage.

  • --server=server

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

NOTES

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges for all objects accessed during the operation.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To show only the disk space usage for the employees and test databases in grid format (the default), use this command:

shell> mysqldiskusage --server=root@localhost employees test
# Source on localhost: ... connected.
# Database totals:
+------------+--------------+
| db_name    |       total  |
+------------+--------------+
| employees  | 205,979,648  |
| test       |       4,096  |
+------------+--------------+

Total database disk usage = 205,983,744 bytes or 196.00 MB

#...done.

To see all disk usage for the server in CSV format, use this command:

shell> mysqldiskusage --server=root@localhost --format=csv -a -vv
# Source on localhost: ... connected.
# Database totals:
db_name,db_dir_size,data_size,misc_files,total
test1,0,0,0,0
db3,0,0,0,0
db2,0,0,0,0
db1,0,0,0,0
backup_test,19410,1117,18293,19410
employees,242519463,205979648,242519463,448499111
mysql,867211,657669,191720,849389
t1,9849,1024,8825,9849
test,56162,4096,52066,56162
util_test_a,19625,2048,17577,19625
util_test_b,17347,0,17347,17347
util_test_c,19623,2048,17575,19623

Total database disk usage = 449,490,516 bytes or 428.00 MB

# Log information.
# The general_log is turned off on the server.
# The slow_query_log is turned off on the server.

# binary log information:
Current binary log file = ./mysql-bin.000076
log_file,size
/data/mysql-bin.000076,125
/data/mysql-bin.000077,125
/data/mysql-bin.000078,556
/data/mysql-bin.000079,168398223
/data/mysql-bin.index,76

Total size of binary logs = 168,399,105 bytes or 160.00 MB

# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
InnoDB_file,size,type,specification
/data/ib_logfile0,5242880,log file,
/data/ib_logfile1,5242880,log file,
/data/ibdata1,220200960,shared tablespace,ibdata1:210M
/data/ibdata2,10485760,shared tablespace,ibdata2:10M:autoextend
/data/employees/departments.ibd,114688,file tablespace,
/data/employees/dept_emp.ibd,30408704,file tablespace,
/data/employees/dept_manager.ibd,131072,file tablespace,
/data/employees/employees.ibd,23068672,file tablespace,
/data/employees/salaries.ibd,146800640,file tablespace,
/data/employees/titles.ibd,41943040,file tablespace,

Total size of InnoDB files = 494,125,056 bytes or 471.00 MB

#...done.

PERMISSIONS REQUIRED

The user must have permissions to read the data directory or use an administrator, super user (sudo), or an account with elevated privileges to obtain access to the data directory.

5.12 mysqlfailover — Automatic replication health monitoring and failover

This utility permits users to perform replication health monitoring and automatic failover on a replication topology consisting of a single master and its slaves. The utility is designed to run interactively or continuously refreshing the health information and checking the master status at periodic intervals. Its primary mission is to monitor the master for failure and when a failure occurs, execute failover to one of the slaves that is in a valid state. The utility accepts an optional list of slaves to be considered for the candidate slave.

This utility is designed to work exclusively for servers that support global transaction identifiers (GTIDs) and have gtid_mode=ON. MySQL server versions 5.6.5 and higher support GTIDs. See Replication with Global Transaction Identifiers for more information. Thus, this utility does not work with anonymous replication servers (binary log + position).

The user can specify the interval in seconds to use for detecting the master status and generating the health report using the --interval option. At each interval, the utility checks to see if the server is alive via a ping operation followed by a check of the connector to detect if the server is still reachable. The ping operation can be controlled with the --ping option (see below).

If the master is found to be offline or unreachable, the utility executes one of the following actions based on the --failover-mode option value. The available values are:

  • auto (default): Execute automatic failover to the list of candidates first and if no slaves are viable, continue to search the remaining slaves for a viable candidate. The command tests each candidate slave listed for the prerequisites. Once a candidate slave is elected, it is made a slave of each of the other slaves thereby collecting any transactions executed on other slaves but not the candidate. In this way, the candidate becomes the most up-to-date slave. If no slave is found to be a viable candidate, the utility generates an error and exit.

  • elect: This mode is the same as auto, except if no candidates specified in the list of candidate slaves are viable, then it does not check the remaining slaves, and instead generates an error and then exits. Use this option to force failover to one or more specific slaves using the --candidates option.

  • fail: This mode produces an error and does not failover when the master is detected as down or unreachable. This mode is used to provide periodic health monitoring without the failover action taken.

For all options that permit specifying multiple servers, the options require a comma-separated list of connection parameters in the following form (where the password, port, and socket are optional).:

*user*[:*passwd*]@*host*[:*port*][:*socket*] or
*login-path*[:*port*][:*socket*]

The utility permits users to discover slaves connected to the master. The discover slaves feature is run automatically on each interval. Furthermore, it is required that slaves use the --master-info-repository=TABLE startup setting.

The discover slaves option requires all slaves use the --report-host and --report-port server startup variables with the correct hostname and port. If these are missing or report the incorrect information, the slave may not be detected and thus not included in the operation of the utility. The discover slaves option ignores any slaves to which it cannot connect.

Note

If you have one or more slaves which do not report their hostname and port and should a failover event occur, those slaves are not included in the resulting topology. That is, they are not a slave of the new master. Be sure to check that all of your slaves are accounted for in the health report before relying on the utility for complete automatic failover.

The utility permits the user to specify an external script to execute before and after the switchover and failover commands. The user can specify these with the --exec-before and --exec-after options. The return code of the script is used to determine success. Each script must report 0 (success) to be considered successful. If a script returns a value other than 0, the result code is presented in an error message.

The utility also permits the user to specify a script to be used for detecting a downed master or an application-level event to trigger failover. This can be specified using the --exec-fail-check option. The return code for the script is used to invoke failover. A return code of 0 indicates failover should not take place. A return code other than 0 indicates failover should take place. This is checked at the start of each interval if a script is supplied. The timeout option is not used in this case and the script is run once at the start of each interval.

The utility permits the user to log all actions taken during the commands. The --log option requires a valid path and filename of the file to use for logging operations. The log is active only when this option is specified. The option --log-age specifies the age in days that log entries are kept. The default is seven (7) days. Older entries are automatically deleted from the log file (but only if the --log option is specified).

The format of the log file includes the date and time of the event, the level of the event (informational - INFO, warning - WARN, error - ERROR, critical failure - CRITICAL), and the message reported by the utility.

The interface provides a number of options for displaying additional information. You can choose to view the replication health report (default), or choose to view the list of GTIDs in use, the UUIDs in use, or view the log file contents if logging is enabled. Each of these reports is described below.

  • health Display the replication health of the topology. This report is the default view for the interface. By default, this includes the host name, port, role (MASTER or SLAVE) of the server, state of the server (UP = is connected, WARN = not connected but can ping, DOWN = not connected and cannot ping), the GTID_MODE, and health state.

    The master health state is based on the following: if GTID_MODE=ON, the server must have the binary log enabled, and a user must exist with the REPLICATE SLAVE privilege.

    The --seconds-behind option is used to detect when a slave is behind the master. It allows users to set a threshold for reporting purposes only. It does not apply to slave candidacy or selection during failover.

    The slave health state is based on the following: the IO_THREAD and SQL_THREADS must be running, it must be connected to the master, there are no errors, the slave delay for non-GTID enabled scenarios is not more than the threshold provided by the --max-position and the slave is reading the correct master log file, and slave delay is not more than the --seconds-behind threshold option.

    At each interval, if the discover slaves option was specified at startup and new slaves are discovered, the health report is refreshed.

  • gtid: Display the master's list of executed GTIDs, contents of the GTID variables; @@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED, and @@GLOBAL.GTID_OWNED. Thus, you can toggle through the four screens by pressing the 'G' key.

  • UUID: Display universally unique identifiers (UUIDs) for all servers.

  • Log: This option displays the contents of the log file, which only visible if the --log option is specified. This can be helpful to see when failover occurred, and which actions or messages were recorded at the time.

The user interface is designed to match the size of the terminal window in which it is run. A refresh option is provided to permit users to resize their terminal windows or refresh the display at any time. However, the interface automatically resizes to the terminal window on each interval.

The interface displays the name of the utility, the master's status including binary log file, position, and filters as well as the date and time of the next interval event.

The interface also permits the user to scroll up or down through a list longer than what the terminal window permits. When a long list is presented, the scroll options become enabled. The user can scroll the list up with the up arrow key and down with the down arrow key.

Use the --verbose option to see additional information in the health report and additional messages during failover.

MODES OF OPERATION

The utility supports two modes of operation. The default mode, running as a console, works as described above. An additional mode that permits you to run the utility as a daemon is provided for POSIX platforms.

When run as a daemon, the utility does not have interactivity. However, all events are written to the log file. You can control what is written to the log by using the --report-values option.

To run the utility as a daemon, use the --daemon option. There are four commands that can be used in --daemon option. These include:

  • start

    Starts the daemon. The --log option is required.

  • stop

    Stops the daemon. If you used the option --pidfile, the value must be the same when starting the daemon.

  • restart

    Restarts the daemon. If you used the option --pidfile, the value must be the same when starting the daemon.

  • nodetach

    Starts the daemon, but does not detach the process from the console. The --log option is required.

OPTIONS

mysqlfailover accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --candidates=candidate slave connections

    Connection information for candidate slave servers. Valid only with failover command. List multiple slaves in comma-separated list.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --connection-timeout=seconds

    Set the connection timeout for TCP and Unix socket connections for all master, slaves, and candidate slaves specified. This changes the connection timeout for the Connector/Python connection to the server. The default is 10 seconds as defined in the Connector/Python library. This setting may be helpful for installations with low network and server reliability.

  • --daemon=command

    Run as a daemon. The command can be start (start daemon), stop (stop daemon), restart (stop then start the daemon) or nodetach (start but do not detach the process). This option is only available for POSIX systems.

  • --discover-slaves-login=user:password

    At startup, query master for all registered slaves and use the user name and password specified to connect. Supply the user and password in the form user[:passwd] or login-path. For example, --discover=joe:secret uses 'joe' as the user and 'secret' as the password for each discovered slave.

  • --exec-after=script

    Name of script to execute after failover or switchover. Script name may include the path.

  • --exec-before=script

    Name of script to execute before failover or switchover. Script name may include the path.

  • --exec-fail-check=script

    Name of script to execute on each interval to invoke failover.

  • --exec-post-failover=script

    Name of script to execute after failover is complete and the utility has refreshed the health report.

  • --failover-mode=mode, -f mode

    Action to take when the master fails. 'auto' = automatically fail to best slave, 'elect' = fail to candidate list or if no candidate meets criteria fail, 'fail' = take no action and stop when master fails. Default = 'auto'.

  • --force

    Override the registration check on master for multiple instances of the console monitoring the same master. See notes.

  • --interval=seconds, -i seconds

    Interval in seconds for polling the master for failure and reporting health. Default = 15 seconds. Minimum is 5 seconds.

  • --log=log_file

    Specify a log file to use for logging messages

  • --log-age=days

    Specify maximum age of log entries in days. Entries older than this are purged on startup. Default = 7 days.

  • --master=connection

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --master-fail-retry=seconds

    Time in seconds to wait to determine if the master is down. The failover check is run again when the retry delay expires. Can be used to introduce a longer period between when master is detected as unavailable to declaring it down. This option is not used with --exec-fail-check option.

    Note

    This option was added in MySQL Utilities 1.6.4.

  • --max-position=position

    Used to detect slave delay. The maximum difference between the master's log position and the slave's reported read position of the master. A value greater than this means the slave is too far behind the master. Default = 0.

  • --pedantic, -p

    Used to stop failover if some inconsistencies are found, such as errant transactions on slaves or SQL thread errors, during server checks. By default, the utility only generates warnings if issues are found when checking a slave's status during failover, and it continues execution unless this option is specified.

  • --pidfile=pidfile

    Pidfile for running mysqlfailover as a daemon. This file contains the PID (process identifier), that uniquely identifies a process. It is needed to identify and control the process forked by mysqlfailover.

  • --ping=number

    The code uses three attempts to contact the server with the ping command as part of the detection algorithm to check to see if the master is alive. This option sets the number of seconds to wait between each ping attempt. The default --ping value is 3 seconds.

    Note

    On some platforms, this is the same as number of seconds to wait for ping to return.

  • --report-values=report_values

    Report values used in mysqlfailover running as a daemon. It can be health, gtid or uuid. Multiple values can be used separated by commas.

    • health

      Display the replication health of the topology.

    • gtid

      Display the master's list of executed GTIDs, contents of the GTID variables; @@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED and @@GLOBAL.GTID_OWNED.

    • uuid

      Display universally unique identifiers (UUIDs) for all servers.

    Default = health.

  • --rpl-user=:replication_user

    The user and password for the replication user requirement, in the form: user[:password] or login-path. E.g. rpl:passwd

    Default = None.

  • --script-threshold=return_code

    Value for external scripts to trigger aborting the operation if result is greater than or equal to the threshold.

    Default = None (no threshold checking).

  • --seconds-behind=seconds

    Used to detect slave delay (only for health reporting purposes). The maximum number of seconds behind the master permitted before slave is considered behind the master in the health report state. Default = 0.

  • --slaves=slave connections

    Connection information for slave servers. List multiple slaves in comma-separated list. The list is evaluated literally whereby each server is considered a slave to the master listed regardless if they are a slave of the master.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --timeout=seconds

    Maximum timeout in seconds to wait for each replication command to complete. For example, timeout for slave waiting to catch up to master.

    Default = 3.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The login user must have the appropriate permissions for the utility to check servers and monitor their status (e.g., SHOW SLAVE STATUS, SHOW MASTER STATUS). The user must also have permissions to execute the failover procedure (e.g., STOP SLAVE, START SLAVE, WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS, CHANGE MASTER TO ...). Lastly, the user must have the REPLICATE SLAVE privilege for slaves to connect to their master. The same permissions are required by the failover utility for master and slaves in order to run successfully. In particular, users connected to slaves, candidates and master require SUPER, GRANT OPTION, REPLICATION SLAVE, RELOAD, DROP, CREATE, INSERT and SELECT privileges.

The DROP, CREATE, INSERT and SELECT privileges are required to register the failover instance on the initial master or the new master (after a successful failover). Therefore, since any slave can become the new master, slaves and candidates also require those privileges. The utility checks permissions for the master, slaves, and candidates at startup.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

For example, if you set up replication using MASTER_HOST=ubuntu.net on the slave and later connect to the slave with mysqlrplcheck and have the master specified as --master=192.168.0.6 using the valid IP address for ubuntu.net, you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine.

Similarly, in order to avoid issues mixing local IP '127.0.0.1' with 'localhost', all the addresses '127.0.0.1' are internally converted to 'localhost' by the utility. Nevertheless, it is best to use the actual hostname of the master when connecting or setting up replication.

The utility checks to see if the slaves are using the option --master-info-repository=TABLE. If they are not, the utility stops with an error.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This allows the utility to use the my_print_defaults tool which is required to read the login-path values from the login configuration file (.mylogin.cnf).

At startup, the console attempts to register itself with the master. If another console is already registered, and the failover mode is auto or elect, the console is blocked from running failover. When a console quits, it unregisters itself from the master. If this process is broken, the user may override the registration check by using the --force option.

The console creates a special table in the mysql database that is used to keep track of which instance is communicating with the master. If you use the --force option, the console removes the rows in this table. The table is constructed with:

CREATE TABLE IF NOT EXISTS mysql.failover_console (host char(30), port char(10))

When the console starts, a row is inserted containing the hostname and port of the master. On startup, if a row matches these values, the console does not start. If you use the --force option, the row is deleted.

When running the utility using the --daemon=nodetach option, the --pidfile option can be omitted. It is ignored if used.

When using the external scripts, the following parameters are passed in the order shown.

Suppose you have a script run_before.sh and you specify that you want it executing before the failover is performed (using the --exec-before option). Further, let us assume the master MySQL Server is using port 3306 on the host ‘host1’ and the MySQL Server that becomes the new master is using port 3308 on host 'can_host2'. The script would therefore be invoked in the following manner.

% run_before.sh host1 3306 can_host2 3308

Table 5.3 External Script Parameters

MySQL Failover OptionParameters Passed to External Script
--exec-beforemaster host, master port, candidate host, candidate port
--exec-afternew master host, new master port
--exec-fail-checkmaster host, master port
--exec-post-failover (no errors during failover)old master host, old master port, new master host, new master port
--exec-post-failover (errors during failover)old master host, old master port

EXAMPLES

To launch the utility, you must specify at a minimum the --master option and either the --discover-slaves-login option or the --slaves option. The --discover-slaves-login option can be used in conjunction with the --slaves option to specify a list of known slaves (or slaves that do not report their host and IP) and to discover any other slaves connected to the master.

An example of the user interface and some of the report views are shown in the following examples.

Note

The "GTID Executed Set" displays the first GTID listed in the SHOW MASTER STATUS view. If there are multiple GTIDs listed, the utility displays [...] to indicate there are additional GTIDs to view. You can view the complete list of GTIDs on the GTID display screens.

The default interface displays the replication health report like the following. In this example the log file is enabled. A sample startup command is shown below:

shell> mysqlfailover --master=root@localhost:3331 --discover-slaves-login=root --log=log.txt

MySQL Replication Monitor and Failover Utility
Failover Mode = auto     Next Interval = Mon Mar 19 15:56:03 2012

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql-bin.000001  571

GTID Executed Set
2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...]

Replication Health Status
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3331  | MASTER  | UP     | ON         | OK      |
| localhost  | 3332  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3333  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3334  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries

Press Q to exit the utility, R to refresh the current display, and H returns to the replication health report.

Press the G key to show a GTID report similar to the following. The first page shown is the master's executed GTID set:

MySQL Replication Monitor and Failover Utility
Failover Mode = auto     Next Interval = Mon Mar 19 15:59:33 2012

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql-bin.000001  571

GTID Executed Set
2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...]

Master GTID Executed Set
+-------------------------------------------+
| gtid                                      |
+-------------------------------------------+
| 2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7  |
| 5503D37E-2DB2-11E2-A781-8077D4C14B33:1-3  |
+-------------------------------------------+

Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll

Continuing to press G cycles through the three GTID lists.

If the list is longer than the screen permits as shown in the example above, the scroll up and down help is also shown. In this case, press the down arrow key to scroll down.

Press U to view the list of UUIDs used in the topology, for example:

MySQL Replication Monitor and Failover Utility
Failover Mode = auto     Next Interval = Mon Mar 19 16:02:34 2012

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql-bin.000001  571

GTID Executed Set
2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...]

UUIDs
+------------+-------+---------+---------------------------------------+
| host       | port  | role    | uuid                                  |
+------------+-------+---------+---------------------------------------+
| localhost  | 3331  | MASTER  | 55c65a00-71fd-11e1-9f80-ac64ef85c961  |
| localhost  | 3332  | SLAVE   | 5dd30888-71fd-11e1-9f80-dc242138b7ec  |
| localhost  | 3333  | SLAVE   | 65ccbb38-71fd-11e1-9f80-bda8146bdb0a  |
| localhost  | 3334  | SLAVE   | 6dd6abf4-71fd-11e1-9f80-d406a0117519  |
+------------+-------+---------+---------------------------------------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries

If, once the master is detected as down and failover mode is auto or elect and there are viable candidate slaves, the failover feature engages automatically and the user sees the failover messages appear. When failover is complete, the interface returns to monitoring replication health after 5 seconds. The following shows an example of failover occurring:

Failover starting...
# Candidate slave localhost:3332 will become the new master.
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at localhost:3332

Failover console will restart in 5 seconds.

After the failover event, the new topology is shown in the replication health report:

MySQL Replication Monitor and Failover Utility
Failover Mode = auto     Next Interval = Mon Mar 19 16:05:12 2012

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql-bin.000001  1117

GTID Executed Set
2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...]

UUIDs
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3332  | MASTER  | UP     | ON         | OK      |
| localhost  | 3333  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3334  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries

Pressing L with the --log option specified causes the interface to show the entries in the log file, such as:

MySQL Replication Monitor and Failover Utility
Failover Mode = auto     Next Interval = Mon Mar 19 16:06:13 2012

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql-bin.000001  1117

GTID Executed Set
2A67DE00-2DA1-11E2-A711-00764F2BE90F:1-7 [...]

Log File
+-------------------------+----------------------------------------- ... --+
| Date                    | Entry                                    ...   |
+-------------------------+----------------------------------------- ... --+
| 2012-03-19 15:55:33 PM  | INFO Failover console started.           ...   |
| 2012-03-19 15:55:33 PM  | INFO Failover mode = auto.               ...   |
| 2012-03-19 15:55:33 PM  | INFO Getting health for master: localhos ...   |
| 2012-03-19 15:55:33 PM  | INFO Master status: binlog: mysql-bin.00 ...   |
+-------------------------+----------------------------------------- ... --+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll\

LIMITATIONS

When the --master-fail-retry option is used, and the value specified exceeds the value of the --interval option, it is possible for an interval event to be delayed if the master retry check is still processing. In this case, should the master not be down, the next interval occurs immediately after the check of the master is complete.

PERMISSIONS REQUIRED

The user must have permissions to monitor the servers on the topology and configure replication to successfully perform the failover operation. Additional permissions are also required to register and unregister the running mysqlfailover instance on the master and slaves. Specifically, the login user must have the following privileges: SUPER, GRANT OPTION, REPLICATION SLAVE, RELOAD, DROP, CREATE, INSERT, and SELECT. (See Privileges Provided by MySQL, for more information about these.)

The referred permissions are required for the login users used for all servers (master, slaves, and candidates).

5.13 mysqlfrm — File reader for .frm files.

The mysqlfrm utility is designed as a recovery tool that reads .frm files and produces equivalent CREATE statements from the table definition data found in the file. In most cases, the generated CREATE statement is usable for recreating the table on another server, or for extended diagnostics. However, some features are not saved in the .frm files and therefore are omitted. The exclusions include but are not limited to:

  • foreign key constraints

  • auto increment number sequences

The mysqlfrm utility has two modes of operation. The default mode is designed to spawn an instance of an installed server by referencing the base directory using the --basedir option, or by connecting to the server with the --server option. The process does not alter the original .frm file(s). This mode also requires the --port option to specify a port to use for the spawned server. It must be different than the port for the installed server and no other server must be using the port. The spawned server is shutdown and all temporary files removed after the .frm files are read.

A diagnostic mode is available by using the --diagnostic option. This switches the utility to read the .frm files byte-by-byte to recover as much information as possible. The diagnostic mode has additional limitations in that it cannot decipher character set or collation values without using an existing server installation specified with either the --server or --basedir option. This can also affect the size of the columns if the table uses multibyte characters. Use this mode when the default mode cannot read the file, or if a MySQL server is not installed on the host.

To read .frm files, list each file as a separate argument for the utility as shown in the following examples. You must specify the path for each .frm file you want to read or supply a path to a directory and all of the .frm files in that directory to be read.

You can specify the database name to be used in the resulting CREATE statement by adding the name of the database followed by a colon to the .frm filename. For example, oltp:t1.frm uses 'oltp' for the database name in the CREATE statement. The optional database name can also be used with paths. For example, /home/me/oltp:t1.frm uses 'oltp' as the database name. If you leave off the optional database name and include a path, the last folder is the database name. For example /home/me/data1/t1.frm uses 'data1' as the database name. If you do not want to use the last folder as the database name, simply specify the colon like this: /home/me/data1/:t1.frm. In this case, the database is omitted from the CREATE statement.

OPTIONS

  • --help

    show the program's help page

  • --license

    Display license information and exit.

  • --basedir=basedir

    The base directory for the server installed. Use this or --server for the default mode.

  • --diagnostic

    Turn on diagnostic mode to read .frm files byte-by-byte and generate best-effort CREATE statement.

  • --new-storage-engine=engine

    Set the ENGINE= option for all .frm files read.

  • --port=port

    The port to use for the spawned server in the default mode. Must be a free port. Required for default mode.

  • --server=server

    Connection information for a server. Use this option or --basedir for the default mode. If provided with the diagnostic mode, the storage engine and character set information are validated against this server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --show-stats, -s

    Show file statistics and general table information for each .frm file read.

  • --start-timeout=timeout_in_seconds

    Number of seconds to wait for spawned server to start. The default is 10 seconds.

  • --user

    Execute the spawned server using this user account. Permits the execution of the utility as one user but the spawned server as another. Required if running the utility as the root user (e.g. su or sudo).

  • --quiet

    Turn off all messages for quiet execution except CREATE statements and errors.

  • --verbose, -v

    Control how much information is displayed. For example, -v = verbose, -vv = more verbose, -vvv = debug

  • --version

    Show program's version number and exit

NOTES

Tables with certain storage engines cannot be read in the default mode. These include PARTITION, PERFORMANCE_SCHEMA. You must read these with the --diagnostic mode.

Use the --diagnostic mode for tables that fail to open correctly in the default mode or if there is no server installed on the host.

To change the storage engine in the CREATE statement generated for all .frm files read, use the --new-storage-engine option

To turn off all messages except the CREATE statement and warnings or errors, use the --quiet option.

Use the --show-stats option to see file statistics for each .frm file.

If you need to run the utility with elevated privileges, use the --user option to execute the spawned server using a normal user account.

If you encounter connection or similar errors when running in default mode, re-run the command with the --verbose option and view the output from the spawned server and repair any errors in launching the server. If mysqlfrm fails in the middle, you may need to manually shutdown the server on the port specified with --port.

EXAMPLES

The following example reads a single .frm file in the default mode from the current working directory using the server installed in /usr/local/bin/mysql and port 3333 for the spawned server. Notice the use of the db:table.frm format for specifying the database name for the table. The database name appears to the left of ':' and the .frm name to the right. In this case, we have database = test1 and table = city, so the CREATE statement reads CREATE TABLE test1.city.

shell> mysqlfrm --basedir=/usr/local/bin/mysql test1:city.frm --port=3333
# Starting the spawned server on port 3333 ... done.
# Reading .frm files
#
# Reading the city.frm file.
#
# CREATE statement for city.frm:
#

CREATE TABLE `test1`.`city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#...done.

The following demonstrates reading multiple .frm files in the default mode using a running server. The .frm files are located in different folders. Notice the use of the database name option for each of the files. The t1 file was given the database name temp1 since that is the folder in which it resides, t2 was given db1 since that was specified in the path, and t3 was not given a database name since we used the ':' without providing a database name.

shell> mysqlfrm --server=root:pass@localhost:3306 /mysql/data/temp1/t1.frm \
          /mysql/data/temp2/db1:t2.frm --port=3310
# Starting the spawned server on port 3333 ... done.
# Reading .frm files
#
#
# Reading the t1.frm file.
#
# CREATE statement for ./mysql-test/std_data/frm_files/t1.frm:
#

CREATE TABLE `temp1`.`t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

# Reading the t2.frm file.
#
# CREATE statement for ./mysql-test/std_data/frm_files/t2.frm:
#

CREATE TABLE `db1`.`t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

#
# Reading the t3.frm file.
#
# CREATE statement for ./mysql-test/std_data/frm_files/t3.frm:
#

CREATE TABLE `t3` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

#...done.

The following demonstrates running the utility in diagnostic mode to read all of the .frm files in a directory.

shell> mysqlfrm --diagnostic /mysql/data/sakila
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /mysql/data/sakila/city.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(150) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY `PRIMARY` (`city_id`),
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=InnoDB;

#...done.

PERMISSIONS REQUIRED

The permissions for using mysqlfrm vary and depend entirely on how you use it. If you use the utility to read .frm files in a protected folder like the example above (in either mode), you must have the ability to run the spawned server with privileges that allow you to read the protected files. For example, you could use a user account that has root-level privileges.

If you use the utility with a server connection, the user you use to connect must have the ability to read system variables at a minimum including read access to the mysql database.

Note

You should never use the root user to spawn the server nor should you use the mysql user when spawning the server or running the utility.

5.14 mysqlgrants — Display grants by object

Managing privileges can be a challenge. Sometimes all a DBA needs to know is which users have access to a given list of objects such as a list of databases, tables, etc. This utility allows DBAs to see which users have what level of access for each object listed. Objects supported include databases, tables, functions, and procedures. The utility follows the grant hierarchy within MySQL displaying global- and object-level access GRANT statements.

Note

This utility was added in MySQL Utilities 1.6.0.

The utility allows the users to choose among three reports: users, user_grants and raw.

  • users

    displays a list of users who have access to the list of objects

  • user_grants

    displays a list of users sorted by object including their access level (privileges)

  • raw

    display the GRANT statements that define the user's privileges

The utility also provides an optional --privileges option that permits users to specify a list of privileges that form the minimal set for access. The list of privileges forms a filter such that a user must have all of the privileges specified for a specific object.

Note

It is possible that the combination of specified privileges can form an invalid set. In such cases, the utility ignores the errant privilege. For example, specifying the SELECT privilege for a routine causes the utility to exclude it from the filter check.

OPTIONS

mysqlgrants accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --inherit-level=level

    Specifies the inheritance level of the GRANT operations. This parameter has three options; global, database, and object. The default value is global.

    • global: (default) indicates grants shown at the global level, such as "GRANT ... ON *.*". All grants are shown.

    • database: indicates grants shown at the database level, such as "GRANT ... ON db1.*". Global level grants are not shown.

    • object: indicates grants shown at the object level, such as "GRANT ... ON db1.tbl1". Database and global level grants are not shown.

    This option was added in MySQL Utilities 1.6.2.

  • --license

    Display license information and exit.

  • --privileges=list of required privileges

    Minimum set of privileges that a user must have for any given object.

  • --server=source

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --show=output_type

    Type of report. Options include users, user_grants and raw.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

To use the users value in the --show option, you must specify at least one privilege using the --privileges option.

If you specify some privileges on the --privileges option that are not valid for all the specified objects, any that do not apply are not included in the list. For example, the SELECT privilege is ignored for stored routines and the EXECUTE privilege is ignored for tables but both are taken into account for databases.

EXAMPLES

Check the grantees and respective privileges over different object types: databases, tables, procedures and functions.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=user_grants util_test util_test.t3 util_test.t2 \
          util_test.t1 util_test.p1 util_test.f1

# DATABASE `util_test`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : EXECUTE, GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : EXECUTE, SELECT, UPDATE
# - 'priv_test_user3'@'%' : ALTER ROUTINE, DELETE, DROP, EXECUTE, TRIGGER, UPDATE

# TABLE `util_test`.`t1`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : ALL PRIVILEGES, GRANT OPTION
# - 'priv_test_user3'@'%' : DELETE, DROP, TRIGGER, UPDATE

# TABLE `util_test`.`t2`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : SELECT, UPDATE
# - 'priv_test_user3'@'%' : DELETE, DROP, TRIGGER, UPDATE

# TABLE `util_test`.`t3`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : SELECT, UPDATE
# - 'priv_test_user3'@'%' : DELETE, DROP, SELECT, TRIGGER, UPDATE

# ROUTINE `util_test`.`f1`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : EXECUTE, GRANT OPTION
# - 'priv_test_user2'@'%' : ALL PRIVILEGES, GRANT OPTION
# - 'priv_test_user3'@'%' : ALL PRIVILEGES

# ROUTINE `util_test`.`p1`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : EXECUTE, GRANT OPTION
# - 'priv_test_user2'@'%' : EXECUTE
# - 'priv_test_user3'@'%' : ALL PRIVILEGES, GRANT OPTION
#...done.

Show the grantees and respective SQL grant statements over a list of objects.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=raw util_test util_test.t3 util_test.t2 \
          util_test.t1 util_test.p1 util_test.f1

# DATABASE `util_test`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'

# TABLE `util_test`.`t1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `util_test`.`t1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'

# TABLE `util_test`.`t2`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'

# TABLE `util_test`.`t3`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT SELECT ON `util_test`.`t3` TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'

# ROUTINE `util_test`.`f1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
# - For 'priv_test_user2'@'%'
GRANT ALTER ROUTINE ON FUNCTION `util_test`.`f1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'

# ROUTINE `util_test`.`p1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO 'priv_test_user3'@'%' WITH GRANT OPTION
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
#...done.

Show only the users that have all privileges over a set of specified objects and the respective SQL grant statements. Notice that while some grantees do not explicitly have the ALL PRIVILEGES grant over a given object, they are still shown as a result of having the set of privileges that is equivalent to ALL PRIVILEGES for the given object type.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=raw  --privileges=ALL util_test util_test.t3 util_test.t2 \
          util_test.t1 util_test.p1 util_test.f1

# DATABASE `util_test`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'

# TABLE `util_test`.`t1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user2'@'%'
GRANT INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `util_test`.`t1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'

# TABLE `util_test`.`t2`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'

# TABLE `util_test`.`t3`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'

# ROUTINE `util_test`.`f1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user2'@'%'
GRANT ALTER ROUTINE ON FUNCTION `util_test`.`f1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'

# ROUTINE `util_test`.`p1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user3'@'%'
GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO 'priv_test_user3'@'%' WITH GRANT OPTION
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
#...done.

Show just the list of users with some specific privileges over a set of objects.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=users  --privileges=SELECT,INSERT,EXECUTE \
          util_test util_test.t3 util_test.t2 util_test.t1 util_test.p1 util_test.f1

# WARNING: EXECUTE does not apply to tables and will be ignored for: `util_test`.`t2`, `util_test`.`t3` and `util_test`.`t1`.
# WARNING: INSERT and SELECT do not apply to routines and will be ignored for: `util_test`.`f1` and `util_test`.`p1`.

# DATABASE `util_test`:

# TABLE `util_test`.`t1`:
# - 'priv_test_user2'@'%'

# TABLE `util_test`.`t2`:

# TABLE `util_test`.`t3`:

# ROUTINE `util_test`.`f1`:
# - 'priv_test_user'@'%', 'priv_test_user2'@'%'

# ROUTINE `util_test`.`p1`:
# - 'priv_test_user'@'%', 'priv_test_user2'@'%', 'priv_test_user3'@'%'
#...done.

The following command shows all of the grants for users that have access to any object in the db1 database, by passing in the --inherit-level option:

shell> mysqlgrants --server=localhost1 db1.* --inherit-level=object --show raw
# Source on localhost: ... connected.

# TABLE `db1`.`tbl1`:
# - For 'joe'@'host1'
GRANT INSERT ON `db1`.`tbl1` TO 'joe'@'host1'
#...done.

The following command shows all of the grants for users that have access to the db1 database, by passing in the --inherit-level option:

shell> mysqlgrants --server=localhost1 db1.* --inherit-level=database --show-raw
# Source on localhost: ... connected.

# TABLE `db1`.`tbl1`:
# - For 'joe'@'host1'
GRANT INSERT ON `db1`.`tbl1` TO 'joe'@'host1'
# - For 'sally'@'host2'
GRANT SELECT ON `db1`.* TO 'sally'@'host2'
#...done.

PRIVILEGES REQUIRED

This utility requires the SELECT privilege on the mysql database.

5.15 mysqlindexcheck — Identify Potentially Redundant Table Indexes

This utility reads the indexes for one or more tables and identifies duplicate and potentially redundant indexes.

To check all tables in a database, only specify the database name. To check a specific table, name the table in db.table format. It is possible to mix database and table names.

You can scan tables in any database except the internal databases mysql, INFORMATION_SCHEMA, and performance_schema.

Depending on the index type, the utility applies the following rules to compare indexes (designated as idx_a and idx_b):

  • BTREE

    idx_b is redundant to idx_a if and only if all the columns from idx_b are a prefix of idx_a. Order and uniqueness count.

  • HASH

    idx_a and idx_b are redundant if they are duplicates, i.e. if and only if they contain the same columns in the same order.

  • SPATIAL

    idx_a and idx_b are duplicates if and only if they contain the same column (only one column is permitted).

  • FULLTEXT

    idx_b is redundant to idx_a if and only if all columns in idx_b are included in idx_a. Order does not count.

To see DROP statements drop redundant indexes, specify the --show-drops option. To examine the existing indexes, use the --verbose option, which prints the equivalent CREATE INDEX (or ALTER TABLE) for primary keys.

To display the best or worst non-primary key indexes for each table, use the --best or --worst option. This causes the output to show the best or worst indexes from tables with 10 or more rows. By default, each option shows five indexes. To override that, provide an integer value for the option.

To change the format of the index lists displayed for the --show-indexes, --best, and --worst options, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • sql

    Print SQL statements rather than a list.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

Note

The --best and --worst lists cannot be printed as SQL statements.

OPTIONS

mysqlindexcheck accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --best[=N]

    If --stats is given, limit index statistics to the best N indexes. The default value of N is 5 if omitted.

  • --format=index_format, -findex_format

    Specify the index list display format for output produced by --stats. Permitted format values are grid, csv, tab, sql, and vertical. The default is grid.

  • --report-indexes, -r

    Reports if a table has neither UNIQUE indexes nor a PRIMARY key.

  • --server=source

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --show-drops, -d

    Display DROP statements for dropping indexes.

  • --show-indexes, -i

    Display indexes for each table.

  • --skip, -s

    Skip tables that do not exist.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --stats

    Show index performance statistics.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

  • --worst[=N]

    If --stats is also passed in, limit index statistics to the worst N indexes. The default value of N is 5, if omitted.

NOTES

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to read all objects accessed during the operation.

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To check all tables in the employees database on the local server to see the possible redundant and duplicate indexes, use this command:

shell> mysqlindexcheck --server=root@localhost employees
# Source on localhost: ... connected.
# The following indexes are duplicates or redundant \
  for table employees.dept_emp:
#
CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)
# The following indexes are duplicates or redundant \
  for table employees.dept_manager:
#
CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)
# The following indexes are duplicates or redundant \
  for table employees.salaries:
#
CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)
# The following indexes are duplicates or redundant \
  for table employees.titles:
#
CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)

PERMISSIONS REQUIRED

Regarding the privileges needed to run this utility, the user needs SELECT privilege on the mysql database as well as for the databases which tables are being checked.

5.16 mysqlmetagrep — Search Database Object Definitions

This utility searches for objects matching a given pattern on all the servers specified using instances of the --server option. It produces output that displays the matching objects. By default, the first non-option argument is taken to be the pattern unless the --pattern option is given. If the --pattern option is given, then all non-option arguments are treated as connection specifications.

Internally, the utility generates an SQL statement for searching the necessary tables in the INFORMATION_SCHEMA database on the designated servers, and then executes it before collecting the result and printing it as a table. Use the --sql option to have mysqlmetagrep display the statement, rather than execute it. This can be useful if you want to feed the output of the statement to another application, such as the mysql client command-line tool.

The MySQL server supports two forms of patterns when matching strings: SQL Simple Patterns (used with the LIKE operator) and POSIX Regular Expressions (used with the REGEXP operator).

By default, the utility uses the LIKE operator to match the name (and optionally, the body) of objects. To use the REGEXP operator instead, use the --regexp option.

Note

Because the REGEXP operator does substring searching, it is necessary to anchor the expression to the beginning of the string if you want to match the beginning of the string.

To specify how to display output, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

SQL Simple Patterns

The simple patterns defined by the SQL standard consist of a string of characters with two characters that have special meaning: % (percent) matches zero or more characters, and _ (underscore) matches exactly one character.

For example:

  • 'john%'

    Match any string that starts with 'john'.

  • '%doe%'

    Match any string containing the word 'doe'.

  • '%_'

    Match any string consisting of one or more characters.

POSIX Regular Expressions

POSIX regular expressions are more powerful than the simple patterns defined in the SQL standard. A regular expression is a string of characters, optionally containing characters with special meaning.

Documenting these regular expressions goes beyond the scope of this manual, but the full syntax is described in the MySQL manual and other locations, such as executing 'man regex' in your terminal.

  • .

    Match any character.

  • ^

    Match the beginning of a string.

  • $

    Match the end of a string.

  • [axy]

    Match a, x, or y.

  • [a-f]

    Match any character in the range a to f (that is, a, b, c, d, e, or f).

  • [^axy]

    Match any character except a, x, or y.

  • a*

    Match a sequence of zero or more a.

  • a+

    Match a sequence of one or more a.

  • a?

    Match zero or one a.

  • ab|cd

    Match ab or cd.

  • a{5}

    Match five instances of a.

  • a{2,5}

    Match from two to five instances of a.

  • (abc)+

    Match one or more repetitions of abc.

OPTIONS

mysqlmetagrep accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --body, -b

    Search the body of stored programs (procedures, functions, triggers, and events). The default is to match only the name.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --database=pattern

    Look only in databases matching this pattern.

  • --format=format, -fformat

    Specify the output display format. Permitted format values are grid (default), csv, tab, and vertical.

  • --object-types=types, --search-objects=types

    Search only the object types named in types, which is a comma-separated list of one or more of the values database, trigger, user, routine, column, table, partition, event and view.

    The default is to search in objects of all types.

  • --pattern=pattern, -e=pattern

    The pattern to use when matching. This is required when the first non-option argument looks like a connection specification rather than a pattern.

    If the --pattern option is given, the first non-option argument is treated as a connection specifier, not as a pattern.

  • --regexp, --basic-regexp, -G

    Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching. This affects the --database and --pattern options.

  • --server=source

    Connection information for a server. Use this option multiple times to search multiple servers.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --sql, --print-sql, -p

    Print rather than executing the SQL code that would be executed to find all matching objects. This can be useful to save the statement for later execution or to use it as input for other programs.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --version

    Display version information and exit.

NOTES

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

Find all objects with a name that matches the pattern 't_' (the letter t followed by any single character):

shell> mysqlmetagrep --pattern="t_" --server=john@localhost
+------------------------+--------------+--------------+-----------+
| Connection             | Object Type  | Object Name  | Database  |
+------------------------+--------------+--------------+-----------+
| john:*@localhost:3306  | TABLE        | t1           | test      |
| john:*@localhost:3306  | TABLE        | t2           | test      |
| john:*@localhost:3306  | TABLE        | tm           | test      |
+------------------------+--------------+--------------+-----------+

To find all object that contain 't2' in the name or the body (for routines, triggers, and events):

shell> mysqlmetagrep -b --pattern="%t2%" --server=john@localhost:3306
+------------------------+--------------+--------------+-----------+
| Connection             | Object Type  | Object Name  | Database  |
+------------------------+--------------+--------------+-----------+
| john:*@localhost:3306  | TRIGGER      | tr_foo       | test      |
| john:*@localhost:3306  | TABLE        | t2           | test      |
+------------------------+--------------+--------------+-----------+

In the preceding output, the trigger name does not match the pattern, but is displayed because its body does.

This is the same as the previous example, but using the REGEXP operator. Note that in the pattern it is not necessary to add wildcards before or after t2:

shell> mysqlmetagrep -Gb --pattern="t2" --server=john@localhost

+------------------------+--------------+--------------+-----------+
| Connection             | Object Type  | Object Name  | Database  |
+------------------------+--------------+--------------+-----------+
| root:*@localhost:3306  | TRIGGER      | tr_foo       | test      |
| root:*@localhost:3306  | TABLE        | t2           | test      |
+------------------------+--------------+--------------+-----------+

PERMISSIONS REQUIRED

The user must have the SELECT privilege on the mysql database.

5.17 mysqlprocgrep — Search Server Process Lists

This utility scans the process lists for the servers specified using instances of the --server option and selects those that match the conditions specified using the --age and --match-xxx options. For a process to match, all conditions given must match. The utility then either prints the selected processes (the default) or executes certain actions on them.

If no --age or --match-xxx options are given, the utility selects all processes.

The --match-xxx options correspond to the columns in the INFORMATION_SCHEMA.PROCESSLIST table. For example, --match-command specifies a matching condition for PROCESSLIST.COMMAND column values. There is no --match-time option. To specify a condition based on process time, use --age.

Processes that can be seen and killed are subject to whether the account used to connect to the server has the PROCESS and SUPER privileges. Without PROCESS, the account cannot see processes belonging to other accounts Without SUPER, the account cannot kill processes belonging to other accounts.

When the --kill-query or --kill-connection option is used, the utility displays those rows from the SHOW PROCESSLIST that match the query and are killed. This behavior exists as of MySQL Utilities 1.6.0.

To specify how to display output, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

Options

mysqlprocgrep accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --age=time

    Select only processes that have been in the current state more than a given time. The time value can be specified in two formats: either using the hh:mm:ss format, with hours and minutes optional, or as a sequence of numbers with a suffix giving the period size.

    The permitted suffixes are s (second), m (minute), h (hour), d (day), and w (week). For example, 4h15m represents 4 hours and 15 minutes.

    For both formats, the specification can optionally be preceded by + or -, where + means older than the given time, and - means younger than the given time.

  • --character-set=charset

    Sets the client character set. The default is retrieved from the server variable character_set_client.

  • --format=format, -fformat

    Specify the output display format. Permitted format values are grid (default), csv, tab, and vertical.

  • --kill-connection

    Kill the connection for all matching processes (like the KILL CONNECTION statement).

  • --kill-query

    Kill the query for all matching processes (like the KILL QUERY statement).

  • --match-command=pattern

    Match all processes where the Command field matches the pattern.

  • --match-db=pattern

    Match all processes where the Db field matches the pattern.

  • --match-host=pattern

    Match all processes where the Host field matches the pattern.

  • --match-id=pattern

    Match all processes where the ID field matches the pattern.

  • --match-info=pattern

    Match all processes where the Info field matches the pattern.

  • --match-state=pattern

    Match all processes where the State field matches the pattern.

  • --match-user=pattern

    Match all processes where the User field matches the pattern.

  • --print

    Print information about the matching processes. This is the default if no --kill-connection or --kill-query option is given. If a kill option is given, --print prints information about the processes before killing them.

  • --regexp, --basic-regexp, -G

    Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching. This affects the --match-xxx options.

  • --server=source

    Connection information for a server. Use this option multiple times to search multiple servers.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --sql, --print-sql, -Q

    Instead of displaying the selected processes, emit the SELECT statement that retrieves information about them. If the --kill-connection or --kill-query option is given, the utility generates a stored procedure named kill_processes() for killing the queries rather than a SELECT statement.

  • --sql-body

    Like --sql, but produces the output as the body of a stored procedure without the CREATE PROCEDURE part of the definition. This could be used, for example, to generate an event for the server Event Manager.

    When used with a kill option, code for killing the matching queries is generated. Note that it is not possible to execute the emitted code unless it is put in a stored routine, event, or trigger. For example, the following code could be generated to kill all idle connections for user www-data:

    shell> mysqlprocgrep --kill-connection --sql-body \
              --match-user=www-data --match-state=sleep
    
    DECLARE kill_done INT;
    DECLARE kill_cursor CURSOR FOR
      SELECT
            Id, User, Host, Db, Command, Time, State, Info
          FROM
            INFORMATION_SCHEMA.PROCESSLIST
          WHERE
              user LIKE 'www-data'
            AND
              State LIKE 'sleep'
    OPEN kill_cursor;
    BEGIN
       DECLARE id BIGINT;
       DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
       kill_loop: LOOP
          FETCH kill_cursor INTO id;
          KILL CONNECTION id;
       END LOOP kill_loop;
    END;
    CLOSE kill_cursor;
    
  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

For each example, assume that the root user on localhost has sufficient privileges to kill queries and connections.

Kill all connections created by user john:

shell> mysqlprocgrep --server=root@localhost \
          --match-user=john --kill-connection --format=CSV
# The following KILL commands were executed:
Id,User,Host,db,Command,Time,State,Info
4,john,localhost:50706,mysql,Sleep,5,,

Kill all connections that have been idle for more than 1 hour:

shell> mysqlprocgrep --server=root@localhost \
          --match-command=sleep --age=1h --kill-connection

PERMISSIONS REQUIRED

The user must have the SELECT privilege on the mysql database.

5.18 mysqlreplicate — Set Up and Start Replication Between Two Servers

This utility permits an administrator to setup and start replication from one server (the master) to another (the slave). The user provides login information for the slave and connection information for connecting to the master. It is also possible to specify a database to be used to test replication.

The utility reports conditions where the storage engines on the master and the slave differ for older versions of the server. It also reports a warning if the InnoDB storage engine type (plugin verus built-in) differs on the master and slave. For InnoDB to be the same, both servers must be running the same "type" of InnoDB (built-in or the InnoDB Plugin), and InnoDB on both servers must have the same major and minor version numbers and enabled state.

By default, the utility issues warnings for mismatches between the sets of storage engines, the default storage engine, and the InnoDB storage engine. To produce errors instead, use the --pedantic option, which requires storage engines to be the same on the master and slave.

The -vv option displays any discrepancies between the storage engines and InnoDB values, with or without the --pedantic option.

Replication can be started using one of the following strategies.

  • Start from the current position (default)

    Start replication from the current master binary log file and position. The utility uses the SHOW MASTER STATUS statement to retrieve this information.

  • Start from the beginning

    Start replication from the first event recorded in the master binary log. To do this, use the --start-from-beginning option.

  • Start from a binary log file

    Start replication from the first event in a specific master binary log file. To do this, use the --master-log-file option.

  • Start from a specific event

    Start replication from specific event coordinates (specific binary log file and position). To do this, use the --master-log-file and --master-log-pos options.

OPTIONS

mysqlreplicate accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --master=master

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

    • login-path (.mylogin.cnf) : login-path[:port][:socket]

    • Configuration file : configuration-file-path[:section]

    • Command-line : user[:passwd]@host[:port][:socket]

  • --master-log-file=master_log_file

    Begin replication from the beginning of this master log file.

  • --master-log-pos=master_log_pos

    Begin replication from this position in the master log file. This option is not valid unless --master-log-file is given.

  • --pedantic, -p

    Fail if both servers do not have the same set of storage engines, the same default storage engine, and the same InnoDB storage engine.

  • --rpl-user=replication_user

    The user and password for the replication user, in the format: user[:password] or login-path.

  • --slave=slave

    Connection information for the slave server. You may specify only one slave for this option.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --start-from-beginning, -b

    Start replication at the beginning of events logged in the master binary log. This option is not valid unless both --master-log-file and --master-log-pos are given.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --test-db=test_database

    The database name to use for testing the replication setup. If this option is not given, no testing is done, only error checking.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The login user for the master server must have the appropriate permissions to grant access to all databases, and have the ability to create user accounts. For example, the user account used to connect to the master must have the WITH GRANT OPTION privilege.

The server IDs on the master and slave must be nonzero and unique. The utility reports an error if the server ID is 0 on either server or the same on the master and slave. Set these values before starting this utility.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

For example, if you setup replication using "MASTER_HOST=ubuntu.net" on the slave and later connect to the slave with mysqlrplcheck and have the master specified as "--master=192.168.0.6" using the valid IP address for "ubuntu.net", you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To set up replication between two MySQL instances running on different ports of the same host using the default settings, use this command:

shell> mysqlreplicate --master=root@localhost:3306 \
          --slave=root@localhost:3307 --rpl-user=rpl:rpl
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

The following command uses --pedantic to ensure that replication between the master and slave is successful if and only if both servers have the same storage engines available, the same default storage engine, and the same InnoDB storage engine:

shell> mysqlreplicate --master=root@localhost:3306 \
          --slave=root@localhost:3307 --rpl-user=rpl:rpl -vv --pedantic
# master on localhost: ... connected.
# slave on localhost: ... connected.
# master id = 2
#  slave id = 99
# Checking InnoDB statistics for type and version conflicts.
# Checking storage engines...
# Checking for binary logging on master...
# Setting up replication...
# Flushing tables on master with read lock...
# Connecting slave to master...
# CHANGE MASTER TO MASTER_HOST = [...omitted...]
# Starting slave...
# status: Waiting for master to send event
# error: 0:
# Unlocking tables on master...
# ...done.

The following command starts replication from the current position of the master (which is the default):

shell> mysqlreplicate --master=root@localhost:3306 \
          --slave=root@localhost:3307 --rpl-user=rpl:rpl
 # master on localhost: ... connected.
 # slave on localhost: ... connected.
 # Checking for binary logging on master...
 # Setting up replication...
 # ...done.

The following command starts replication from the beginning of recorded events on the master:

shell> mysqlreplicate --master=root@localhost:3306 \
     --slave=root@localhost:3307 --rpl-user=rpl:rpl \
     --start-from-beginning
 # master on localhost: ... connected.
 # slave on localhost: ... connected.
 # Checking for binary logging on master...
 # Setting up replication...
 # ...done.

The following command starts replication from the beginning of a specific master binary log file:

shell> mysqlreplicate --master=root@localhost:3306 \
          --slave=root@localhost:3307 --rpl-user=rpl:rpl \
          --master-log-file=my_log.000003
 # master on localhost: ... connected.
 # slave on localhost: ... connected.
 # Checking for binary logging on master...
 # Setting up replication...
 # ...done.

The following command starts replication from specific master binary log coordinates (specific log file and position):

shell> mysqlreplicate --master=root@localhost:3306 \
          --slave=root@localhost:3307 --rpl-user=rpl:rpl \
          --master-log-file=my_log.000001 --master-log-pos=96
 # master on localhost: ... connected.
 # slave on localhost: ... connected.
 # Checking for binary logging on master...
 # Setting up replication...
 # ...done.

RECOMMENDATIONS

You should set read_only=1 in the my.cnf file for the slave to ensure that no accidental data changes, such as INSERT, DELETE, UPDATE, and so forth, are permitted on the slave other than those produced by events read from the master.

Use the --pedantic and -vv options for setting up replication on production servers to avoid possible problems with differing storage engines.

PERMISSIONS REQUIRED

The users on the master need the following privileges: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. The slave users need the SUPER privilege. The repl user, used as the argument for the --rpl-user option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege.

5.19 mysqlrplms — Set Up and Start Replication from a Slave to Multiple Masters

This utility permits a user to start replication from multiple master servers (also called multi-source replication) to a single slave. The user provides login information for the slave and each of the masters.

The utility reports conditions where the storage engines on the masters and the slave differ. It also reports a warning if the InnoDB storage engine differs on the master and slave. For InnoDB to be the same, both servers must be running the same "type" of InnoDB (built-in or the InnoDB Plugin), and InnoDB on both servers must have the same major and minor version numbers and enabled state. By default, the utility issues warnings for mismatches between the sets of storage engines, the default storage engine, and the InnoDB storage engine.

The -vv option displays any discrepancies between the storage engines and InnoDB values.

A round-robin scheduling is used to setup replication among the masters and slave.

The mysqlrplms utility follows these assumptions:

  • All servers must have GTIDs enabled.

  • There are no conflicts between transactions from different masters. For example, there are no updates to the same object from multiple masters.

  • Replication is asynchronous.

OPTIONS

mysqlrplms accepts the following command-line options:

  • --daemon=command

    Run as a daemon. The command can be start (start daemon), stop (stop daemon), restart (stop then start the daemon) or nodetach (start but do not detach the process). This option is only available for POSIX systems.

  • --format=format, -f format

    Display the replication health output in either grid (default), tab, csv, or vertical format.

  • --help

    Display a help message and exit.

  • --interval=seconds, -i seconds

    Interval in seconds for reporting health. Default = 15 seconds. Minimum is 5 seconds.

  • --license

    Display license information and exit.

  • --log=log_file

    Specify a log file to use for logging messages

  • --log-age=days

    Specify maximum age of log entries in days. Entries older than this are purged on startup. Default = 7 days.

  • --masters=master connections

    Connection information for master servers. List multiple masters in comma-separated list.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --report-values=report_values

    Report values used in mysqlrplms. It can be health, gtid or uuid. Multiple values can be used separated by commas.

    • health

      Display the replication health of the topology.

    • gtid

      Display the master's list of executed GTIDs, contents of the GTID variables; @@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED and @@GLOBAL.GTID_OWNED.

    • uuid

      Display universally unique identifiers (UUIDs) for all servers.

    Default = health.

  • --rpl-user=replication_user

    The user and password for the replication user, in the format: user[:password] or login-path.

  • --slave=slave

    Connection information for the slave server. You may specify only one slave for this option.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --start-from-beginning, -b

    Start replication at the beginning of events logged in the master binary log.

  • --switchover-interval=seconds

    Interval in seconds for switching masters. Default = 60 seconds. Minimum is 30 seconds.

  • --pidfile=pidfile

    Pidfile for running mysqlrplms as a daemon. This file contains the PID (process identifier), that uniquely identify a process. It is needed to identify and control the process forked by mysqlrplms.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The login user for the master servers must have the appropriate permissions to grant access to all databases, and have the ability to create user accounts. For example, the user accounts used to connect to each of the masters must have the WITH GRANT OPTION privilege.

The server IDs on the masters and slave must be nonzero and unique. The utility reports an error if the server ID is 0 on either server or the same on the masters and slave. Set these values before starting this utility.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempts to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

Due to a known server issue, there are some limitations with the use of temporary tables with multi-source replication. In order to avoid problems, we recommend the execution of all statements for a temporary table in a single transaction. See Replication and Temporary Tables, for more information.

EXAMPLES

To set up multi-source replication among two masters and a slave, running on different ports of the same host using the default settings, use this command:

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308
# Starting multi-source replication...
# Press CTRL+C to quit.
# Switching to master 'localhost:3307'.
# master on localhost: ... connected.
# slave on localhost: ... connected.
#
# Current Master Information:
+-------------------+-----------+---------------+-------------------+
| Binary Log File   | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+-------------------+-----------+---------------+-------------------+
| clone-bin.000001  | 594       | N/A           | N/A               |
+-------------------+-----------+---------------+-------------------+
# GTID Executed Set: 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2
#
# Health Status:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3307  | MASTER  | UP     | ON         | OK      |
| localhost  | 3306  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
#
(...)

The following command uses --report-values to report health, GTID and UUID status:

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308\n
       --report-values=health,gtid,uuid
# Starting multi-source replication...
# Press CTRL+C to quit.
# Switching to master 'localhost:3307'.
# master on localhost: ... connected.
# slave on localhost: ... connected.
#
# Current Master Information:
+-------------------+-----------+---------------+-------------------+
| Binary Log File   | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+-------------------+-----------+---------------+-------------------+
| clone-bin.000001  | 594       | N/A           | N/A               |
+-------------------+-----------+---------------+-------------------+
# GTID Executed Set: 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2
#
# Health Status:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3307  | MASTER  | UP     | ON         | OK      |
| localhost  | 3306  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
#
# GTID Status - Transactions executed on the servers:
+------------+-------+---------+-------------------------------------------+
| host       | port  | role    | gtid                                      |
+------------+-------+---------+-------------------------------------------+
| localhost  | 3307  | MASTER  | 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2  |
| localhost  | 3306  | SLAVE   | 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2  |
| localhost  | 3306  | SLAVE   | faf0874f-a839-11e3-8bd6-28d244017f26:1    |
+------------+-------+---------+-------------------------------------------+
#
# UUID Status:
+------------+-------+---------+---------------------------------------+
| host       | port  | role    | uuid                                  |
+------------+-------+---------+---------------------------------------+
| localhost  | 3307  | MASTER  | 00a4e027-a83a-11e3-8bd6-28d244017f26  |
| localhost  | 3306  | SLAVE   | faf0874f-a839-11e3-8bd6-28d244017f26  |
+------------+-------+---------+---------------------------------------+
#
(...)

Start multi-source replication running as a daemon (POSIX only):

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308 \
       --log=rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=start

Restart multi-source replication running as a daemon:

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308 \
       --log=rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=restart

Stop multi-source replication running as a daemon:

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308 \
       --log=rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=stop

RECOMMENDATIONS

You should set read_only=1 in the my.cnf file for the slave to ensure that no accidental data changes, such as INSERT, DELETE, UPDATE, and so forth, are permitted on the slave other than those produced by events read from the master.

PERMISSIONS REQUIRED

The users on the masters need the following privileges: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. The slave users need the SUPER privilege. The rpl user, used as the argument for the --rpl-user option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege.

5.20 mysqlrpladmin — Administration utility for MySQL replication

This utility permits users to perform administrative actions on a replication topology consisting of a single master and its slaves. The utility is designed to make it easy to recover from planned maintenance of the master, or from an event that takes the master offline unexpectedly.

The act of taking the master offline intentionally and switching control to another slave is called switchover. In this case, there is no loss of transactions as the master is locked and all slaves are allowed to catch up to the master. Once the slaves have read all events from the master, the master is shutdown and control switched to a slave (in this case called a candidate slave).

Recovering from the loss of a downed master is more traumatic and since there is no way to know what transactions the master may have failed to send, the new master (called a candidate slave) must be the slave that is most up-to-date. How this is determined depends on the version of the server (see below). However, it can result in the loss of some transactions that were executed on the downed master but not sent to the slaves.

The utility accepts a list of slaves to be considered the candidate slave. If no slave is found to meet the requirements, the operation searches the list of known slaves.

Detection of a downed master is performed as follows. If the connection to the master is lost, wait --ping seconds and check again. If the master connection is lost and the master cannot be pinged or reconnected, the failover event occurs.

For all commands that require specifying multiple servers, the options require a comma-separated list of connection parameters in the following form (where the password, port, and socket are optional).:

*user*[:*passwd*]@*host*[:*port*][:*socket*] or
*login-path*[:*port*][:*socket*]

The utility permits users to discover slaves connected to the master.

The discover slaves option requires all slaves use the --report-host and --report-port server startup variables with the correct hostname and port. If these are missing or report the incorrect information, the slave may not be detected and thus not included in the operation of the utility. The discover slaves option ignores any slaves to which it cannot connect.

Note

If discovered slaves are missing or report the incorrect information, the slaves health may not be reported correctly or the slave may not be listed at all.

The utility permits the user to demote a master to a slave during the switchover operation. The --demote-master option tells the utility to, once the new master is established, make the old master a slave of the new master. This permits rotation of the master role among a set of servers.

The utility permits the user to specify an external script to execute before and after the switchover and failover commands. The user can specify these with the --exec-before and --exec-after options. The return code of the script is used to determine success thus each script must report 0 (success) to be considered successful. If a script returns a value other than 0, the result code is presented in an error message.

The utility permits the user to log all actions taken during the commands. The --log option requires a valid path and filename of the file to use for logging operations. The log is active only when this option is specified. The option --log-age specifies the age in days that log entries are kept. The default is seven (7) days. Older entries are automatically deleted from the log file (but only if the --log option is specified).

The format of the log file includes the date and time of the event, the level of the event (informational - INFO, warning - WARN, error - ERROR, critical failure - CRITICAL), and the message reported by the utility.

The utility has a number of options each explained in more detail below. Some of the options are specific to certain commands. Warning messages are issued whenever an option is used that does not apply to the command requested. A brief overview of each command and its options is presented in the following paragraphs.

The start, stop, and reset commands require the --slaves option to list all of the slaves in the topology. Optionally, the --master option can be specified for the utility to check if the specified slaves are associated to the given master before executing the command, making sure that the command is only applied to slaves connected to the right replication master.

The options required for the elect, health and gtid commands include the --master option to specify the existing master, and either the --slaves option to list all of the slaves in the topology or the --discover-slaves-login option to provide the user name and password to discover any slaves in the topology that are registered and connected to the master.

The options required for switchover include the --master option to specify the existing master, the --new-master option to specify the candidate slave (the slave to become the new master), and either the --slaves option to list the considered slaves in the topology or the --discover-slaves-login option to provide the user name and password to discover any slaves in the topology that are registered and connected to the master.

The failover command requires only the --slaves option to explicitly list all of the slaves in the topology because it is expected that the master is down when this command is used. It does not use the --discover-slaves-login option and an error is presented if the user tries to include it.

Note

The option to pass in --slaves without also passing in --master was added in MySQL Utilities 1.6.0.

Use the --verbose option to see additional information in the health report and additional messages during switchover or failover.

COMMANDS

The utility also provides a number of useful commands for managing a replication topology including the following.

elect This command is available to only those servers supporting global transaction identifiers (GTIDs), perform slave election and report the candidate slave to use in the event a switchover or failover is required. Slave election is simply the first slave to meet the prerequisites. GTIDs are supported in version 5.6.5 and higher. This command requires the options --master and either --slaves or --discover-slaves-login.

failover This command is available to only those servers supporting GTIDs. Conduct failover to the best slave. The command tests each candidate slave listed for the prerequisites. Once a candidate slave is elected, it is made a slave of each of the other slaves thereby collecting any transactions executed on other slaves but not the candidate. In this way, the candidate becomes the most up-to-date slave. This command requires the --slaves option. The --discover-slaves-login option is not allowed because, for failover, the master is presumed to be offline or otherwise unreachable (so there is no way to discover the slaves). The --master option is ignored for this command.

gtid This command is available to only those servers supporting GTIDs. It displays the contents of the GTID variables, @@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED, and @@GLOBAL.GTID_OWNED. The command also displays universally unique identifiers (UUIDs) for all servers. This command requires one of the following combinations: --master and --slaves, or --master and --discover-slaves-login.

health Display the replication health of the topology. By default, this includes the host name, port, role (MASTER or SLAVE) of the server, state of the server (UP = is connected, WARN = not connected but can ping, DOWN = not connected and cannot ping), the GTID_MODE, and health state. This command can be run with the following combination of options:

Note

The health column displays "no master specified" when generating a health report for a collection of slaves and no --master option specified.

The master health state is based on the following; if GTID_MODE=ON, the server must have binary log enabled, and there must exist a user with the REPLICATE SLAVE privilege.

The slave health state is based on the following; the IO_THREAD and SQL_THREADS must be running, it must be connected to the master, there are no errors, the slave delay for non-gtid enabled scenarios is not more than the threshold provided by the --max-position and the slave is reading the correct master log file, and slave delay is not more than the --seconds-behind threshold option.

reset Execute the STOP SLAVE and RESET SLAVE commands on all slaves. This command requires the --slaves option. The --discover-slaves-login option is not allowed because it might not provide the expected result, excluding slaves with the IO thread stopped. Optionally, the --master option can also be used and in this case the utility performs an additional check to verify if the specified slaves are associated (replication is configured) to the given master.

start Execute the START SLAVE command on all slaves. This command requires the --slaves option. The --discover-slaves-login option is not allowed because it might not provide the expected result, excluding slaves with the IO thread stopped. Optionally, the --master option can also be used and in this case the utility performs an additional check to verify if the specified slaves are associated (replication is configured) to the given master.

stop Execute the STOP SLAVE command on all slaves. This command requires the --slaves option. The --discover-slaves-login option is not allowed because it might not provide the expected result, excluding slaves with the IO thread stopped. Optionally, the --master option can also be used and in this case the utility performs an additional check to verify if the specified slaves are associated (replication is configured) to the given master.

switchover Perform slave promotion to a specified candidate slave as designated by the --new-master option. This command is available for both gtid-enabled servers and non-gtid-enabled scenarios. This command requires one of the following combinations:

OPTIONS

mysqlrpladmin accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --candidates=candidate slave connections

    Connection information for candidate slave servers for failover. Valid only with failover command. List multiple slaves in comma-separated list.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --demote-master

    Make master a slave after switchover.

  • --discover-slaves-login=slave_login

    At startup, query master for all registered slaves and use the user name and password specified to connect. Supply the user and password in the form user[:passwd] or login-path. For example, --discover=joe:secret uses 'joe' as the user and 'secret' as the password for each discovered slave.

  • --exec-after=script

    Name of external script to execute after failover or switchover. Script name may include the full path.

    The return code of the script is used to determine success, thus each script must report 0 (success) to be considered successful. If a script returns a value other than 0, the result code is presented in an error message. The script specified using this option only runs if the switchover/failover executed with success.

  • --exec-before=script

    Name of external script to execute before failover or switchover. Script name may include the full path.

    The return code of the script is used to determine success, thus each script must report 0 (success) to be considered successful. If a script returns a value other than 0, the result code is presented in an error message.

  • --force

    Ignore prerequisite checks or any inconsistencies found, such as errant transactions on the slaves or SQL thread errors, thus forcing the execution of the specified command. This option must be used carefully as it does not solve any detected issue, but only ignores them and displays a warning message.

  • --format=format, -f format

    Display the replication health output in either grid (default), tab, csv, or vertical format.

  • --log=log_file

    Specify a log file to use for logging messages

  • --log-age=days

    Specify maximum age of log entries in days. Entries older than this are purged on startup. Default = 7 days.

  • --master=connection

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --max-position=position

    Used to detect slave delay. The maximum difference between the master's log position and the slave's reported read position of the master. A value greater than this means the slave is too far behind the master. Default = 0.

  • --new-master=connection

    Connection information for the slave to be used to replace the master for switchover. Valid only with switchover command.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --no-health

    Turn off health report after switchover or failover.

  • --ping=number

    Number of ping attempts for detecting downed server. Note: on some platforms this is the same as number of seconds to wait for ping to return. This value is also used to check down status of master. Failover waits for ping seconds to check master response. If no response, failover event occurs.

  • --quiet, -q

    Turn off all messages for quiet execution.

  • --rpl-user=replication_user

    The user and password for the replication user requirement, in the format: user[:password] or login-path. E.g. rpl:passwd Default = None.

  • --script-threshold=return_code

    Value for external scripts to trigger aborting the operation if result is greater than or equal to the threshold.

    Default = None (no threshold checking).

  • --seconds-behind=seconds

    Used to detect slave delay. The maximum number of seconds behind the master permitted before slave is considered behind the master. Default = 0.

  • --slaves=slave connections

    Connection information for slave servers. List multiple slaves in comma-separated list. The list is evaluated literally whereby each server is considered a slave to the master listed regardless if they are a slave of the master.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --timeout=seconds

    Maximum timeout in seconds to wait for each replication command to complete. For example, timeout for slave waiting to catch up to master. Default = 300 seconds.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The login user must have the appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW VARIABLES on the appropriate servers as well as grant the REPLICATE SLAVE privilege. The utility checks permissions for the master, slaves, and candidates at startup.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempts to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

For example, if you setup replication using "MASTER_HOST=ubuntu.net" on the slave and later connect to the slave with mysqlrplcheck and have the master specified as "--master=192.168.0.6" using the valid IP address for "ubuntu.net", you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine.

Similarly, if you use localhost to connect to the master, the health report may not show all of the slaves. It is best to use the actual hostname of the master when connecting or setting up replication.

If the user does not specify the --rpl-user and the user has specified the switchover or failover command, the utility checks to see if the slaves are using --master-info-repository=TABLE. If they are not, the utility stops with an error.

All the commands require either the --slaves or --discover-slaves-login option but both cannot be used at the same time. In fact, some commands only allow the use of the --slaves option which is safer to specify the list slaves, because --discover-slaves-login might not provide an up to date list of available slaves.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To perform best slave election for a topology with GTID_MODE=ON (server version 5.6.5 or higher) where all slaves are specified with the --slaves option, run the following command.:

shell> mysqlrpladmin --master=root@localhost:3331 \
          --slaves=root@localhost:3332,root@localhost:3333,root@localhost:3334 elect
# Electing candidate slave from known slaves.
# Best slave found is located on localhost:3332.
# ...done.

To perform best slave election supplying a candidate list, use the following command.:

shell> mysqlrpladmin --master=root@localhost:3331 \
  --slaves=root@localhost:3332,root@localhost:3333,root@localhost:3334 \
  --candidates=root@localhost:3333,root@localhost:3334 elect
# Electing candidate slave from candidate list then slaves list.
# Best slave found is located on localhost:3332.
# ...done.

To perform failover after a master has failed, use the following command.:

shell> mysqlrpladmin  \
  --slaves=root@localhost:3332,root@localhost:3333,root@localhost:3334 \
  --candidates=root@localhost:3333,root@localhost:3334 failover
# Performing failover.
# Candidate slave localhost:3333 will become the new master.
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# ...done.

To see the replication health of a topology with GTID_MODE=ON (server version 5.6.5 or higher) and discover all slaves attached to the master, run the following command. We use the result of the failover command above.:

shell> mysqlrpladmin --master=root@localhost:3333 \
  --slaves=root@localhost:3332,root@localhost:3334 health
# Getting health for master: localhost:3333.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3333  | MASTER  | UP     | ON         | OK      |
| localhost  | 3332  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3334  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
# ...done.

To view a detailed replication health report but with all of the replication health checks revealed, use the --verbose option as shown below. In this example, we use vertical format to make viewing easier.:

shell> mysqlrpladmin --master=root@localhost:3331 \
          --slaves=root@localhost:3332,root@localhost:3333,root@localhost:3334 \
          --verbose health
# Getting health for master: localhost:3331.
# Attempting to contact localhost ... Success
# Attempting to contact localhost ... Success
# Attempting to contact localhost ... Success
# Attempting to contact localhost ... Success
#
# Replication Topology Health:
*************************       1. row *************************
            host: localhost
            port: 3331
            role: MASTER
           state: UP
       gtid_mode: ON
          health: OK
         version: 5.6.5-m8-debug-log
 master_log_file: mysql-bin.000001
  master_log_pos: 571
       IO_Thread:
      SQL_Thread:
     Secs_Behind:
 Remaining_Delay:
    IO_Error_Num:
        IO_Error:
*************************       2. row *************************
            host: localhost
            port: 3332
            role: SLAVE
           state: UP
       gtid_mode: ON
          health: OK
         version: 5.6.5-m8-debug-log
 master_log_file: mysql-bin.000001
  master_log_pos: 571
       IO_Thread: Yes
      SQL_Thread: Yes
     Secs_Behind: 0
 Remaining_Delay: No
    IO_Error_Num: 0
        IO_Error:
*************************       3. row *************************
            host: localhost
            port: 3333
            role: SLAVE
           state: UP
       gtid_mode: ON
          health: OK
         version: 5.6.5-m8-debug-log
 master_log_file: mysql-bin.000001
  master_log_pos: 571
       IO_Thread: Yes
      SQL_Thread: Yes
     Secs_Behind: 0
 Remaining_Delay: No
    IO_Error_Num: 0
        IO_Error:
*************************       4. row *************************
            host: localhost
            port: 3334
            role: SLAVE
           state: UP
       gtid_mode: ON
          health: OK
         version: 5.6.5-m8-debug-log
 master_log_file: mysql-bin.000001
  master_log_pos: 571
       IO_Thread: Yes
      SQL_Thread: Yes
     Secs_Behind: 0
 Remaining_Delay: No
    IO_Error_Num: 0
        IO_Error:
4 rows.
# ...done.

To run the same failover command above, but specify a log file, use the following command.:

shell> mysqlrpladmin  \
  --slaves=root@localhost:3332,root@localhost:3333,root@localhost:3334 \
  --candidates=root@localhost:3333,root@localhost:3334 \
  --log=test_log.txt failover
# Performing failover.
# Candidate slave localhost:3333 will become the new master.
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# ...done.

After this command, the log file contains entries like the following:

2012-03-19 14:44:17 PM INFO Executing failover command...
2012-03-19 14:44:17 PM INFO Performing failover.
2012-03-19 14:44:17 PM INFO Candidate slave localhost:3333 will become the new master.
2012-03-19 14:44:17 PM INFO Preparing candidate for failover.
2012-03-19 14:44:19 PM INFO Creating replication user if it does not exist.
2012-03-19 14:44:19 PM INFO Stopping slaves.
2012-03-19 14:44:19 PM INFO Performing STOP on all slaves.
2012-03-19 14:44:19 PM INFO Switching slaves to new master.
2012-03-19 14:44:20 PM INFO Starting slaves.
2012-03-19 14:44:20 PM INFO Performing START on all slaves.
2012-03-19 14:44:20 PM INFO Checking slaves for errors.
2012-03-19 14:44:21 PM INFO Failover complete.
2012-03-19 14:44:21 PM INFO ...done.

To perform switchover and demote the current master to a slave, use the following command.:

shell> mysqlrpladmin --master=root@localhost:3331 \
  --slaves=root@localhost:3332,root@localhost:3333,root@localhost:3334 \
  --new-master=root@localhost:3332 --demote-master switchover
# Performing switchover from master at localhost:3331 to slave at localhost:3332.
# Checking candidate slave prerequisites.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
# ...done.

If the replication health report is generated on the topology following the above command, it displays the old master as a slave as shown below.:

# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3332  | MASTER  | UP     | ON         | OK      |
| localhost  | 3331  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3333  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3334  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

You can use the discover slaves feature, if and only if all slaves report their host and port to the master. A sample command to generate a replication health report with discovery is shown below. Note that the option --discover-slaves-login cannot be used in conjunction with the --slaves option.:

shell> mysqlrpladmin --master=root@localhost:3332 --discover-slaves-login=root  health

# Discovering slaves for master at localhost:3332
# Discovering slave at localhost:3331
# Found slave: localhost:3331
# Discovering slave at localhost:3333
# Found slave: localhost:3333
# Discovering slave at localhost:3334
# Found slave: localhost:3334
# Checking privileges.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3332  | MASTER  | UP     | ON         | OK      |
| localhost  | 3331  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3333  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3334  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
# ...done.

PERMISSIONS REQUIRED

The users on the master need the following privileges: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. The slave users need the SUPER privilege. The repl user, used as the argument for the --rpl-user option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege.

To run the mysqlrpladmin utility with the health command, the account used on the master needs an extra SUPER privilege.

As for the switchover command all the users need the following privileges: SUPER, GRANT OPTION, SELECT, RELOAD, DROP, CREATE and REPLICATION SLAVE

5.21 mysqlrplcheck — Check Replication Prerequisites

This utility checks the prerequisites for replication between a master and a slave. These checks (called tests) are designed to ensure a healthy replication setup. The utility performs the following tests:

  1. Is the binary log enabled on the master?

  2. Are there binary logging exceptions (such as *_do_db or *_ignore_db settings)? If so, display them.

  3. Does the replication user exist on the master with the correct privileges?

  4. Are there server_id conflicts?

  5. Is the slave connected to this master? If not, display the master host and port.

  6. Are there conflicts between the master.info file on the slave and the values shown in SHOW SLAVE STATUS on the master?

  7. Are the InnoDB configurations compatible (plugin vs. native)?

  8. Are the storage engines compatible (have same on slave as master)?

  9. Are the lower_case_tables_names settings compatible? Warn if there are settings for lowercase/uppercase table names that can cause problems. See Bug #59240.

  10. Is the slave behind the master?

The utility runs each test in turn unless there is a fatal error preventing further testing, such as a loss of connection to the servers.

Each test can complete with one of the following states: pass (the prerequisites are met), fail (the prerequisites were met but one or more errors occurred or there are exceptions to consider), or warn (the test found some unusual settings that should be examined further but may not be in error).

Use the --verbose option to see additional information such as server IDs, lower_case_table_name settings, and the contents of the master information file on the slave.

To see the values from the SHOW SLAVE STATUS statement, use the --show-slave-status option.

OPTIONS

mysqlrplcheck accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --master=source

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --master-info-file=file

    The name of the master information file on the slave. The default is master.info read from the data directory. Note: This option requires that you run the utility on the slave and that you have appropriate read access for the file.

  • --quiet, -q

    Turn off all messages for quiet execution. Note: Errors and warnings are not suppressed.

  • --show-slave-status, -s

    Display the values from SHOW SLAVE STATUS on the master.

  • --slave=source

    Connection information for the slave server. You may specify only one slave for this option.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --suppress

    Suppress warning messages.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

  • --width=number

    Change the display width of the test report. The default is 75 characters.

NOTES

The login user must have the appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW VARIABLES on the appropriate servers.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

For example, if you setup replication using MASTER_HOST=ubuntu.net on the slave and later connect to the slave with mysqlrplcheck and have the master specified as --master=192.168.0.6 using the valid IP address for ubuntu.net, you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To check the prerequisites of a master and slave that currently are actively performing replication, use the following command:

shell> mysqlrplcheck --master=root@host1:3310 --slave=root@host2:3311
# master on host1: ... connected.
# slave on host2: ... connected.
Test Description                                                  Status
------------------------------------------------------------------------
Checking for binary logging on master                             [pass]
Are there binlog exceptions?                                      [pass]
Replication user exists?                                          [pass]
Checking server_id values                                         [pass]
Is slave connected to master?                                     [pass]
Check master information file                                     [pass]
Checking InnoDB compatibility                                     [pass]
Checking storage engines compatibility                            [pass]
Checking lower_case_table_names settings                          [pass]
Checking slave delay (seconds behind master)                      [pass]
# ...done.

As shown in the example, you must provide valid login information for both the master and the slave.

To perform the same command but also display the contents of the master information file on the slave and the values of SHOW SLAVE STATUS as well as additional details, use this command:

shell> mysqlrplcheck --master=root@host1:3310 --slave=root@host2:3311 \
  --show-slave-status -vv
# master on host1: ... connected.
# slave on host2: ... connected.
Test Description                                                  Status
------------------------------------------------------------------------
Checking for binary logging on master                              [pass]
Are there binlog exceptions?                                       [pass]
Replication user exists?                                           [pass]
Checking server_id values                                          [pass]

 master id = 10
  slave id = 11

Is slave connected to master?                                      [pass]
Check master information file                                      [pass]

#
# Master information file:
#
               Master_Log_File : clone-bin.000001
           Read_Master_Log_Pos : 482
                   Master_Host : host1
                   Master_User : rpl
               Master_Password : XXXX
                   Master_Port : 3310
                 Connect_Retry : 60
            Master_SSL_Allowed : 0
            Master_SSL_CA_File :
            Master_SSL_CA_Path :
               Master_SSL_Cert :
             Master_SSL_Cipher :
                Master_SSL_Key :
 Master_SSL_Verify_Server_Cert : 0

Checking InnoDB compatibility                                      [pass]
Checking storage engines compatibility                             [pass]
Checking lower_case_table_names settings                           [pass]

  Master lower_case_table_names: 2
   Slave lower_case_table_names: 2

Checking slave delay (seconds behind master)                       [pass]

#
# Slave status:
#
                Slave_IO_State : Waiting for master to send event
                   Master_Host : host1
                   Master_User : rpl
                   Master_Port : 3310
                 Connect_Retry : 60
               Master_Log_File : clone-bin.000001
           Read_Master_Log_Pos : 482
                Relay_Log_File : clone-relay-bin.000006
                 Relay_Log_Pos : 251
         Relay_Master_Log_File : clone-bin.000001
              Slave_IO_Running : Yes
             Slave_SQL_Running : Yes
               Replicate_Do_DB :
           Replicate_Ignore_DB :
            Replicate_Do_Table :
        Replicate_Ignore_Table :
       Replicate_Wild_Do_Table :
   Replicate_Wild_Ignore_Table :
                    Last_Errno : 0
                    Last_Error :
                  Skip_Counter : 0
           Exec_Master_Log_Pos : 482
               Relay_Log_Space : 551
               Until_Condition : None
                Until_Log_File :
                 Until_Log_Pos : 0
            Master_SSL_Allowed : No
            Master_SSL_CA_File :
            Master_SSL_CA_Path :
               Master_SSL_Cert :
             Master_SSL_Cipher :
                Master_SSL_Key :
         Seconds_Behind_Master : 0
 Master_SSL_Verify_Server_Cert : No
                 Last_IO_Errno : 0
                 Last_IO_Error :
                Last_SQL_Errno : 0
                Last_SQL_Error :
# ...done.

PERMISSIONS REQUIRED

The users on the master need the following privileges: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. The slave users need the SUPER privilege.

Also, when using GTIDs, the slave users must also have SELECT privilege over the mysql database.

5.22 mysqlrplshow — Show Slaves for Master Server

This utility shows the replication slaves for a master. It prints a graph of the master and the slaves connected labeling each with the host name and port number.

You must specify the --discover-slaves-login option to provide the user name and password to discover any slaves in the topology.

The discover slaves option requires all slaves use the --report-host and --report-port server startup variables with the correct hostname and port. If these are missing or report the incorrect information, the slave may not be detected and thus not included in the operation of the utility. The discover slaves option ignores any slaves to which it cannot connect.

To explore the slaves for each client, use the --recurse option. This causes the utility to connect to each slave found and attempt to determine whether it has any slaves. If slaves are found, the process continues until the slave is found in the list of servers serving as masters (a circular topology). The graph displays the topology with successive indents. A notation is made for circular topologies.

If you use the --recurse option, the utility attempts to connect to the slaves using the user name and password provided for the master. By default, if the connection attempt fails, the utility throws an error and stops. To change this behavior, use the --prompt option, which permits the utility to prompt for the user name and password for each slave that fails to connect. You can also use the --num-retries=n option to reattempt a failed connection 'n' times before the utility fails.

An example graph for a typical topology with relay slaves is shown here:

# Replication Topology Graph::

localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE)

MASTER, SLAVE, and SLAVE+MASTER indicate that a server is a master only, slave only, and both slave and master, respectively.

A circular replication topology is shown like this, where <--> indicates circularity:

# Replication Topology Graph

localhost:3311 (MASTER)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE + MASTER)
           |
           +--- localhost:3311 <--> (SLAVE)

To produce a column list in addition to the graph, specify the --show-list option. In this case, to specify how to display the list, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

The utility uses of the SHOW SLAVE HOSTS statement to determine which slaves the master has. If you want to use the --recurse option, slaves must have been started with the --report-host and --report-port options set to their actual host name and port number or the utility may not be able to connect to the slaves to determine their own slaves.

OPTIONS

mysqlrplshow accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --discover-slaves-login=slave-login

    Supply the user and password in the form user[:passwd] or login-path for discovering slaves and relay slaves in the topology. For example, --discover=joe:secret uses 'joe' as the user and 'secret' as the password for each discovered slave.

  • --format=format, -fformat

    Specify the display format for column list output. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option applies only if --show-list is given.

  • --master=source

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --max-depth=N

    The maximum recursion depth. This option is valid only if --recurse is given.

  • --num-retries=num_retries, -nnum_retries

    The number of retries permitted for failed slave login attempts. This option is valid only if --prompt is given.

  • --prompt, -p

    Prompt for the slave user and password if different from the master user and password.

    If you give this option, the utility sets --num-retries to 1 if that option is not set explicitly. This ensures at least one attempt to retry and prompt for the user name and password should a connection fail.

  • --quiet, -q

    Turn off all messages for quiet execution. This option does not suppress errors or warnings.

  • --recurse, -r

    Traverse the list of slaves to find additional master/slave connections. User this option to map a replication topology.

  • --show-list, -l

    Display a column list of the topology.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. If this option is used, the IO thread status of each slave is also displayed. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. If you use -vvv, the output contains the state of the IO and SQL threads for each slave.

  • --version

    Display version information and exit.

NOTES

The login user must have the REPLICATE SLAVE and REPLICATE CLIENT privileges to successfully execute this utility. Specifically, the login user must have appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW SLAVE HOSTS.

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

For example, if you setup replication using MASTER_HOST=ubuntu.net on the slave and later connect to the slave with mysqlrplcheck and have the master specified as --master=192.168.0.6 using the valid IP address for ubuntu.net, you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To show the slaves for a master running on port 3311 on the local host, use the following command:

shell> mysqlrplshow  --master=root@localhost:3311 --discover-slaves-login=root
# master on localhost: ... connected.
# Finding slaves for master: localhost:3311

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE)

As shown in the example, you must provide valid login information for the master.

To show additional information about the IO thread status (to confirm if the slaves are really connected to the master) use the option --verbose:

shell> mysqlrplshow  --master=root@localhost:3311 --discover-slaves-login=root --verbose
# master on localhost: ... connected.
# Finding slaves for master: localhost:3311

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 [IO: Yes, SQL: Yes] - (SLAVE)
   |
   +--- localhost:3312 [IO: Yes, SQL: Yes] - (SLAVE)

To show the full replication topology of a master running on the local host, use the following command:

shell> mysqlrplshow  --master=root@localhost:3311 --recurse --discover-slaves-login=root
# master on localhost: ... connected.
# Finding slaves for master: localhost:3311

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE)

To show the full replication topology of a master running on the local host, prompting for the user name and password for slaves that do not have the same user name and password credentials as the master, use the following command:

shell> mysqlrplshow --recurse --prompt --num-retries=1 \
          --master=root@localhost:3331 --discover-slaves-login=root

Server localhost:3331 is running on localhost.
# master on localhost: ... connected.
# Finding slaves for master: localhost:3331
Server localhost:3332 is running on localhost.
# master on localhost: ... FAILED.
Connection to localhost:3332 has failed.
Please enter the following information to connect to this server.
User name: root
Password:
# master on localhost: ... connected.
# Finding slaves for master: localhost:3332
Server localhost:3333 is running on localhost.
# master on localhost: ... FAILED.
Connection to localhost:3333 has failed.
Please enter the following information to connect to this server.
User name: root
Password:
# master on localhost: ... connected.
# Finding slaves for master: localhost:3333
Server localhost:3334 is running on localhost.
# master on localhost: ... FAILED.
Connection to localhost:3334 has failed.
Please enter the following information to connect to this server.
User name: root
Password:
# master on localhost: ... connected.
# Finding slaves for master: localhost:3334

# Replication Topology Graph
localhost:3331 (MASTER)
   |
   +--- localhost:3332 - (SLAVE)
   |
   +--- localhost:3333 - (SLAVE + MASTER)
       |
       +--- localhost:3334 - (SLAVE)

PERMISSIONS REQUIRED

The user connected to the master must have the REPLICATION SLAVE privilege.

The user specified with the --discover-slaves-login option that logs into each slave must have the REPLICATION CLIENT privilege.

5.23 mysqlrplsync — Replication synchronization checker

This utility permits you to check replication servers for synchronization of replicated events. The utility permits users to check data consistency between a master and slaves or between two slaves. The utility reports missing objects as well as missing data. The utility can also be used to synchronize the replicated data on the servers.

The utility can operate on an active replication topology applying a synchronization process to check the data. Those servers where replication is not active can still be checked but the synchronization process is skipped. In that case, it is up to the user to manually synchronize the servers.

The user must provide connection parameters for the servers. That is, the utility requires the master and slaves using the --master and --slaves options. To compare only slaves, the user need only provide the --slaves option.

The utility also provides a feature to discover slaves connected to the master using the --discover-slaves-login and --master options.

The discover slaves option requires all slaves use the --report-host and --report-port server startup variables with the correct hostname and port. If these are missing or report the incorrect information, the slave may not be detected and thus not included in the operation of the utility. The discover slaves option ignores any slaves to which it cannot connect.

By default, all data is included in the comparison. To check specific databases or tables, list each element as a separated argument for the utility using fully qualified names. The user can also choose to exclude some databases or tables from the check using the --exclude option.

The utility also provides some important features that allow users to adjust the execution of the consistency check to their system. For example, the user may wish the utility to minimize execution of the synchronization process. To do so, the user uses the --rpl-timeout to define the maximum time for each slave to synchronize. More specifically, allow slaves to catch up with the master in order to compare the data. During this waiting step, the slaves status is periodically polled according to a predefined time interval. This polling interval to verify if the slaves are synced can be adjusted with the --interval option. A checksum query is used to compare the data of each table between servers. The checksum calculation step is skipped if its execution exceeds a predefined time, avoiding undesirable performance impacts on the target system if it takes too long to execute. The user can change the checksum timeout using the --checksum-timeout option.

Users can also use the --verbose option to see additional information when the utility executes.

This utility is designed to work exclusively for servers that support global transaction identifiers (GTIDs) and have gtid_mode=ON. Servers with GTID disabled are skipped by the utility. See Replication with Global Transaction Identifiers, for more information about GTID.

The utility takes into consideration the use of replication filtering rules on the servers skipping the check for filtered databases and tables according to the defined options. Nevertheless, the use of replication filters can still lead to data consistency issues depending on how statements are evaluated. See How Servers Evaluate Replication Filtering Rules, for more information.

OPTIONS

mysqlrplsync accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --checksum-timeout=checksum_timeout_in_seconds

    Maximum timeout in seconds to wait for the checksum query to complete.

    Default = 3 seconds.

  • --discover-slaves-login=user_login

    Detect registered slaves at startup and use the user name and password specified to connect in the format: user [:password] or login-path. For example, --discover-slaves-login=joe:secret uses 'joe' as the user and 'secret' as the password for each discovered slave.

  • --exclude=databases_tables_to_exclude

    Fully qualified name for the databases or tables to exclude: db_name [.tbl_name]. List multiple data objects in a comma-separated list.

  • --interval=interval_in_seconds, -i interval_in_seconds

    Interval in seconds for periodically polling the slaves sync status to verify if the sync point was reached.

    Default = 3 seconds.

  • --master=master_connection

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --rpl-timeout=rpl_timeout_in_seconds

    Maximum timeout in seconds to wait for synchronization. More precisely, the time to wait for the replication process on a slave to reach a sync point (GTID set).

    Default = 300 seconds.

  • --slaves=slaves_connections

    Connection information for slave servers . List multiple slaves in comma-separated list.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The data consistency check is performed per table using a checksum on the table. If the calculated checksum differs, it indicates the tables are not synchronized. Nevertheless, since the checksum operation is not collision free, there is a very small probability that two tables with differing data can produce the same checksum.

Mixing IP and hostnames is not recommended. The replication-specific utilities attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master. However, if your installation does not support reverse name lookup, the comparison could fail. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master.

For example, if you setup replication using MASTER_HOST=ubuntu.net on the slave and later connect to the slave with mysqlrplcheck and have the master specified as --master=192.168.0.6 using the valid IP address for ubuntu.net, you must have the ability to do a reverse name lookup to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to determine if they are the same machine.

Similarly, in order to avoid issues mixing local IP '127.0.0.1' with 'localhost', the addresse '127.0.0.1' is converted to 'localhost' by the utility.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

LIMITATIONS

This utility is designed to work exclusively for servers that support global transaction identifiers (GTIDs) and have gtid_mode=ON. Due to known server issues with some operations required for the synchronization process, only MySQL Server versions 5.6.14 and higher are supported by this utility.

Some replication filtering options are not supported by this utility due to known issues on the server side, namely: replicate_do_db, replicate_ignore_db, and replicate_wild_do_table. In case a non supported replication filtering option is detected on a server, the utility issues an appropriate error and exits. This check is performed at the beginning when the utility starts.

EXAMPLES

To check the data consistency on an active replication system explicitly specifying the master and slaves:

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 12 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

To check the data consistency on an active replication system using slave discovery:

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --discover-slaves-login=rpl:pass
# Discovering slaves for master at localhost:3310
# Discovering slave at localhost:3311
# Found slave: localhost:3311
# Discovering slave at localhost:3312
# Found slave: localhost:3312
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 15 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

To check the data consistency on an active replication system, but only between specific slaves:

shell> mysqlrplsync --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312
#
# Checking data consistency.
#
# Using Slave 'localhost@3311' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

To check the data consistency of a specific database and table on an active replication system:

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 \
          test_rplsync_db test_db.t1
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 12 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

To check the data consistency on an active replication system excluding a specific database and table:

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 \
          --exclude=test_rplsync_db,test_db.t1
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 12 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

The following is an example of a replication check that has data inconsistencies:

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is up-to-date.
# - Slave 'localhost@3312' is up-to-date.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# [DIFF] Database NOT on base server but found on 'localhost@3311': only_on_slave_db
# Checking 'test_rplsync_db' database...
#   [DIFF] Table NOT on base server but found on 'localhost@3311': t3
#   [DIFF] Table NOT on base server but found on 'localhost@3312': t3
#   [DIFF] Table 'test_rplsync_db.t0' NOT on server 'localhost@3311'.
# - Checking 't0' table data...
#   [DIFF] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   WARNING: Slave not active 'localhost@3311' - Sync skipped.
#   [DIFF] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# - Checking 't2' table data...
#   WARNING: Slave not active 'localhost@3311' - Sync skipped.
#   [OK] `test_rplsync_db`.`t2` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t2` checksum for server 'localhost@3312'.
# Checking 'only_on_master_db' database...
#   [DIFF] Database 'only_on_master_db' NOT on server 'localhost@3311'.
#   [DIFF] Database 'only_on_master_db' NOT on server 'localhost@3312'.
#
#...done.
#
# SUMMARY: 8 data consistency issues found.
#

Check a replication topology with filtering:

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 \
          --verbose
# Checking users permission to perform consistency check.
#
# WARNING: Replication filters found on checked servers. This can lead data consistency issues depending on how statements are evaluated.
# More information: http://dev.mysql.com/doc/en/replication-rules.html
# Master 'localhost@3310':
#   - binlog_do_db: test_rplsync_db1
# Slave 'localhost@3311':
#   - replicate_do_table: test_rplsync_db1.t1
# Slave 'localhost@3312':
#   - replicate_ignore_table: test_rplsync_db1.t2
#   - replicate_wild_ignore_table: test\_rplsync\_db1.%3
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is up-to-date.
# - Slave 'localhost@3312' is up-to-date.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db1' database...
# [SKIP] Table 't0' check for 'localhost@3311' - filtered by replication rule.
# - Checking 't0' table data...
#   Setting data synchronization point for slaves.
#   Compute checksum on slaves (wait to catch up and resume replication).
#   [OK] `test_rplsync_db1`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   Setting data synchronization point for slaves.
#   Compute checksum on slaves (wait to catch up and resume replication).
#   [OK] `test_rplsync_db1`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db1`.`t1` checksum for server 'localhost@3312'.
# [SKIP] Table 't2' check for 'localhost@3311' - filtered by replication rule.
# [SKIP] Table 't2' check for 'localhost@3312' - filtered by replication rule.
# [SKIP] Table 't3' check for 'localhost@3311' - filtered by replication rule.
# [SKIP] Table 't3' check for 'localhost@3312' - filtered by replication rule.
# [SKIP] Database 'test_rplsync_db0' check - filtered by replication rule.
# [SKIP] Database 'test_rplsync_db2' check - filtered by replication rule.
# [SKIP] Database 'test_rplsync_db3' check - filtered by replication rule.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

PERMISSIONS REQUIRED

The user for the master must have permissions to lock tables, perform the checksum, and get information about the master status. Specifically, the user used to connect to the master requires the following privileges: SUPER or REPLICATION CLIENT, LOCK TABLES and SELECT.

The user for the slaves must have permissions to start/stop the slave, perform the checksum, and get information about the slave status. More specifically, the login user to connect to slaves requires the following privileges: SUPER and SELECT.

5.24 mysqlserverclone — Clone Existing Server to Create New Server

This utility enables you to clone an existing MySQL server instance to create a new server instance on the same host. The utility creates a new datadir (--new-data), and, on Unix systems, starts the server with a socket file. You can optionally add a password for the login user account on the new instance.

If the user does not have read and write access to the folder specified by the --new-data option, the utility issues an error.

Similarly, if the folder specified by --new-data exists and is not empty, the utility does not delete the folder and issues an error message. Users must specify the --delete-data option to permit the utility to remove the folder prior to starting the cloned server.

The utility does not copy any data. It merely creates a new running instance of the cloned server with the same options (or additional options if specified). Thus, to create a copy of a server, you must copy the data after the server is cloned.

OPTIONS

mysqlserverclone accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --delete-data

    Delete the folder specified by --new-data if it exists and is not empty.

  • --basedir

    The base directory for the MySQL server source, as an alternative to the --server option.

    shell> mysqlserverclone --basedir=/source/mysql-5.6 \
    --new-data=/source/temp_3007 --new-port=3007 --new-id=101 \
    --root=root --mysqld="--log-bin --gtid-mode=on --log-slave-updates \
    --enforce-gtid-consistency --master-info-repository=table \
    --report-host=localhost --report-port=3007" --delete
           
  • --force

    Ignore the maximum path length and the low space checks for the --new-data option.

  • --mysqld=options

    Additional options for mysqld. To specify multiple options, separate them by spaces. Use appropriate quoting as necessary. For example, to specify --log-bin=binlog and --general-log-file="mylogfile", use:

    If the option --skip-innodb is included when connecting to a MySQL server version 5.7.5 or higher, the option is ignored and a warning is issued.

    --mysqld="--log-bin=binlog --general-log-file='my log file'"
    
  • --new-data=path_to_new_datadir

    The full path to the location of the data directory for the new instance. The path size must be 200 characters or less and it requires at least 120 MB of free space.

  • --new-id=server_id

    The server_id value for the new server instance. The default is 2.

  • --new-port=port

    The port number for the new server instance. The default is 3307.

  • --quiet, -q

    Turn off all messages for quiet execution.

  • --root-password=password

    The password for the root user of the new server instance.

  • --server=source

    Connection information for the server to be cloned.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --start-timeout=timeout_in_seconds

    Number of seconds to wait for server to start. Default = 10 seconds.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

  • --write-command=file_name, -wfile_name

    Path name of file in which to write the command used to launch the new server instance.

EXAMPLES

The following command demonstrates how to create a new instance of a running server, set the root user password and enable binary logging:

shell> mkdir /source/test123
shell> mysqlserverclone --server=root:pass@localhost \
    --new-data=/Users/cbell/source/test123 --new-port=3310 \
    --root-password=pass --mysqld=--log-bin=mysql-bin
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Setting the root password...
# ...done.

NOTES

Should the server fail to start due to an error in the server startup sequence, the utility presents an error message along with suggestions for how to identify the error. The best way to find the error is to run the utility again with verbosity turned on (using the -vvv option). This displays all of the messages from the server setup. Examine this list to find the exact error/reason why the server did not start.

PERMISSIONS REQUIRED

The user must have permission to read all databases. Since we are using the root account for these examples (and you typically would), permissions are not generally a problem.

You also need permissions to create the new data directory and write data to it.

5.25 mysqlserverinfo — Display Common Diagnostic Information from a Server

This utility displays critical information about a server for use in diagnosing problems. The information displayed includes the following:

  • Server connection information

  • Server version number

  • Data directory path name

  • Base directory path name

  • Plugin directory path name

  • Configuration file location and name

  • Current binary log coordinates (filename and position)

  • Current relay log coordinates (filename and position)

This utility can be used to see the diagnostic information for servers that are running or offline. If you want to see information about an offline server, the utility starts the server in read-only mode. In this case, you must specify the --basedir, --datadir, and --start options to prevent the utility from starting an offline server accidentally. Note: Be sure to consider the ramifications of starting an offline server on the error and similar logs. It is best to save this information prior to running this utility.

To specify how to display output, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

To turn off the headers for grid, csv, or tab display format, specify the --no-headers option.

To see the common default settings for the local server's configuration file, use the --show-defaults option. This option reads the configuration file on the machine where the utility is run, not the machine for the host that the --server option specifies.

To run the utility against several servers, specify the --server option multiple times. In this case, the utility attempts to connect to each server and read the information.

To see the MySQL servers running on the local machine, use the --show-servers option. This shows all the servers with their process ID and data directory. On Windows, the utility shows only the process ID and port.

OPTIONS

mysqlserverinfo accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --basedir=basedir

    The base directory for the server. This option is required for starting an offline server.

    Is also used to access server tools, such as my_print_defaults that is required to read the login-path values from the login configuration file (.mylogin.cnf).

  • --datadir=datadir

    The data directory for the server. This option is required for starting an offline server.

  • --format=format, -fformat

    Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid.

  • --no-headers, -h

    Do not display column headers. This option applies only for grid, csv, and tab output.

  • --port-range=start:end

    The port range to check for finding running servers. This option applies only to Windows and is ignored unless --show-servers is given. The default range is 3306:3333.

  • --server=server

    Connection information for a server. Use this option multiple times to see information for multiple servers.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --show-defaults, -d

    Display default settings for mysqld from the local configuration file. It uses my_print_defaults to obtain the options.

  • --show-servers

    Display information about servers running on the local host. The utility examines the host process list to determine which servers are running.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --start, -s

    Start the server in read-only mode if it is offline. With this option, you must also give the --basedir and --datadir options.

  • --start-timeout

    Number of seconds to wait for the server to be online when started in read-only mode using the --start option. The default value is 10 seconds.

    The --start-timeout option is available as of MySQL Utilities 1.2.4 / 1.3.3.

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

EXAMPLES

To display the server information for the local server and the settings for mysqld in the configuration file with the output in a vertical list, use this command:

shell> mysqlserverinfo --server=root:pass@localhost -d --format=vertical
# Source on localhost: ... connected.
*************************       1. row *************************
         server: localhost:3306
        version: 5.1.50-log
        datadir: /usr/local/mysql/data/
        basedir: /usr/local/mysql-5.1.50-osx10.6-x86_64/
     plugin_dir: /usr/local/mysql-5.1.50-osx10.6-x86_64/lib/plugin
    config_file: /etc/my.cnf
     binary_log: my_log.000068
 binary_log_pos: 212383
      relay_log: None
  relay_log_pos: None
1 rows.

Defaults for server localhost:3306
  --port=3306
  --basedir=/usr/local/mysql
  --datadir=/usr/local/mysql/data
  --server_id=5
  --log-bin=my_log
  --general_log
  --slow_query_log
  --innodb_data_file_path=ibdata1:778M;ibdata2:50M:autoextend
#...done.

PERMISSIONS REQUIRED

The permissions required include the ability to read the mysql database and to have read access to the data directory.

The user must have permissions to read the data directory or use an administrator or super user (sudo) account to obtain access to the data directory.

5.26 mysqlslavetrx — Slave transaction skip utility

This utility allows users to skip multiple transactions on slaves in a single step for gtid-enabled servers. In particular, it injects empty transactions on all specified slaves for each GTID in the specified GTID set.

Note

Skipping transactions can be useful to recover from erroneous situations that can occur during the replication process. However, this technique must be applied with extreme caution and full knowledge of its consequences because it might lead to data inconsistencies between the replication servers.

For example, let's consider that a transaction that inserts some data 'row1' into table 't1' fails on 'slave1'. If that transaction is simply skipped to quickly resume replication on 'slave1' without any additional intervention, then 'row1' is missing from that slave. Moreover, 'row1' is no longer replicated from the master since the GTID for the skipped transaction is associated to an empty transaction. As a consequence, the data for table 't1' on 'slave1' is inconsistent with the one on the master and other slaves because 'row1' is missing. For this reason, we should make sure that the technique to skip transactions is applied in the right situations and that all additional operations to keep the data consistent are also taken.

Skipping transactions is also useful to ignore errant transactions on slaves in order to avoid those transactions from being replicated if a failover occurs. For example, consider that some transactions with custom data changes were accidentally committed on a slave without turning off binary logging, and that those changes are specific to that slave and should not be replicated (e.g., additional data for reporting purposes, data mining, or local administrative commands). If that slave becomes the new master as a result of a failover or switchover, then those errant transactions start being replicated across the topology. In order to avoid this situation, errant transactions should be skipped on all slaves.

Note

An errant transaction is a transaction that exists on a slave but not on all of the slaves connected to the master. An errant transaction has a GTID associated with the UUID of the slave to which it was committed. These type of transactions can result from write operations performed on the slave while binary logging is enabled. By nature, these transactions should not be replicated.

It is considered poor practice to execute write operation on slave with binary logging enabled because it creates errant transactions that can lead to unstable topologies in failover scenarios. The best way to deal with errant transactions is to avoid writing or applying query statements to the slave directly without turning off binary logging first.

There are other situations like provisioning and scale out where injecting empty transaction can be a useful technique. See Using GTIDs for Failover and Scaleout, for more information about this scenario.

Users must specify the set of GTIDs for the transactions to skip with the --gtid-set option, and the server connection parameters for the list of target slaves using the --slaves option.

The utility displays the GTID set that is effectively skipped for each slave. If any of the specified GTIDs correspond to an already committed transaction on a slave, then those GTIDs are ignored for that slave (not skipped) because no other transaction (empty or not) can be applied with the same GTID. Users can execute the utility in dry run mode using the --dryrun option to confirm which transactions would be skipped with the provided input values without effectively skipping them.

The utility does not require replication to be stopped. However, in some situations it is recommended. For example, in order to skip a transaction from the master on a slave, that slave should be stopped otherwise the target transaction might be replicated before the execution of the skip operation and therefore not skipped as expected.

Users can also use the --verbose option to see additional information when the utility executes. This includes a list of slaves not supporting GTIDs and the GTIDs of the injected transactions.

OPTIONS

mysqlslavetrx accepts the following command-line options:

  • --dryrun

    Execute the utility in dry-run mode, show the transactions (GTID) that would have been skipped for each slave but without effectively skipping them. This option is useful to verify if the correct transactions are skipped.

  • --gtid-set=gtid-set

    Set of Global Transaction Identifiers (GTID) to skip.

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --slaves=slaves_connections

    Connection information for slave servers. List multiple slaves in comma-separated list.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

LIMITATIONS

The utility requires all target slaves to support global transaction identifiers (GTIDs) and have gtid_mode=ON.

EXAMPLES

Skip multiple GTIDs on the specified slaves:

shell> mysqlslavetrx --gtid-set=af6b22ee-7b0b-11e4-aa8d-606720440b68:7-9 \
          --slaves=user:pass@localhost:3311,user:pass@localhost:3312
WARNING: Using a password on the command line interface can be insecure.
#
# GTID set to be skipped for each server:
# - localhost@3311: af6b22ee-7b0b-11e4-aa8d-606720440b68:7-9
# - localhost@3312: af6b22ee-7b0b-11e4-aa8d-606720440b68:7-9
#
# Injecting empty transactions for 'localhost:3311'...
# Injecting empty transactions for 'localhost:3312'...
#
#...done.
#

Execute the utility in dryrun mode to verify which GTIDs would have been skipped on all specified slaves:

shell> mysqlslavetrx --gtid-set=af6b22ee-7b0b-11e4-aa8d-606720440b68:6-12 \
          --slaves=user:pass@localhost:3311,user:pass@localhost:3312
          --dryrun
WARNING: Using a password on the command line interface can be insecure.
#
# WARNING: Executing utility in dry run mode (read only).
#
# GTID set to be skipped for each server:
# - localhost@3311: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
# - localhost@3312: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
#
# (dry run) Injecting empty transactions for 'localhost:3311'...
# (dry run) Injecting empty transactions for 'localhost:3312'...
#
#...done.
#

Skip multiple GTIDs on the specified slaves using the verbose mode:

shell> mysqlslavetrx --gtid-set=af6b22ee-7b0b-11e4-aa8d-606720440b68:6-12 \
          --slaves=user:pass@localhost:3311,user:pass@localhost:3312
          --verbose
WARNING: Using a password on the command line interface can be insecure.
#
# GTID set to be skipped for each server:
# - localhost@3311: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
# - localhost@3312: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
#
# Injecting empty transactions for 'localhost:3311'...
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:6
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:10
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:11
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:12
# Injecting empty transactions for 'localhost:3312'...
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:6
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:10
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:11
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:12
#
#...done.
#

PERMISSIONS REQUIRED

The user used to connect to each slave must have the required permissions to inject empty transactions, more precisely the SUPER privilege is required to set the gtid_next variable.

5.27 mysqluc — Command line client for running MySQL Utilities

This utility provides a command line environment for running MySQL Utilities.

The mysqluc utility, hence console, allows users to execute any of the currently installed MySQL Utilities commands (the MySQL Utility scripts such as mysqluserclone). The option --utildir is used to provide a path to the MySQL Utilities scripts if the location is different from where the utility is executed.

The console has a list of console or base commands. These allow the user to interact with the features of the console itself. The list of base commands is shown below along with a brief description.

Command                 Description
----------------------  ---------------------------------------------------
help utilities          Display list of all utilities supported.
help <utility>          Display help for a specific utility.
help | help commands    Show this list.
exit | quit             Exit the console.
set <variable>=<value>  Store a variable for recall in commands.
show options            Display list of options specified by the user on
                        launch.
show variables          Display list of variables.
<ENTER>                 Press ENTER to execute command.
<ESCAPE>                Press ESCAPE to clear the command entry.
<DOWN>                  Press DOWN to retrieve the previous command.
<UP>                    Press UP to retrieve the next command in history.
<TAB>                   Press TAB for type completion of utility, option,
                        or variable names.
<TAB><TAB>              Press TAB twice for list of matching type
                        completion (context sensitive).

One of the most helpful base commands is the ability to see the options for a given utility by typing 'help utility'. When the user enters this command, the console displays a list of all of the options for the utility.

The console provides tab completion for all commands, options for utilities, and user-defined variables. Tab completion for commands allows users to specify the starting N characters of a command and press TAB to complete the command. If there are more than one command that matches the prefix, and the user presses TAB twice, a list of all possible matches is displayed.

Tab completion for options is similar. The user must first type a valid MySQL Utility command then types the first N characters of a command and presses TAB, for example –-verbTAB. In this case, the console completes the option. For the cases where an option requires a value, the console completes the option name and append the '=' character. Tab completion for options works for both the full name and the alias (if available). If the user presses TAB twice, the console displays a list of matching options. Pressing TAB twice immediately after typing the name of a MySQL Utility displays a list of all options for that utility.

Tab completion for variables works the same as that for options. In this case, the user must first type the '$' character then press TAB. For example, if a variable $SERVER1 exists, when the user types –-server=$SERTAB, the console completes the $SERVER variable name. For cases where there are multiple variables, pressing TAB twice displays a list of all matches to the first $+N characters. Pressing TAB twice after typing only the $ character displays a list of all variables.

Note

The 'mysql' prefix is optional in the console. For example, typing 'diskuTAB' in the console completes the command as 'diskusage '.

Executing utilities is accomplished by typing the complete command and pressing ENTER. The user does not have to type 'python' or provide the '.py' file extension. The console adds these if needed when the command is executed.

The user can also run commands using the option --execute. The value for this option is a semi-colon separated list of commands to execute. These can be base commands or MySQL Utility commands. The console executes each command and display the output. All commands to be run by the console must appear inside a quoted string and separated by semi-colons. Commands outside of the quoted string are treated as arguments for the mysqluc utility itself and thus ignored for execution.

Note

In the console, an error in the console or related code stop sexecuting commands at the point of failure. Commands may also be piped into the console using a mechanism such as 'echo "commands" | mysqluc'.

The console also allows users to set user-defined variables for commonly used values in options. The syntax is simply 'set VARNAME=VALUE'. The user can see a list of all variables by entering the 'show variables' command. To use the values of these variables in utility commands, the user must prefix the value with a '$'. For example, --server=$SERVER1 substitutes the value of the SERVER1 user-defined variable when the utility is executed.

Note

User-defined variables have a session lifetime. They are not saved from one execution to another in the users console.

User-defined variables may also be set by passing them as arguments to the mysqluc command. For example, to set the SERVER1 variable and launch the console, the user can launch the console using this command.:

shell> mysqluc SERVER1=root@localhost

The user can provide any number of user-defined variables but they must contain a value and no spaces around the '=' character. Once the console is launched, the user can see all variables using the 'show variables' command.

OPTIONS

  • --version

    show program's version number and exit

  • --help

    show the program's help page

  • --license

    Display license information and exit.

  • --verbose, -v

    control how much information is displayed. For example, -v = verbose, -vv = more verbose, -vvv = debug

  • --quiet

    suppress all informational messages

  • --execute commands, -e commands

    Execute commands and exit. Multiple commands are separated with semi-colons.

    Note

    Some platforms may require double quotes around the command list.

  • --utildir path

    location of utilities

  • --width number

    Display width

NOTES

Using the --execute option or piping commands to the console may require quotes or double quotes (for example, on Windows).

EXAMPLES

To launch the console, use this command:

shell> mysqluc

The following demonstrates launching the console and running the console command 'help utilities' to see a list of all utilities supported. The console executes the command then exits.:

shell> mysqluc -e "help utilities"

Utility           Description
----------------  ---------------------------------------------------------
mysqlindexcheck   check for duplicate or redundant indexes
mysqlrplcheck     check replication
mysqluserclone    clone a MySQL user account to one or more new users
mysqldbcompare    compare databases for consistency
mysqldiff         compare object definitions among objects where the
                  difference is how db1.obj1 differs from db2.obj2
mysqldbcopy       copy databases from one server to another
mysqlreplicate    establish replication with a master
mysqldbexport     export metadata and data from databases
mysqldbimport     import metadata and data from files
mysqlmetagrep     search metadata
mysqlprocgrep     search process information
mysqldiskusage    show disk usage for databases
mysqlserverinfo   show server information
mysqlserverclone  start another instance of a running server

The following demonstrates launching the console to run several commands using the --execute option to including setting a variable for a server connection and executing a utility using variable substitution.

Note

It may be necessary to escape the '$' on some platforms, such as Linux.

The output below is an excerpt and is representational only:

shell> mysqluc -e "set SERVER=root@host123; mysqldiskusage --server=\$SERVER"

# Source on host123: ... connected.

NOTICE: Your user account does not have read access to the datadir. Data
sizes will be calculated and actual file sizes may be omitted. Some features
may be unavailable.

# Database totals:
+--------------------+--------------+
| db_name            |       total  |
+--------------------+--------------+
...
| world              |           0  |
...
+--------------------+--------------+

Total database disk usage = 1,072,359,052 bytes or 1022.00 MB

#...done.

The following demonstrates launching the console using the commands shown above but piped into the console on the command line. The results are the same as above.:

shell> echo "set SERVER=root@host123; mysqldiskusage --server=\$SERVER" | mysqluc

The following demonstrates launching the console and setting variables via the command line.:

shell> mysqluc SERVER=root@host123 VAR_A=57 -e "show variables"

Variable  Value
--------  -----------------------------------------------------------------
SERVER    root@host123
VAR_A     57

PERMISSIONS REQUIRED

There are no special permissions required to run mysqluc however, you must have the necessary privileges to execute the desired utilities. See the PERMISSIONS REQUIRED section for each command you wish to execute.

5.28 mysqluserclone — Clone Existing User to Create New User

This utility uses an existing MySQL user account on one server as a template, and clones it to create one or more new user accounts with the same privileges as the original user. The new users can be created on the original server or a different server.

To list users for a server, specify the --list option. This prints a list of the users on the source (no destination is needed). To control how to display list output, use one of the following values with the --format option:

  • grid (default)

    Display output in grid or table format like that of the mysql client command-line tool.

  • csv

    Display output in comma-separated values format.

  • tab

    Display output in tab-separated format.

  • vertical

    Display output in single-column format like that of the \G command for the mysql client command-line tool.

OPTIONS

mysqluserclone accepts the following command-line options:

  • --help

    Display a help message and exit.

  • --license

    Display license information and exit.

  • --destination=destination

    Connection information for the destination server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --dump, -d

    Display the GRANT statements to create the account rather than executing them. In this case, the utility does not connect to the destination server and no --destination option is needed.

  • --format=list_format, -flist_format

    Specify the user display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option is valid only if --list is given.

  • --force

    Drop the new user account if it exists before creating the new account. Without this option, it is an error to try to create an account that already exists.

  • --include-global-privileges

    Include privileges that match base_user@% as well as base_user@host.

  • --list

    List all users on the source server. With this option, a destination server need not be specified.

  • --quiet, -q

    Turn off all messages for quiet execution.

  • --source=source

    Connection information for the source server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

  • --version

    Display version information and exit.

NOTES

For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

When cloning users that are defined with an authentication plugin, the utility attempts to use the authentication plugin for the destination user(s). The utility issues an error if the plugin is not on the destination machine or is inactive and the operation is aborted. A warning is issued if a password is assigned to a new user when the source user has an authentication plugin. In this case, the utility uses the password and not the authentication plugin for the new user.

EXAMPLES

To clone joe as sam and sally with passwords and logging in as root on the local machine, use this command:

shell> mysqluserclone --source=root@localhost \
          --destination=root@localhost \
          joe@localhost sam:secret1@localhost sally:secret2@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 2 users...
# Cloning joe@localhost to user sam:secret1@localhost
# Cloning joe@localhost to user sally:secret2@localhost
# ...done.

The following command shows all users on the local server in the most verbose output in CSV format:

shell> mysqluserclone --source=root@localhost --list --format=csv -vvv
# Source on localhost: ... connected.
user,host,database
joe,localhost,util_test
rpl,localhost,
sally,localhost,util_test
sam,localhost,util_test
joe,user,util_test

PERMISSIONS REQUIRED

The account used on the source server must have privileges to read the mysql database. The account used to connect to the destination server must have privileges to execute CREATE USER (and DROP USER if the --force option is given), and privileges to execute GRANT for all privileges to be granted to the new accounts.