Table of Contents
This chapter includes the manual pages for each of the utilities. Each manual page is formatted similar to a typical Unix man page.
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.
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).
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.
The location to copy the specified audit log file. The path must be locally accessible for the current user.
Display the audit log file statistics.
Display a help message and exit.
Display license information and exit.
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.
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
]
Display the audit log system variables.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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 used to set variables based on the specified commands, such as policy and rotate_on_size.
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.
Display version information and exit.
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.
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.
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=.
The user must have permissions to read the audit log file(s) on disk and write the file(s) to the remote location.
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.
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.
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.
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.
mysqlauditgrep accepts the following command-line options:
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".
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.
Display the audit log file statistics.
Output format to display the resulting data. Supported format values: GRID (default), TAB, CSV, VERTICAL and RAW.
Display a help message and exit.
Display license information and exit.
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.
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.
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.
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.
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.
Comma-separated list of user names, to search for their associated log entries. For example: "dan,jon,john,paul,philip,stefan".
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.
Display version information and exit.
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".
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 |
+---------+----------------------+--------+---------------------------------------------------------------------+----------------+
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 |
+---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
The user must have permissions to read the audit log file(s) on disk.
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.
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).
This utility was added in MySQL Utilities 1.6.0.
mysqlbinlogmove accepts the following command-line options:
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'.
Location (full path) of the binary log index file. If not specified, it is assumed to be located in the binary log directory.
Display a help message and exit.
Display license information and exit.
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.
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 the binary log flush operation to refresh server's internal information after moving the binary log files.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
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).
This utility does not support remote access to binary log files and must be executed on the local server.
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.
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.
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.
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.
mysqlbinlogpurge provides the following command-line options:
Show the program's version number.
Display the help message.
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
]
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.
The path to a file that contains a list of trusted SSL certificate authorities.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
Binary log filename to not to purge. All the binary log files prior to the specified file are removed.
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.
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
]
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
]
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.
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.
mysqlbinlogpurge cannot verify slaves that are finds a slave which is not actively replicating from the master.
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'
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.
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.
mysqlbinlogrotate accepts the following command-line options:
Shows the program's version number.
Displays the help message.
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
]
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).
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
Rotate the active binary log file only if the file size exceeds the specified value in bytes.
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.
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).
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'
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.
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.
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.
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:
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.
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.
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.
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.
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.
mysqldbcompare accepts the following command-line options:
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.
The sys database is ignored as of Utilities 1.6.2.
Display a help message and exit.
Display license information and exit.
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.
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.
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 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.
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.
Specify the display format for changed or missing rows. Permitted format values are grid, csv, tab, and vertical. The default is grid.
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.
Do not print anything. Return only an exit code of success or failure.
Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching.
Do not halt at the first difference found. Process all objects.
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
]
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
]
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 the CHECKSUM TABLE step in the data consistency check. Added in release-1.4.3.
Skip the data consistency check.
Skip the object definition difference check.
Skip the object comparison check.
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.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
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
;]"
Change the display width of the test report. The default is 75 characters.
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.
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
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.
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.
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.
mysqldbcopy accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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.
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 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.
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 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.
Before MySQL Utilities 1.4.2, this option was named
--force
.
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.
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.
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.
Turn off all messages for quiet execution.
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).
The user and password for the replication user requirement in
the form:
user
[:password
]
or login-path
. E.g. rpl:passwd
Default = None.
Skip creation and execution of GTID statements during the copy operation.
Copy all of the databases on the server.
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.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
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).
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.
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.
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.
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.
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.
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.
mysqldbexport accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
Use bulk insert statements for data.
Sets the client character set. The default is retrieved from
the server variable
character_set_client
.
Place the replication statements in comment statements. Valid
only with the --rpl
option.
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 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.
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.
Specify the export format. Permitted format values include the following. The default is definitions.
Table 5.1 mysqldbexport Export Types
Export Type | Definition |
---|---|
definitions (default) | Only export the definitions (metadata) for the objects in the database list |
data | Only export the table data for the tables in the database list |
both | Export both the definitions (metadata) and data |
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.
Specify the output display format. Permitted format values are sql, grid, tab, csv, and vertical. The default is sql.
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.
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.
Do not display column headers. This option applies only for csv and tab output.
Specify the path and filename to store the generated export output. By default the standard output is used (no file).
Turn off all messages for quiet execution.
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.
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.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Do not export BLOB
data.
Skip creation of GTID_PURGED statements.
Generate an export file with all of the databases and the GTIDs executed to that point.
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.
Display version information and exit.
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).
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; #
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.
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.
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.
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.
mysqldbimport accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
Enable autocommit for data import. By default, autocommit is off and data changes are only committed once at the end of each imported file.
Use bulk insert statements for data.
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 each database to be imported if exists before importing anything into it.
Import the files and generate the statements but do not execute them. This is useful for testing input file validity.
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 Type | Definition |
---|---|
definitions (default) | Only import the definitions (metadata) for the objects in the database list |
data | Only import the table data for the tables in the database list |
both | Import 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.
Specify the maximum number of INSERT statements to bulk, by
default 30000. This option is only used with
--bulk-insert
.
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.
Input does not contain column headers. This option only applies to the csv and tab file formats.
Turn off all messages for quiet execution.
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
]
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.
Do not import BLOB
data.
Skip execution of GTID_PURGED
statements.
Do not execute replication commands.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
Specify the table for importing. This option is required while
using --format=raw_csv
.
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.
Display version information and exit.
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.
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.
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.
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.
mysqldiff accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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.
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.
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.
Do not halt at the first difference found. Process all objects to find all differences.
Do not print anything. Return only an exit code of success or failure.
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
]
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
]
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.
The reverse changes are annotated and marked as comments.
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.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
Change the display width of the test report. The default is 75 characters.
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.
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`'.
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.
The user must have SELECT privileges for both objects on both servers as well as SELECT on the mysql database.
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.
mysqldiskusage accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
Display all disk usage. This includes usage for databases, logs, and InnoDB tablespaces.
Display binary log usage.
Include empty databases.
Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid.
Display InnoDB tablespace usage. This includes information about the shared InnoDB tablespace as well as .idb files for InnoDB tables with their own tablespace.
Display general query log, error log, and slow query log usage.
Do not display column headers. This option applies only for grid, csv, and tab output.
Suppress informational messages.
Display relay log usage.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
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.
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
).
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.
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.
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.
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.
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:
Starts the daemon. The
--log
option is
required.
Stops the daemon. If you used the option
--pidfile
, the value
must be the same when starting the daemon.
Restarts the daemon. If you used the option
--pidfile
, the value
must be the same when starting the daemon.
Starts the daemon, but does not detach the process from the
console. The --log
option is required.
mysqlfailover accepts the following command-line options:
Display a help message and exit.
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
]
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.
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.
Name of script to execute after failover or switchover. Script name may include the path.
Name of script to execute before failover or switchover. Script name may include the path.
Name of script to execute on each interval to invoke failover.
Name of script to execute after failover is complete and the utility has refreshed the health report.
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'.
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.
Specify a log file to use for logging messages
Specify maximum age of log entries in days. Entries older than this are purged on startup. Default = 7 days.
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
]
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.
This option was added in MySQL Utilities 1.6.4.
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.
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 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.
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.
On some platforms, this is the same as number of seconds to wait for ping to return.
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.
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).
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.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
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.
Display version information and exit.
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 Option | Parameters Passed to External Script |
---|---|
--exec-before | master host, master port, candidate host, candidate port |
--exec-after | new master host, new master port |
--exec-fail-check | master 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 |
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.
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\
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.
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).
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.
show the program's help page
Display license information and exit.
The base directory for the server installed. Use this or
--server
for the default
mode.
Turn on diagnostic mode to read .frm files byte-by-byte and generate best-effort CREATE statement.
Set the ENGINE= option for all .frm files read.
The port to use for the spawned server in the default mode. Must be a free port. Required for default mode.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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 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.
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).
Turn off all messages for quiet execution except CREATE statements and errors.
Control how much information is displayed. For example,
-v
= verbose, -vv
= more
verbose, -vvv
= debug
Show program's version number and exit
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
.
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.
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.
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.
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.
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.
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.
mysqlgrants accepts the following command-line options:
Display a help message and exit.
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.
Display license information and exit.
--privileges=list of required
privileges
Minimum set of privileges that a user must have for any given object.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
Type of report. Options include users, user_grants and raw.
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.
Display version information and exit.
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.
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.
This utility requires the SELECT privilege on the mysql database.
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.
mysqlindexcheck accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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.
Reports if a table has neither UNIQUE indexes nor a PRIMARY key.
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
]
Display DROP statements for dropping indexes.
Display indexes for each table.
Skip tables that do not exist.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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 index performance statistics.
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.
Display version information and exit.
If --stats
is also
passed in, limit index statistics to the worst
N indexes. The default value of
N is 5, if omitted.
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
).
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)
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.
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.
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.
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 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.
mysqlmetagrep accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
Search the body of stored programs (procedures, functions, triggers, and events). The default is to match only the name.
Sets the client character set. The default is retrieved from
the server variable
character_set_client
.
Look only in databases matching this pattern.
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.
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.
Perform pattern matches using the
REGEXP operator. The
default is to use LIKE for
matching. This affects the
--database
and
--pattern
options.
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
]
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.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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
).
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 |
+------------------------+--------------+--------------+-----------+
The user must have the SELECT privilege on the mysql database.
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.
mysqlprocgrep accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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.
Sets the client character set. The default is retrieved from
the server variable
character_set_client
.
Specify the output display format. Permitted format values are grid (default), csv, tab, and vertical.
Kill the connection for all matching processes (like the
KILL CONNECTION
statement).
Kill the query for all matching processes (like the
KILL QUERY
statement).
Match all processes where the Command field matches the pattern.
Match all processes where the Db field matches the pattern.
Match all processes where the Host field matches the pattern.
Match all processes where the ID field matches the pattern.
Match all processes where the Info field matches the pattern.
Match all processes where the State field matches the pattern.
Match all processes where the User field matches the pattern.
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.
Perform pattern matches using the
REGEXP operator. The
default is to use LIKE for
matching. This affects the --match-xxx
options.
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
]
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.
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;
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
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
).
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
The user must have the SELECT privilege on the mysql database.
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.
mysqlreplicate accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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.
Fail if both servers do not have the same set of storage engines, the same default storage engine, and the same InnoDB storage engine.
The user and password for the replication user, in the format:
user
[:password
]
or login-path
.
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 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.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
The database name to use for testing the replication setup. If this option is not given, no testing is done, only error checking.
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.
Display version information and exit.
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
).
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.
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.
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.
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.
mysqlrplms accepts the following command-line options:
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.
Display the replication health output in either grid (default), tab, csv, or vertical format.
Display a help message and exit.
--interval=seconds
, -i
seconds
Interval in seconds for reporting health. Default = 15 seconds. Minimum is 5 seconds.
Display license information and exit.
Specify a log file to use for logging messages
Specify maximum age of log entries in days. Entries older than this are purged on startup. Default = 7 days.
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 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.
The user and password for the replication user, in the format:
user
[:password
]
or login-path
.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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 replication at the beginning of events logged in the master binary log.
Interval in seconds for switching masters. Default = 60 seconds. Minimum is 30 seconds.
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.
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.
Display version information and exit.
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.
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
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.
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.
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.
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.
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.
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:
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:
mysqlrpladmin accepts the following command-line options:
Display a help message and exit.
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
]
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.
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.
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.
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.
Display the replication health output in either grid (default), tab, csv, or vertical format.
Specify a log file to use for logging messages
Specify maximum age of log entries in days. Entries older than this are purged on startup. Default = 7 days.
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
]
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.
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
]
Turn off health report after switchover or failover.
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.
Turn off all messages for quiet execution.
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).
Used to detect slave delay. The maximum number of seconds behind the master permitted before slave is considered behind the master. Default = 0.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
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.
Display version information and exit.
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
).
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.
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
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:
Is the binary log enabled on the master?
Are there binary logging exceptions (such as
*_do_db
or *_ignore_db
settings)? If so, display them.
Does the replication user exist on the master with the correct privileges?
Are there server_id
conflicts?
Is the slave connected to this master? If not, display the master host and port.
Are there conflicts between the master.info
file on the slave and the values shown in
SHOW SLAVE STATUS on the
master?
Are the InnoDB configurations compatible (plugin vs. native)?
Are the storage engines compatible (have same on slave as master)?
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.
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.
mysqlrplcheck accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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
]
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.
Turn off all messages for quiet execution. Note: Errors and warnings are not suppressed.
Display the values from SHOW SLAVE STATUS on the master.
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 warning messages.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
Change the display width of the test report. The default is 75 characters.
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).
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.
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.
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.
mysqlrplshow accepts the following command-line options:
Display a help message and exit.
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.
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.
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
]
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 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.
Turn off all messages for quiet execution. This option does not suppress errors or warnings.
Traverse the list of slaves to find additional master/slave connections. User this option to map a replication topology.
Display a column list of the topology.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
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).
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)
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.
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.
mysqlrplsync accepts the following command-line options:
--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.
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
]
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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).
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.
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. #
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.
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.
mysqlserverclone accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
Delete the folder specified by --new-data
if
it exists and is not empty.
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
Ignore the maximum path length and the low space checks for
the --new-data
option.
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.
The server_id
value for the new server
instance. The default is 2.
The port number for the new server instance. The default is 3307.
Turn off all messages for quiet execution.
The password for the root
user of the new
server instance.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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.
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.
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.
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.
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.
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.
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.
mysqlserverinfo accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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).
The data directory for the server. This option is required for starting an offline server.
Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid.
Do not display column headers. This option applies only for grid, csv, and tab output.
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.
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
]
Display default settings for mysqld from the local configuration file. It uses my_print_defaults to obtain the options.
Display information about servers running on the local host. The utility examines the host process list to determine which servers are running.
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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 the server in read-only mode if it is offline. With this
option, you must also give the
--basedir
and
--datadir
options.
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.
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.
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).
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.
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.
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.
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.
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.
mysqlslavetrx accepts the following command-line options:
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.
Set of Global Transaction Identifiers (GTID) to skip.
Display a help message and exit.
Display license information and exit.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
Display version information and exit.
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).
The utility requires all target slaves to support global
transaction identifiers (GTIDs) and have
gtid_mode=ON
.
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. #
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.
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.
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.
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.
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.
show program's version number and exit
show the program's help page
Display license information and exit.
control how much information is displayed. For example,
-v
= verbose, -vv
= more
verbose, -vvv
= debug
suppress all informational messages
--execute commands
, -e
commands
Execute commands and exit. Multiple commands are separated with semi-colons.
Some platforms may require double quotes around the command list.
location of utilities
Display width
Using the --execute
option or piping commands to
the console may require quotes or double quotes (for example, on
Windows).
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.
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
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.
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.
mysqluserclone accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
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
]
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.
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 privileges that match base_user@%
as well as base_user@host
.
List all users on the source server. With this option, a destination server need not be specified.
Turn off all messages for quiet execution.
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
]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
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).
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.
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).
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.
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
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.