[04] MySQL_Workbench

mysqldiskusage - Show Database Disk Usage --changes-for=server1 --difftype=sql \\ [FAIL] db1.table1:dbx.table3 # server1 on host1: ... connected. # server2 on host2: ... connected. # Comparing db1.table1 to dbx.table3 # Transformation statments: 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: $ mysqldiff --server1=root@host1 --server2=root@host2 \\ [FAIL] --changes-for=server2 --difftype=sql \\ db1.table1:dbx.table3 # server1 on host1: ... connected. # server2 on host2: ... connected. # Comparing db1.table1 to dbx.table3 # Transformation statments: 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: $ mysqldiff --server1=root@host1 --server2=root@host2 \\ [FAIL] --show-reverse --difftype=sql \\ db1.table1:dbx.table3 # server1 on host1: ... connected. # server2 on host2: ... connected. # Comparing db1.table1 to dbx.table3 # Transformation statments: # --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. mysqldiskusage - Show Database Disk Usage SYNOPSIS mysqldiskusage [options] db ... DESCRIPTION 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 show only database disk usage. 243

mysqldiskusage - 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 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 monitor. • 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 monitor. To turn off the headers for csv or tab display format, specify the --no-headers option. OPTIONS mysqldiskusage accepts the following command-line options: • --help Display a help message and exit. • --all, -a Display all disk usage. This includes usage for databases, logs, and InnoDB tablespaces. • --binlog, -b Display binary log usage. • --empty, -m Include empty databases. 244

mysqldiskusage - Show Database Disk Usage • --format=<format>, -f<format> Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. • --innodb, -i Display InnoDB tablespace usage. This includes information about the shared InnoDB tablespace as well as .idb files for InnoDB tables with their own tablespace. • --logs, -l Display general query log, error log, and slow query log usage. • --no-headers, -h Do not display column headers. This option applies only for csv and tab output. • --quiet, -q Suppress informational messages. • --relaylog, -r Display relay log usage. • --server=<server> Connection information for the server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value. NOTES You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges for all objects accessed during the operation. EXAMPLES To show only the disk space usage for the employees and test databases in grid format (the default), use this command: $ mysqldiskusage --server=root@localhost employees test # Source on localhost: ... connected. # Database totals: +------------+--------------+ | db_name | total | +------------+--------------+ | employees | 205,979,648 | 245

mysqlfailover - Automatic replication health monitoring and failover | 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: $ 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,specificaton /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. mysqlfailover - Automatic replication health monitoring and failover SYNOPSIS mysqlfailover [options] 246

mysqlfailover - Automatic replication health monitoring and failover DESCRIPTION This utility permits users to perform replication health monitoring and automatic failover on a replication topology consisting of a master and its slaves. The utility is designed to run interactively or continuously refreshing the health information at periodic intervals. Its primary mission is to monitor the master for failure and when a failure occurs, execute failover to the best slave available. The utility accepts a list of slaves to be considered 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 the MySQL server online reference manual for more information about setting up replication with GTIDs enabled. 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 will check 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 will execute one of the following actions based on the value of the --failover-mode option. auto Execute automatic failover to the list of candidates first and if no slaves are viable, continue to locate a viable candidate from the list of slaves. If no slaves are found to be a viable candidate, the utility will generate and error and exit. Once a candidate is found, the utility will conduct failover to the best slave. The command will test 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. elect This mode is the same as auto except if no candidates specified in the list of candidate slaves are viable, it does not check the remaining slaves and generates and error and exits. fail This mode produces an error and does not failover when the master is downed. 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*>], The utility permits users to discover slaves connected to the master. In order to use the discover slaves feature, all slaves must use the –report-host and –report-port startup variables to specify the correct hostname and ip port of the slave. If these 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 discover slaves feature ignores any slaves it cannot connect to. The discover slaves feature is run automatically on each interval. 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 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 247

mysqlfailover - Automatic replication health monitoring and failover 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 file name 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 the user with a number of options for displaying additional information. The user can choose to see the replication health report (default), or choose to see the list of GTIDs in use, the UUIDs in use, and if logging is enabled the contents of the log file. 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 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. 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 contents of the GTID variables, @@GLOBAL.GTID_DONE, @@GLOBAL.GTID_LOST, and @@GLOBAL.GTID_OWNED. UUID Display universally unique identifiers (UUIDs) for all servers. Log This option is visible only if the --log option is specified. Show the contents of the log file. This can be helpful to see at a later time when failover occurred and the actions or messages 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 will automatically resize to the terminal window on each interval. The interface will display 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 will also permit 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. OPTIONS mysqlfailover accepts the following command-line options: 248

mysqlfailover - Automatic replication health monitoring and failover • --help Display a help message and exit. • --candidates=<candidate slave connections> Connection information for candidate slave servers for failover in the form: <user>[:<passwd>]@<host>[:<port>][:<socket>]. Valid only with failover command. List multiple slaves in comma- separated list. • --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>]. For example, – discover=joe:secret will use ‘joe’ as the user and ‘secret’ as the password for each discovered slave. • --exec-after=<script> Name of script to execute after failover or switchover. Script name may include the path. • --exec-before=<script> Name of script to execute before failover or switchover. Script name may include the path. • --exec-fail-check=<script> Name of script to execute on each interval to invoke failover. • --exec-post-failover=<script> Name of script to execute after failover is complete and the utility has refreshed the health report. • --failover-mode=<mode>, -f <mode> Action to take when the master fails. ‘auto’ = automatically fail to best slave, ‘elect’ = fail to candidate list or if no candidate meets criteria fail, ‘fail’ = take no action and stop when master fails. Default = ‘auto’. • --force Override the registration check on master for multiple instances of the console monitoring the same master. • --interval=<seconds>, -i <seconds> Interval in seconds for polling the master for failure and reporting health. Default = 15 seconds. Minimum is 5 seconds. • --log=<log_file> Specify a log file to use for logging messages • --log-age=<days> Specify maximum age of log entries in days. Entries older than this will be purged on startup. Default = 7 days. • --master=<connection> Connection information for the master server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. 249

mysqlfailover - Automatic replication health monitoring and failover • --max-position=<position> Used to detect slave delay. The maximum difference between the master’s log position and the slave’s reported read position of the master. A value greater than this means the slave is too far behind the master. Default = 0. • --ping=<number> Number of ping attempts for detecting downed server. Note: on some platforms this is the same as number of seconds to wait for ping to return. • --seconds-behind=<seconds> Used to detect slave delay. The maximum number of seconds behind the master permitted before slave is considered behind the master. Default = 0. • --slaves=<slave connections> Connection information for slave servers in the form: <user>[:<passwd>]@<host>[:<port>][:<socket>]. List multiple slaves in comma-separated list. • --timeout=<seconds> Maximum timeout in seconds to wait for each replication command to complete. For example, timeout for slave waiting to catch up to master. Default = 3. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. NOTES The login user must have the appropriate permissions 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. At startup, the console will attempt to register itself with the master. If another console is already registered, and the failover mode is auto or elect, the console will be blocked from running failover. When a console quits, it deregisters itself from the master. If this process is broken, the user may override the registration check by using the --force option. EXAMPLES To launch the utility, you must specify at a minimum the --master option and either the --discover- slaves-login option or the --slaves option. The option: 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 default interface will display the replication health report like the following. In this example the log file is enabled. A sample startup command is shown below: 250

mysqlfailover - Automatic replication health monitoring and failover $ mysqlfailover --master=root@localhost:3331 --discover-slaves-login=root MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 15:56:03 2012 Master Information Binlog_Do_DB Binlog_Ignore_DB ------------------ Binary Log File Position mysql-bin.000001 571 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 Pressing the ‘q’ key will exit the utility. Pressing the ‘r’ key will refresh the current display. Pressing the ‘h’ key will return to the replication health report. If the user presses the ‘g’ key, the gtid report is shown like the following.: MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 15:59:33 2012 Master Information Binlog_Do_DB Binlog_Ignore_DB ------------------ Binary Log File Position mysql-bin.000001 571 Transactions executed on the server +------------+-------+---------+-------------------------------------------+ | host | port | role | uuid | +------------+-------+---------+-------------------------------------------+ | localhost | 3331 | MASTER | 55C65A00-71FD-11E1-9F80-AC64EF85C961:1-2 | | localhost | 3332 | SLAVE | 55C65A00-71FD-11E1-9F80-AC64EF85C961:1-2 | | localhost | 3332 | SLAVE | 5DD30888-71FD-11E1-9F80-DC242138B7EC:1 | | localhost | 3333 | SLAVE | 55C65A00-71FD-11E1-9F80-AC64EF85C961:1-2 | +------------+-------+---------+-------------------------------------------+ Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll If the user continues to press the ‘g’ key, the display will cycle 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, if the user presses the down arrow, the list will scroll down. If the user presses the ‘u’ key, the list of UUIDs used in the topology are shown.: MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 16:02:34 2012 Master Information Binlog_Do_DB Binlog_Ignore_DB ------------------ Binary Log File Position mysql-bin.000001 571 UUIDs +------------+-------+---------+---------------------------------------+ | host | port | role | uuid | +------------+-------+---------+---------------------------------------+ | localhost | 3331 | MASTER | 55c65a00-71fd-11e1-9f80-ac64ef85c961 | | localhost | 3332 | SLAVE | 5dd30888-71fd-11e1-9f80-dc242138b7ec | 251

mysqlfailover - Automatic replication health monitoring and failover | 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 will engage automatically and the user will see 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 Binlog_Do_DB Binlog_Ignore_DB ------------------ Binary Log File Position mysql-bin.000001 1117 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 If the user presses the ‘l’ key and the --log option was specified, the interface will show the entries in the log file. Note: example truncated for space allowance.: MySQL Replication Monitor and Failover Utility Failover Mode = auto Next Interval = Mon Mar 19 16:06:13 2012 Master Information Binlog_Do_DB Binlog_Ignore_DB ------------------ Binary Log File Position mysql-bin.000001 1117 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 ... | +-------------------------+----------------------------------------- ... --+ 252

mysqlindexcheck - Identify Potentially Redundant Table Indexes Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll mysqlindexcheck - Identify Potentially Redundant Table Indexes SYNOPSIS mysqlindexcheck [options] db[:table] ... DESCRIPTION This utility reads the indexes for one or more tables and identifies duplicate and potentially redundant indexes. To check all tables in a database, specify only 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 the first n columns in idx_b also appear in idx_a. Order and uniqueness count. • HASH idx_a and idx_b are duplicates if and only if they contain the same columns in the same order. Uniqueness counts. • 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 counts. To see DROP statements to 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 nonprimary 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 monitor. • csv Display output in comma-separated values format. 253

mysqlindexcheck - Identify Potentially Redundant Table Indexes • 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 monitor. Note: The --best and --worst lists cannot be printed as SQL statements. OPTIONS mysqlindexcheck accepts the following command-line options: • --help Display a help message and exit. • --best[=<N>] If --stats is given, limit index statistics to the best N indexes. The default value of N is 5 if omitted. • --format=<index_format>, -f<index_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. • --server=<source> Connection information for the server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --show-drops, -d Display DROP statements for dropping indexes. • --show-indexes, -i Display indexes for each table. • --skip, -s Skip tables that do not exist. • --stats Show index performance statistics. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. 254

mysqlmetagrep - Search Database Object Definitions • --worst[=<N>] If --stats is given, limit index statistics to the worst N indexes. The default value of N is 5 if omitted. NOTES You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to read all objects accessed during the operation. For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value. EXAMPLES To check all tables in the employees database on the local server to see the possible redundant and duplicate indexes, use this command: $ 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) mysqlmetagrep - Search Database Object Definitions SYNOPSIS mysqlmetagrep [options] [pattern | server] ... DESCRIPTION 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 nonoption argument is taken to be the pattern unless the --pattern option is given. If the --pattern option is given, all nonoption 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 executes it in turn before collecting the result and printing it as a table. Use the --sql option to have the utility display the statement rather 255

mysqlmetagrep - Search Database Object Definitions than execute it. This can be useful if you want to feed the output of the statement to another application such as the mysql monitor. The MySQL server supports two forms of patterns when matching strings: SQL Simple Patterns (used with the LIKE operator) and POSIX Regular Expressions (used with the REGEXP operator). By default, the utility uses the LIKE operator to match the name (and optionally, the body) of objects. To use the REGEXP operator instead, use the --regexp option. Note that since 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 monitor. • 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 monitor. SQL Simple Patterns The simple patterns defined by the SQL standard consist of a string of characters with two characters that have special meaning: % (percent) matches zero or more characters and _ (underscore) matches exactly one character. For example: • 'mats%' Match any string that starts with ‘mats’. • '%kindahl%' Match any string containing the word ‘kindahl’. • '%_' Match any string consisting of one or more characters. POSIX Regular Expressions POSIX regular expressions are more powerful than the simple patterns defined in the SQL standard. A regular expression is a string of characters, optionally containing characters with special meaning: •. Match any character. 256

mysqlmetagrep - Search Database Object Definitions •^ 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 excepta, 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. This is but a brief set of examples of regular expressions. The full syntax is described in the MySQL manual, but can often be found in regex(7). OPTIONS mysqlmetagrep accepts the following command-line options: • --help Display a help message and exit. • --body, -b 257

mysqlmetagrep - Search Database Object Definitions Search the body of stored programs (procedures, functions, triggers, and events). The default is to match only the name. • --database=<pattern> Look only in databases matching this pattern. • --format=<format>, -f<format> Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. • --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 procedure, function, event, trigger, table, and database. The default is to search in objects of all types. • --pattern=<pattern>, -e=<pattern> The pattern to use when matching. This is required when the first nonoption argument looks like a connection specification rather than a pattern. If the --pattern option is given, the first nonoption argument is treated as a connection specifier, not as a pattern. • --regexp, --basic-regexp, -G Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching. This affects the --database and --pattern options. • --server=<source> Connection information for a server to search in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. Use this option multiple times to search multiple servers. • --sql, --print-sql, -p Print rather than executing the SQL code that would be executed to find all matching objects. This can be useful to save the statement for later execution or to use it as input for other programs. • --version Display version information and exit. NOTES For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value. EXAMPLES Find all objects with a name that matches the pattern 't_' (the letter t followed by any single character): $ mysqlmetagrep --pattern=\"t_\" --server=mats@localhost +------------------------+--------------+--------------+-----------+ 258

mysqlprocgrep - Search Server Process Lists | Connection | Object Type | Object Name | Database | +------------------------+--------------+--------------+-----------+ | mats:*@localhost:3306 | TABLE | t1 | test | | mats:*@localhost:3306 | TABLE | t2 | test | | mats:*@localhost:3306 | TABLE | t3 | test | +------------------------+--------------+--------------+-----------+ To find all object that contain 't2' in the name or the body (for routines, triggers, and events): $ mysqlmetagrep -b --pattern=\"%t2%\" --server=mats@localhost:3306 +------------------------+--------------+--------------+-----------+ | Connection | Object Type | Object Name | Database | +------------------------+--------------+--------------+-----------+ | root:*@localhost:3306 | TRIGGER | tr_foo | test | | root:*@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: $ mysqlmetagrep -Gb --pattern=\"t2\" --server=mats@localhost +------------------------+--------------+--------------+-----------+ | Connection | Object Type | Object Name | Database | +------------------------+--------------+--------------+-----------+ | root:*@localhost:3306 | TRIGGER | tr_foo | test | | root:*@localhost:3306 | TABLE | t2 | test | +------------------------+--------------+--------------+-----------+ mysqlprocgrep - Search Server Process Lists SYNOPSIS mysqlprocgrep [options] DESCRIPTION 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 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 monitor. 259

mysqlprocgrep - Search Server Process Lists • 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 monitor. Options mysqlprocgrep accepts the following command-line options: • --help Display a help message and exit. • --age=<time> Select only processes that have been in the current state more than a given time. The time value can be specified in two formats: either using the hh:mm:ss format, with hours and minutes optional, or as a sequence of numbers with a suffix giving the period size. The permitted suffixes are s (second), m (minute), h (hour), d (day), and w (week). For example, 4h15m mean 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. • --format=<format>, -f<format> Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. • --kill-connection Kill the connection for all matching processes (like the KILL CONNECTION statement). • --kill-query Kill the query for all matching processes (like the KILL QUERY statement). • --match-command=<pattern> Match all processes where the Command field matches the pattern. • --match-db=<pattern> Match all processes where the Db field matches the pattern. • --match-host=<pattern> Match all processes where the Host field matches the pattern. • --match-info=<pattern> 260

mysqlprocgrep - Search Server Process Lists Match all processes where the Info field matches the pattern. • --match-state=<pattern> Match all processes where the State field matches the pattern. • --match-user=<pattern> Match all processes where the User field matches the pattern. • --print Print information about the matching processes. This is the default if no --kill-connection or -- kill-query option is given. If a kill option is given, --print prints information about the processes before killing them. • --regexp, --basic-regexp, -G Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching. This affects the --match-xxx options. • --server=<source> Connection information for a server to search in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. Use this option multiple times to search multiple servers. • --sql, --print-sql, -Q Instead of displaying the selected processes, emit the SELECT statement that retrieves information about them. If the --kill-connection or --kill-query option is given, the utility generates a stored procedure named kill_processes() for killing the queries rather than a SELECT statement. • --sql-body Like --sql, but produces the output as the body of a stored procedure without the CREATE PROCEDURE part of the definition. This could be used, for example, to generate an event for the server Event Manager. When used with a kill option, code for killing the matching queries is generated. Note that it is not possible to execute the emitted code unless it is put in a stored routine, event, or trigger. For example, the following code could be generated to kill all idle connections for user www-data: $ 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; 261

mysqlreplicate - Set Up and Start Replication Between Two Servers KILL CONNECTION id; END LOOP kill_loop; END; CLOSE kill_cursor; • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. NOTES For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value. EXAMPLES For each example, assume that the root user on localhost has sufficient privileges to kill queries and connections. Kill all queries created by user mats that are younger than 1 minute: mysqlprocgrep --server=root@localhost \\ --match-user=mats --age=-1m --kill-query Kill all connections that have been idle for more than 1 hour: mysqlprocgrep --server=root@localhost \\ --match-command=sleep --age=1h --kill-connection mysqlreplicate - Set Up and Start Replication Between Two Servers SYNOPSIS mysqlreplicate [options] DESCRIPTION This utility permits an administrator to 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. 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. 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. 262

mysqlreplicate - Set Up and Start Replication Between Two Servers • Start from the current position (default) Start replication from the current master binary log file and position. The utility uses the SHOW MASTER STATUS statement to retrieve this information. • Start from the beginning Start replication from the first event recorded in the master binary log. To do this, use the --start- from-beginning option. • Start from a binary log file Start replication from the first event in a specific master binary log file. To do this, use the --master- log-file option. • Start from a specific event Start replication from specific event coordinates (specific binary log file and position). To do this, use the --master-log-file and --master-log-pos options. OPTIONS mysqlreplicate accepts the following command-line options: • --help Display a help message and exit. • --master=<master> Connection information for the master server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --master-log-file=<master_log_file> Begin replication from the beginning of this master log file. • --master-log-pos=<master_log_pos> Begin replication from this position in the master log file. This option is not valid unless --master-log- file is given. • --pedantic, -p Fail if both servers do not have the same set of storage engines, the same default storage engine, and the same InnoDB storage engine. • --rpl-user=<replication_user> The user and password for the replication user, in name:passwd format. The default is rpl:rpl. • --slave=<slave> Connection information for the slave server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --start-from-beginning, -b Start replication at the beginning of events logged in the master binary log. This option is not valid unless both --master-log-file and --master-log-pos are given. 263

mysqlreplicate - Set Up and Start Replication Between Two Servers • --test-db=<test_database> The database name to use for testing the replication setup. If this option is not given, no testing is done, only error checking. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. NOTES The login user for the master server must have the appropriate permissions to grant access to all databases and the ability to create a user account. 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. EXAMPLES To set up replication between two MySQL instances running on different ports of the same host using the default settings, use this command: $ 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: $ 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): 264

mysqlrpladmin - Administration utility for MySQL replication $ 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: $ 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: $ 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): $ mysqlreplicate --master=root@localhost:3306 \\ --slave=root@localhost:3307 --rpl-user=rpl:rpl \\ --master-log-file=my_log.000001 --master-log-pos=96 # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. RECOMMENDATIONS You should set read_only=1 in the my.cnf file for the slave to ensure that no accidental data changes, such as INSERT, DELETE, UPDATE, and so forth, are permitted on the slave other than those produced by events read from the master. Use the --pedantic and -vv options for setting up replication on production servers to avoid possible problems with differing storage engines. mysqlrpladmin - Administration utility for MySQL replication SYNOPSIS mysqlrpladmin [options] DESCRIPTION This utility permits users to perform administrative actions on a replication topology consisting of a 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. 265

mysqlrpladmin - Administration utility for MySQL replication 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. The utility accepts a list of slaves to be considered the candidate slave. If no slave is found to meet the requirements, the operation will search the list of known slaves. 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 best slave election and report best slave to use in the event a switchover or failover is required. Best slave election is simply the first slave to meet the prerequisites. GTIDs are supported in version 5.6.5 and higher. failover This command is available to only those servers supporting GTIDs. Conduct failover to the best slave. The command will test 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. gtid This command is available to only those servers supporting GTIDs. It displays the contents of the GTID variables, @@GLOBAL.GTID_DONE, @@GLOBAL.GTID_LOST, and @@GLOBAL.GTID_OWNED. The command also displays universally unique identifiers (UUIDs) for all servers. 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. 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. start Execute the START SLAVE command on all slaves. stop Execute the STOP SLAVE command on all slaves. 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. Detection of a downed master is performed as follows. If the connection to the master is lost, wait -- timeout 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.: 266

mysqlrpladmin - Administration utility for MySQL replication <*user*>[:<*passwd*>]@<*host*>[:<*port*>][:<*socket*>], The utility permits users to discover slaves connected to the master. In order to use the discover slaves feature, all slaves must use the –report-host and –report-port startup variables to specify the correct hostname and ip port of the slave. If these 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 discover slaves feature ignores any slaves it cannot connect to. 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 file name 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 elect, failover, start, stop, and reset commands require 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 to the master but are not listed in the --slaves option. The options required for the 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 to the master but are not listed in the --slaves option. Use the --verbose option to see additional information in the health report and additional messages during switchover or failover. 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. OPTIONS mysqlrpladmin accepts the following command-line options: • --help Display a help message and exit. • --candidates=<candidate slave connections> 267

mysqlrpladmin - Administration utility for MySQL replication Connection information for candidate slave servers for failover in the form: <user>[:<passwd>]@<host>[:<port>][:<socket>]. Valid only with failover command. List multiple slaves in comma- separated list. • --demote-master Make master a slave after switchover. • --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>]. For example, – discover=joe:secret will use ‘joe’ as the user and ‘secret’ as the password for each discovered slave. • --exec-after=<script> Name of script to execute after failover or switchover. Script name may include the path. • --exec-before=<script> Name of script to execute before failover or switchover. Script name may include the path. • --format=<format>, -f <format> Display the replication health output in either grid (default), tab, csv, or vertical format. • --log=<log_file> Specify a log file to use for logging messages • --log-age=<days> Specify maximum age of log entries in days. Entries older than this will be purged on startup. Default = 7 days. • --master=<connection> Connection information for the master server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --max-position=<position> Used to detect slave delay. The maximum difference between the master’s log position and the slave’s reported read position of the master. A value greater than this means the slave is too far behind the master. Default = 0. • --new-master=<connection> Connection information for the slave to be used to replace the master for switchover in the form: <user>[:<passwd>]@<host>[:<port>][:<socket>]. Valid only with switchover command. • --no-health Turn off health report after switchover or failover. • --ping=<number> Number of ping attempts for detecting downed server. Note: on some platforms this is the same as number of seconds to wait for ping to return. 268

mysqlrpladmin - Administration utility for MySQL replication • --quiet, -q Turn off all messages for quiet execution. • --seconds-behind=<seconds> Used to detect slave delay. The maximum number of seconds behind the master permitted before slave is considered behind the master. Default = 0. • --slaves=<slave connections> Connection information for slave servers in the form: <user>[:<passwd>]@<host>[:<port>][:<socket>]. List multiple slaves in comma-separated list. • --timeout=<seconds> Maximum timeout in seconds to wait for each replication command to complete. For example, timeout for slave waiting to catch up to master. Default = 3. Also used to check down status of master. Failover will wait timeout seconds to check master response. If no response, failover event occurs. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. NOTES The login user must have the appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW VARIABLES on the appropriate servers as well as grant the REPLICATE SLAVE privilege. The utility checks permissions for the master, slaves, and candidates at startup. The --force option cannot be used with the failover command. EXAMPLES To perform best slave election for a topology with GTID_MODE=ON (server version 5.6.5 or higher) where all slaves are specified with the --slaves1 option, run the following command.: $ 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.: $ 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.: $ mysqlrpladmin \\ 269

mysqlrpladmin - Administration utility for MySQL replication --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.: $ 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.: $ 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 270

mysqlrpladmin - Administration utility for MySQL replication 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.: $ 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 will contain entries like the following: 271

mysqlrpladmin - Administration utility for MySQL replication 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.: $ 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 will display 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 | +------------+-------+---------+--------+------------+---------+ To use the discover slaves feature, you can omit the --slaves option 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. he option: 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.: $ mysqlrpladmin --master=root@localhost:3332 \\ --discover-slaves-login=root health # Discovering slaves for master at localhost:3332 # Getting health for master: localhost:3332. # # 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 | +------------+-------+---------+--------+------------+---------+ 272

mysqlrplcheck - Check Replication Prerequisities # ...done. mysqlrplcheck - Check Replication Prerequisities SYNOPSIS mysqlrplcheck [options] DESCRIPTION This utility checks the prerequisites for replication between a master and a slave. These checks (called tests) are designed to ensure a healthy replication setup. The utility performs the following tests: 1. Is the binary log enabled on the master? 2. Are there binary logging exceptions (such as *_do_db or *_ignore_db settings)? If so, display them. 3. Does the replication user exist on the master with the correct privileges? 4. Are there server_id conflicts? 5. Is the slave connected to this master? If not, display the master host and port. 6. Are there conflicts between the master.info file on the slave and the values shown in SHOW SLAVE STATUS on the master? 7. Are the InnoDB configurations compatible (plugin vs. native)? 8. Are the storage engines compatible (have same on slave as master)? 9. Are the lower_case_tables_names settings compatible? Warn if there are settings for lowercase/ uppercase table names that can cause problems. See Bug #59240. 10. Is the slave behind the master? The utility runs each test in turn unless there is a fatal error preventing further testing, such as a loss of connection to the servers. Each test can complete with one of the following states: pass (the prerequisites are met), fail (the prerequisites were met but one or more errors occurred or there are exceptions to consider), or warn (the test found some unusual settings that should be examined further but may not be in error). Use the --verbose option to see additional information such as server IDs, lower_case_table_name settings, and the contents of the master information file on the slave. To see the values from the SHOW SLAVE STATUS statement, use the --show-slave-status option. OPTIONS mysqlrplcheck accepts the following command-line options: • --help Display a help message and exit. • --master=<source> Connection information for the master server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. 273

mysqlrplcheck - Check Replication Prerequisities • --master-info-file=<file> The name of the master information file on the slave. The default is master.info read from the data directory. Note: This option requires that you run the utility on the slave and that you have appropriate read access for the file. • --quiet, -q Turn off all messages for quiet execution. Note: Errors and warnings are not suppressed. • --show-slave-status, -s Display the values from SHOW SLAVE STATUS on the master. • --slave=<source> Connection information for the slave server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --suppress Suppress warning messages. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. • --width=<number> Change the display width of the test report. The default is 75 characters. NOTES The login user must have the appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW VARIABLES on the appropriate servers. EXAMPLES To check the prerequisites of a master and slave that currently are actively performing replication, use the following command: $ 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. 274

mysqlrplcheck - Check Replication Prerequisities 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: $ 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 : 275

mysqlrplshow - Show Slaves for Master Server 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. mysqlrplshow - Show Slaves for Master Server SYNOPSIS mysqlrplshow [options] DESCRIPTION This utility shows the replication slaves for a master. It prints a graph of the master and its slaves labeling each with the host name and port number. 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. 276

mysqlrplshow - Show Slaves for Master Server 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 monitor. • 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 monitor. 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 should have been started with the --report-host and --report-port options set to their actual host name and port number or the utility may not be able to connect to the slaves to determine their own slaves. OPTIONS mysqlrplshow accepts the following command-line options: • --help Display a help message and exit. • --format=<format>, -f<format> Specify the display format for column list output. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option applies only if --show-list is given. • --master=<source> Connection information for the master server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --max-depth=<N> The maximum recursion depth. This option is valid only if --recurse is given. • --num-retries=<num_retries>, -n<num_retries> The number of retries permitted for failed slave login attempts. This option is valid only if --prompt is given. 277

mysqlrplshow - Show Slaves for Master Server • --prompt, -p Prompt for the slave user and password if different from the master user and password. If you give this option, the utility sets --num-retries to 1 if that option is not set explicitly. This ensures at least one attempt to retry and prompt for the user name and password should a connection fail. • --quiet, -q Turn off all messages for quiet execution. This option does not suppress errors or warnings. • --recurse, -r Traverse the list of slaves to find additional master/slave connections. User this option to map a replication topology. • --show-list, -l Display a column list of the topology. • --version Display version information and exit. NOTES The login user must have the REPLICATE SLAVE and REPLICATE CLIENT privileges to successfully execute this utility. Specifically, the login user must have appropriate permissions to execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW SLAVE HOSTS. For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value. EXAMPLES To show the slaves for a master running on port 3311 on the local host, use the following command: $ mysqlrplshow --master=root@localhost:3311 # 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 the full replication topology of a master running on the local host, use the following command: $ mysqlrplshow --master=root@localhost:3311 --recurse # master on localhost: ... connected. # Finding slaves for master: localhost:3311 # Replication Topology Graph localhost:3311 (MASTER) | +--- localhost:3310 - (SLAVE) 278

mysqlserverclone - Clone Existing Server to Create New Server | +--- 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: $ mysqlrplshow --recurse --prompt --num-retries=1 \\ --master=root@localhost:3331 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) mysqlserverclone - Clone Existing Server to Create New Server SYNOPSIS mysqlserverclone [options] DESCRIPTION This utility permits an administrator to clone an existing MySQL server instance to start a new server instance on the same host. The utility creates a new datadir (--new-data), and starts the server with a socket file. You can optionally add a password for the login user account on the new instance. OPTIONS mysqlserverclone accepts the following command-line options: 279

mysqlserverclone - Clone Existing Server to Create New Server • --help Display a help message and exit. • --mysqld=<options> Additional options for mysqld. To specify multiple options, separate them by spaces. Use appropriate quoting as necessary. For example, to specify --log-bin=binlog and --general-log- file=\"mylogfile\", use: --mysqld=\"--log-bin=binlog --general-log-file='my log file'\" • --new-data=<path_to_new_datadir> The full path name of the location of the data directory for the new server instance. If the directory does not exist, the utility will create it. • --new-id=<server_id> The server_id value for the new server instance. The default is 2. • --new-port=<port> The port number for the new server instance. The default is 3307. • --quiet, -q Turn off all messages for quiet execution. • --root-password=<password> The password for the root user of the new server instance. • --server=<source> Connection information for the server to be cloned in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. • --write-command=<file_name>, -w<file_name> Path name of file in which to write the command used to launch the new server instance. EXAMPLES The following command demonstrates how to create a new instance of a running server, set the root user password and enable binary logging: $ mkdir /source/test123 $ mysqlserverclone --server=root:pass@localhost \\ --new-data=/Users/cbell/source/test123 --new-port=3310 \\ --root-password=pass --mysqld=--log-bin=mysql-bin 280

mysqlserverinfo - Display Common Diagnostic Information from a Server # 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. mysqlserverinfo - Display Common Diagnostic Information from a Server SYNOPSIS mysqlserverinfo [options] DESCRIPTION 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 (file name and position) • Current relay log coordinates (file name 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 monitor. • 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 monitor. 281

mysqlserverinfo - Display Common Diagnostic Information from a Server To turn off the headers for csv or tab display format, specify the --no-headers option. To see the common default settings for the local server’s configuration file, use the --show-defaults option. This option reads the configuration file on the machine where the utility is run, not the machine for the host that the --server option specifies. To run the utility against several servers, specify the --server option multiple times. In this case, the utility attempts to connect to each server and read the information. To see the MySQL servers running on the local machine, use the --show-servers option. This shows all the servers with their process ID and data directory. On Windows, the utility shows only the process ID and port. OPTIONS mysqlserverinfo accepts the following command-line options: • --help Display a help message and exit. • --basedir=<basedir> The base directory for the server. This option is required for starting an offline server. • --datadir=<datadir> The data directory for the server. This option is required for starting an offline server. • --format=<format>, -f<format> Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. • --no-headers, -h Do not display column headers. This option applies only for csv and tab output. • --port-range=<start:end> The port range to check for finding running servers. This option applies only to Windows and is ignored unless --show-servers is given. The default range is 3306:3333. • --server=<server> Connection information for a server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. Use this option multiple times to see information for multiple servers. • --show-defaults, -d Display default settings for mysqld from the local configuration file. It uses my_print_defaults to obtain the options. • --show-servers Display information about servers running on the local host. The utility examines the host process list to determine which servers are running. 282

mysqluserclone - Clone Existing User to Create New User • --start, -s Start the server in read-only mode if it is offline. With this option, you must also give the --basedir and --datadir options. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. For the --format option, the permitted values are not case sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value. EXAMPLES To display the server information for the local server and the settings for mysqld in the configuration file with the output in a vertical list, use this command: $ 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. mysqluserclone - Clone Existing User to Create New User SYNOPSIS mysqluserclone [options] base_user new_user[:password][@host_name] ... DESCRIPTION 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. 283

mysqluserclone - Clone Existing User to Create New User 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 monitor. • 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 monitor. OPTIONS mysqluserclone accepts the following command-line options: • --help Display a help message and exit. • --destination=<destination> Connection information for the destination server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --dump, -d Display the GRANT statements to create the account rather than executing them. In this case, the utility does not connect to the destination server and no --destination option is needed. • --format=<list_format>, -f<list_format> Specify the user display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option is valid only if --list is given. • --force Drop the new user account if it exists before creating the new account. Without this option, it is an error to try to create an account that already exists. • --include-global-privileges Include privileges that match base_user@% as well as base_user@host. • --list List all users on the source server. With this option, a destination server need not be specified. • --quiet, -q Turn off all messages for quiet execution. 284

Parsers • --source=<source> Connection information for the source server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. • --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. • --version Display version information and exit. NOTES You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects in the operation. The account used to connect to 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. 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. EXAMPLES To clone joe as sam and sally with passwords and logging in as root on the local machine, use this command: $ 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: $ 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 Parsers mysql.utilities.parser — Parse MySQL Log Files This module provides classes for parsing MySQL log files. Currently, Slow Query Log and General Query Log are supported. 285

mysql.utilities.parser — Parse MySQL Log Files Classes class mysql.utilities.parser.GeneralQueryLog(stream) This class parses the MySQL General Query Log. Instances are iterable, but the class does not provide multiple independent iterators. For example, to read the log and print the entries: >>> general_log = open(\"/var/lib/mysql/mysql.log\") >>> log = GeneralQueryLog(general_log) >>> for entry in log: ... print entry Parameters: • stream (file type) – a valid file type; for example, the result of the built-in Python function open() version Returns: Version of the MySQL server that produced the log Return type: tuple program Returns: Full path of the MySQL server executable Return type: str port Returns: TCP/IP port on which the MySQL server was Return type: listening int socket Returns: Full path of the MySQL server Unix socket Return type: str start_datetime Returns: Date and time of the first read log entry Return type: datetime.datetime lastseen_datetime Returns: Date and time of the last read log entry Return type: datetime.datetime class mysql.utilities.parser.SlowQueryLog(stream) This class parses the MySQL Slow Query Log. Instances are iterable, but the class does not provide multiple independent iterators. 286

mysql.utilities.parser — Parse MySQL Log Files For example, to read the log and print the entries: >>> slow_log = open(\"/var/lib/mysql/mysql-slow.log\") >>> log = SlowQueryLog(slow_log) >>> for entry in log: ... print entry Parameters: • stream (file type) – a valid file type; for example, the result of the built-in Python function open() version Returns: Version of the MySQL server that produced the log Return type: tuple program Returns: Full path of the MySQL server executable Return type: str port Returns: TCP/IP port on which the MySQL server was Return type: listening int socket Returns: Full path of the MySQL server Unix socket Return type: str start_datetime Returns: Date and time of the first read log entry Return type: datetime.datetime lastseen_datetime Returns: Date and time of the last read log entry Return type: datetime.datetime 287

288

Appendix A. Third Party Licenses Table of Contents .NET Flat TabControl License ......................................................................................................... 290 ANTLR 3.4 License ........................................................................................................................ 290 Bitstream Vera License .................................................................................................................. 291 Boost Library License ..................................................................................................................... 292 Cairo License ................................................................................................................................. 292 CTemplate (Google Template System) License ............................................................................... 293 cURL (libcurl) License ............................................................................................................... 293 DockPanel Suite License ................................................................................................................ 294 Dojo Toolkit v1.7.0b1 License ......................................................................................................... 294 GLib License (for MySQL Workbench) ............................................................................................ 295 Glitz License .................................................................................................................................. 295 GNU Lesser General Public License Version 2.1, February 1999 ...................................................... 296 HtmlRenderer (System.Drawing.Html) ............................................................................................. 304 Libiconv License ............................................................................................................................ 304 Libintl License ................................................................................................................................ 305 Libxml2 License ............................................................................................................................. 305 Libzip License ................................................................................................................................ 306 Lua (liblua) License ........................................................................................................................ 306 Paramiko License .......................................................................................................................... 307 PCRE License ............................................................................................................................... 307 Pixman License ............................................................................................................................. 308 PyCrypto License ........................................................................................................................... 310 Python License .............................................................................................................................. 310 Scintilla License ............................................................................................................................. 320 ScintillaNET License ...................................................................................................................... 322 TinyXML License ........................................................................................................................... 322 TreeViewAdv for .NET License ....................................................................................................... 323 VSQLite++ License ........................................................................................................................ 323 zlib License ................................................................................................................................ 324 Use of any of this software is governed by the terms of the licenses that follow. MySQL Workbench • .NET Flat TabControl License • ANTLR 3.4 License • Bitstream Vera License • Boost Library License • Cairo License • CTemplate (Google Template System) License • cURL (libcurl) License • DockPanel Suite License 289

.NET Flat TabControl License • Dojo Toolkit v1.7.0b1 License • GLib License (for MySQL Workbench) • Glitz License • GNU Lesser General Public License Version 2.1, February 1999 • HtmlRenderer (System.Drawing.Html) • Libiconv License • Libintl License • Libxml2 License • Libzip License • Lua (liblua) License • Paramiko License • PCRE License • Pixman License • PyCrypto License • Python License • Scintilla License • ScintillaNET License • TinyXML License • TreeViewAdv for .NET License • VSQLite++ License • zlib License .NET Flat TabControl License The following software may be included in this product: • .NET Flat TabControl Use of any of this software is governed by the terms of the license below: It is free. Public domain! Oscar Londono ANTLR 3.4 License The following software may be included in this product: ANTLR 3.4 This product was build using ANTLR, which was provided to Oracle under the 290

Bitstream Vera License following terms: Copyright (c) 2010 Terence Parr All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of the author nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS \"AS IS\" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. Bitstream Vera License The following software may be included in this product: Bitstream Vera Copyright (c) 2003 by Bitstream, Inc. All Rights Reserved. Bitstream Vera is a trademark of Bitstream, Inc. Permission is hereby granted, free of charge, to any person obtaining a copy of the fonts accompanying this license (\"Fonts\") and associated documentation files (the \"Font Software\"), to reproduce and distribute the Font Software, including without limitation the rights to use, copy, merge, publish, distribute, and/or sell copies of the Font Software, and to permit persons to whom the Font Software is furnished to do so, subject to the following conditions: The above copyright and trademark notices and this permission notice shall be included in all copies of one or more of the Font Software typefaces. The Font Software may be modified, altered, or added to, and in particular the designs of glyphs or characters in the Fonts may be modified and additional glyphs or characters may be added to the Fonts, only if the fonts are renamed to names not containing either the words \"Bitstream\" or the word \"Vera\". This License becomes null and void to the extent applicable to Fonts or Font Software that has been modified and is distributed under the \"Bitstream Vera\" names. The Font Software may be sold as part of a larger 291

Boost Library License software package but no copy of one or more of the Font Software typefaces may be sold by itself. THE FONT SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO ANY WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF COPYRIGHT, PATENT, TRADEMARK, OR OTHER RIGHT. IN NO EVENT SHALL BITSTREAM OR THE GNOME FOUNDATION BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, INCLUDING ANY GENERAL, SPECIAL, INDIRECT, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF THE USE OR INABILITY TO USE THE FONT SOFTWARE OR FROM OTHER DEALINGS IN THE FONT SOFTWARE. Except as contained in this notice, the names of Gnome, the Gnome Foundation, and Bitstream Inc., shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Font Software without prior written authorization from the Gnome Foundation or Bitstream Inc., respectively. For further information, contact: fonts at gnome dot org. Boost Library License The following software may be included in this product: Boost C++ Libraries Use of any of this software is governed by the terms of the license below: Boost Software License - Version 1.0 - August 17th, 2003 Permission is hereby granted, free of charge, to any person or organization obtaining a copy of the software and accompanying documentation covered by this license (the \"Software\") to use, reproduce, display, distribute, execute, and transmit the Software, and to prepare derivative works of the Software, and to permit third-parties to whom the Software is furnished to do so, all subject to the following: The copyright notices in the Software and this entire statement, including the above license grant, this restriction and the following disclaimer, must be included in all copies of the Software, in whole or in part, and all derivative works of the Software, unless such copies or derivative works are solely in the form of machine-executable object code generated by a source language processor. THE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. Cairo License The following software may be included in this product: Cairo 292